日志（二）

• 二进制日志(bin log)

binlog可以说是MySQL中比较 重要 的日志了，在日常开发及运维过程中，经常会遇到。binlog即binary log，二进制日志文件，也叫作变更日志（update log）。
它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。
binlog主要应用场景：
    一是用于 数据恢复
    二是用于 数据复制

# 查看二进制日志
show variables like '%log_bin%';

# 永久修改二进制日志
# 修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数：
log-bin=atguigu-bin    # 日志文件名称
binlog_expire_logs_seconds=600    # 有效期
max_binlog_size=100M    # 最大大小
 
# 重新启动MySQL服务生效
 
# 临时设置二进制日志，只能是session级别
SET sql_log_bin=0;
 
# 每重启一次mysql服务器，都会生成一个新的二进制日志文件
 
# 查看生成了多少个二进制日志文件
SHOW BINARY LOGS;
 
# 执行一些增删改查操作
 
# 使用mysqlbinlog工具查看二进制日志文件的内容
mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"

• mysqlbinlog工具使用技巧

# 可查看参数帮助
mysqlbinlog --no-defaults --help
# 查看最后100行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail -100
# 根据position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A 20 '4939002'

• 方式2查看二进制日志

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
 
IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）　
FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
LIMIT [offset] ：偏移量(不指定就是0)
row_count :查询总条数（不指定就是所有行）
 
# 查看二进制日志文件
show binlog events in 'atguigu-bin.000002';
 
# 从指定event开始查看
show binlog events in 'atguigu-bin.000002' from 468;

# 从指定event开始，只展示2条记录
show binlog events in 'atguigu-bin.000002' from 468 limit 0, 2;

• 查看二进制日志的默认格式

show variables like 'binlog_format';
 
Statement: 每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
Row: 5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
Mixed: 从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

• 使用二进制日志恢复数据

# 在数据库中执行增删改查操作
# 例如某些数据误删除了，这些都会记录在二进制日志中，这时可使用二进制日志恢复数据
# 查看二进制日志文件，发现第2个文件变大了，说明之前的sql操作都记录在二进制文件中了
# 这时如果使用第2个日志文件做数据恢复，恢复操作也会记录在第2个日志文件中
# 刷新生成第3个日志文件，这样恢复操作会记录在第3个日志文件中，第2个日志文件专门用来做数据恢复

# 如果想根据时间来恢复，使用mysqlbinlog
# 如果想根据事件来恢复，使用binlog events
 
# mysqlbinlog恢复数据的语法
mysqlbinlog [option] filename|mysql –uuser -ppass;
 
# 案例如下
# 恢复从884到1729
# 指定要恢复的数据库
# 指定从那个二进制日志文件中恢复  
# 指定用户名和密码

• 方式2：根据时间恢复
  

• 删除二进制日志

# 删除指定文件名之前的日志
PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
 
# 删除指定日期之前的二进制日志
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’
 
# 删除所有二进制日志文件
reset master;