Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Backup and Restore a MySQL Database or Table

DZone's Guide to

Backup and Restore a MySQL Database or Table

Here's a quick tutorial to get your MySQL data backed up and restored. Better safe than sorry.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

In this tutorial, we will show you how to use backup and restore a MySQL database or tables.

Backup Examples

1.1 Quick reference – mysqldump


#Backup a database
$ mysqldump -u [username] -p [dbname] > filename.sql

#Backup a table
$ mysqldump -u [username] -p [dbname] [table] > filename.sql

#Backup multiple tables
$ mysqldump -u [username] -p [dbname] [table1] [table2] > filename.sql

#Backup a database and gzip it
$ mysqldump -u [username] -p [dbname] | gzip > filename.sql.gz


1.2 Backup a database ‘wrdp1’ to a SQL file ‘db.20160726.sql’.


$ mysqldump -u mkyong -p wrdp1 > db.20160726.sql
Enter password:


1.3 Backup and gzip together.


$ mysqldump -u mkyong -p wrdp1 | gzip > db.20160726.sql.gz
Enter password:


1.4 Back a table ‘wp_postmeta’ to a SQL file ‘wp_postmeta.sql’


$ mysqldump -u mkyong -p wrdp1 wp_postmeta > wp_postmeta.sql
Enter password:

# backup + gzip
$ mysqldump -u mkyong -p wrdp1 wp_postmeta | gzip > wp_postmeta.sql

#multiple tables - wp_postmeta and wp_posts
$ mysqldump -u mkyong -p wrdp1 wp_postmeta wp_posts > tables.sql

Restore Examples

2.1 Quick reference – mysql


#Restore, uses 'mysql' command for backup database and table  
$ mysql -u [username] -p [dbname] < filename.sql

#Restore back a gzip SQL file.
$ gunzip -c filename.sql.gz | mysql -u [username] -p [dbname]


Note: If you are restoring a SQL file, please use command mysql NOT mysqldump

2.2 Restore back a SQL file 'db.20160726.sql' to a database 'wrdp1'.


$ mysql -u mkyong -p wrdp1 < db.20160726.sql
Enter password:

#table, same command
$ mysql -u mkyong -p wrdp1 < wp_postmeta.sql


2.3 Restore back a gzip SQL file 'db.20160726.sql.gz' to a database 'wrdp1'.


$ gunzip -c db.20160726.sql.gz | mysql -u mkyong -p wrdp1
Enter password:


Case Study

Here is my MySQL backup and restore steps, just for self-reference.

  1. SSH into a server 'myserver1'.
  2. Backup and gzip a database 'db1'.
  3. Download the backup file from 'myserver1' via SCP command.
  4. Restore the backup file to a local database 'local_db1'.

# local environment
$ ssh myserver1

# server environment
$ mysqldump -u mkyong -p db1 | gzip > db1.20160726.sql.gz

# server environment
$ exit

# local environment
$ scp mkyong@myserver1:db1.20160726.sql.gz .

# local environment
$ $ gunzip -c db1.20160726.sql.gz | mysql -u mkyong -p local_db1


Done!

Note: Please use this command for more examples: mysqldump --help

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
reference ,backup ,database ,tables ,mysql database ,mysql ,steps

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}