MySQL database backup and restore common commands

MysqlDatabase backup and restore common commands are the key to MySQL database backup and restore, there is no command, WhatDo not have to do, not to mention what backup restore, only to the system this command, let it to execute, in order to complete the MySQL database backup and restore operations, the following is the operation of the common commands.

One, Backup command

1, backup the MySQL database command

Mysqldump-hhostname-uusername-ppassword Databasename>backupfile.sql

2, backup MySQL database for the format with the deleted table

Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.

Mysqldump-–add-drop-table-uusername-ppassword Databasename>backupfile.sql

3, directly to the MySQL database compression backup

Mysqldump-hhostname-uusername-ppassword databasenamegzip>backupfile.sql.gz

4, Backup MySQL database some (some) table

Mysqldump-hhostname-uusername-ppassword databasename Specific_table1 Specific_table2>backupfile.sql

5. Backup multiple MySQL databases at the same time

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

6. Only BACKUP DATABASE structure

Mysqldump–no-data–databases databasename1 databasename2 Databasename3>structurebackupfile.sql

7, backup all databases on the server

Mysqldump–all-databases>allbackupfile.sql

Second, restore command

1, restore the MySQL database command

Mysql-hhostname-uusername-ppassword Databasename

2, restore the compressed MySQL database

Gunzip

3, transfer the database to the new server

Mysqldump-uusername-ppassword databasenamemysql–host=*.*.*.*-c DatabaseName

Summarize:

Do data backup and restore, set appropriate backup strategy, this is a DBA to do a small part of things, the beginning of the difficult, starting from now!