MySQL binlog 的恢复操作
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL binlog 的恢复操作,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6818字,纯文字阅读大概需要10分钟。
内容图文
![MySQL binlog 的恢复操作](/upload/InfoBanner/zyjiaocheng/492/468c0ff448de4ea6934883ede9741dc8.jpg)
1 insert into t1 values(1,‘xiaoxiao‘,20),(2,‘huahua‘,21),(3,‘lili‘,22); ###mysqb-bin.0000001 2 flush logs 3 insert into t1 values(4,‘xiaohong‘,18); # mysql-bin.000002 insert into t1 values(5,‘aying‘,22); 4 flush logs 5 delete from t1 where id<4; #mysql-bin.000003
这个时候我们看到我们有了3个binlog文件
show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 499 | | mysql-bin.000002 | 774 | | mysql-bin.000003 | 194 | +------------------+-----------+
/usr/local/mysql/bin/mysqlbinlog --start-position=219 mysql-bin.000001 >/tmp/ok.txt
###查看以下是日志信息
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170922 10:14:10 server id 2003309 end_log_pos 123 CRC32 0x509b4a7a Start: binlog v 4, server v 5.7.19-log created 170922 10:14:10 at startup ROLLBACK/*!*/; BINLOG ‘ 8nHEWQ9tkR4AdwAAAHsAAAAAAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADyccRZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AXpKm1A= ‘/*!*/; # at 219 #170922 10:19:50 server id 2003309 end_log_pos 290 CRC32 0x5073a29d Query thread_id=183 exec_time=0 error_code=0 SET TIMESTAMP=1506046790/*!*/; SET @@session.pseudo_thread_id=183/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 290 #170922 10:19:50 server id 2003309 end_log_pos 338 CRC32 0xca27d49b Table_map: `zst`.`t1` mapped to number 223 # at 338 #170922 10:19:50 server id 2003309 end_log_pos 421 CRC32 0xacc98577 Write_rows: table id 223 flags: STMT_END_F BINLOG ‘ RnPEWRNtkR4AMAAAAFIBAAAAAN8AAAAAAAEAA3pzdAACdDEAAwMPAwI8AASb1CfK RnPEWR5tkR4AUwAAAKUBAAAAAN8AAAAAAAEAAgAD//gBAAAACHhpYW94aWFvFAAAAPgCAAAABmh1 YWh1YRUAAAD4AwAAAARsaWxpFgAAAHeFyaw= ‘/*!*/; # at 421 #170922 10:19:50 server id 2003309 end_log_pos 452 CRC32 0x57228b9c Xid = 3692 COMMIT/*!*/; # at 452 #170922 10:20:02 server id 2003309 end_log_pos 499 CRC32 0xe31d7a38 Rotate to mysql-bin.000002 pos: 4 SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
/usr/local/mysql/bin/mysqlbinlog -v mysql-bin.000001 >/tmp/faile.txt
#### 查看以下的日志信息
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170922 10:14:10 server id 2003309 end_log_pos 123 CRC32 0x509b4a7a Start: binlog v 4, server v 5.7.19-log created 170922 10:14:10 at startup ROLLBACK/*!*/; BINLOG ‘ 8nHEWQ9tkR4AdwAAAHsAAAAAAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADyccRZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AXpKm1A= ‘/*!*/; # at 123 #170922 10:14:10 server id 2003309 end_log_pos 154 CRC32 0x2a8835fb Previous-GTIDs # [empty] # at 154 #170922 10:19:50 server id 2003309 end_log_pos 219 CRC32 0xbf230db3 GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= ‘6fb6a0c6-9dd2-11e7-a4e8-0050569c404d:1‘/*!*/; #### 这个操作,直接导致恢复整个日志文件会出错。 # at 219 #170922 10:19:50 server id 2003309 end_log_pos 290 CRC32 0x5073a29d Query thread_id=183 exec_time=0 error_code=0 SET TIMESTAMP=1506046790/*!*/; SET @@session.pseudo_thread_id=183/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 290 #170922 10:19:50 server id 2003309 end_log_pos 338 CRC32 0xca27d49b Table_map: `zst`.`t1` mapped to number 223 # at 338 #170922 10:19:50 server id 2003309 end_log_pos 421 CRC32 0xacc98577 Write_rows: table id 223 flags: STMT_END_F BINLOG ‘ RnPEWRNtkR4AMAAAAFIBAAAAAN8AAAAAAAEAA3pzdAACdDEAAwMPAwI8AASb1CfK RnPEWR5tkR4AUwAAAKUBAAAAAN8AAAAAAAEAAgAD//gBAAAACHhpYW94aWFvFAAAAPgCAAAABmh1 YWh1YRUAAAD4AwAAAARsaWxpFgAAAHeFyaw= ‘/*!*/; ### INSERT INTO `zst`.`t1` ### SET ### @1=1 ### @2=‘xiaoxiao‘ ### @3=20 ### INSERT INTO `zst`.`t1` ### SET ### @1=2 ### @2=‘huahua‘ ### @3=21 ### INSERT INTO `zst`.`t1` ### SET ### @1=3 ### @2=‘lili‘ ### @3=22 # at 421 #170922 10:19:50 server id 2003309 end_log_pos 452 CRC32 0x57228b9c Xid = 3692 COMMIT/*!*/; # at 452 #170922 10:20:02 server id 2003309 end_log_pos 499 CRC32 0xe31d7a38 Rotate to mysql-bin.000002 pos: 4 SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
./mysql -h127.0.0.1 -P3309 -uroot -p </tmp/ok.txt (因为我们导出的时候是基于位置点的,所有我们跳过了gtid) 能够还原出删除的数据 ./mysql -h127.0.0.1 -P3309 -uroot -p </tmp/faile.txt(里面有SET @@SESSION.GTID_NEXT= ‘6fb6a0c6-9dd2-11e7-a4e8-0050569c404d:1 所以我们需要跳过GTID ) 不能够还原删除的数据 把 faile.txt 导出文件里面 SET @@SESSION.GTID_NEXT= ‘6fb6a0c6-9dd2-11e7-a4e8-0050569c404d:1‘/*!*/; 删除或者注释掉可以恢复删除的数据 解决办法:1:像刚才我们看到的日志一样,我们需要把SET @@SESSION.GTID_NEXT= ‘6fb6a0c6-9dd2-11e7-a4e8-0050569c404d:1‘/*!*/; 跳过进行了,比如说我们把日志里面过滤掉或者注释掉在导入是能成功的。 2:在导入前我们需要reset我们的所有日志文件,在reset master log之前,请备份好自己的日志文件,否则后果可能很惨。 或者加入 -f 参数强制导入 这样在导入。 所以问题的重点是GTID,至于这么操作根据自己的实际情况来恢复数据吧。
MySQL binlog 的恢复操作
标签:tomat mod sql check query /tmp 复数 nod level
本文系统来源:http://www.cnblogs.com/gostars/p/7574409.html
内容总结
以上是互联网集市为您收集整理的MySQL binlog 的恢复操作全部内容,希望文章能够帮你解决MySQL binlog 的恢复操作所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。