【mysql 系列2】通过bin-log恢复数据
1 mysql数据恢复原理
bin-log记录了所有的DDL和DML(除了数据查询语句)语句，当mysql发生灾难性错误时，可以通过bin-log做完整恢复，基于时间点的恢复，和基于位置的恢复。
不过完全基于bin-log的日志恢复，通常是不现实的。 如果你一个数据库运作了10年，不可能把所有的bin-log日志全部都保存下来（bin-log的体积成长还是很快的，特别对于一些增删改比较频繁的业务）。即使你保存下来， 重头跑一遍这样的bin-log， 这个时间估计你也受不了。 所以一般的数据恢复都是恢复数据备份到某个时间点，然后重跑bin-log部分的新增量。 所以日常应用中别指望完全通过bin-log恢复数据， 还是要定时备份数据。
通常情况下有个误解就是bin-log在恢复数据的时候，并非是撤销对数据的操作，而是回到某个备份点以后，执行增量的bin-log文件，跳过你想撤销的数据操作来达到恢复数据的。 重申一下bin-log只记录DDL和DML的操作记录，并不保存数据的snapshot。
2. 数据恢复实例
2.1 实例环境
#查看mysql版本SHOW VARIABLES LIKE '%version%';Variable_name Value----------------------- ------------------------------innodb_version 5.7.26protocol_version 10slave_type_conversionstls_version TLSv1,TLSv1.1version 5.7.26-logversion_comment MySQL Community Server (GPL)version_compile_machine x86_64version_compile_os Linux
2.2 创建数据库（test_db）
CREATE DATABASE test_db;
2.3 业务场景1
2.3.1 假如1:00，我们执行了一次数据备份
/usr/bin/mysqldump -uroot -pXXXX -lF -B test_db > /root/test_db_bak.sql
由于我使用了-F选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下
mysql> show master status;File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set---------------- -------- ------------ ---------------- -------------------mysql-bin.000007 123
2.3.2 假如2:00 对数据库做了一些业务操作
# 创建业务表，插入数据CREATE TABLE IF NOT EXISTS `tb1` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(16) NOT NULL,`age` INT(10) NOT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO tb1 VALUES (1, 'aaa', 10),(2, 'bbb', 20);INSERT INTO tb1 VALUES (3, 'ccc', 30);
2.3.3 假如3:00 在前面的数据基础上又做了一些变更操作
UPDATE tb1 SET NAME='aaa-000' WHERE id=1;INSERT INTO tb1 VALUES (4, 'ddd', 40);
2.3.4 假如4:00 做了一次错误操作删除了数据表（比如数据库被恶意删除）
delete from tb1;
2.3.5 假如5:00 我们才发现这个问题。此时又进行了一些其他业务操作
INSERT INTO tb1 VALUES (5, 'eeee', 50);
2.4 业务场景2
针对于上述场景中的数据如何恢复呢？ 首先我们先保留现场，然后分析原因。
2.4.1 保护现场
执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，这样出问题的那个log文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；
mysql> flush logs;mysql> show master status;
2.4.2 读取binlog日志，分析问题
mysql> show binlog events in 'mysql-bin.000007';Log_name Pos Event_type Server_id End_log_pos Info---------------- ------ -------------- --------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------mysql-bin.000007 4 Format_desc 1 123 Server ver: 5.7.26-log, Binlog ver: 4mysql-bin.000007 123 Previous_gtids 1 154mysql-bin.000007 154 Anonymous_Gtid 1 219 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 219 Query 1 489 use `test_db`; CREATE TABLE IF NOT EXISTS `tb1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(16) NOT NULL,`age` int(10) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql-bin.000007 489 Anonymous_Gtid 1 554 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 554 Query 1 629 BEGINmysql-bin.000007 629 Rows_query 1 705 # insert into tb1 values (1, 'aaa', 10),(2, 'bbb', 20)mysql-bin.000007 705 Table_map 1 758 table_id: 154 (test_db.tb1)mysql-bin.000007 758 Write_rows 1 819 table_id: 154 flags: STMT_END_Fmysql-bin.000007 819 Xid 1 850 COMMIT /* xid=3114 */mysql-bin.000007 850 Anonymous_Gtid 1 915 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 915 Query 1 990 BEGINmysql-bin.000007 990 Rows_query 1 1051 # INSERT INTO tb1 VALUES (3, 'ccc', 30)mysql-bin.000007 1051 Table_map 1 1104 table_id: 154 (test_db.tb1)mysql-bin.000007 1104 Write_rows 1 1152 table_id: 154 flags: STMT_END_Fmysql-bin.000007 1152 Xid 1 1183 COMMIT /* xid=3123 */mysql-bin.000007 1183 Anonymous_Gtid 1 1248 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 1248 Query 1 1323 BEGINmysql-bin.000007 1323 Rows_query 1 1387 # update tb1 set name='aaa-000' where id=1mysql-bin.000007 1387 Table_map 1 1440 table_id: 154 (test_db.tb1)mysql-bin.000007 1440 Update_rows 1 1506 table_id: 154 flags: STMT_END_Fmysql-bin.000007 1506 Xid 1 1537 COMMIT /* xid=3132 */mysql-bin.000007 1537 Anonymous_Gtid 1 1602 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 1602 Query 1 1677 BEGINmysql-bin.000007 1677 Rows_query 1 1738 # INSERT INTO tb1 VALUES (4, 'ddd', 40)mysql-bin.000007 1738 Table_map 1 1791 table_id: 154 (test_db.tb1)mysql-bin.000007 1791 Write_rows 1 1839 table_id: 154 flags: STMT_END_Fmysql-bin.000007 1839 Xid 1 1870 COMMIT /* xid=3141 */mysql-bin.000007 1870 Anonymous_Gtid 1 1935 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 1935 Query 1 2010 BEGINmysql-bin.000007 2010 Rows_query 1 2049 # delete from tb1mysql-bin.000007 2049 Table_map 1 2102 table_id: 154 (test_db.tb1)mysql-bin.000007 2102 Delete_rows 1 2193 table_id: 154 flags: STMT_END_Fmysql-bin.000007 2193 Xid 1 2224 COMMIT /* xid=3172 */mysql-bin.000007 2224 Anonymous_Gtid 1 2289 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'mysql-bin.000007 2289 Query 1 2364 BEGINmysql-bin.000007 2364 Rows_query 1 2426 # INSERT INTO tb1 VALUES (5, 'eeee', 50)mysql-bin.000007 2426 Table_map 1 2479 table_id: 154 (test_db.tb1)mysql-bin.000007 2479 Write_rows 1 2528 table_id: 154 flags: STMT_END_Fmysql-bin.000007 2528 Xid 1 2559 COMMIT /* xid=3182 */
注： 或许你的log-bin显示的和我不太一样，看不到sql。 请注意event类型， QUERY_EVENT是可以看到sql语句的，ROWS_EVENT 不会显示sql语句。 mysql5.7 默认是使用ROWS_EVENT方式的， 所以你需要在my.cnf修改参数
binlog_rows_query_log_events=1
然后重启mysql。即可
分析结果： pos【1870-2193】 执行了一条删除操作（delete from tb1）， 这是一条非法数据。 前面的业务和后面发生的业务都是正常业务。 所以我们只需要将 pos【1870- 2193 】这段恢复即可。
第一部分已经说明，log-bin 并没有恢复某条命令的过程。 所以我们的思路是：
a) 使用1:00的备份文件恢复到那个时间点；
b) 重做mysql-bin.000007 到pos1870以前（pos1839）
c) 跳过pos1870-pos2193，从pos2193以后（pos2224）继续执行到log文件结束
d) 重做mysql-bin.000008 所有的日志
a) 恢复1:00的最新备份数据，作为数据起点
/usr/bin/mysql -uroot -pXXXX -v < /root/test_db_bak.sql
b) 重做mysql-bin.000007 到pos1870以前（pos1839）
/usr/bin/mysqlbinlog --stop-position=1870 --database=test_db /usr/mysql/data/mysql-bin.000007 | /usr/bin/mysql -uroot -pXXXXX -v test_db
恢复语法格式：
# mysqlbinlog mysql-bin.000007 | mysql -u用户名 -p密码 数据库名
常用选项：
--start-position 起始pos点
--stop-position 结束pos点
--start-datetime 起始时间点
--stop-datetime 结束时间点
--database 指定只恢复指定的数据库(一台主机上往往有多个数据库，只限本地log日志)
不常用选项：
--user=name Connect to the remote server as username.连接到远程主机的用户名
--password[=name] Password to connect to remote server.连接到远程主机的密码
--host=name Get the binlog from server.从远程主机上获取binlog日志
--read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志
c) 跳过pos1870-pos2193，从pos2193继续执行到log文件结束
/usr/bin/mysqlbinlog --start-position=pos2224 --database=test_db /usr/mysql/data/mysql-bin.000007 | /usr/bin/mysql -uroot -pXXXXX -v test_db
d) 重做mysql-bin.000008 所有的日志
/usr/bin/mysqlbinlog --database=test_db /usr/mysql/data/mysql-bin.000008 | /usr/bin/mysql -uroot -pXXXXX -v test_db
到此结束
雨点打透心脏的1/2处
野性酷女
刺骨的言语ヽ痛彻心扉
还没有评论，来说两句吧...