10.MySQL的BinLog

MySQL的BinLog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 是否启用binlog日志
show variables like 'log_bin';
-- 查看binlog格式
show variables like 'binlog_format';

# 查看详细的日志配置信息
show global variables like '%log%';

# mysql数据存储目录
show variables like '%dir%';

# 查看binlog的目录
show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看主服务器使用的biglog文件及大小

# 查看最新一个binlog日志文件名称和Position
show master status;

1
2
3
4
5
6

-- 1、查看所有binlog日志列表
show master logs;
-- 2、查看最新一个binlog日志文件名称和Position
show master status;
-- 3、删除binlog
reset master;

配置文件

Windows:C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

1
2
3

max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

3、MySQL binlog的三种工作模式

1. Row level

   日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

   优点：能清楚的记录每一行数据修改的细节

   缺点：数据量太大

2. Statement level（默认）

   每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

   优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能

   缺点：容易出现主从复制不一致

3. Mixed（混合模式）

   结合了Row level和Statement level的优点

解析日志

配置MySQL的环境变量，可以在控制台直接使用命令
PS C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog.exe –base64-output=decode-rows -v DESKTOP-FANGYOU-bin.000001
导出文件：
PS C:\ProgramData\MySQL\MySQL Server 8.0\Data> mysqlbinlog.exe –base64-output=decode-rows -v DESKTOP-FANGYOU-bin.000001 -r D:\1.sql
转换为SQL语句
[root@localhost workspace]# cat 1.sql | sed -n ‘/###/p’ | sed ‘s/### //g;s//*./,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;’ | sed -r ‘s/(@4.),/\1;/g’ | sed ‘s/@[1-9]=//g’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 先去除###无用信息
[root@localhost sql]# cat a1.sql | sed -n '/^###/'p > 2.sql
# 还原语句
[root@localhost sql]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 2.sql | sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' 
UPDATE `scorpio`.`student`
SET
  @1=7
  @2='史蒂夫'
  @3=27
  @4='2021-12-16 16:35:40'
WHERE
  @1=7
INSERT INTO `scorpio`.`student`
SET
  @1=10
  @2='刘邦'
  @3=22
  @4='2021-12-20 09:29:55'

使用mysql的binlog恢复误操作(update|delete)的数据
Mysql通过binlog恢复误update的数据
mysql 通过binlog来进行忘记where误操作(delete/update)的数据恢复