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;

🔗 Links

• mysql docs