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.