mysql binlog

🧙 Questions

mysql binlog使用

☄️ Ideas

• binlog路径： /data/mysql/data
• binlog路径： /var/lib/mysql/binlog

查看binlog是否开启

-- log_bin ON 开启
show variables like 'log_%';
show variables like 'binlog_format';
-- 查看binlog最大size
show variables like 'max_binlog_%';

开启binlog

docker启动的mysql，默认是开启的

sudo vim /data/mysql/conf.d/my.cnf

log-bin = 0 为关闭

[mysqld]
log_bin = /var/lib/mysql/binlog
binlog_format = ROW 
expire_logs_days = 30
server_id = 1

查看binlog文件

-- 查询binlog文件列表
show binary logs
-- 展开第一个binlog，即最老的一个binlog文件
show binlog events;
-- 展开指定binlog文件
show binlog events in 'binlog.000027'
-- 只有mysql:8.0才有
show master status

binlog权限

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%';
Flush Privileges;

查看用户是否有读取binlog权限

-- SHOW GRANTS FOR 'username'@'host';
-- 能查出来说明就是有权限的
SHOW BINARY LOGS;

查看binlog

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

# 看看binlog是否加密
show variables like 'binlog_encryption';
# 解开
set GLOBAL binlog_encryption = OFF
show variables like 'binlog_format';
SHOW MASTER STATUS;
cd /var/lib/mysql
mysqlbinlog --no-defaults /var/lib/mysql/binlog.000003
mysqlbinlog --no-defaults /var/lib/mysql/binlog.000003 | grep -A 5 -B 5 'cdc_target'
mysqlbinlog --no-defaults --database=ispong_db /var/lib/mysql/binlog.000003
mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/binlog.000003 > /tmp/output.sql
mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-datetime="2024-12-26 10:13:32" --stop-datetime="2024-12-26 10:13:47" /var/lib/mysql/binlog.000003 > /tmp/output.sql
cat /tmp/output.sql

一般搜索 # at 就可以查看记录

# at 1274 这是position
#241226  9:32:29 server id 1  end_log_pos 1322 CRC32 0x4809c73c         Write_rows: table id 182 flags: STMT_END_F
### INSERT INTO `isxcode_db`.`cdc_target`
### SET
###   @1='张三'
###   @2=12

# at 1322 这是position
#241226  9:32:29 server id 1  end_log_pos 1353 CRC32 0x924ff7cd         Xid = 427
COMMIT/*!*/;

🔗 Links

• mysql docs