提取mysql的binlog文件用作数据恢复

灰太狼 2022-05-25 12:57 231阅读 0赞

binlog 基本认识  
    MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。  
  
  
    一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:   
    其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。   
    其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。  
      
    二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000\*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。  
  
  
一、开启binlog日志：  
    vi编辑打开mysql配置文件  
    \# vi /usr/local/mysql/etc/my.cnf  
    在\[mysqld\] 区块  
    设置/添加 log-bin=mysql-bin  确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；  
  
  
二、也可登录mysql服务器，通过mysql的变量配置表，查看二进制日志是否已开启 单词：variable\[ˈvɛriəbəl\] 变量  
  
  
    登录服务器  
    \# /usr/local/mysql/bin/mysql -uroot -p123456  
  
  
    mysql> show variables like 'log\_%'; 】  
  
  
四、查看某个binlog日志内容，常用有两种方式：  
  
  
    1.使用mysqlbinlog自带查看命令法：  
      注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看  
          binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)  
      在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项  
      
      \# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013  
        下面截取一个片段分析：  
  
  
         ...............................................................................  
         \# at 552  
         \#131128 17:50:46 server id 1  end\_log\_pos 665   Query   thread\_id=11    exec\_time=0     error\_code=0 ---->执行时间:17:50:46；pos点:665  
         SET TIMESTAMP=1385632246/\*!\*/;  
         update zyyshop.stu set name='李四' where id=4              ---->执行的SQL  
         /\*!\*/;  
         \# at 665  
         \#131128 17:50:46 server id 1  end\_log\_pos 692   Xid = 1454 ---->执行时间:17:50:46；pos点:692   
         ...............................................................................  
  
  
         注: server id 1     数据库主机的服务号；  
             end\_log\_pos 665 pos点  
             thread\_id=11    线程号  
  
  
  
  
    2.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：  
  
  
      mysql> show binlog events \[IN 'log\_name'\] \[FROM pos\] \[LIMIT \[offset,\] row\_count\];  
  
  
             选项解析：  
               IN 'log\_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)  
               FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)  
               LIMIT \[offset,\] 偏移量(不指定就是0)  
               row\_count       查询总条数(不指定就是所有行)  
  
  
             截取部分查询结果：  
             \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 20. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Log\_name: mysql-bin.000021  ----------------------------------------------> 查询的binlog日志文件名  
                     Pos: 11197 ----------------------------------------------------------> pos起始点:  
              Event\_type: Query ----------------------------------------------------------> 事件类型：Query  
               Server\_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的  
             End\_log\_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)  
                    Info: use \`zyyshop\`; INSERT INTO \`team2\` VALUES (0,345,'asdf8er5') ---> 执行的sql语句  
             \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 21. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Log\_name: mysql-bin.000021  
                     Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)  
              Event\_type: Query  
               Server\_id: 1  
             End\_log\_pos: 11417  
                    Info: use \`zyyshop\`; /\*!40000 ALTER TABLE \`team2\` ENABLE KEYS \*/  
             \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 22. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Log\_name: mysql-bin.000021  
                     Pos: 11417  
              Event\_type: Query  
               Server\_id: 1  
             End\_log\_pos: 11510  
                    Info: use \`zyyshop\`; DROP TABLE IF EXISTS \`type\`  
  
  
      这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；  
        
      A.查询第一个(最早)的binlog日志：  
        mysql> show binlog events\\G;   
      
      B.指定查询 mysql-bin.000021 这个文件：  
        mysql> show binlog events in 'mysql-bin.000021'\\G;  
  
  
      C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：  
        mysql> show binlog events in 'mysql-bin.000021' from 8224\\G;  
  
  
      D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条  
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\\G;  
  
  
      E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条  
        mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\\G;  
  
  
过滤查询输出；  
root@localhost bin\]\# mysqlbinlog --set-charset=utf-8  /usr/local/mysql/mysqlbinlog/mysql-relay-bin.000010 | grep INSERT