How to Back Up and Restore a MySQL Database

As you know your website data is stored in your database server. To prevent disaster, backup your data regularly is a must for you and every website owner. There are many ways to backup MySQL data, one of them is using mysqldump to dump your MySQL data to a file.

Dump all MySQL Databases

mysqldump –user=XXXXXXXX –password=XXXXXXX –all-databases > /PATH/TO/DUMPFILE.sql

Dump Individual or Multiple MySQL Databases

mysqldump –user=XXXXXXXX –password=XXXXXXX –databases DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.sql

Dump only certain tables from a MySQL Database

mysqldump –user=XXXXXXXX –password=XXXXXXXX –databases DB_NAME –tables TABLE_NAME > /PATH/TO/DUMPFILE.sql

Dump and Compress MySQL Database

mysqldump –user=XXXXXXXX –password=XXXXXXXX –databases DB_NAME | gzip -9 > /PATH/TO/DUMPFILE.sql.gz

Note: The mysqldump will dump MySQL data according to the credential given, the output will pipeline to gzip to compress it with highest compression rate and output as DUMPFILE.sql.gz file.

Restore MySQL Dump file

mysql -u [uname] -p[pass] [dbname] < /PATH/TO/DUMPFILE.sql

Restore Compressed MySQL Dump file

gunzip < /PATH/TO/DUMPFILE.sql.gz | mysql -u [uname] -p[pass] [dbname]

Note: gunzip will extract the compressed file and the output (the sql commands) will be run in MySQL database as credential given. The sql command will create or overwrite the existing table and data.

Permanent link to this article:

Leave a Reply

Your email address will not be published.