数据库中如何使用binlog日志

叁歲伎倆 2023-03-01 12:42 5阅读 0赞

## 使用binlog日志 ##

### 1 问题 ###

利用binlog恢复库表，要求如下：  
• 启用binlog日志  
• 创建db1库tb1表，插入3条记录  
• 删除tb1表中刚插入的3条记录  
• 使用mysqlbinlog恢复删除的3条记录

### 2 步骤 ###

##### 步骤一：启用binlog日志 #####

1）调整/etc/my.cnf配置，并重启服务

[root@zhai ~]# vim  /etc/my.cnf
    [mysqld]
    ......
    log-bin-index=mysql-bin            //启用二进制日志，并指定前缀
    server_id=1
    binlog_format=STATEMENT                               
    //在Mysql5.7中，binlog日志格式默认为ROW，但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT
    ......
    [root@zhai ~]# systemctl  restart mysqld.service

2）确认binlog日志文件  
新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：

[root@zhai ~]# ls /var/lib/mysql/mysql-bin.*
    /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
       // 其中mysql-bin.index文件记录了当前保持的二进制文件列表：
    [root@zhai ~]# cat /var/lib/mysql/mysql-bin.index
    ./mysql-bin.000001

重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：

[root@zhai ~]# ls /var/lib/mysql/mysql-bin.*
    /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
    /var/lib/mysql/mysql-bin.000002
    
    [root@zhai ~]# cat /var/lib/mysql/mysql-bin.index
    ./mysql-bin.000001
    ./mysql-bin.000002

##### 步骤二：利用binlog日志重做数据库操作 #####

1）执行数据库表添加操作  
创建db1·库tb1表，表结构自定义:

mysql> CREATE DATABASE db1;
    Query OK, 1 row affected (0.05 sec)
    	
    mysql> USE db1;
    Database changed
    mysql> CREATE TABLE tb1(
        -> id int(4) NOT NULL,name varchar(24)
        -> );
    Query OK, 0 rows affected (0.28 sec)

插入3条表记录：

mysql> INSERT INTO tb1 VALUES
        -> (1,'Jack'),
        -> (2,'Kenthy'),
        -> (3,'Bob');
    Query OK, 3 rows affected (0.12 sec)
    Records: 3  Duplicates: 0  Warnings: 0

确认插入的表记录数据：

mysql> SELECT * FROM tb1;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | Jack   |
    |  2 | Kenthy |
    |  3 | Bob    |
    +----+--------+
    3 rows in set (0.00 sec)

2）删除前一步添加的3条表记录  
执行删除所有表记录操作：

mysql> DELETE FROM tb1;
    	Query OK, 3 rows affected (0.09  sec)

确认删除结果：

mysql> SELECT * FROM tb1;
    Empty set (0.00 sec)

##### 步骤三：通过binlog日志恢复表记录 #####

binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。  
根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。

1）查看mysql-bin.000002日志内容

[root@zhai ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002

2） 执行指定Pos节点范围内的sql命令恢复数据

[root@zhai ~]# mysqlbinlog \
       --start-datetime="2017-04-12 12:06:55" \ 
       --stop-datetime="2017-04-12 12:07:23" \
    /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
    Enter password:                        //验证口令

3）确认恢复结果

mysql> SELECT * FROM db1.tb1;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | Jack   |
    |  2 | Kenthy |
    |  3 | Bob    |
    +----+--------+
    3 rows in set (0.00 sec)