A summary of common commands for Mysql database backup and restore _mysql

Commands for backing up the MySQL database

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

back up the MySQL database to back up the MySQL database in a format with a delete table in the format with the deleted table, you can have the backup overwrite the existing database without manually deleting the original database.

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

Directly compress MySQL database to backup

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

Backing up a MySQL database (some) tables

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

Backup multiple MySQL databases at the same time

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

Back up all databases on the server

Mysqldump--all-databases Allbackupfile.sql

command to restore MySQL database

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

Restoring a compressed MySQL database

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

Transferring a database to a new server

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

Compress backup

Backup and use gzip compression:

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

Restore from gzip Backup:

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

Backup and compress using bZIP:

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

Restore from bzip2 Backup:

Bunzip2 < OUTPUTFILE.SQL.BZ2 | MySQL < MySQL options>