DBA最敬畏的大神"莫非"先生提到"可能发生的事情就一定会发生", 数据丢失的风险就像达摩克利斯之剑在头上高悬,数据作为最核心的资产来不得半点懈怠,于是我们从网络上冗余了交换机路由器, 存储上我们使用了昂贵的san(raid10)磁盘阵列,甚至使用多路cpu,和电源,甚至用8路网络绑定成1个网络, 似乎很完美, 不过还是无法避免出现人为的误操作,我们一定要敬畏莫非定律, 因为人的因素是无法预测的, 比如拔错存储电源, 误操作rm命令,网络也有可能会错乱导致无法访问, 数据丢失,这些都是我亲身经历过的事情, 以下我将从mysql角度使用最后的防线来做时间点恢复.
开启log_bin
创建数据库及测试数据
> create database test;
> use test;
> create table tb1 (id int auto_increment primary key,addtime datetime);
> insert into tb1 values(null,now());
> insert into tb1 values(null,now());
> insert into tb1 values(null,now());
> select * from tb1;
+----+---------------------+
id addtime
+----+---------------------+
1 2017-12-15 01:51:53
2 2017-12-15 01:51:56
3 2017-12-15 01:51:56
+----+---------------------+
3 rows in set (0.00 sec)
模拟误操作
此时出现了某个误操作, 直接将表清空了,损失已造成
> select now();
+---------------------+
now()
+---------------------+
2017-12-15 01:55:50
+---------------------+
1 row in set (0.00 sec)
> truncate table tb1;
了解mysqlbinlog
一般来讲我们可以通过mysqldump全备+binlog日志增量备份的方式来做时间点恢复, 不过此处我们假设知道明确的开始时间与结束时间, 将指定的时间区域binlog解析执行就可以了.
查看当前binlog日志文件
查看所有binlog信息
由于本次产生的binlog很小, 所以可以很确定需要的数据在最新的binlog文件node1_mysql-bin.000019中.
mysqlbinlog常用参数
--disable-log-bin或-D 在解析的语句中添加 set sql_log_bin=0 可以禁止恢复过程产生日志
-r 或--result-file 结果文件保存路径
-server-id 指定mysql服务器,确保是由给定服务器id的mysql服务器所生成的日志
--database=db_name,-d db_name只列出该数据库的条目
--offset=N,-o N跳过前N个条目
--start-position=N
从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=N
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。
--short-form,-s只显示日志中包含的语句,不显示其它信息。
--start-datetime=datetime
--stop-datetime=datetime
可以使用通配符解析多个文件:
mysqlbinlog hostname-bin.[0-9]*
mysqlbinlog不加参数解析出来部分的sql显示为base64编码格式,需要加上相应的参数才能显示出sql语句,注意并不是具体的操作语句,而是通过二进制转换过来的语句.
--base64-output=decode-rows -v
--verbose选项可以获取更多的可读信息,但是并不是一个原始的SQL语句(类似的)
binlog使用
1. 不使用任何参数
#BINLOG部分是真实的SQL语句,无法看到具体内容
[root@node1 binlog]# mysqlbinlog node1_mysql-bin.000019 more
.....
BINLOG '
lIYyWg8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcFTI6g=
...
2.添加-v参数
可以看到, 加-v参数后可以看到insert into语句,不过还是没看到create database及create table语句
...
BINLOG '
lIYyWg8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcFTI6g=
'/*!*/;
# at 123
#171214 22:11:32 server id 1 end_log_pos 194 CRC32 0xf13eec28 Previous-GTIDs
.....
BINLOG '
poYyWhMBAAAAMAAAAIMBAAAAAA4BAAAAAAEABHRlc3QAA3RiMQACAxIBAAJvIxpK
poYyWh4BAAAALQAAALABAAAAAA4BAAAAAAEAAgAC//wGAAAAmZ5dYvKy7e15
'/*!*/;
### INSERT INTO `test`.`tb1`
### SET
### @1=6
### @2='2017-12-14 22:11:50'
# at 432
3.添加--base64-output=DECODE-ROWS
可以看到create以及insert语句
...
create database test
...
create table tb1 (id int auto_increment primary key,
addtime datetime)
/*!*/;
# at 1686
#171215 1:51:53 server id 1 end_log_pos 1751 CRC32 0x4ab93933 GTIDlast_committed=8sequence_number=9rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# at 1751
#171215 1:51:53 server id 1 end_log_pos 1831 CRC32 0xecdfb825 Querythread_id=25exec_time=0error_code=0
BEGIN
/*!*/;
# at 1831
#171215 1:51:53 server id 1 end_log_pos 1879 CRC32 0xf2f7a65e Table_map: `test`.`tb1` mapped to number 272
# at 1879
#171215 1:51:53 server id 1 end_log_pos 1924 CRC32 0x91f5452b Write_rows: table id 272 flags: STMT_END_F
### INSERT INTO `test`.`tb1`
### SET
### @1=1
### @2='2017-12-15 01:51:53'
添加2个v参数后还可以看到参数的数据类型
mysqlbinlog node1_mysql-bin.000019 -vv --base64-output=DECODE-ROWS
### INSERT INTO `test`.`tb1`
### SET
### @1=6/*INTmeta=0 nullable=0 is_null=0 */
### @2='2017-12-14 22:11:50' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 432
开始恢复
确定所需binlog范围并应用
方法1, 确定position
第一个insert位置
注意, 实际测试时使用1879导致失去第一个insert, 需要使用insert的前一个position 1831
# at 1831
#171215 1:51:53 server id 1 end_log_pos 1879 CRC32 0xf2f7a65e Table_map: `test`.`tb1` mapped to number 272
# at 1879
#171215 1:51:53 server id 1 end_log_pos 1924 CRC32 0x91f5452b Write_rows: table id 272 flags: STMT_END_F
### INSERT INTO `test`.`tb1`
### SET
truncate前位置
# at 2558
#171215 1:55:59 server id 1 end_log_pos 2643 CRC32 0x661d959c Querythread_id=25exec_time=0error_code=0
truncate table tb1
测试查看有几个insert
mysqlbinlog node1_mysql-bin.000019 -vv --base64-output=DECODE-ROWS --start-position=1831 --stop-position=2558 grep -i insert
### INSERT INTO `test`.`tb1`
### INSERT INTO `test`.`tb1`
### INSERT INTO `test`.`tb1`
开始恢复指定数据:
由于开启了gtid, 此处需要添加参数 --skip-gtids.
mysqlbinlog node1_mysql-bin.000019 --skip-gtids --start-position=1831 --stop-position=2558 mysql --login-path=admin
数据已恢复
(root:localhost:Fri Dec 15 06:07:06 2017)[test]> select * from tb1;
+----+---------------------+
id addtime
+----+---------------------+
1 2017-12-15 01:51:53
2 2017-12-15 01:51:56
3 2017-12-15 01:51:56
+----+---------------------+
方法2:使用时间限定范围
方法1中其实也找到了我们需要的时间范围
'171215 1:51:53' 到'171215 1:55:59'
[root@node1 binlog]# mysqlbinlog --disable-log-bin --skip-gtids --start-datetime='2017-12-15 1:51:53' --stop-datetime='2017-12-15 1:55:59' --base64-output=DECODE-ROWS -vv node1_mysql-bin.000019grep -i insert
### INSERT INTO `test`.`tb1`
### INSERT INTO `test`.`tb1`
### INSERT INTO `test`.`tb1`
恢复方法类似:
mysqlbinlog node1_mysql-bin.000019 --skip-gtids --start-datetime='2017-12-15 1:51:53' --stop-datetime='2017-12-15 1:55:59' mysql --login-path=admin
领取专属 10元无门槛券
私享最新 技术干货