【Mysql】从binlog中找出单个表的binlog信息

1. 如下：
2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
   /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
   DELIMITER /*!*/;
   # at 4
   #160229 15:19:52 server id 162 end_log_pos 123 CRC32 0xbbe9b551 Start: binlog v 4, server v 5.7.9-log created 160229 15:19:52 at startup
   # Warning: this binlog is either in use or was not closed properly.
   ROLLBACK/*!*/;
   BINLOG '
   GPHTVg+iAAAAdwAAAHsAAAABAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
   AAAAAAAAAAAAAAAAAAAY8dNWEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
   AVG16bs=
   '/*!*/;
   # at 123
   #160229 15:19:52 server id 162 end_log_pos 154 CRC32 0xd473f6f4 Previous-GTIDs
   # [empty]
   # at 154
   #160229 15:18:07 server id 162 end_log_pos 219 CRC32 0xe86bb499 Anonymous_GTID last_committed=0 sequence_number=1
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 219
   #160229 15:18:07 server id 162 end_log_pos 291 CRC32 0xe1803afe Query thread_id=102887 exec_time=341 error_code=0
   SET TIMESTAMP=1456730287/*!*/;
   SET @@session.pseudo_thread_id=102887/*!*/;
   SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
   SET @@session.sql_mode=524288/*!*/;
   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 291
   #160229 15:18:07 server id 162 end_log_pos 343 CRC32 0xcfc435f2 Table_map: `test`.`ty` mapped to number 127
   # at 343
   #160229 15:18:07 server id 162 end_log_pos 463 CRC32 0xb4ef18fe Write_rows: table id 127 flags: STMT_END_F
   BINLOG '
   r/DTVhOiAAAANAAAAFcBAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAP8jXEzw==
   r/DTVh7iAAAAeAAAAM8BAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB
   QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA
   AAD+GO+0
   '/*!*/;
   # at 463
   #160229 15:18:07 server id 162 end_log_pos 494 CRC32 0xed1fb95b Xid = 53
   
   
   COMMIT/*!*/; -------到第一个commit为第一部分 为binlog必须的，缺失会报错
   # at 494
   #160229 15:18:07 server id 162 end_log_pos 559 CRC32 0xef3ac14c Anonymous_GTID last_committed=1 sequence_number=2
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 559
   #160229 15:18:07 server id 162 end_log_pos 631 CRC32 0xe834f1d8 Query thread_id=102887 exec_time=350 error_code=0
   SET TIMESTAMP=1456730287/*!*/;
   BEGIN
   /*!*/;
   # at 631
   #160229 15:18:07 server id 162 end_log_pos 683 CRC32 0xc64ac724 Table_map: `test`.`ty` mapped to number 127
   # at 683
   #160229 15:18:07 server id 162 end_log_pos 803 CRC32 0x4a5f1c75 Write_rows: table id 127 flags: STMT_END_F
   BINLOG '
   r/DTVhOiAAAANAAAAKsCAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAPJMdKxg==
   r/DTVh7iAAAAeAAAACMDAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB
   QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA
   AAB1HF9K
   '/*!*/;
   # at 803
   #160229 15:18:07 server id 162 end_log_pos 834 CRC32 0x96148022 Xid = 77
   
   
   
   COMMIT/*!*/; ------到下一个commit为第二部分，一个事物语句，可以缺失
   # at 834
   #160229 15:18:07 server id 162 end_log_pos 899 CRC32 0xa1e52982 Anonymous_GTID last_committed=2 sequence_number=3
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 899
   #160229 15:18:07 server id 162 end_log_pos 971 CRC32 0x89fc0e48 Query thread_id=102887 exec_time=359 error_code=0
   SET TIMESTAMP=1456730287/*!*/;
   BEGIN
   /*!*/;
   # at 971
   #160229 15:18:07 server id 162 end_log_pos 1023 CRC32 0xc1309696 Table_map: `test`.`ty` mapped to number 127
   # at 1023
   #160229 15:18:07 server id 162 end_log_pos 1143 CRC32 0x0993d08b Write_rows: table id 127 flags: STMT_END_F
   BINLOG '
   r/DTVhOiAAAANAAAAP8DAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAPlpYwwQ==
   r/DTVh7iAAAAeAAAAHcEAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB
   QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA
   AACL0JMJ
   '/*!*/;
   # at 1143
   #160229 15:18:07 server id 162 end_log_pos 1174 CRC32 0x7c2054fd Xid = 101
   
   
   
   。。。。。省略
   
   
   
   
   COMMIT/*!*/; -------最后一个commit到最后必须存在
   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*/;
   

1. 代码如下：
2. [root@localhost chenliang]# more v1.sh
   #!/bin/bash
   # author:Jonnychen
   # date:2016-3-1
   # set -x
   
   
   # get the key value of input
   get_key_value()
   {
   echo "$1" | sed 's/^--[A-Za-z_-]*=//'
   }
   
   
   
   
   use_help()
   {
   cat << EOF
   Info :
   Author:Michael.xu
   Usage :
   Usage : $0 [configure-options]
   -? , --help
   --binlog=<> , set binlogfile
   --database=<> , set database
   --table=<>,set table
   EOF
   }
   
   
   
   
   parse_options()
   {
   while [ $# -gt 0 ]
   do
   case "$1" in
   --binlog=*)
   vbinlog=`get_key_value "$1"`;;
   --database=*)
   vdatabase=`get_key_value "$1"`;;
   --table=*)
   vtable=`get_key_value "$1"`;;
   -? | --help)
   use_help
   exit 0;;
   *)
   echo "unknown configure option '$1'"
   exit 1;;
   esac
   shift
   done
   }
   
   
   
   
   
   
   
   
   parse_options "$@"
   echo "$vbinlog,$vdatabase,$vtable"
   
   
   
   
   if [ ! -n "$vbinlog" ];then
   use_help
   exit
   fi
   
   
   
   
   if [ ! -n "$vdatabase" ];then
   use_help
   exit
   fi
   
   
   if [ ! -n "$vtable" ];then
   use_help
   exit
   fi
   
   
   #####binlog日志格式化为txt文件
   mysqlbinlog_dir=`which mysqlbinlog`
   ${mysqlbinlog_dir} ${vbinlog}>${vbinlog}.txt
   filename=${vbinlog}.txt
   
   
   #####txt日志文件按照commit字段进行切割
   csplit $filename /COMMIT\/*\// -n 1 -s {*} -f vcommit
   
   
   cou_comm=`ls -l | grep vcommit|awk -F " " '{print $9}'|wc -l`
   let "maxseq=${cou_comm}-1"
   #echo $maxseq
   let "sec_max=$maxseq-1"
   #echo ${sec_max}
   
   
   
   
   cat vcommit0>>${vbinlog}.sql #####提取第一部分
   
   
   for((i=1;i<=${sec_max};i++))
   do
   hastab=$(grep "\`${vdatabase}\`.\`${vtable}\`" vcommit$i|wc -l ) ---循环从每个分割部分去找出单个表的信息
   echo "vcommit$i:$hastab"
   if [ $hastab -ge 1 ]
   then
   cat vcommit$i>>${vbinlog}.sql
   fi
   done
   
   
   cat vcommit$maxseq>>${vbinlog}.sql ###提取最后一部分
   
   
   
   rm -rf $filename vcommit*
   
   

1. 1：mysql> select * from TEAMS; 原始数据
2. +--------+----------+----------+
   | TEAMNO | PLAYERNO | DIVISION |
   +--------+----------+----------+
   | 1 | 6 | first |
   | 2 | 27 | second |
   | 3 | 89 | asd |
   | 4 | 89 | BBBB |
   | 5 | 89 | DDDD |
   | 6 | 67 | GGGG |
   | 7 | 77 | KKKK |
3. 
   
4. 
   
5. 模拟此时做了个备份
6. mysqldump -uroot -p`cat /etc/sqlpass ` -F TENNIS>TENNIS.sql --此时binlog到了19
   

1. 模拟做些操作
2. mysql> insert into TEAMS values(8,77,'uuuuuu');
   Query OK, 1 row affected (0.00 sec)
   
   
   mysql> insert into TEAMS values(9,77,'iiiiii');
   Query OK, 1 row affected (0.00 sec)
   
3. mysql> insert into TEAMS values(10,77,'oooo');
   Query OK, 1 row affected (0.01 sec)
   
   mysql> update TEAMS set DIVISION='pppp' where TEAMNO in(9,10); ---误操作
   Query OK, 2 rows affected (0.01 sec)
   Rows matched: 2 Changed: 2 Warnings: 0
   
   
4. 
   

点击(此处)折叠或打开

1. 查看提取出来的信息，删除update那部分即可
2. COMMIT/*!*/;
   # at 704
   #160301 13:55:35 server id 162 end_log_pos 769 CRC32 0x0593de75 Anonymous_GTID last_committed=2 sequence_number=3
   SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
   # at 769
   #160301 13:55:35 server id 162 end_log_pos 843 CRC32 0x2978f290 Query thread_id=2 exec_time=0 error_code=0
   SET TIMESTAMP=1456811735/*!*/;
   BEGIN
   /*!*/;
   # at 843
   #160301 13:55:35 server id 162 end_log_pos 897 CRC32 0xe52bc252 Table_map: `TENNIS`.`TEAMS` mapped to number 134
   # at 897
   #160301 13:55:35 server id 162 end_log_pos 946 CRC32 0x7331e254 Update_rows: table id 134 flags: STMT_END_F
   
   
   BINLOG '
   1y7VVhOiAAAANgAAAIEDAAAAAIYAAAAAAAEABlRFTk5JUwAFVEVBTVMAAwMD/gL+EgBSwivl
   1y7VVh7iAAAAMQAAALIDAAAAAIYAAAAAAAEAAgAD//gKAAAATQAAAARvb29vVOIxcw==
   '/*!*/;
   # at 946
   #160301 13:55:35 server id 162 end_log_pos 977 CRC32 0x7da650c4 Xid = 271
   COMMIT/*!*/; ----从sql中将update的binlog信息删除即可