MySQL incremental backup and restore detailed

Features of incremental backup and recovery

• The advantage of incremental backup is that there is no duplicate data, the amount of backup is small and the time is short. However, the disadvantage is obvious, and it is necessary to establish all the deltas after the last full and full backup to recover.
• MySQL does not provide a direct incremental backup method, but incremental backups can be implemented indirectly through MySQL binary logs. 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 recording after starting the MySQL server and re-creates the new log file after the file reaches its size or the flush logs command is received.
• (3) Simply follow the flush logs method to recreate the new log, generate the binary sequence, and save the files in a safe place in a timely manner to complete an incremental backup of a time period.

Incremental backup and recovery create a basic database

create databse schooluse schoolcreate table info (name varchar(10),score decimal(5,2));insert into info (name,score) values (‘zhangsan‘,80);insert into info (name,score) values (‘lisi‘,80);//数据库建立以及表数据添加完成

• Edit the configuration file to open the binary logging feature

#vim/etc/my.cnf

log-bin=mysql-bin   //添加二进制日志文件

#systemctl Restart Mysqld.service

#msyqldump-u root-p School >/opt/school.sql//Backup database file

• Start by generating a binary log that creates test01 data:

#mysql-U root-p

>use school;>insert into info (name,score) values (test01,88);>quit//完成数据插入

#mysqladmin-U root-p flush-logs//refresh generate MySQL Incremental backup file
#cd/usr/local/mysql/data/
#ls//binary log has been generated

• To view the binary log file: (determines that the increase test01 operation is in the mysql-bin.000002 log)

  #mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000002//using 64-bit decoding method

• Next, generate a binary log that creates test02 data:

#mysql-U root-p

>use school;>insert into info (name,score) values (test02,90);>quit//完成数据插入

#mysqladmin-U root-p flush-logs//refresh generate MySQL Incremental backup log file
#cd/usr/local/mysql/data/
#ls//binary log has been generated

• When the worker inadvertently removes the test01 and test02 users, causing the database to be unavailable, and the full data for the backup is only Zhangsan and Lisi accounts,
  You need to restore test01 or test02 on demand according to the binary log file;

Analog Error Operation:

#mysql-U root-p

>use school>delete from info where name=‘test01‘;    //执行误操作>select * from info;    >delete from info where name=‘test02‘;    //执行误操作>select * from info;

Restore the full database first:

#mysql-U root-p School </opt/school.sql

• When the test01 user adds data in the binary log msyql-bin.0000002:
• Recovery test01:

#msyqlbinlog--no-defaults/usr/local/mysql/data/msyql-bin.0000002 | Msyql-u root-p
#msyql-U root-p

>use school>select * from info;     //发现test01已经恢复

• When the test02 user adds data in the binary log msyql-bin.0000003:
• Recovery test02:

#mysqlbinlog--no-defaults/usr/local/mysql/data/msyql-bin.0000003 | Msyql-u root-p
#mysql-U root-p

>use school>select * from info;     //发现test02已经恢复

Time-and location-based recovery

• To create a default database:

  create databse schooluse schoolcreate table info (name varchar(10),score decimal(5,2));insert into info (name,score) values (‘tom‘,80);insert into info (name,score) values (‘jerry‘,90);//数据库建立以及表数据添加完成

• To turn on the binary logging feature

#vim/etc/my.cnf

log-bin=mysql-bin   //添加二进制日志文件

#systemctl Restart Mysqld.service

#mysqldump-u root-p School >/opt/school.sql//Backup database file with only the default users of Tom and Jerry

#mysqladmin-U root-p flush-logs//refresh generate MySQL Incremental backup log file
#cd/usr/local/mysql/data/
#ls//binary log has been generated

Analog failure:

• When a worker adds test01 and test02 accounts, the middle of accidentally deleting the default account in the database, Tom, causes the integrity of the database to be corrupted.
  The following need to restore test01 and test02 data and Tom data in binary log files by location and time;

mysql> insert into info (name,score) values (‘test01‘,88);Query OK, 1 row affected (0.00 sec)mysql> delete from info where name=‘tom‘;   //故障操作为误删除默认用户tomQuery OK, 1 row affected (0.00 sec)mysql> insert into info (name,score) values (‘test02‘,70);Query OK, 1 row affected (0.00 sec)

#mysqladmin-U root-p flush-logs//At this time the incremental backup has been completed on the basis of mis-operation

• Since the completion of the database backup file only the default Tom and Jerry users, and no test01 and test02, if you restore the incremental backup file, you will delete the Tom user, the following will use the binary log incremental backup file breakpoints to restore test01 and test02 without deleting the tom user;

• Restore the full database first:

  drop table info;  //删除原有损坏的数据库

#mysql-U root-p School </opt/school.sql//restore BACKUP Database

• To view the binary log incremental backup file:

• Find the location of the breakpoint you want to restore:

• The restore sequence executes from the beginning of the file and ends at the--stop-datetime error operation Point due to the incremental backup file restore of the breakpoint;

• Because the restore sequence is performed at the beginning of the file at the point of the incremental backup file restore, it ends at the end of the--stop-datetime error operation, and when the error point is skipped, the--start-datetime starts at the end of the backup file from the next correct operation point in time.

• Revert to test01:

#mysqlbinlog--no-defaults--stop-datetime= ' 2018-07-03 19:05:42 '/usr/local/mysql/data/msyql-bin.0000001 | Mysql-u root-p//Note Modify date format xxx-xx-xx

• Skip error Time: Start with the next correct operation time:

#mysqlbinlog--no-defaults--start-datetime= ' 2018-07-03 19:06:03 '/usr/local/mysql/data/msyql-bin.0000001 | Mysql-u root-p//Note Modify date format xxx-xx-xx

• This skips the error operation step and directly selects the correct operation to restore.
• Note: A breakpoint restore can only skip a single error if there are multiple error operations in the middle that are not continuously restored.

Node restore

• Note the AT node number before the error operation:

#mysqlbinlog--no-defaults--stop-position= ' 563 '/usr/local/mysql/data/msyql-bin.0000001 | Mysql-u root-p

• Skip error Time: Start with the next correct operation node:

#mysqlbinlog--no-defaults--stop-position= ' 660 '/usr/local/mysql/data/msyql-bin.0000001 | Mysql-u root-p

As above, the experiment on node recovery has been completed.

MySQL incremental backup and restore detailed