MySql--binlog

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 925 阅读时长 ≈ 3 分钟

摘要

binlog相关

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,其以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的，所以binlog内记录的是事务提交成功后的内容。

  • 查看binlog状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 查看bin‐log是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------+
| Variable_name                   | Value                                              |
+---------------------------------+----------------------------------------------------+
| log_bin                         | ON                                                 |
| log_bin_basename                | /usr/local/soft/mysql8/datas/mysql/mysql-bin       |
| log_bin_index                   | /usr/local/soft/mysql8/datas/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                                |
| log_bin_use_v1_row_events       | OFF                                                |
| sql_log_bin                     | ON                                                 |
+---------------------------------+----------------------------------------------------+

# 查看最后一个bin‐log日志的相关信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |   138605 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 会多一个最新的bin‐log日志
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 清空所有的bin‐log日志
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

  • binlog数据恢复

1
2
# 查看binlog日志，重点关注begin和commit之间的内容，
mysqlbinlog --no-defaults /usr/local/soft/mysql8/datas/mysql/mysql-bin.000001

如下为查看binlog日志的内容片段，end_log_pos后面的就是Position的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN
/*!*/;
# at 311
#220928  8:22:15 server id 3306  end_log_pos 367 CRC32 0x4f3bcf2a 	Table_map: `test`.`film` mapped to number 186
# at 367
#220928  8:22:15 server id 3306  end_log_pos 412 CRC32 0x3fc87530 	Write_rows: table id 186 flags: STMT_END_F

BINLOG '
NwQ0YxPqDAAAOAAAAG8BAAAAALoAAAAAAAEABHRlc3QABGZpbG0AAgMPAh4AAgEBAAIBISrPO08=
NwQ0Yx7qDAAALQAAAJwBAAAAALoAAAAAAAEAAgAC/wAEAAAABHRlc3Qwdcg/
'/*!*/;
# at 412
#220928  8:22:15 server id 3306  end_log_pos 443 CRC32 0x0712832c 	Xid = 17662
COMMIT/*!*/;

常用的恢复方法

1
2
3
4
5
6
7
8
# 恢复全部数据
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p test(数据库名)

# 恢复指定位置数据
mysqlbinlog --no-defaults --start-position="367" --stop-position="443" mysql-bin.000001 | mysql -uroot -p test(数据库)

# 恢复指定时间段数据
mysqlbinlog --no-defaults --stop-date= "2022‐09‐28 08:22:15" --start-date= "2022‐09‐28 08:22:15" mysql-bin.000001 | mysql -uroot -p test(数据库)

一个数据恢复的小示例

  • 假设数据库每天都有全量备份

1
mysqldump -uroot -ppassword -B dbName > $bakpath/dbName_$(date +%F).sql

  • 突然某天数据库出现异常，比如关闭后无法重启，此时可以通过全量备份和binlog日志进行数据恢复

  • 先将binlog日志备份

1
cp /usr/local/soft/mysql8/datas/mysql/mysql-bin.* $bakpath/

  • 清空mysql的datadir目录

1
rm -rf /usr/local/soft/mysql8/datas/mysql/*

  • 重新初始化数据库并启动

1
2
mysqld --user=mysql --initialize-insecure
systemctl start mysqld

  • 导入全量备份，假设查看备份完成时间是 2022-11-10 12:30:30

1
mysql < $bakpath/dbName_$(date +%F).sql

  • 导入binlog数据

1
2
# 这里就从备份完成时间开始导入，实际上这个时间并不准确，因为备份文件中并没有记录最后的position，所以很难比较出准确的时间或者position
mysqlbinlog --no-defaults --start-date="2022-11-10 12:30:30" $bakpath/mysql-bin.* | mysql dbName

注意
如果数据库开启了GTID，需要先关闭再导入binlog数据，否则不能导入binlog数据
临时关闭方式: 更改 GTID_MODE 状态顺序为 ON<->ON_PERMISSIVE<->OFF_PERMISSIVE<->OFF ，需要按照顺序依次改变。

1
2
3
4
5
6
7
8
# 查看 GTID_MODE 当前状态
show global variables like 'gtid_mode';
# 修改GTID_MODE 状态为 ON_PERMISSIVE
set @@GLOBAL.GTID_MODE=ON_PERMISSIVE;
# 修改GTID_MODE 状态为 OFF_PERMISSIVE
set @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;
# 修改GTID_MODE 状态为 OFF
set @@GLOBAL.GTID_MODE=OFF;