这是我参与8月更文挑战的第28天，活动详情查看：8月更文挑战
前言
mysql-binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（(除了数据查询语句）信息。可以使用mysqlbin命令查看二进制日志的内容。
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。 Binlog日志的两个最重要的使用场景
- MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
- 数据恢复：通过使用 mysqlbinlog工具来使恢复数据
1 、Binlog概念
Binlog 是逻辑日记，用于记录数据库执行的写入操作（查询不记录）信息，Server层记录和引擎层无关，并且是以追加方式进行写入，可以通过参数 max_binlog_size 设置每个Binlog文件的大小，文件大小达到设定值时会生成新的文件来保存日记。
2、 Binlog 作用
在实际应用中，主要用在两个场景：主从复制和数据恢复
- 主从复制场景：在Master主端开启Binlog，将Binlog发生到各个Slave从端，Slave从端重放Binlog从而达到主从数据一致
- 数据恢复场景：通过使用 mysqlbinlog 工具来恢复数据
3、 Binlog 记录过程及刷盘时机
Binlog何时记录将在第六点进行介绍，大致记录过程是先写Binlog Buffer，然后通过刷盘时机，控制刷入OS Buffer，控制fsync()进行写入Binlog File日记磁盘的过程。
对于Binlog，MySQL是通过参数sync_binlog参数来控制刷盘时机，取值是0、1和N三种值。0表示由系统自行判断何时调用sync()写入磁盘；1表示每次事务commit都要调用fsync()写入磁盘；N表示每N个事务，才会调用fsync()写入磁盘。
查看binlog日志
查看某个binlog日志内容，常用有两种方式：
1.使用mysqlbinlog自带查看命令法：
注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看
binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)
在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项
# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013
下面截取一个片段分析：
...............................................................................
# at 552
#131128 17:50:46 server id 1 end_log_pos 665 Query thread_id=11 exec_time=0 error_code=0 ---->执行时间:17:50:46；pos点:665
SET TIMESTAMP=1385632246/*!*/;
update zyyshop.stu set name='李四' where id=4 ---->执行的SQL
/*!*/;
# at 665
#131128 17:50:46 server id 1 end_log_pos 692 Xid = 1454 ---->执行时间:17:50:46；pos点:692
...............................................................................
注: server id 1 数据库主机的服务号；
end_log_pos 665 pos点
thread_id=11 线程号
2.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
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 查询总条数(不指定就是所有行)
截取部分查询结果：
*************************** 20. row ***************************
Log_name: mysql-bin.000021 ----------------------------------------------> 查询的binlog日志文件名
Pos: 11197 ----------------------------------------------------------> pos起始点:
Event_type: Query ----------------------------------------------------------> 事件类型：Query
Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
*************************** 21. row ***************************
Log_name: mysql-bin.000021
Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
Event_type: Query
Server_id: 1
End_log_pos: 11417
Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
*************************** 22. row ***************************
Log_name: mysql-bin.000021
Pos: 11417
Event_type: Query
Server_id: 1
End_log_pos: 11510
Info: use `zyyshop`; DROP TABLE IF EXISTS `type`
这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
A.查询第一个(最早)的binlog日志：
mysql> show binlog events\G;
B.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021'\G;
C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224\G;
D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;
E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
show variables like 'log_bin';
# 查看详细的日志配置信息
show global variables like '%log%';
# mysql数据存储目录
show variables like '%dir%';
# 查看binlog的目录
show global variables like "%log_bin%";
# 查看当前服务器使用的biglog文件及大小
show binary logs;
# 查看主服务器使用的biglog文件及大小
# 查看最新一个binlog日志文件名称和Position
show master status;
# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 查看 binlog 内容
show binlog events;
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';
# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3;
# 删除当前的binlog文件
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';
# 删除指定日志文件
purge master logs to 'master.000003';
清理binlog
reset master; //删除master的binlog，线上不要使用
purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件
如果想直接删除binlog
rm binlog.000002
(不要删除当前正在使用的binlog，也就是编号最大的binlog)
[root@namenode mysql3306]# cat mysql-bin.index 不会自动更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002\
恢复
恢复是binlog的两大主要作用之一，接下来通过实例演示如何利用binlog恢复数据：
a.首先，看下当前binlog位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1847 | | | |
+------------------+----------+--------------+------------------+-------------------+
b.向表tb_person中插入两条记录：
insert into tb_person set name="person_1", address="beijing", sex="man", other="test-1";
insert into tb_person set name="person_2", address="beijing", sex="man", other="test-2";
c.记录当前binlog位置：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 2585 | | | |
+------------------+----------+--------------+------------------+-------------------+
d.查询数据
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
e.删除一条: delete from tb_person where name ="person_2";
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
+----+----------+---------+-----+--------+
f. binlog恢复（指定pos点恢复/部分恢复）
mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
mysql> source /var/lib/mysql/3306/test.sql
d.数据恢复完成
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
e.总结
恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已
