mysql binlog 笔记

朴灿烈づ我的快乐病毒、 2023-07-09 02:28 3阅读 0赞

binlog 是 mysql 对操作日志的记录，本身为二进制文件，需要使用 mysqlbinlog 工具命令查看具体内容。包括三种模式：

*  STATEMENT 记录每一条修改语句，无需记录每一条SQL 语句和每一行的数据变化，减少了日志量；但某些场景下会导致 master-slave 中的数据不一致，如 sleep 函数，last\_insert\_id(),user define function等。
 *  ROW 不记录每条 SQL 的上下文信息，**仅需记录哪条数据被修改了，修改成什么样了**。缺点是会产生大量的日志，alter 的时候会导致日志暴涨。
 *  MIXED 混合模式，一般的复制使用 statement模式保存 binlog，对于 statement 模式无法复制的操作使用 row 模式保存 binlog，具体决策由 mysql 自动选择。

日志文件会根据配置进行更新，比如重启 mysql，主动执行`flush logs`，日志量、日志天数到达配置触发条件等操作，

查看当前mysql服务器是否开启 binlog

# 看到 log-format 是 ROW
    # 以及 log_bin 为 ON 的状态即可
    show variables like '%log_%';

查看 master 有哪些日志文件

show master logs;

查看当前 master 日志记录到了什么状态

show master status

查看 binlog 事件

# 日志文件参考上一步
    show binlog events in 'binlog.000002';

查看 binlog 具体内容，我们通常查看最新的 binlog 文件（编号最大的那个），在需要隔断新日志记录的时候，执行下`flush logs`来避免服务器对旧的 binlog 文件的写入。

mysqlbinlog --no-defaults --base64-output=decode-rows binlog.000002

可以使用的参数有：

*  `--start-datetime='2020-02-27 15:00:00'`
 *  `--end-datetime='2020-02-28 00:00:00'`
 *  `--start-position=1232`
 *  `--stop-position=3434`

可以根据区间或者配合 events 中显示的某条命令的 position 进行区间选择。

> 在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项

### 实际操作 ###

root@vps90:/tmp#docker run -d -p 3333:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=master-mysql mysql
    
    root@vps90:/tmp# docker ps -a
    CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                      PORTS                                                            NAMES
    698036278ab2        mysql                       "docker-entrypoint.s…"   3 seconds ago       Up 2 seconds                33060/tcp, 0.0.0.0:3333->3306/tcp                                master-mysql
    
    root@vps90:/tmp# docker exec -it 698036278ab2 /bin/bash
    
    root@698036278ab2:/# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 8.0.19 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.01 sec)
    
    mysql> show master logs;
    +---------------+-----------+-----------+
    | Log_name      | File_size | Encrypted |
    +---------------+-----------+-----------+
    | binlog.000001 |   3084516 | No        |
    | binlog.000002 |       155 | No        |
    +---------------+-----------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> create database ops;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> CREATE TABLE IF NOT EXISTS `member` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(16) NOT NULL,
    `sex` enum('m','w') NOT NULL DEFAULT 'm',
    `age` tinyint(3) unsigned NOT NULL,
    `classid` char(6) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected, 3 warnings (0.02 sec)
    
    
    mysql>  insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    
    mysql> select * from member;
    +----+-----------+-----+-----+---------+
    | id | name      | sex | age | classid |
    +----+-----------+-----+-----+---------+
    |  1 | wangshibo | m   |  27 | cls1    |
    |  2 | guohuihui | w   |  27 | cls2    |
    +----+-----------+-----+-----+---------+
    2 rows in set (0.00 sec)

手动模拟下对 mysql 的备份

root@698036278ab2:/# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/tmp/ops_$(date +%F).sql.gz
    Enter password:
    root@698036278ab2:/# ls /tmp
    ops_2020-02-27.sql.gz
    root@698036278ab2:/#

参数说明：

*  B：指定数据库
 *  F：刷新日志
 *  R：备份存储过程等
 *  x：锁表
 *  –master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！

下面开始模拟业务对数据的修改

mysql> select * from member;
    +----+-----------+-----+-----+---------+
    | id | name      | sex | age | classid |
    +----+-----------+-----+-----+---------+
    |  1 | wangshibo | m   |  27 | cls1    |
    |  2 | guohuihui | w   |  27 | cls2    |
    +----+-----------+-----+-----+---------+
    2 rows in set (0.00 sec)
    
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000003 |      155 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
    Query OK, 5 rows affected (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    mysql> select * from member;
    +----+-----------+-----+-----+---------+
    | id | name      | sex | age | classid |
    +----+-----------+-----+-----+---------+
    |  1 | wangshibo | m   |  27 | cls1    |
    |  2 | guohuihui | w   |  27 | cls2    |
    |  3 | yiyi      | w   |  20 | cls1    |
    |  4 | xiaoer    | m   |  22 | cls3    |
    |  5 | zhangsan  | w   |  21 | cls5    |
    |  6 | lisi      | m   |  20 | cls4    |
    |  7 | wangwu    | w   |  26 | cls6    |
    +----+-----------+-----+-----+---------+
    7 rows in set (0.00 sec)
    
    mysql>
    mysql>
    mysql> update ops.member set name='' where id=4;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql>
    mysql> update ops.member set name='' where id=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from member;
    +----+-----------+-----+-----+---------+
    | id | name      | sex | age | classid |
    +----+-----------+-----+-----+---------+
    |  1 | wangshibo | m   |  27 | cls1    |
    |  2 |           | w   |  27 | cls2    |
    |  3 | yiyi      | w   |  20 | cls1    |
    |  4 |           | m   |  22 | cls3    |
    |  5 | zhangsan  | w   |  21 | cls5    |
    |  6 | lisi      | m   |  20 | cls4    |
    |  7 | wangwu    | w   |  26 | cls6    |
    +----+-----------+-----+-----+---------+
    7 rows in set (0.00 sec)
    
    mysql>

可以看到数据库中内容的确被修改了。然后模拟误操作行为

mysql> drop database ops;
    Query OK, 1 row affected (0.02 sec)

1 **正式的解决步骤**：

# 到“服务器”上备份下之前的 binlog 文件
    root@698036278ab2:/var/lib/mysql# cp binlog.000003 /tmp

2 然后执行`flush logs` 来避免新的操作对原来 binlog 文件的修改，让新的操作都记录到新的 binlog 文件中。

mysql> flush logs;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show master logs
        -> ;
    +---------------+-----------+-----------+
    | Log_name      | File_size | Encrypted |
    +---------------+-----------+-----------+
    | binlog.000001 |   3084516 | No        |
    | binlog.000002 |      1152 | No        |
    | binlog.000003 |      1406 | No        |
    | binlog.000004 |       155 | No        |
    +---------------+-----------+-----------+
    4 rows in set (0.00 sec)
    
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000004 |      155 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

1.  **读取 binlog 分析问题原因**

*  `mysqlbinlog binlog.00000x --`方式，这个不是很直观
 *  `show binlog events in 'binlog.000003'` 这个更加直观。

mysql> show binlog events in 'binlog.000003';
    +---------------+------+----------------+-----------+-------------+--------------------------------------+
    | Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
    +---------------+------+----------------+-----------+-------------+--------------------------------------+
    | binlog.000003 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.19, Binlog ver: 4    |
    | binlog.000003 |  124 | Previous_gtids |         1 |         155 |                                      |
    | binlog.000003 |  155 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
    | binlog.000003 |  234 | Query          |         1 |         308 | BEGIN                                |
    | binlog.000003 |  308 | Table_map      |         1 |         372 | table_id: 118 (ops.member)           |
    | binlog.000003 |  372 | Write_rows     |         1 |         500 | table_id: 118 flags: STMT_END_F      |
    | binlog.000003 |  500 | Xid            |         1 |         531 | COMMIT /* xid=66 */                  |
    | binlog.000003 |  531 | Anonymous_Gtid |         1 |         610 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
    | binlog.000003 |  610 | Query          |         1 |         693 | BEGIN                                |
    | binlog.000003 |  693 | Table_map      |         1 |         757 | table_id: 118 (ops.member)           |
    | binlog.000003 |  757 | Update_rows    |         1 |         825 | table_id: 118 flags: STMT_END_F      |
    | binlog.000003 |  825 | Xid            |         1 |         856 | COMMIT /* xid=68 */                  |
    | binlog.000003 |  856 | Anonymous_Gtid |         1 |         935 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
    | binlog.000003 |  935 | Query          |         1 |        1018 | BEGIN                                |
    | binlog.000003 | 1018 | Table_map      |         1 |        1082 | table_id: 118 (ops.member)           |
    | binlog.000003 | 1082 | Update_rows    |         1 |        1153 | table_id: 118 flags: STMT_END_F      |
    | binlog.000003 | 1153 | Xid            |         1 |        1184 | COMMIT /* xid=69 */                  |
    | binlog.000003 | 1184 | Anonymous_Gtid |         1 |        1261 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
    | binlog.000003 | 1261 | Query          |         1 |        1362 | drop database ops /* xid=71 */       |
    | binlog.000003 | 1362 | Rotate         |         1 |        1406 | binlog.000004;pos=4                  |
    +---------------+------+----------------+-----------+-------------+--------------------------------------+
    20 rows in set (0.00 sec)

可以看出 Pos=1261 时，执行了 drop 操作。而且出问题的 Position 范围是 **1261-1362**，我们要进行数据恢复，只需要把数据恢复到 1261 之前就好。

4 **恢复数据**

先把之前备份的那个全量数据拿到，然后导入,为了更加逼真，这里新起一个服务器来测试

root@vps90:~# docker run -d -p 4444:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=slave-mysql mysql
    2a1967f3a6149be220a810b1eaccda5b84ebe398c5ed4096b5130b51d6d16a9b
    root@vps90:~# docker ps -a | grep mysql
    2a1967f3a614        mysql                       "docker-entrypoint.s…"   9 seconds ago       Up 8 seconds                33060/tcp, 0.0.0.0:4444->3306/tcp                                slave-mysql
    698036278ab2        mysql                       "docker-entrypoint.s…"   20 minutes ago      Up 20 minutes               33060/tcp, 0.0.0.0:3333->3306/tcp                                master-mysql
    root@vps90:~# docker cp 698036278ab2:/tmp/ops_2020-02-27.sql.gz 2a1967f3a614:/tmp/
    copying between containers is not supported
    root@vps90:~# docker cp 698036278ab2:/tmp/ops_2020-02-27.sql.gz /tmp
    root@vps90:~# docker cp /tmp/ops_2020-02-27.sql.gz 2a1967f3a614:/tmp
    root@vps90:~#
    
    root@vps90:~# mysql -uroot -p
    // 省略一些输出
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> source /tmp/ops_2020-02-27.sql
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (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.00 sec)
    
    Query OK, 0 rows affected (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.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Database changed
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    Query OK, 0 rows affected (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.00 sec)
    
    Query OK, 0 rows affected (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.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | ops                |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.01 sec)
    
    mysql> select * from ops;
    ERROR 1146 (42S02): Table 'ops.ops' doesn't exist
    mysql> use ops;
    Database changed
    mysql> select * from member;
    +----+-----------+-----+-----+---------+
    | id | name      | sex | age | classid |
    +----+-----------+-----+-----+---------+
    |  1 | wangshibo | m   |  27 | cls1    |
    |  2 | guohuihui | w   |  27 | cls2    |
    +----+-----------+-----+-----+---------+
    2 rows in set (0.00 sec)
    
    mysql>

然后就是根据 binlog 中出问题的语句进行 fix（就是删掉对应的 drop 语句）**注意这里是走了新的服务器**

root@vps90:/tmp# docker cp 698036278ab2:/tmp/binlog.000003.sql ./
    root@vps90:/tmp# cp binlog.000003.sql binlog.000003.sql.bake
    root@vps90:/tmp# vim binlog.000003.sql
    root@vps90:/tmp# diff binlog.000003.sql binlog.000003.sql.bake
    119a120
    > drop database ops
    root@vps90:/tmp#
    root@vps90:/tmp# docker cp binlog.000003.sql 2a1967f3a614:/tmp
    root@vps90:/tmp# docker exec -it 2a1967f3a614 /bin/bash
    root@2a1967f3a614:/# tail /tmp/binlog.000003.sql
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 1261
    #200227  7:50:50 server id 1  end_log_pos 1362 CRC32 0xd32877cf 	Query	thread_id=10	exec_time=0	error_code=0	Xid = 71
    SET TIMESTAMP=1582789850/*!*/;
    /*!*/;
    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*/;
    root@2a1967f3a614:/#
    root@2a1967f3a614:/tmp# mysql -uroot -p -v ops < binlog.000003.sql
    // 登陆进去，看下是否恢复
    mysql> select * from member;
    +----+-----------+-----+-----+---------+
    | id | name      | sex | age | classid |
    +----+-----------+-----+-----+---------+
    |  1 | wangshibo | m   |  27 | cls1    |
    |  2 |           | w   |  27 | cls2    |
    |  3 | yiyi      | w   |  20 | cls1    |
    |  4 |           | m   |  22 | cls3    |
    |  5 | zhangsan  | w   |  21 | cls5    |
    |  6 | lisi      | m   |  20 | cls4    |
    |  7 | wangwu    | w   |  26 | cls6    |
    +----+-----------+-----+-----+---------+
    7 rows in set (0.00 sec)

拿到 binlog 手动修改失败部分是一个方式，也可以使用`--stop-position=1261`的方式进行修改(前提是有database 和 member，也就是先前的备份数据ops\_2020-02-27.sql **得是存在的**)， 这一点经测试发现，还是很重要的。

/usr/bin/mysqlbinlog --stop-position=1261 --database=ops /var/lib/mysql/binlog.000003 | /usr/bin/mysql -uroot -p123456 -v ops

**整理处理流程：**

*  导入备份数据
 *  分析binlog，导出 SQL
 *  修改异常 SQL 语句
 *  修改好的 SQL 导回 mysql
 *  查看数据是否恢复

--------------------

参考文献：https://www.cnblogs.com/kevingrace/p/5907254.html