MySQL incremental backup recovery and point-in-time and location-based recovery

Why use incremental backups?

There are two ways to fully backup, one is to use tar to package the data file, and the other is to use mysqldump for a full backup. The problem with a full backup is easy to see, every time the data content is backed up, there is a large amount of duplicate data in the backup data, and full backup time and recovery time is very long. The problem with a full backup is to use incremental backup, which is the backup of files or content that has been added or changed since the last backup.

Features of incremental backups:

The advantage of incremental backup is that there is no duplicate data, the amount of backup is small and the time is short. The disadvantage is also obvious, requiring all incremental backups after the last full backup and full backup to recover, and a push-to-reverse recovery of all incremental backups, which is cumbersome to do.
MySQL does not provide a direct incremental backup method, but it is possible to implement incremental backups indirectly via the MySQL binary log (binary logs) profile. The meaning of the binary log to the backup is as follows:

• (1) The binary log saves all updates or operations that may update the database.
• (2) The binary log starts logging after starting the MySQL server and re-creates the new log file after the file reaches the size set by max_binlog_size or receives the Flush-logs command.
• (3) You only need to execute the Flush-logs method periodically to recreate the new log, generate a sequence of binaries, and save these logs to a safe place in a timely manner to complete an incremental backup of the time period.

Lab Environment:

The MySQL-5.7.17 on the CentOS7.3

One, incremental backup Recovery 1. Create a database, a data table, and add data

#systemctl stop firewalld.service           //关闭防火墙#setenforce 0                           //关闭增强安全功能#systemctl restart mysqld.service       //重启mysql#mysql –u root –p                   //登录进入mysql>create database school;                //创建数据库school>use school;                            //进入school>create table info (name varchar(10),score decimal(5,2));   //创建数据表info>insert into info (name,score) values (‘zhangsan’,88);      //向info中插入数据>insert into info (name,score) values (‘lisi’,88);          >select * from info;                                    //查看数据表info中数据>exit

2. Modify the MySQL configuration file, turn on the binary logging function, and restart the MySQL service.

#vim /etc/my.cnf[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1log-bin=mysql-bin                       //添加二进制日志功能#systemctl restart mysql.service            //重启mysql服务

3. Backing up the school database using mysqldump

#mysqldump –u root –p school > /opt/school.sql

4. Execute flush-logs to generate a new binary file

#mysqladmin –u root –p flush-logs#ls /usr/local/mysql/data               //生成了新的二进制文件mysql-bin.000002

5. Add test01 data and execute flush-logs to generate a new binary file

#mysql –u root –p>use school;>insert into info (name,score) values (‘test01’,60);>exit#mysqladmin –u root –p flush-logs#ls

6. Add test02 data and execute flush-logs to generate a new binary file

#mysql –u root –p>use school;>insert into info (name,score) values (‘test02’,60);>exit#mysqladmin –u root –p flush-logs#ls

7. Analog Misoperation Delete test01, test02

#mysql –u root –p>use school;>delete from info where name=’test01’;>delete from info where name=’test02’;>exit

8. Use binary files for recovery operations, you need to be aware of the order of recovery, to first restore the first generated binaries, and then execute in turn.

#mysqlbinlog --no-defaults mysql-bin.000002 | mysql –u root –p#mysql –u root –p>use school;>select * from info;                //test01已经恢复，可知test01删除记录保存在mysql-bin.000002中>exit#mysqlbinlog –no-defaults mysql-bin.000003 | mysql –u root –p#mysql  -u root –p>use school;>select * from info;                //test02已恢复，test02的删除记录保存在mysql-bin.000003中>exit

9. The command used to view the contents of the binary change file is

#mysqlbinlog --no-defaults --base64-output=decode-rows –v mysql-bin.000002

Two MySQL based on point-in-time recovery 1. First create a good school database, info table, add data to the table Name= ' Tom ', Score=88;name= ' Jerry ', score=882. Modify the MySQL configuration file, turn on the binary logging feature, Restart MySQL Service

#vim /etc/my.cnf[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1log-bin=mysql-bin#systemctl restart mysql.service

3. Perform full and incremental backups

#mysqldump –u root –p school > /opt/school.sql              //进行完全备份#mysqladmin –u root –p flush-logs                           //进行增量备份生成mysql-bin.000002

4. Simulate misoperation, add test01, delete tom, add test02

>insert into info(name,score) values (‘test01’,88);>delete from info where name=’tom’;>insert into info(name,score) values (‘test02’,88);

5. Add an incremental backup

#mysqladmin –u root –p flush-logs                           //生成增量备份mysql-bin.000003

6. Use binary change files to view specific operations and import new text documents for easy management

#mysqlbinlog --no-defaults --base64-output=decode-rows –v mysql-bin.000002 > /opt/info.txt#vim /opt/info.txt

7. Find the time stamp and position marker for the corresponding operation

• 180705 9:55:42 Error operation time--stop-datetime
• 180705 9:55:49 correct operation time--start-datetime
• At 563 last correct operation node--stop-position
• At 660 the next right operation node--start-position

8. Restore Tom and Jerry's data with a full backup first

>drop table info;#mysql –u root –p school < /opt/school.sql>mysql –u root –p>use school;>show tables;>select * from info;>exit

9. Point-in-time recovery

#mysqlbinlog –no-defaults –stop-datetime=’2018-07-05 9:55:42’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p             //注意时间格式需要用-连接

After entering info you can find that test01 has been restored

##mysqlbinlog –no-defaults –start-datetime=’2018-07-05 9:55:49’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p

Enter info to discover that test02 has been restored, resulting in point-in-time recovery completion

Three Location-based Recovery 1. First prepare the operating environment, the TEST01,TEST02 environment

>delete from info where name=’test01’;>delete from info where name=’test02’;

2. Location-based recovery

#mysqlbinlog –no-defaults –stop-position =’563’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p                   //上一次正确操作节点#mysqlbinlog –no-defaults –start-position=’660’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p                   //下一次正确操作节点

Enter school's info again to see that test01 and test02 have recovered.

MySQL incremental backup recovery and point-in-time and location-based recovery