“开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 3 天，点击查看活动详情”

一、binlog基本概念

binlog是一个二进制格式的文件，可以说是MySQL最重要的日志，它记录了所有的DDL(create、alter、drop)和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。需要注意的一点是，即便update操作没有造成数据变化，也是会记入binlog。
默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看

二、binlog使用场景

主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
数据恢复：通过mysqlbinlog工具来恢复数据。

三、binlog相关配置

备注：我是在docker中装的mysql，操作都是在容器内进行的。

1.修改mysql配置文件my.cnf，在末尾添加代码：

[mysqld]
log-bin=mysql-bin
server-id=1s

2.重启docker容器，使配置生效

docker restart 容器ID

备注：每次重启服务，mysql都会自动调用flush logs，创建一个新的binlog日志。

3.查看binlog日志是否开启

mysql -uroot -p

show variables like 'log_%';

如图所示mysql的binlog开启成功。

四、binlog日志常用操作命令

1.查看所有binlog日志列表

show master logs;

2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

show master status;

3.flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件

flush logs；

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4.清空所有的binlog日志

reset master；

五、查看binlog日志内容

1.使用mysqlbinlog命令查看

mysqlbinlog mysql-bin.000001

2.在MySQL中使用show命令查询

命令格式：

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 'mysql-bin.000001'\G;

上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！如下操作示例：

1）查询第一个(最早)的binlog日志：

show binlog events\G;

2）指定查询 mysql-bin.000001这个文件：

show binlog events in 'mysql-bin.000001'\G;

3）指定查询 mysql-bin.000001这个文件，从pos点:624开始查起：

show binlog events in 'mysql-bin.000001' from 624\G;

4）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

5）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条

show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

使用binlog日志恢复MySQL数据

1.创建test数据库

create database test;

2.查看数据库是否创建成功

show databases;

3.创建member表，查看表是否创建成功

use test;
CREATE TABLE IF NOT EXISTS `member` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`classid` varchar(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show tables;

4.插入数据

insert into member(`name`,`age`,`classid`) values('LL',24,'cls1'),('HH',23,'cls2');
select * from member;

5.误删除数据库，数据全部丢失

drop database test;

6.查看最新一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；

7.接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。

flush logs;

8.读取binlog日志，分析问题。

9.从binlog日志恢复数据，恢复命令的语法格式：

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

常用参数选项解释：

--start-position=687 起始pos点
--stop-position=772 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

不常用选项：

-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志

mysqlbinlog --start-position=311 --database=test /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -proot  -v

Mysql-binlog介绍及使用