MySQL incremental backup and recovery instance

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 MySQL binlog log.
The MySQL version of this operation is 5.5.40 for Linux (x86_64) .

Incremental backup to ensure that binary logs are turned on, refer to the MySQL log system:

variables like ‘%log_bin%‘;

First make a full backup of the PAK database:

$Mysqldump-h localhost -upak - Ppwd -p3306 - -master- data=2 --single-transaction --< Span class= "hljs-comment" >opt pak > pak_bak_fullsql              

At this point, you will get a fully-prepared file pak_bak_full.sql. The mysqldump operation causes the log to scroll once, assuming that the new Binlog file is mysql-bin.000002.

Analog insert data and mis-operation

A. Insert some data into a table in the Pak Library and execute the flush logs command. This will result in a new binary log file, mysql-bin.000003,mysql-bin.000002, which saves all the changes that were made after the full backup, as well as the addition of the recorded operations to the mysql-bin.00002.

B. Add two records to the T_user table in the Pak library and delete the T_user table by mistake. The actions for adding records and deleting tables in T_user are recorded in mysql-bin.000003.

Start recovery

The recovery process does not log:

set global sql_log_bin=0; 

First, fully-prepared data is imported

$ mysql -h localhost -upak -ppwd < pak_bak_full.sql或mysql> source /path/backup/pak_bak_full.sql

We can also see the binlog position at full standby:

head -50 backup-file.sql |grep ‘CHANGE MASTER‘-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=4321;

To view the location in your current binary log:

master status;

According to the above two position can probably determine which several binlog files need to be fully recovered.

Recovery mysql-bin.000002
Position or point-in-time before recovery, full-ready binlog need to be fully recovered, multiple files separated by spaces

$ mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p

At this point the query can get the first two data.

Recovering part mysql-bin.000003
This log contains the new record and the two parts of the mis-deleted table, we need to restore to the new record, the location of the previous error delete operation.

If you know the command for the Misoperation DROP TABLE , you can find the position before the misoperation in the Binlog file by using the following method:
(as shown in the following information, DROP TABLE POS before misoperation is 775, done at datetime 141204 15:08:04 or pos 882 DROP TABLE )

$ mysqlbinlog /var/lib/mysql/mysql-bin.000003 |grep -C 5 ‘DROP TABLE‘#141204 15:07:05 server id 1 end_log_pos 775 Xid = 376COMMIT/*!*/; # at 775 #141204 15:08:04 server id 1 end_log_pos 882 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1417676884/*!*/; DROP TABLE `t_user` /* generated by server */ /*!*/; # at 882 

Restore command:

$ mysqlbinlog /var/lib/mysql/mysql-bin.000003 --stop-position=775 | mysql -h localhost -uroot -p

If position is difficult to determine, but knows the exact (server) time to restore to, you can also use DateTime:

/var/lib/mysql/mysql-bin.000003 --stop-datetime="2014-12-04 15:08:00" | mysql -uroot -p

If the database is migrated instead of the wrong operation, then no position or datetime is required and incremental recovery is done with all binlog files.

When you are sure that the recovery is successful, remember to turn on logging:

set global sql_log_bin=1;

Error
1. Unknown variable ' Default-character-set=utf8 '
When using mysqlbinlog the view binary log, the following error is prompted:

/usr/local/mysql/bin/mysqlbinlog:unknown variable ' Default-character-set=utf8 '

The reason is that in my order to unify the MySQL client to the service side of the character encoding, in the /etc/my.cnf file [client] , the [mysqld] section joins default-character-set = utf8 , mysqlbinlog will be my.cnf read from the [client] configuration, But Mysqlbinlog does not recognize this option, which is said to be a bug.

There are two ways to deal with this bug:
First, nature is the annotation to the [client] character set in the configuration;
Second, instead loose-default-character-set = utf8 . Added before the option, indicating that this option is skipped loose- when the program does not recognize this option and gives a warning.

Original link Address: http://seanlook.com/2014/12/05/mysql_incremental_backup_example/

MySQL incremental backup and recovery instance