【mysql】binlog日志

目录

1.1 基本说明

• 1.全称binary log，二进制日志
• 2.记录了所有的DDL语句（Data Definition Language数据定义语言）和DML语句（Data Manipulation /məˌnɪpjuˈleɪʃn/ Language数据操作语言）
• 3.不包括数据查询语句（select、show）
• 4.作用：灾难时的数据恢复；mysql的主从复制
• 5.mysql8.0版本，默认二进制日志是开启的
• 6.使用sql：show variables like ‘%log_bin%’;参数log_bin：ON表示开启；参数log_bin_basename：日志位置

1.2 binlog日志格式

• 1.statement：基于sql语句的日志记录，记录的是sql语句，对数据进行修改的sql都会记录在日志文件中
• 2.row：基于行的日志记录，记录的是每一行的数据变更，默认是基于行的日志记录
• 3.mixed：混合statement和row两种格式，默认采用statement，在某些特殊情况下会自动切换为row进行记录。这些情况是NDB引擎，表的增删改的DML操作会以row格式记录、SQL语句里包含UUID()函数、SQL语句里使用了用户定义函数(UDF)、包含了insert delayed语句、使用了临时表、自增字段被更新

1.3 binlog日志查看

• 1.日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog查看
• 2.mysqlbinlog -d logfilename 指定数据库名称，只列出指定的数据库相关操作
• 3.mysqlbinlog -o 忽略掉日志中的前n行命令
• 4.mysqlbinlog -v 将行事件（数据变更）重构为sql语句
• 5.mysqlbinlog -vv 将行事件（数据变更）重构为sql语句，并输出注释信息

1.4 binlog日志删除

• 1.二进制日志长时间不清除会占用大量磁盘空间
• 2.reset master指令：删除全部binlog日志，删除之后，日志编号，将从binlog.000001重新开始
• 3.purge master logs to 'binlog.000003’指令：删除000003编号之前的所有日志
• 4.purge master logs before 'yyyy-mm-dd hh24:mi:ss’指令：删除日志为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志
• 5.在mysq的配置文件中配置二进制日志的过期时间，设置之后二进制日志过期会自动删除
• 6.show variables like ‘%binlog_expire_logs_seconds’;显示当前二进制日志的过期时间，单位秒

1.5 binlog操作示例

• 1.查询当前日志格式

show variables like '%binlog_format%';

• 2.查看是否开启了二进制日志

// 有结果表示已开启，若报错则表示未开启
show binary logs;

// 开启binlog, 修改C:\ProgramData\MySQL\MySQL Server 8.0的my.init配置文件
// 日志文件格式
log-bin="DESKTOP-EVRO81H-bin"
// 日志格式
binlog-format=ROW

• 3.查询二进制日志存放位置

show variables like '%log_bin_basename%';

• 4.mysqlbinlog命令直接查看日志文件

// 切换到二进制日志文件目录下
cd C:\ProgramData\MySQL\MySQL Server 8.0\Data

// 查看日志文件
mysqlbinlog DESKTOP-EVRO81H-bin.000024
// 按row日志格式基本看不懂，所以要加-v参数

// 能看到一些update语句和insert语句以及表操作的语句
mysqlbinlog -v DESKTOP-EVRO81H-bin.000024

• 5.设置日志格式为STATEMMENT

// 修改C:\ProgramData\MySQL\MySQL Server 8.0的my.init配置文件，保存后重启
// 日志格式
binlog-format=STATEMENT

• 6.执行条insert语句后执行mysqlbinlog命令

// cd C:\ProgramData\MySQL\MySQL Server 8.0\Data
// 执行mysqlbinlog DESKTOP-EVRO81H-bin.000025，不需要加-v就能看到insert语句，因为是sql语句
mysqlbinlog DESKTOP-EVRO81H-bin.000025

• 7.删除binlog日志

// 删除000025之前的日志
purge master logs to 'DESKTOP-EVRO81H-bin.000025';

// reset master清除所有日志
reset master;

• 8.查看binlog日志过期时间

// 默认30天过期，在my.int设置binlog_expire_logs_seconds便可调整过期时间
show variables like '%binlog_expire%';