Summary of common commands for MySQL database backup and restore

Backup the command for the MySQL database

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

Backing up the MySQL database in a format with deleted tables enables the backup to overwrite the existing database without manually deleting the original database.

mysqldump ---add-drop-table -uusername -ppassword databasename > backupfile.sql

Compress the MySQL database directly

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

Back up some tables in the MySQL database

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

Backup multiple MySQL databases simultaneously

mysqldump -hhostname -uusername -ppassword --databases databasename1 databasename2 databasename3 > multibackupfile.sql

Just back up the database structure

mysqldump --no-data --databases databasename1 databasename2 databasename3 > structurebackupfile.sql

Backup all databases on the server

mysqldump --all-databases allbackupfile.sql

Restore the MySQL database command

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

Restore the compressed MySQL database

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

Move the database to the new server

mysqldump \-uusername \-ppassword databasename \| mysql \--host=*.*.*.\* \-C databasename

Compression backup

Backup and gzip compression:

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:

bunzip2 < outputfile.sql.bz2 | mysql < mysql options >