小趙同學
0%

MySQL Binary Log

发表于 更新于
binlog日志由包含“事件”的文件组成，这些文件 描述了对数据库内容的修改

概述

binlog是什么？

https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以”事务”的形式保存在磁盘中；

binlog的作用

  • 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
  • 数据恢复：通过mysqlbinlog工具恢复数据
  • 增量备份

文件类型

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

开启binlog

1. 编辑 my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 如不确定my.cnf配置文件位置，可使用以下两种方法；

mysql> select @@basedir;

+--------------------------------------------+
| @@basedir                                  |
+--------------------------------------------+
| /usr/local/mysql-8.0.23-macos10.15-x86_64/ |
+--------------------------------------------+
1 row in set (0.00 sec)

shell> mysql --help | grep 'Default options' -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 开启binlog，共两种方式，推荐第二种，更简便。

#第一种方式:
#开启binlog日志
log_bin=ON
#binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index
#配置serverid
server-id=1

#第二种方式:
#此一行等同于上面log_bin三行
log-bin=/var/lib/mysql/mysql-bin
#配置serverid
server-id=1

2. 重启MySQL

3. 检查确认

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
43
44
45
46
47
48
49
50
51
52
53
54
55
# 查看日志开启状态
mysql> show variables like 'log_bin%';

+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /usr/local/mysql/data/binlog       |
| log_bin_index                   | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.01 sec)

# 查看所有binlog日志列表
mysql> show binary logs;

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000032 |   1657759 | No        |
| binlog.000033 |     45693 | No        |
| binlog.000034 |    527514 | No        |
| binlog.000035 |    573115 | No        |
| binlog.000036 |   2248902 | No        |
| binlog.000037 |    342330 | No        |
| binlog.000038 |   3067113 | No        |
| binlog.000039 |   1795100 | No        |
| binlog.000040 |      1477 | No        |
| binlog.000041 |       179 | No        |
| binlog.000042 |    435497 | No        |
| binlog.000043 |   3771591 | No        |
| binlog.000044 |      9178 | No        |
| binlog.000045 |    235614 | No        |
| binlog.000046 |    654699 | No        |
| binlog.000047 |     13053 | No        |
| binlog.000048 |       156 | No        |
+---------------+-----------+-----------+
17 rows in set (0.03 sec)

# 查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点 
mysql> SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000048 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

# 刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果 
flush logs;

# 清空所有binlog日志 
reset master;

配置binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写
log-bin = /data/mysql/logs/mysql-bin.log
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m

重启MySQL生效，如果不方便重启服务，也可以直接修改对应的变量即可。

删除binlog

删除7天前的

1
purge binary logs before now() - interval 7 day;

删除指定日志文件之前的

1
2
# 会保留'mysql-bin.000034'
purge binary logs to 'mysql-bin.000034';

删除某个时间点之前的

1
purge binary logs before '2021-07-01 00:00:00';

demo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 可以看到六月份的日志比较多
zhaohongye:data root# ls -al binlog.*
-rw-r-----  1 _mysql  _mysql   527514 Jun  7 20:57 binlog.000034
-rw-r-----  1 _mysql  _mysql   573115 Jun 10 09:17 binlog.000035
-rw-r-----  1 _mysql  _mysql  2248902 Jun 11 09:42 binlog.000036
-rw-r-----  1 _mysql  _mysql   342330 Jun 11 11:27 binlog.000037
-rw-r-----  1 _mysql  _mysql  3067113 Jun 16 10:49 binlog.000038
-rw-r-----  1 _mysql  _mysql  1795100 Jun 18 09:41 binlog.000039
-rw-r-----  1 _mysql  _mysql     1477 Jun 21 08:28 binlog.000040
-rw-r-----  1 _mysql  _mysql      179 Jun 21 08:44 binlog.000041
-rw-r-----  1 _mysql  _mysql   435497 Jun 23 09:36 binlog.000042
-rw-r-----  1 _mysql  _mysql  3771591 Jun 24 08:31 binlog.000043
-rw-r-----  1 _mysql  _mysql     9178 Jun 25 09:33 binlog.000044
-rw-r-----  1 _mysql  _mysql   235614 Jun 28 08:32 binlog.000045
-rw-r-----  1 _mysql  _mysql   654699 Jun 30 09:32 binlog.000046
-rw-r-----  1 _mysql  _mysql    13053 Jul  5 08:38 binlog.000047
-rw-r-----  1 _mysql  _mysql      156 Jul  5 08:39 binlog.000048
-rw-r-----  1 _mysql  _mysql      240 Jul  8 10:14 binlog.index

删除所有binlog日志

1
2
3
reset master;

#序号重置为000001 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000047 |     13053 | No        |
| binlog.000048 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0.01 sec)
 
mysql> reset master;
Query OK, 0 rows affected (0.10 sec)
 
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

刷新日志

1
2
#刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果 
flush logs;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
 
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
 
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       200 | No        |
| binlog.000002 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

查看自动清理规则

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 默认二进制日志过期时间为 2592000 秒，等于 30 天（30*24*60*60 秒）

mysql> show variables like '%expire_logs_%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
| expire_logs_days           | 0       |
+----------------------------+---------+
2 rows in set (0.01 sec)
 
mysql> set global binlog_expire_logs_seconds=24*60*60;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show variables like '%expire_logs_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| binlog_expire_logs_seconds | 86400 |
| expire_logs_days           | 0     |
+----------------------------+-------+
2 rows in set (0.01 sec)

查看binlog

使用cat、more、vim等命令查看binlog日志，显示乱码

正确查看的两种方式：

  • shell命令行，mysqlbinlog,例如：mysqlbinlog /usr/local/mysql/data/binlog.00000
  • mysql命令行, show binlog events,例如：show binlog events in 'binlog.000047';

mysqlbinlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
zhaohongye:data root# mysqlbinlog --skip-gtids /usr/local/mysql/data/binlog.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210708 10:52:23 server id 1  end_log_pos 125 CRC32 0x2844b0e8 	Start: binlog v 4, server v 8.0.23 created 210708 10:52:23 at startup
ROLLBACK/*!*/;
BINLOG '
Z2jmYA8BAAAAeQAAAH0AAAAAAAQAOC4wLjIzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABnaOZgEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigB6LBEKA==
'/*!*/;
/*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
# at 125
# at 156
#210708 11:01:14 server id 1  end_log_pos 200 CRC32 0xd046c0d9 	Rotate to binlog.000002  pos: 4
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1
2
3
4
注: 
server id 1     数据库主机的服务号；
end_log_pos 665 pos点
thread_id=11    线程号

show binlog events

1
2
3
4
5
6
7
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       查询总条数(不指定就是所有行)

根据binlog恢复数据

https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-binlog.html

shell> mysqlbinlog binlog_files | mysql -u root -p

多个binlog文件恢复数据

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

或者

1
2
3
shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"