MySQL binlog 实战

binlog简介

什么是binlog：记录所有数据库表结构变更（create、alter）以及表数据修改（insert、update、delete）的二进制文件。

binlog分类：包含二进制索引文件(.index)，用于记录所有二进制文件；二进制日志文件(.000000*)，记录数据库所有DDL和DML（除了数据查询语句）事件。

录入格式：statement、row、mixed

statement级别：每一条会修改数据的sql都会记录在binlog中，不需要记录每一行的编号，减少日志量，节约IO，由于SQL执行是有上下文的，因此在保存时需要保存相关信息。使用了函数的语句无法被记录复制。

row级别：不记录sql语句上下文相关信息，仅保存哪条记录被修改，记录单元为每一行的改动。保存信息多，日志量大

mixed级别：折中方案，普通操作使用statement记录、当无法使用statement时用row

binlog实战

更改配置

首先在配置文件/etc/my.cnf的[mysqld]下添加如下配置

# 指定二进制日志文件名称，用于记录对数据造成更改的所有语句（默认不开启，最好开启）
log-bin=master-bin
# 基于行的复制
binlog-format=ROW
# 指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中
binlog-do-db=my
# 用于标识数据库实例
server-id=123456

然后重启mysql
service mysqld restart

进入mysql验证

查看配置
mysql> show variables like ‘log_bin’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| log_bin | ON |
±--------------±------+
1 row in set (0.00 sec)
发现配置已经打开了

制造现场

制造需要恢复日志的场景，可以给指定库建表插入数据后删除表或数据

查看日志

查看binlog文件
mysql> show master logs;
±------------------±----------+
| Log_name | File_size |
±------------------±----------+
| master-bin.000001 | 3545 |
±------------------±----------+
1 row in set (0.00 sec)

查看sql事件
mysql> 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 ‘master-bin.000001’;

恢复数据语法格式：

# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

例：

mysqlbinlog  --no-defaults master-bin.000001 --start-position=817 --stop-position=1657 | mysql -uroot -p123456 my