MySQL - graphic code case of database incremental data recovery

This article introduces the graphic code case of MySQL-database incremental data recovery. Friends in need can refer to it

1. Usage scenarios

• MySQL database is automatically fully prepared at zero o'clock every day

• One day at 9 o'clock in the morning, Zhang San accidentally dropped a database

• We need to recover data through complete data files and incremental binlog files
  

2. Main ideas and principles

• Use the CHANGE MASTER statement recorded in the complete sql file, the binlog file and its location information to find the incremental part of the binlog file

• Use the mysqlbinlog command to export the above binlog file as a sql file, and remove the drop statement

• Export sql files through full files and incremental binlog files , you can restore to complete data

3. Process diagram

4. Operation process

1 , Simulation data

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 

mysql> insert student values(1,'zhangsan',20); 
mysql> insert student values(2,'lisi',21); 
mysql> insert student values(3,'wangwu',22);

2. Full command

# mysqldump -uroot -p -B -F -R -x --master-data=2 test|gzip >/server/backup/test_$(date +%F).sql.gz

参数说明：
-B 指定数据库
-F 刷新日志
-R 备份存储过程等
-x 锁表
--master-data 在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

3. Continue to insert data and delete the database

mysql> insert student values(6,'xiaoming',20);
 
mysql> insert student values(6,'xiaohong',20); 

此时误操作，删除了test数据库
mysql> drop database test;

At this time, the data written by the user between the complete preparation and the moment of misoperation In the binlog, you need to restore it

4. Check the newly added binlog files after the full backup

# cd /server/backup/
# ls
test_2017-03-04.sql.gz
# gzip -d test_2017-03-04.sql.gz 
# grep CHANGE test_2017-03-04.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

This is the location of the binlog file at the time of full preparation, which is line 107 of mysql-bin.000003, so the data in the binlog file before this file is already included in this full sql file

5. Move the binlog file, read the sql, and remove the drop statement

# cp /data/3306/mysql-bin.000003 /server/backup/
# mysqlbinlog -d test mysql-bin.000003 >003bin.sql
# 用vim编辑文件，剔除drop语句

In recovery The binlog file must be moved out before fully preparing the data. Otherwise, statements will continue to be written to the binlog during the recovery process, eventually causing the incremental recovery data part to become confusing.

6. Restore data

# mysql -uroot -p <test_2017-03-04.sql 
# mysql -uroot -p -e "select * from test.student;"
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  20 |
|  2 | lisi     |  21 |
|  3 | wangwu   |  22 |
+----+----------+-----+
//此时恢复了全备时刻的数据
//然后使用003bin.sql文件恢复全备时刻到删除数据库之间，新增的数据
# mysql -uroot -p test<003bin.sql <span style="color: #3366ff;" data-mce-style="color: #3366ff;"><-需要指定恢复的数据库
</span># mysql -uroot -p -e "select * from test.student;"
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  20 |
|  2 | lisi     |  20 |
|  3 | wangwu   |  20 |
|  4 | xiaoming |  20 | 
|  5 | xiaohong |  20 |
+----+----------+-----+
完成

##5. Summary

• Suitable for repairing misoperations caused by human SQL statements or hot backup situations without master-slave replication, etc.

• The recovery conditions must be complete and All incremental data

• When restoring, it is recommended to stop updating externally, that is, prohibit updating the database

• Restore the full amount first, and then click the full backup time Later incremental logs are restored to SQL files in order, and then the problematic SQL statements in the files are deleted (you can also use time and location points), and then restored to the database

The above is the detailed content of MySQL - graphic code case of database incremental data recovery. For more information, please follow other related articles on the PHP Chinese website!