MySQL Backup and restore a database or table

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]

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.

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