MySQL uses BINLOG for Incremental Backup

When backing up Mysql Data, you may encounter a large data base. If you back up the entire database every day, it will consume a lot of resources, at this time, you can use BINLOG for Incremental backup and record a breakpoint every day. During the backup, you can perform Incremental backup from the previous day's breakpoint. In another case, you can also use Incremental backup, that is, when migrating data online, you can use Incremental backup to supplement incremental data without stopping services.

When using BINLOG for Incremental backup, if you do not need to filter specific databases and tables, you can directly perform Incremental Backup through the Master/Slave mechanism that comes with MySQL. If you need to filter out a specific database or table, you need to writeCodeFilter BINLOG. The following describes how to filter BINLOG and perform Incremental backup.

1. Use the % mysql_home %/bin/mysqlbinlog tool to parse the BINLOG binary file into text, which can be written to the hard disk or put into the pipeline.

2. a binlog filter is required.ProgramThe BINLOG of the text is filtered to find the SQL statements of the specific database and table.

3. when filtering, you cannot perform grep by row because one SQL statement may be in multiple rows. In this case, you can use the separator ";" to divide the statement, note that the comment should be removed first, because the comment may contain ";" (MySQL comments include/*, #, and --), and "; "cannot be included (when processing strings, pay attention to the escape of double quotation marks ).

4. filter SQL statements one by one. Here we also need some string processing. Note that the context of the Use statement determines the database corresponding to the SQL statement, then, the SQL command is listed as a whitelist to match the table name and position. After obtaining the table name, filter it again.

5. because a new BINLOG is generated while the BINLOG is backed up, a loop is required to perform the preceding four steps until the remaining BINLOG size is small enough, you can stop MySQL write operations for a short period of time and synchronize the remaining binlogs completely.