MySQL – Backup and restore a database or table

mysql logo

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

1. MySQL – 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

2. MySQL – 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 back 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:

3. 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 ultimate command for more example - mysqldump --help

References

  1. Dumping Data in SQL Format with mysqldump
  2. StackOverflow - How do you mysqldump specific tables
  3. mysqldump man page
  4. Copy file to / from server via SCP command

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
3 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
3 Comment authors
aliSavaniArmando Couto Recent comment authors
newest oldest most voted
ali
Guest
ali

mysqldump ! the problem when you are using java on linux or mac exec will not work even you have to write the path for mysqldump.exe ! is there any solution for creating backup without using mysqldump only standalone java code ?

Savani
Guest
Savani

Hello Mkyong – Could you please also create tutorials for Spring Boot ? That will help all your followers.

Armando Couto
Guest
Armando Couto

Muito massa.