MySQL incremental backup and recovery instance _ MySQL

A small number of databases can be fully backed up every day, because it does not take much time. However, when the database is very large, it is unlikely that a full backup is performed every day. at this time, incremental backup can be used. The principle of incremental backup is to use mysql binlog. The M database in this operation can be fully backed up every day, because it does not take much time. However, when the database is very large, it is unlikely that a full backup will be performed every day, at this time, you can use incremental backup. The principle of incremental backup is to use mysql binlog.

The MySQL version for this operation is5.5.40 for Linux (x86_64).

To enable the binary log for incremental backup, refer to the mysql log system:

mysql> show variables like'%log_bin%';

First, make a complete backup of the pak database:

$ mysqldump -h localhost -upak -ppwd -P3306 --master-data=2 --single-transaction --opt pak > pak_bak_full.sql 

At this time, you will get a full-backup file pak_bak_full. SQL. The mysqldump operation will result in a rolling log, assuming that the new binlog file is a mysql-bin.000002.

Simulate data insertion and misoperations

A. insert some data into a table in the pak database and executeflush logsCommand. A new mysql-bin.000003 of the binary log file is generated, and the mysql-bin.000002 saves all the changes after full backup, both adding record operations and saving them in the mysql-bin.00002.

B. add two records to the t_user table in the pak database and delete the t_user table by mistake. The operations for adding records in t_user and for Deleting tables are recorded in the mysql-bin.000003.

Start Recovery

Do not log the recovery process:

mysql > setglobal sql_log_bin=0;

First import full backup data

$ Mysql-h localhost-upak-ppwd <pak_bak_full. SQL or mysql> source/path/backup/pak_bak_full. SQL

We can also see the binlog location for full backup:

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

View the location in the current binary log:

mysql> show master status;

Based on the above two positions, you can determine which binlog files need to be completely restored.

Recovery mysql-bin.000002

Binlogs before or after the position or time point to be restored and after full backup need to be completely restored. multiple files are separated by spaces.

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

In this case, you can obtain the first two pieces of data.

Recovery partial mysql-bin.000003

This log contains two parts: add record and accidentally delete table. we need to restore to the location after the newly added record and before the accidental delete operation.

If you know the incorrect command, suchDROP TABLE, You can find the position before misoperation in the binlog file using the following method:

(The following information is displayed, indicating incorrect operation.DROP TABLEThe previous pos is 775, completed at 15:08:04 datetime 141204 or pos 882.DROP TABLEOperation)

 $ 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=0SET TIMESTAMP=1417676884/*!*/;DROP TABLE `t_user` /* generated by server *//*!*/;# at 882

Recovery command:

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

If the position is difficult to determine, but you know the exact (server) time to be restored, you can also use datetime:

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

If it is not due to misoperations, but to migrate the database, you can use all binlog files for incremental recovery without position or datetime.

After the recovery is successful, remember to open the log record:

mysql > setglobal sql_log_bin=1;

Error

1. unknown variable 'default-character-set = utf8'

In usemysqlbinlogWhen viewing binary logs, the following error is prompted:

/Usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set = utf8'

The reason is that in order to unify the character encoding from the mysql client to the server/etc/my.cnfFile[client],[mysqld]And other sections addeddefault-character-set = utf8,mysqlbinlogFrommy.cnfIn[client]Read the configuration, but mysqlbinlog does not know this option (it is said to be a bug.

There are two methods to deal with this bug:

First, it is naturally annotated[client]The character set configuration in;

Second, useloose-default-character-set = utf8. Addedloose-, Indicates that when the program does not recognize this option, it will skip this option and give a warning.