甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

QQ :532337155
电话：028-86922220
 
成都网站建设设计

将想法与焦点和您一起共享

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

无聊研究binlog写了个提取单个表的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*/;
思路就是先将第一部分以及最后一部分提取出来，我们csplite文件（以COMMIT/*!*/; 为分隔标示，不熟悉csplit命令的先去熟悉一下用法），在第一以及最后一部分中间插入单个表的binlog 信息而拼接成一个符合binlog格式的日志文件


  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     |


  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


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

[root@localhost chenliang]# mysql -uroot -p`cat /etc/sqlpass ` TENNIS
[root@localhost chenliang]# sh v1.sh --binlog=mysqlbin.000019 --database=TENNIS --table=TEAMS   ---提前单表的binlog 信息

点击(此处)折叠或打开

目前创新互联公司已为成百上千家的企业提供了网站建设、域名、虚拟空间、网站运营、企业网站设计、黄骅网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

  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信息删除即可
恢复binlog信息
[root@localhost chenliang]# mysql -uroot -p`cat /etc/sqlpass ` TENNIS
检查
mysql> select * from TEAMS;
+--------+----------+----------+
| TEAMNO | PLAYERNO | DIVISION |
+--------+----------+----------+
|      1 |        6 | first    |
|      2 |       27 | second   |
|      3 |       89 | asd      |
|      4 |       89 | BBBB     |
|      5 |       89 | DDDD     |
|      6 |       67 | GGGG     |
|      7 |       77 | KKKK     |
|      8 |       77 | uuuuuu   |
|      9 |       77 | iiiiii   |
|     10 |       77 | oooo     |
+--------+----------+----------+
成功

TIP：脚本有点小bug，就是有时vcommit0 第一个begin commit可能记录的是别的表的信息而导致恢复时报错，那么就手工替换这部分的为第一个我们目标表的binlog信息再执行


分享名称：【Mysql】从binlog中找出单个表的binlog信息
文章来源：http://chengdu.cdxwcx.cn/article/ggoscc.html

其他资讯