MySQL data backup and restore command

Reprint: Original Address

First, backup common Operation Basic command 1, backup command mysqldump format

Format:mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql

2. Backup MySQL database as a format with delete table

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

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

3, the MySQL database directly compressed backup

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

4. Back up some (some) tables of MySQL Database

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

5. Back up multiple MySQL databases at the same time

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

6. Backup DATABASE structure only

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

7. Back up all databases on the server

mysqldump –all-databases > allbackupfile.sql

8. command to restore MySQL database

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

9. Restore the compressed MySQL database

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

10. Transfer the database to the new server

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

One by one 、--Master-data and--single-transaction

When used in mysqldump --master-data=2 , information about binlog files and position is recorded. --single-transactionThe isolation level is set torepeatable-commited

12. Import the Database

Common source command, using use to enter into a database, the mysql>source d:\test.sql following parameters are the script file.

13. View Binlog Log

View Binlog logs available with command mysqlbinlog binlog log name |more

14, General_log

General_log Record any operation of the database, view the status and location of the General_log can be command show variables like "general_log%" , open GeneralLog can be command ' set globalgeneral Log=on '

Two, incremental backup

A small number of databases can be fully backed up every day, as this will not take much time, but when the database is large, it is unlikely that a full backup will take place every day, and an incremental backup can be used. The principle of incremental backup is to use the Binlog log of MySQL.

1, first do a full backup:

mysqldump -h10.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data=2 test>test.

SQL will now get a fully-prepared file Test.sql

In the SQL file we will see that -- CHANGE MASTER TO MASTER_LOG_FILE=‘bin-log.000002‘, MASTER_LOG_POS=107; all changes are saved to the bin-log.000002 binaries after the backup. 2. Add two records to the T_student table in the test library, and then execute the flush logs command. This will result in a new binary log file, bin-log.000003,bin-log.000002, which saves all the changes that were made after the full backup, as well as the addition of the recorded operations to the bin-log.00002.

3. Add two records to the a table in test library and delete the TStudent table and a table by mistake. The operations for adding records in a and deleting tables A and Tstudent are recorded in bin-log.000003.

Third, restore 1, the first to import fully prepared data

mysql -h10.6.208.183 -utest2 -p123 -P3310 < test.sql, or you can import it directly under the MySQL command line with source

2, Restore bin-log.000002

mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123 -P3310

3, Recovery part bin-log.000003

In the General_log, find the time point of the accidental deletion, and then the corresponding point in time to bin-log.000003 to find the corresponding position point, you need to revert to the first position point of the accidental deletion.

You can use the following parameters to control the interval of the Binlog

--start-positionStart point --stop-position end point

--start-dateStart time --stop-date End time

After you find the recovery point, you can start the recovery.

mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123 -P3310

MySQL data backup and restore command