binlog
mysql的二进制日志binlog是mysql很最要的日志，它记录了所有DDL和DML语句，以事件形式记录，还包含语句所执行的消耗时间，mysql的二进制日志是事务安全型的。binlog的主要目的是提供了mysql的备份、复制和恢复能力。
- 数据恢复：当出现数据库故障或误操作导致数据丢失时，通过binlog可以找回丢失的数据。
- 数据复制：binlog可以用来实现主从复制，即在另一台MySQL服务器上复制原始MySQL服务器上的数据。
- 数据备份：通过binlog进行备份，可以只备份某个时间段后的数据，而不需要备份整个数据库。这样可以减少备份的时间和空间占用。
- 性能分析：binlog也可以用于性能分析，可以通过统计binlog中的修改操作类型和频率，来优化数据库的性能。
binlog常见操作命令
binlog的查询方式一般分为两种，一种是mysql控制台进行查询，另一种是通过mysql提供是工具mysqlbinlog进行查询。
mysql cli
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
|
#查询binlog的格式
show VARIABLES like 'binlog_format';
#查看binlog文件列表
show master logs;
或
show binary logs;
#查看binlog日志状态
show variables like '%log_bin%';
#查看最后一个binlog日志编号名称及最后一个操作事件pos结束点的值
show master status;
#刷新binlog日志缓存，此操作会产生一个新的binlog文件
flush logs;
#查看当前binlog的路径和文件名
show VARIABLES like 'log_bin_basename';
##显示MySQL二进制日志中的事件
##注意使用show binlog events时，如果当前binlog文件很大，而且没有设定limit，会导致对资源的
##过渡消耗，因为需要将binlog的全部内容处理，返回并显示出来。
#查看二进制日志文件中所有的事件
show binlog events;
#查看指定二进制日志文件中
show binlog events in 'binlog.000004';
#查看指定二进制日志文件中指定位置之后的事件 80858是在上面输出的pos列里获取的
show binlog events in 'binlog.000004' from 80858;
#查看指定二进制日志文件中指定位置之后的10行事件
show binlog events in 'binlog.000004' from 80858 limit 10;
|
mysqlbinlog
可能出现的问题：mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
两个方法可以解决这个问题
一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
二是用mysqlbinlog --no-defaults binlog.000004 命令打开
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
#指定要解析的二进制日志文件 进入到binlog日志所在目录操作
mysqlbinlog --no-defaults binlog.000004
#将解析后的sql语句输出到标椎输出
mysqlbinlog --no-defaults binlog.000005 > output.sql
#解析指定时间段内的二进制日志文件
mysqlbinlog --no-defaults --start-datetime="2022-02-01 00:00:00" --stop-datetime="2022-02-03 00:00:00" binlog.000005
#指定mysql服务器连接信息，并解析最近的二进制日志文件
mysqlbinlog --no-defaults --host=localhost --user=username --password=password --read-from-remote-server binlog文件名
#过滤特定表或列的修改
mysqlbinlog --no-defaults --database=mydb binlog.000004 | grep table_name
mysqlbinlog --no-defaults --database=mydatabase binlog.000004 | grep -E 'UPDATE `table_name` SET `column_name_1`|`column_name_2`'
#解析特定GTID范围的二进制日志文件
mysqlbinlog --no-defaults --start-position=4 --stop-position=10 binlog.000004
|
mysqlbinlog解析出来内容详解
1
2
3
|
# at 154
#220523 15:34:42 server id 1 end_log_pos 219 CRC32 0x307bcae4 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
|
- #at 154：标志着这条事件的开始位置。
- #220523 15:34:42：事件发生的时间戳，格式为YYMMDD HH:mm:ss。
- server id 1：mysql实例的唯一标识符，通常作为主库或从库的标识符。
- end_log_pos 219：事件在二进制日志中的结束位置，以字节为单位。
- CRC32 0x307bcae4：事件的CRC32校验值，用于检测日志文件的完成性。
- Anonymous_GTID：GTID事件的类型，可以标识这是基于UUID的全局事务 ID还是基于整数的事务ID。
- last_committed=0：表示这是一个新的事务。
- sequence_number=1：表示这是当前事务的第一条日志事件。
- rbr_only=no：表示这不是基于行的复制事件。
- SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;：GTID 下一步设置语句。
binlog恢复数据
正式的数据库中，应该有定时的数据备份以及开启binlog。
在执行数据恢复前，如果是生产环境，建议：
- 使用
flush logs命令，替换当前正在使用的binlog文件，这样可以将误操作定位在一个binlog文件中，便于以后的数据分析和恢复。避免操作正在被使用的binlog文件，防止发生意外情况。
- 数据恢复不要再生产库中执行，要先在临时库恢复，确认无误后，再倒回生产库。防止对数据的二次伤害。
一般来说，恢复有两个步骤：
- 在临时库中先恢复定时备份时间点数据
- 然后基于binlog恢复备份时间点之后到误操作之前的数据。
准备数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
#创建数据库
CREATE DATABASE IF NOT EXISTS test_log default charset utf8 COLLATE utf8_general_ci;
#进入数据库
use test_log;
#创建表
CREATE TABLE sync_test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入数据
insert into sync_test (id, name) values (null, 'a');
insert into sync_test (id, name) values (null, 'b');
insert into sync_test (id, name) values (null, 'c');
#查看数据
select * from sync_test;
|
模拟误删操作
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
|
#删除id为3的数据
delete from sync_test where id=3
#插入几条数据
insert into sync_test (id, name) values (null, 'd');
insert into sync_test (id, name) values (null, 'e');
insert into sync_test (id, name) values (null, 'f');
#删除表之前确认一下数据，好和恢复之后做对比
select * from sync_test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 4 | d |
| 5 | e |
| 6 | f |
+----+------+
#误删除表
DROP TABLE sync_test;
#查看表
select * from sync_test;
1146 - Table 'test_log.sync_test' doesn't exist
|
数据恢复
这里演示，就不专门找临时库进行全量备份恢复了，直接操作
数据恢复前
1
2
3
4
5
6
7
8
9
10
11
|
#查看正在使用的binlog文件
show master status;
#显示为binlog.000011
#执行flush logs 操作，生成新的binlog
flush logs;
#查看正在使用的binlog文件
show master status;
#显示为binlog.000012
|
恢复
查看创建表的事件位置和删除表的事件位置
注意：–base64-output=decode-rows -v将row格式的binlog日志中的binlog语句转换为可读的SQL语句,我们可以很直观的分析 binlog 日志。但是恢复到数据库中的时候，不能使用–base64-output=decode-rows。可能会导致数据无法正确恢复
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
|
##知道大体时间就可以根据时间确定位置信息，不知道时间的话就直接读取整个binlog文件
## --start-datetime "2023-05-22 10:00:00" 时间开始参数
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database test_log binlog.000011 | less
#输出
......
# at 384
#230524 13:32:44 server id 1 end_log_pos 449 CRC32 0x25080305 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 449
#230524 13:32:44 server id 1 end_log_pos 688 CRC32 0xa5f6aa19 Query thread_id=42 exec_time=0 error_code=0
use `test_log`/*!*/;
SET TIMESTAMP=1684906364/*!*/;
CREATE TABLE sync_test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
........
........
........
# at 2603
#230524 13:34:47 server id 1 end_log_pos 2668 CRC32 0x6a2480bd Anonymous_GTID last_committed=9 sequence_number=10 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2668
#230524 13:34:47 server id 1 end_log_pos 2798 CRC32 0xe4d49706 Query thread_id=42 exec_time=0 error_code=0
SET TIMESTAMP=1684906487/*!*/;
DROP TABLE `sync_test` /* generated by server */
/*!*/;
|
在执行操作之前都会有一个服务定位 所以恢复的位置要在执行误操作之前的一个at值那里
根据位置导出sql文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
#导出文件
#如果起始位置和终止位置不在一个binlog文件里可以指定两个binlog文件
#mysqlbinlog --no-defaults -vv --start-position=449 --stop-position=2603 --database test_log binlog.000010 binlog.000011 > test_log.sql
mysqlbinlog --no-defaults -vv --start-position=449 --stop-position=2603 --database test_log binlog.000011 > test_log.sql
#使用mysql进行恢复
mysql -u root -p < test_log.sql
或 登录mysql后操作
source /home/mysql-data/binlog/test_log.sql
#查看数据
select * from sync_test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 4 | d |
| 5 | e |
| 6 | f |
+----+------+
|
MySQL 5.7 中无论是否打开 GTID 的配置，在每次事务开启时，都首先会出 GTID 的一个事务，用于并行复制。所以在确定导出开始事务位置时，要算上这个事件。
在使用 –stop-position 导出时，会导出在指定位置的前一个事件，所以这里要推后一个事务。
对于 DML 的语句，主要结束位置要算上 COMMIT 的位置。
根据以上办法，也可以恢复删除id为3的数据，只需要找到插入时的起始位置执行一遍上述操作即可，这里不作赘述。
支付宝
微信