ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 1651|回复: 7

[原创] binlog无法恢复数据

[复制链接]
论坛徽章:
4
优秀写手
日期:2015-02-11 06:00:14优秀写手
日期:2015-02-12 06:00:15慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
跳转到指定楼层
1#
发表于 2017-5-9 12:10 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 zimeng0 于 2017-5-9 16:18 编辑

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.30-log |
+------------+
1 row in set (0.00 sec)

注意,该实验一定要在开启GTID模式下实施。binlog_format=row模式下,从binlog中恢复不出数据?

[root@master data]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/ ... ation-defaults.html

配置文件如下:
[client]
port    = 3306
socket  = /tmp/mysql.sock

[mysqld]
user    = mysql
port    = 3306
socket  = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data
skip-name-resolve
character-set-server = utf8
#slow_query_log = 1
#long_query_time = 2
#slow_query_log_file = /data/slow.log
expire_logs_days = 1
log-error = /data/error.log
server-id       = 1
log-bin = /data/mysql-bin
binlog_format=row
log-slave-updates=true         
gtid-mode=on   
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=1
binlog-checksum=CRC32
master-verify-checksum=1
max_allowed_packet = 128M
slave-sql-verify-checksum=1
#binlog-rows-query-log_events=1
binlog_rows_query_log_events=1
lower_case_table_names=1
wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 128M




插入数据:
mysql> select * from db02.item;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|  100 |
+------+
4 rows in set (0.01 sec)

全库备份:
[root@master data]# mydump db02 > db02.sql

插入数据:
mysql> delete from db02.item where id=100;
Query OK, 1 row affected (0.00 sec)

mysql> insert into db02.item values(4);
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table db02.item;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from db02.item;
ERROR 1146 (42S02): Table 'db02.item' doesn't exist
mysql> exit
Bye


全库恢复:
[root@master data]# my db02 < db02.sql

mysql> select * from db02.item;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|  100 |
+------+
4 rows in set (0.00 sec)



查看binlog文件里的内容,发现增量恢复只需要mysql-bin.000002文件:
[root@master data]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows  mysql-bin.000002 > 2.sql
[root@master data]# more 2.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170509 20:00:44 server id 1  end_log_pos 120 CRC32 0x43a71b7d  Start: binlog v 4, server v 5.6.30-log created 170509 20:00:44
# at 120
#170509 20:00:44 server id 1  end_log_pos 191 CRC32 0xaa4fced1  Previous-GTIDs
# 0dbdf52e-0b1e-11e7-a0af-000c29df5573:1
# at 191
#170509 20:00:56 server id 1  end_log_pos 239 CRC32 0xeed0f3f4  GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '0dbdf52e-0b1e-11e7-a0af-000c29df5573:2'/*!*/;
# at 239
#170509 20:00:56 server id 1  end_log_pos 307 CRC32 0xbaaaddd6  Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1494331256/*!*/;
SET @@session.pseudo_thread_id=14/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 307
#170509 20:00:56 server id 1  end_log_pos 365 CRC32 0xbecb7a9a  Rows_query
# delete from db02.item where id=100
# at 365
#170509 20:00:56 server id 1  end_log_pos 412 CRC32 0x16c6a184  Table_map: `db02`.`item` mapped to number 71
# at 412
#170509 20:00:56 server id 1  end_log_pos 452 CRC32 0x06978991  Delete_rows: table id 71 flags: STMT_END_F
### DELETE FROM `db02`.`item`
### WHERE
###   @1=100 /* INT meta=0 nullable=1 is_null=0 */
# at 452
#170509 20:00:56 server id 1  end_log_pos 483 CRC32 0x62920f82  Xid = 269
COMMIT/*!*/;
# at 483
#170509 20:01:03 server id 1  end_log_pos 531 CRC32 0x3819a1af  GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '0dbdf52e-0b1e-11e7-a0af-000c29df5573:3'/*!*/;
# at 531
#170509 20:01:03 server id 1  end_log_pos 599 CRC32 0x14c0fb39  Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1494331263/*!*/;
BEGIN
/*!*/;
# at 599
#170509 20:01:03 server id 1  end_log_pos 654 CRC32 0xc728dcf2  Rows_query
# insert into db02.item values(4)
# at 654
#170509 20:01:03 server id 1  end_log_pos 701 CRC32 0x607c9744  Table_map: `db02`.`item` mapped to number 71
# at 701
#170509 20:01:03 server id 1  end_log_pos 741 CRC32 0x4c5f86a7  Write_rows: table id 71 flags: STMT_END_F
### INSERT INTO `db02`.`item`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 741
#170509 20:01:03 server id 1  end_log_pos 772 CRC32 0x34f731a7  Xid = 270
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# at 772
#170509 20:01:05 server id 1  end_log_pos 819 CRC32 0xff2765ea  Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@master data]#
指定start-postion和stop-postion从binlog中进行增量恢复:
[root@master data]# mysqlbinlog   mysql-bin.000002  --start-position=307  --stop-position=531 |mysql -uroot -p123456 db02
[root@master data]# my db02 -e "select * from db02.item"
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
[root@master data]# mysqlbinlog   mysql-bin.000002  --start-position=599  --stop-position=772 |mysql -uroot -p123456 db02
[root@master data]# my db02 -e "select * from db02.item"
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+


start-postion和stop-postion之间如果只有一条sql语句,是可以正常恢复的。但是在start-position=307到stop-position=772之间有一条delete和一条insert这两条语句。同时恢复一下看看:

首先对全库进行恢复:
[root@master data]# my db02 < ~/db02.sql
[root@master data]# my db02 -e "select * from db02.item"
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|  100 |
+------+

增量恢复:
[root@master data]# mysqlbinlog   mysql-bin.000002  --start-position=307  --stop-position=772 |mysql -uroot -p123456 db02
[root@master data]# my db02 -e "select * from db02.item"
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+


可以看到,只恢复了delete id=100这条sql,insert id=4这条语句并没有恢复出来。不知道问题出在了哪里?






论坛徽章:
0
2#
发表于 2017-5-9 13:54 | 只看该作者
mysqlbinlog   mysql-bin.000002  --start-position=599  --stop-position=772
这个日志贴出来看看

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2015-02-11 06:00:14优秀写手
日期:2015-02-12 06:00:15慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
3#
 楼主| 发表于 2017-5-9 13:57 | 只看该作者
本帖最后由 zimeng0 于 2017-5-9 14:00 编辑
清凉薄荷糖 发表于 2017-5-9 13:54
mysqlbinlog   mysql-bin.000002  --start-position=599  --stop-position=772
这个日志贴出来看看



这个是可以恢复出insert id=4的记录来的:
[root@master data]# my db02 < ~/db02.sql
[root@master data]# mysqlbinlog   mysql-bin.000002  --start-position=599  --stop-position=772 |mysql -uroot -p123456 db02
[root@master data]# my db02 -e "select * from db02.item"
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|  100 |
|    4 |
+------+

使用道具 举报

回复
论坛徽章:
0
4#
发表于 2017-5-9 14:08 | 只看该作者
zimeng0 发表于 2017-5-9 13:57
这个是可以恢复出insert id=4的记录来的:
[root@master data]# my db02 < ~/db02.sql
[root@mast ...

mysqlbinlog   mysql-bin.000002  --start-position=307  --stop-position=772
这个内容呢

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2015-02-11 06:00:14优秀写手
日期:2015-02-12 06:00:15慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
5#
 楼主| 发表于 2017-5-9 14:25 | 只看该作者
清凉薄荷糖 发表于 2017-5-9 14:08
mysqlbinlog   mysql-bin.000002  --start-position=307  --stop-position=772
这个内容呢

这是整个mysql-bin.00002的内容:

[root@master data]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows  mysql-bin.000002 > 2.sql
[root@master data]# more 2.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170509 20:00:44 server id 1  end_log_pos 120 CRC32 0x43a71b7d  Start: binlog v 4, server v 5.6.30-log created 170509 20:00:44
# at 120
#170509 20:00:44 server id 1  end_log_pos 191 CRC32 0xaa4fced1  Previous-GTIDs
# 0dbdf52e-0b1e-11e7-a0af-000c29df5573:1
# at 191
#170509 20:00:56 server id 1  end_log_pos 239 CRC32 0xeed0f3f4  GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '0dbdf52e-0b1e-11e7-a0af-000c29df5573:2'/*!*/;
# at 239
#170509 20:00:56 server id 1  end_log_pos 307 CRC32 0xbaaaddd6  Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1494331256/*!*/;
SET @@session.pseudo_thread_id=14/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 307
#170509 20:00:56 server id 1  end_log_pos 365 CRC32 0xbecb7a9a  Rows_query
# delete from db02.item where id=100
# at 365
#170509 20:00:56 server id 1  end_log_pos 412 CRC32 0x16c6a184  Table_map: `db02`.`item` mapped to number 71
# at 412
#170509 20:00:56 server id 1  end_log_pos 452 CRC32 0x06978991  Delete_rows: table id 71 flags: STMT_END_F
### DELETE FROM `db02`.`item`
### WHERE
###   @1=100 /* INT meta=0 nullable=1 is_null=0 */
# at 452
#170509 20:00:56 server id 1  end_log_pos 483 CRC32 0x62920f82  Xid = 269
COMMIT/*!*/;
# at 483
#170509 20:01:03 server id 1  end_log_pos 531 CRC32 0x3819a1af  GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '0dbdf52e-0b1e-11e7-a0af-000c29df5573:3'/*!*/;
# at 531
#170509 20:01:03 server id 1  end_log_pos 599 CRC32 0x14c0fb39  Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1494331263/*!*/;
BEGIN
/*!*/;
# at 599
#170509 20:01:03 server id 1  end_log_pos 654 CRC32 0xc728dcf2  Rows_query
# insert into db02.item values(4)
# at 654
#170509 20:01:03 server id 1  end_log_pos 701 CRC32 0x607c9744  Table_map: `db02`.`item` mapped to number 71
# at 701
#170509 20:01:03 server id 1  end_log_pos 741 CRC32 0x4c5f86a7  Write_rows: table id 71 flags: STMT_END_F
### INSERT INTO `db02`.`item`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 741
#170509 20:01:03 server id 1  end_log_pos 772 CRC32 0x34f731a7  Xid = 270
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# at 772
#170509 20:01:05 server id 1  end_log_pos 819 CRC32 0xff2765ea  Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2015-02-11 06:00:14优秀写手
日期:2015-02-12 06:00:15慢羊羊
日期:2015-03-04 14:53:332015年新春福章
日期:2015-03-06 11:58:39
6#
 楼主| 发表于 2017-5-9 15:20 | 只看该作者
问题已经解决,是因为GTID导致的,解决方法参考博客:http://blog.csdn.net/shaochenshuo/article/details/54863522

使用道具 举报

回复
论坛徽章:
3
懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18蒙奇·D·路飞
日期:2017-09-21 11:23:37
7#
发表于 2017-5-10 11:05 | 只看该作者
不错的分享,应为线上没有使用GTID,这个问题倒没有注意

使用道具 举报

回复
认证徽章
论坛徽章:
4
会员2006贡献徽章
日期:2006-04-17 13:46:34生肖徽章2007版:鼠
日期:2008-01-02 17:35:53ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41秀才
日期:2015-11-12 17:43:40
8#
发表于 2017-5-26 09:43 | 只看该作者
解决方法就是指定--skip-gtids=true吧?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
http://www.vxiaotou.com