Summary of common commands for MySQL database backup and restore

Commands to back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

backing up the MySQL database for a format with a delete table the MySQL database is formatted with a delete table, allowing the backup to overwrite the existing database without having to manually delete the existing database.

Mysqldump---add-drop-table-uusername-ppassword databasename > Backupfile.sql

Compress the MySQL database directly backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Back up a MySQL database (some) table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword--databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Backing up the database structure only

Mysqldump--no-data--databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

Mysqldump--all-databases Allbackupfile.sql

Commands to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restore a compacted MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

To transfer a database to a new server

Mysqldump \-uusername \-ppassword databasename \| MySQL \--host=*.*.*.\* \-c databasename

Compress backup

Backup and compress with gzip:

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

Restore from gzip Backup:

Gunzip < outputfile.sql.gz | MySQL < MySQL options>

Backup and compress with bZIP:

mysqldump < mysqldump options> | bzip2 > outputfile.sql.bz2

Restore from bzip2 Backup:

Summary of common commands for MySQL database backup and restore