Incremental backup and recovery for MySQL

MySQL Incremental backup and recovery

• Incremental backup: Only those files that were modified after the last full or incremental backup are backed up.

• Advantages: No duplication of data, backup volume is small, time is short.

• Disadvantage: All incremental backups need to be restored after the last full and full backup, and all incremental backups are re-pushed back-by-side for a cumbersome operation.

One, incremental backup 1, edit the MySQL configuration file, turn on the binary function

Vim/etc/my.cnf

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

Systemctl Restart Mysql.service

• After the service restarts, an empty binary log file is generated in the/use/local/mysql/data directory;

2. Create a base database

Mysql-u root-p #进入MySQL

Create database client; #创建一个名为client的库

Use client; #使用数据库

CREATE TABLE info (name varchar, score decimal (5.2)); #创建表

Insert into info (name,score) VALUES (' Zhangsan ', 88); #插入内容

Insert into info (name,score) VALUES (' Lisi ', 88);

Quit #退出MySQL

3, back up the database, and generate a new binary log file;

Mysqldump-u root-p Client Test >/opt/client.sql

Mysqladmin-u root-p Flush-logs

• The contents of the database are generated into the first binary log file, in addition to an empty binary log file;

4, add new content in the database, and generate a new binary log file;

Mysql-u root-p

Use client

Insert into info (name,score) VALUES (' test01 ', 88);

Quit #退出MySQL

Mysqladmin-u root-p Flush-logs

• The newly added content in the database is generated to the second binary log file, and a blank binary log file is generated;

Second, restore 1, delete the table, and perform a full backup recovery operation;

Mysql-u root-p;

Use client;

Drop tables test;

Quit

Mysql-u Root-p Client </opt/client.sql

2, the use of binary log files for recovery operations;

Mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000002 | Mysql-u root-p

Third, based on the time-point and location of the recovery 1, insert two data content, and delete a data, simulation operation error;

Mysql-u root-p

Use client

Insert into info (name,score) VALUES (' test01 ', 88);

Delete from test where name= ' Lisi ';

Insert into info (name,score) VALUES (' test02 ', 88);

Quit

2. Generate binary log files, delete tables, and perform full backup recovery operations;

Mysqladmin-u root-p Flush-logs

Mysql-u root-p;

Use client;

Drop tables test;

Quit

Mysql-u Root-p Client </opt/client.sql

3, by the time point of recovery (skip the wrong operation time);

Mysqlbinlog--no-defaults--stop-datetime= ' 18-07-06 10:39:23 '/usr/local/mysql/data/mysql-bin.000003 | Mysql-u root-p

Mysqlbinlog--no-defaults--start-datetime= ' 18-07-06 10:39:33 '/usr/local/mysql/data/mysql-bin.000003 | Mysql-u root-p

4, restore by location (skip the wrong position);

Mysqlbinlog--no-defaults--stop-position= ' 1151 '/usr/local/mysql/data/mysql-bin.000003 | Mysql-u root-p

Mysqlbinlog--no-defaults--start-position= ' 1226 '/usr/local/mysql/data/mysql-bin.000003 | Mysql-u root-p

Incremental backup and recovery for MySQL