20151223
主题:利用mysqlbinlog恢复部分数据
（来自MySQL reference manual 4.6.8）
=============================================
利用binlog恢复可以基于时间和基于position；其实数据恢复很简单，为了演示在恢复过程中有可能出现的异常，所以演示复杂了些。
实验
基于position恢复部分数据
第一步:向表中插入数据并删除
mysql> select * from wifidog_user_source;
+----+--------+-----------+
| id | userid | sourceid |
+----+--------+-----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
+----+--------+----------+
mysql> insert into wifidog_user_source values(7,985,12);
mysql> insert into wifidog_user_source values(8,985,12);
mysql> insert into wifidog_user_source values(8,985,12);
mysql> insert into wifidog_user_source values(10,985,12)
mysql> insert into wifidog_user_source values(11,985,12);
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 7 | 985 | 12 |
| 8 | 985 | 12 |
| 9 | 985 | 12 |
| 10 | 985 | 12 |
| 11 | 985 | 12 |
+----+--------+----------+
mysql> delete from wifidog_user_source where id=7;
mysql> delete from wifidog_user_source where id=8;
mysql> delete from wifidog_user_source where id=9;
mysql> delete from wifidog_user_source where id=10;
mysql> delete from wifidog_user_source where id=11;
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
+----+--------+----------+
目的，假设id为9和10的数据是误删除的，我只想恢复id为9和10的数据
第二步: 在binlog中找到插入数据10的位置
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019|grep "^# at 3251" -A 7 -B 3
SET TIMESTAMP=1447881836/*!*/;
insert into wifidog_user_source values(10,985,12)
/*!*/;
# at 3251
#151119 5:23:56 server id 2 end_log_pos 3282 CRC32 0xeb61eb05 Xid = 283
COMMIT/*!*/;
# at 3282
#151119 5:24:03 server id 2 end_log_pos 3363 CRC32 0xb5a058b8 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881843/*!*/;
BEGIN
/*!*/;
发现在3251的地方，插入id为10的这条语句结束，但是。。。
我们恢复一下看下数据
第三步:恢复数据到id为10的位置
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --stop-position=3251|mysql
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 7 | 985 | 12 |
| 8 | 985 | 12 |
| 9 | 985 | 12 |
+----+--------+----------+
9 rows in set (0.00 sec)
怎么没有id为10的数据
？
那是因为插入数据没有commit
于是乎我们改一下stop-position使其包含commit语句，重复执行了一遍
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --stop-position=3282|mysql
ERROR 1062 (23000) at line 154: Duplicate entry '7' for key 'PRIMARY'
为何报错
？不解释
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=3251 --stop-position=3282|mysql
再看一把
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 7 | 985 | 12 |
| 8 | 985 | 12 |
| 9 | 985 | 12 |
+----+--------+----------+
9 rows in set (0.00 sec)
还是没有
？
那就重新插入一遍吧
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=3126 --stop-position=3282|mysql
再查一把
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 7 | 985 | 12 |
| 8 | 985 | 12 |
| 9 | 985 | 12 |
| 10 | 985 | 12 |
+----+--------+----------+
10 rows in set (0.00 sec)
终于有了
把11也恢复回来吧
第四步:其实这一步没有必要，因为id为11的数据不是我们想要的
# at 3282
#151119 5:24:03 server id 2 end_log_pos 3363 CRC32 0xb5a058b8 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881843/*!*/;
BEGIN
/*!*/;
# at 3363
#151119 5:24:03 server id 2 end_log_pos 3488 CRC32 0xf73eb257 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881843/*!*/;
insert into wifidog_user_source values(11,985,12)
/*!*/;
# at 3488
#151119 5:24:03 server id 2 end_log_pos 3519 CRC32 0x4936ffb2 Xid = 284
COMMIT/*!*/;
# at 3519
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=3182 --stop-position=3519|mysql
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1852402688, event_type: 108
又报错了
原来是3282写错成3182了，而3182是位于
# at 3126
#151119 5:23:56 server id 2 end_log_pos 3251 CRC32 0x10fe259b Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881836/*!*/;
insert into wifidog_user_source values(10,985,12)
/*!*/;
# at 3251
日志的中间位置，跨event了，没有包含完整的event；
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=3282 --stop-position=3519|mysql
第五步 删除id为7和8的数据
# at 3830
#151119 5:24:31 server id 2 end_log_pos 3948 CRC32 0x0c1d1051 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881871/*!*/;
delete from wifidog_user_source where id=8
/*!*/;
# at 3948
#151119 5:24:31 server id 2 end_log_pos 3979 CRC32 0xa4fe3d65 Xid = 286
COMMIT/*!*/;
# at 3979
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=3519 --stop-position=3979|mysql
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 9 | 985 | 12 |
| 10 | 985 | 12 |
| 11 | 985 | 12 |
+----+--------+----------+
9 rows in set (0.00 sec)
第六步:删除id为11的数据
# at 4521
#151119 5:24:44 server id 2 end_log_pos 4640 CRC32 0x5cde0452 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881884/*!*/;
delete from wifidog_user_source where id=11
/*!*/;
# at 4640
#151119 5:24:44 server id 2 end_log_pos 4671 CRC32 0xd7d118cb Xid = 289
COMMIT/*!*/;
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=4521|mysql
ERROR 1062 (23000) at line 188: Duplicate entry '9' for key 'PRIMARY'
为什么
？
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 7 | 985 | 12 |
| 8 | 985 | 12 |
| 9 | 985 | 12 |
| 10 | 985 | 12 |
+----+--------+----------+
10 rows in set (0.00 sec)
删除的数据又回来了
第七步:没办法删掉吧
[root@mysqlslave ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000019 --start-position=3519 --stop-position=3979>delete_recover.sql
[root@mysqlslave ~]# cat delete_recover.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
#151119 4:24:17 server id 2 end_log_pos 120 CRC32 0xc2d59959 Start: binlog v 4, server v 5.6.20-log created 151119 4:24:17 at startup
ROLLBACK/*!*/;
BINLOG '
cd5MVg8CAAAAdAAAAHgAAAAAAAQANS42LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABx3kxWEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAVmZ
1cI=
'/*!*/;
# at 3519
#151119 5:24:28 server id 2 end_log_pos 3600 CRC32 0x4d01d758 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881868/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
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 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 3600
#151119 5:24:28 server id 2 end_log_pos 3718 CRC32 0x64074fc6 Query thread_id=9 exec_time=0 error_code=0
use `linzj`/*!*/;
SET TIMESTAMP=1447881868/*!*/;
delete from wifidog_user_source where id=7
/*!*/;
# at 3718
#151119 5:24:28 server id 2 end_log_pos 3749 CRC32 0x428ce398 Xid = 285
COMMIT/*!*/;
# at 3749
#151119 5:24:31 server id 2 end_log_pos 3830 CRC32 0x9bc65069 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881871/*!*/;
BEGIN
/*!*/;
# at 3830
#151119 5:24:31 server id 2 end_log_pos 3948 CRC32 0x0c1d1051 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1447881871/*!*/;
delete from wifidog_user_source where id=8
/*!*/;
# at 3948
#151119 5:24:31 server id 2 end_log_pos 3979 CRC32 0xa4fe3d65 Xid = 286
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> source ./delete_recover.sql
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
第八步:验证结果
mysql> select * from wifidog_user_source;
+----+--------+----------+
| id | userid | sourceid |
+----+--------+----------+
| 1 | 101 | 29 |
| 2 | 35 | 1 |
| 3 | 35 | 2 |
| 4 | 35 | 13 |
| 5 | 35 | 15 |
| 6 | 211 | 12 |
| 9 | 985 | 12 |
| 10 | 985 | 12 |
+----+--------+----------+
8 rows in set (0.00 sec)
验毕！！！
总结：因为刚才的恢复id为7到11的操作也记录到这个binlog了，所以又将7和8插回来了。
一个好的习惯是将需要恢复的log管道到一个或多个文件，建议多个文件，确定无误后再source到DB。
