MySQL full-scale backup and incremental backup

1. Full-scale backup

To put it simply, all databases or a library are backed up.

2. Incremental backup

It is called an incremental backup from the last full-volume backup to the next full-volume backup.

For the Mysql,binlong log is MySQL data, the backup of Binlong is a backup of MySQL.

To lock the table when backing up, affect the user experience, so the frequency of backup has advantages and disadvantages.

When does MySQL's mysqldump backup come in handy?

1. When migrating or upgrading a database,

2. Increase the time from the library

3. Man-made DDLDML statement, Master Library no way, all libraries will execute, restore backup

4. Cross-room disaster preparedness, need to backup to offsite.

   
   

Requirements for MySQL data recovery

1. Turn on Binlog, there is a backup of all incremental binlog files at the time of the full standby to the point of the problem.

command to back up all:

[Email protected] ~]# mysqldump-uroot-p123456-f--single-transaction-a-B | gzip > test.sql.gz

When backing up data and recovering data, be sure to lock the table.

Simulate:

1. Make a full backup of a library first:

#mysqldump-uroot-p123456-b--master-data=1-f XPG | gzip > xpg_$ (Date +%f). sql.gz

2. Then use Insert to insert two data into the XPG library.

3. Use drop database dbname; Delete this library, etc.

4. Is to communicate who is doing what operation and combine Binlog log

5. Down is the point: Recover, lock the table first, or stop the Web service from writing data to MySQL.

6. Restore the full amount of backup data first, because the use of the option-F specified, so the next operation will be written to the new Binlog log file, the down file in the SQL statement extracted, of course, do not drop the command, and then execute the SQL command.

* It is important to note that if it is not the drop, but update destroys the data, the resolution is complex, in order to prevent the error operation of the update: Use the option when logging in to MySQL

-U: The function is to make an error if you do not use the Where condition when executing the update. Mysql-u can be added to the alias;

Another way to specify where to position and where to end

Option:--start-position--stop-position

MySQL full-scale backup and incremental backup