mysql相关（二）、binlog

一、MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

   一般来说开启binlog日志大概会有1%的性能损耗。但开启后可以用来恢复数据。

二、开启binlog

show variables like 'log_%';  #查看binlog是否开启，log_bin值为ON 则已开启

如未开启，则可以按下面方法开启;

编辑打开mysql配置文件/etc/my.cnf在[mysqld]区块添加

log-bin=mysql-bin(也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin)
server-id=1  # id值唯一
binlog-format = ROW  #选择行模式
binlog_format=MIXED(加入此参数才能记录到insert语句)

配置好后，重启mysql即可。

三、常用binlog命令

            1、 查看所有binlog日志列表

　　　　show master logs;

　　　　

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

　　　　show master status;

　　　　

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

　　　　flush logs;

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

　　4、重置（清空）所有binlog日志

　　　　reset master;

四、查看binlog内容

binlog为二进制文件，查看内容必须使用自带的mysqlbinlog命令查看

使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析分析：

　　　　　　　　

　　　　　　　　解释：

　　　　　　　　　　server id 1:数据库主机的服务号

　　　　　　　　　　end_log_pos 796 :sql结束时的pos节点

　　　　　　　　　　thread_id=11:线程号

也可以根据时间查看

mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

　　　　mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

　　　　参数解释：

　　　　　　a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）

　　　　　　b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）

　　　　　　c、LIMIT【offset】：偏移量(不指定就是0)

　　　　　　d、row_count :查询总条数（不指定就是所有行）

　　　　　　

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

　　　　a、查询第一个最早的binlog日志：

　　　　　　show binlog eventsG;

　　　　b、指定查询mysql-bin.000002这个文件

　　　　　　show binlog events in 'mysql-bin.000002'G;

　　　　c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：

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

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

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

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

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

五、使用binlog恢复数据

 一般mysql数据都会定时备份，比如每天凌晨5点备份一次：

mysqldump -utan -p***** -d --databases xxx > /mysql_bak/dump.sql  #-d只备份表结构

mysqldump -utan -p***** -F -R --databases xxx > /mysql_bak/$DATE.sql  #备份表数据

其中在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log,这样就会自动产生一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库'增删改操作'。

  -R:备份存储过程等

这样如果在早上9点上班后悲剧发生了，不小心删了库。。。

这个时候千万不要慌张，既然悲剧已经发生，尽快弥补

首先，通知相关人员知悉此事

然后，停止业务，避免新的数据产生

接着，登录服务器查看最后一个binlog文件，比如mysql-bin.000003 然后备份一下，这个是最重要的文件，不能再有闪失了，然后数据库内执行flush logs;生成一个新的日志文件，

然后，读取binlog文件，查看删库的时间点

mysql> show binlog events in 'mysql-bin.000003';

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                               |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4                                              |
| mysql-bin.000003 | 123 | Previous_gtids |         1 |         154 |                                                                                    |
| mysql-bin.000003 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| mysql-bin.000003 | 219 | Query          |         1 |         298 | BEGIN                                                                              |
| mysql-bin.000003 | 298 | Intvar         |         1 |         330 | INSERT_ID=22                                                                       |
| mysql-bin.000003 | 330 | Query          |         1 |         474 | use `data`; insert into `user_info` (sex,age) values (0,77777),(0,88888),(0,99999) |
| mysql-bin.000003 | 474 | Xid            |         1 |         505 | COMMIT /* xid=8138 */                                                              |
| mysql-bin.000003 | 505 | Anonymous_Gtid |         1 |         570 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| mysql-bin.000003 | 570 | Query          |         1 |         670 | create  database `xxxxx`                                                           |
| mysql-bin.000003 | 670 | Anonymous_Gtid |         1 |         735 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| mysql-bin.000003 | 735 | Query          |         1 |         827 | drop database data                                                                 |
| mysql-bin.000003 | 827 | Anonymous_Gtid |         1 |         892 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                               |
| mysql-bin.000003 | 892 | Query          |         1 |         989 | create  database `qqqq`                                                            |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)

通过分析发现，删库pos点是827，只要回复到相应pos点之前即可。

先用备份文件恢复5点之前的数据

mysql> source /root/dump.sql   #恢复表结构
source /root/mysql.2019-08-01.mysql; #恢复上次dump备份的数据

此时恢复了截止凌晨5:00前的备份数据了，接着从binlog中恢复5:00到删库之间的数据

恢复格式如下：

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

常用参数选项解释：

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

因为我们删库pos点是827，上一条sql语句pos点是735，所以可以执行

mysqlbinlog mysql-bin.000003  --stop-position=735 --database=mysqlbinlog mysql-bin.000003  --stop-position=735 --database=data | mysql -uroot -p -vdata | mysql -uroot -p -v

切记不要把结束点写成827，否则会执行827的语句再次删库

因为加了--database=data因此不会恢复二进制日志中关于其他库的相应操作，如果需要恢复其他库的相应操作，则再加上--database=xxx即可

还可以按时间区间，按pos点区间恢复，步骤原理大同小异。

六、总结：

　　所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。