MySQL Backup and restore

First, backup common Operation Basic command

1. Backup Command mysqldump format

Format: MYSQLDUMP-H host name-P Port-u user name-p password –database database name > file name. 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.sql only 6, backup copy 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

Using--master-data=2 in mysqldump, binlog files and position information are recorded. --single-transaction will set the isolation level to repeatable-commited

12. Import the Database

Common source command, with use to enter into a database, Mysql>source D:\test.sql, the 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 general_log status and location can be command show variables like "general_log%", open General_log can use command set global General_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 get a fully-prepared file by this time Test.sql

In the SQL file we will see:
--Change MASTER to master_log_file= ' bin-log.000002 ', master_log_pos=107; means that all changes are saved to the bin-log.000002 binaries after the backup is made.
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 T_student table and a table by mistake. The operations for adding records in a and deleting tables A and t_student are recorded in bin-log.000003.

Third, recovery

1, first import full data

mysql-h10.6.208.183-utest2-p123-p3310 < Test.sql can also be imported 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-position start point--stop-position end point

--start-date start 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 Backup and restore