简单了解binlog

binlog是一个二进制格式的文件,用于记录用户对数据库更新的sql语句信息,但是不包括select和show这类操作,因为这类操作对数据本身并没有修改。然后,若操作本身并没有导致数据库发生变化,那么该操作也会写入二进制日志。

默认情况下,binlog日志是二进制格式的,不能使用查看文本工具的命令(比如,cat,vi等)查看,而使用mysqlbinlog解析查看。

一般来说开启二进制日志大概会有1%的性能损耗,其他介绍可以查看 官网文档介绍

binlog作用

binlog日志有两个最重要的使用场景

1、mysql主从复制

MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
可以参考:《mysql 主从复制 基于binlog 简单实践》

2、数据恢复

通过使用mysqlbinlog程序处理二进制日志文件,来使恢复数据。

binlog日志包括两类文件

1、二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件
2、二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。在mysql 主从复制中有见到。

开启binlog日志

1、修改配置文件

可以通过如下命令查看mysql读取的配置文件,顺序排前的优先

  1. root@ba586179fe4b:/# mysql --help|grep 'my.cnf'
  2. order of preference, my.cnf, $MYSQL_TCP_PORT,
  3. /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

编辑配置文件,然后重启mysql

  1. root@ba586179fe4b:/# vi /etc/my.cnf
  2. [mysqld]
  3. # 开启二进制日志功能,mysql-bin 是日志的基本名或前缀名
  4. log-bin=mysql-bin

2、登录数据库 mysql -u root -p123456

查看binlog日志是否开启,log_bin为ON表示开启binlog日志

  1. mysql> show variables like 'log_%';
  2. +---------------------------------+-------+
  3. | Variable_name | Value |
  4. +---------------------------------+-------+
  5. | log_bin | ON |
  6. | log_bin_trust_function_creators | OFF |
  7. | log_error | |
  8. | log_output | FILE |
  9. | log_queries_not_using_indexes | OFF |
  10. | log_slave_updates | OFF |
  11. | log_slow_queries | OFF |
  12. | log_warnings | 1 |
  13. +---------------------------------+-------+
  14. 8 rows in set (0.00 sec)

binlog日志常用操作命令

1、查看所有binlog日志列表

  1. mysql> show master logs;
  2. +------------------+-----------+
  3. | Log_name | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000001 | 8184 |
  6. | mysql-bin.000002 | 107 |
  7. +------------------+-----------+
  8. 2 rows in set (0.00 sec)

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

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000002 | 107 | | |
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)

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

  1. mysql> flush logs;
  2. Query OK, 0 rows affected (0.01 sec)

4、重置(清空)所有binlog日志

  1. mysql> reset master;
  2. Query OK, 0 rows affected (0.01 sec)

查看binlog日志内容,常用有两种方式

1、使用mysqlbinlog自带查看命令

注: binlog是二进制文件,普通文件查看器cat more vi等都无法打开,必须使用自带的 mysqlbinlog 命令查看,binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)
在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上”—no-defaults”选项

mysql数据存放在/var/lib/mysql目录,通过mysqlbinlog打开日志文件

  1. /usr/local/mysql/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000001

截取最后一次执行的日志片段

  1. # at 1778
  2. #190221 6:58:51 server id 1 end_log_pos 1899 Query thread_id=1 exec_time=0 error_code=0
  3. SET TIMESTAMP=1550732331/*!*/;
  4. INSERT INTO `proxy` (`id`, `name`) VALUES ('6', 'code')
  5. /*!*/;
  6. # at 1899
  7. #190221 6:58:51 server id 1 end_log_pos 1926 Xid = 45
  • 主要参数解释:
    • server id 1 : 数据库主机的服务号;
    • end_log_pos 1899: sql结束时的pos节点
    • thread_id=1: 线程号

2、使用show binlog events命令查看

上面这种办法读取出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 查询总条数(不指定就是所有行)

  1. mysql> show master logs;
  2. +------------------+-----------+
  3. | Log_name | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000001 | 326 |
  6. +------------------+-----------+
  7. 1 row in set (0.00 sec)
  8. mysql> show binlog events in 'mysql-bin.000001'\G;
  9. *************************** 1. row ***************************
  10. Log_name: mysql-bin.000001
  11. Pos: 4
  12. Event_type: Format_desc
  13. Server_id: 1
  14. End_log_pos: 107
  15. Info: Server ver: 5.5.62-log, Binlog ver: 4
  16. *************************** 2. row ***************************
  17. Log_name: mysql-bin.000001
  18. Pos: 107
  19. Event_type: Query
  20. Server_id: 1
  21. End_log_pos: 178
  22. Info: BEGIN
  23. *************************** 3. row ***************************
  24. Log_name: mysql-bin.000001
  25. Pos: 178
  26. Event_type: Query
  27. Server_id: 1
  28. End_log_pos: 299
  29. Info: use `codehui`; INSERT INTO `proxy` (`id`, `name`) VALUES ('6', 'code')
  30. *************************** 4. row ***************************
  31. Log_name: mysql-bin.000001
  32. Pos: 299
  33. Event_type: Xid
  34. Server_id: 1
  35. End_log_pos: 326
  36. Info: COMMIT /* xid=61 */
  37. 4 rows in set (0.00 sec)
  38. ERROR:
  39. No query specified
  40. mysql>

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

  • 查询第一个(最早)的binlog日志:
  1. mysql> show binlog events\G;
  • 指定查询 mysql-bin.000021 这个文件:
  1. mysql> show binlog events in 'mysql-bin.000021'\G;
  • 指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起:
  1. mysql> show binlog events in 'mysql-bin.000021' from 8224\G;
  • 指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,查询10条
  1. mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;
  • 指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,偏移2行,查询10条
  1. mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

通过binlog数据恢复

这个数据测试比较麻烦,我们先模拟个场景

codehui数据库会在每天凌晨1点使用计划任务进行一次备份,这里先手动执行一下备份任务。然后就有了数据库截止今天凌晨1点的数据库备份文件。早上9点和中午12点数据库都执行了增删改操作,然后下午18点直接删掉了codehui数据库,场景大概就是这样,下面进行测试数据的恢复。

先在codehui数据库插入测试数据

  1. mysql> use codehui;
  2. Database changed
  3. mysql> CREATE TABLE `test` (
  4. -> `id` int(11) NOT NULL AUTO_INCREMENT,\
  5. -> `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  6. -> PRIMARY KEY (`id`)
  7. -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  8. Query OK, 0 rows affected (0.10 sec)
  9. mysql> desc test;
  10. +-------+--------------+------+-----+---------+----------------+
  11. | Field | Type | Null | Key | Default | Extra |
  12. +-------+--------------+------+-----+---------+----------------+
  13. | id | int(11) | NO | PRI | NULL | auto_increment |
  14. | name | varchar(255) | YES | | NULL | |
  15. +-------+--------------+------+-----+---------+----------------+
  16. 2 rows in set (0.00 sec)
  17. mysql> INSERT INTO `test` (`id`, `name`) VALUES ('1', 'code');
  18. Query OK, 1 row affected (0.00 sec)
  19. mysql> INSERT INTO `test` (`id`, `name`) VALUES ('2', 'php');
  20. Query OK, 1 row affected (0.04 sec)
  21. mysql> select * from test;
  22. +----+------+
  23. | id | name |
  24. +----+------+
  25. | 1 | code |
  26. | 2 | php |
  27. +----+------+
  28. 2 rows in set (0.00 sec)
  29. mysql>

1、先备份一下数据库
备份数据库方法mysqldump,详细请参见 mysql 数据备份

  1. root@ba586179fe4b:/# /usr/local/mysql/bin/mysqldump -uroot -p123456 -B -F -R -x --master-data=2 codehui|gzip > /opt/backup/codehui.bak.sql.gz
  2. root@ba586179fe4b:/# ls /opt/backup
  3. codehui.bak.sql.gz

mysqldump备份方法参数说明:

-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
—master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

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

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000003 | 107 | | |
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)

也就是说, mysql-bin.000003 是用来记录凌晨1点之后对数据库的所有”增删改”操作。

2、早上9点对数据库进行”增”操作,新插入3条数据

  1. mysql> INSERT INTO `test` (`id`, `name`) VALUES ('3', 'java'),('4','golang'),('5','shell');
  2. Query OK, 3 rows affected (0.02 sec)
  3. Records: 3 Duplicates: 0 Warnings: 0
  4. mysql> select * from test;
  5. +----+--------+
  6. | id | name |
  7. +----+--------+
  8. | 1 | code |
  9. | 2 | php |
  10. | 3 | java |
  11. | 4 | golang |
  12. | 5 | shell |
  13. +----+--------+
  14. 5 rows in set (0.00 sec)

3、中午12点对数据库进行”改”操作,修改1条数据

  1. mysql> UPDATE `test` SET `name`='mysql' WHERE `id`='1';
  2. Query OK, 1 row affected (0.02 sec)
  3. Rows matched: 1 Changed: 1 Warnings: 0
  4. mysql> select * from test;
  5. +----+--------+
  6. | id | name |
  7. +----+--------+
  8. | 1 | mysql |
  9. | 2 | php |
  10. | 3 | java |
  11. | 4 | golang |
  12. | 5 | shell |
  13. +----+--------+
  14. 5 rows in set (0.00 sec)

4、下午18点,某程序员因心情不爽准备跑路,删掉了数据库codehui

  1. mysql> drop database codehui;
  2. Query OK, 3 rows affected (0.14 sec)

5、此刻先别慌,他忘记了我们还有大招,就是binlog日志。
先仔细查看最后一个binlog日志,并记录下关键的pos点,到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步);

我们先备份一下最后一个binlog日志

  1. root@ba586179fe4b:/# cp -v /var/lib/mysql/mysql-bin.000003 /opt/backup/
  2. '/var/lib/mysql/mysql-bin.000003' -> '/opt/backup/mysql-bin.000003'

此时执行一次刷新日志索引操作,重新开始新的binlog日志记录文件。理论说 mysql-bin.000003 这个文件不会再有后续写入了(便于我们分析原因及查找pos点),以后所有数据库操作都会写入到下一个日志文件;

  1. mysql> flush logs;
  2. Query OK, 0 rows affected (0.03 sec)
  3. mysql> show master status;
  4. +------------------+----------+--------------+------------------+
  5. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  6. +------------------+----------+--------------+------------------+
  7. | mysql-bin.000004 | 107 | | |
  8. +------------------+----------+--------------+------------------+
  9. 1 row in set (0.00 sec)

6、读取日志 分析问题,读取日志方法上面已经说到,这里使用第二种

  1. mysql> show binlog events in 'mysql-bin.000003';
  2. +------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
  3. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
  4. +------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
  5. | mysql-bin.000003 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.62-log, Binlog ver: 4 |
  6. | mysql-bin.000003 | 107 | Query | 1 | 178 | BEGIN |
  7. | mysql-bin.000003 | 178 | Query | 1 | 327 | use `codehui`; INSERT INTO `test` (`id`, `name`) VALUES ('3', 'java'),('4','golang'),('5','shell') |
  8. | mysql-bin.000003 | 327 | Xid | 1 | 354 | COMMIT /* xid=184 */ |
  9. | mysql-bin.000003 | 354 | Query | 1 | 425 | BEGIN |
  10. | mysql-bin.000003 | 425 | Query | 1 | 544 | use `codehui`; INSERT INTO `proxy` (`id`, `name`) VALUES ('1', 'my') |
  11. | mysql-bin.000003 | 544 | Xid | 1 | 571 | COMMIT /* xid=188 */ |
  12. | mysql-bin.000003 | 571 | Query | 1 | 642 | BEGIN |
  13. | mysql-bin.000003 | 642 | Query | 1 | 784 | use `codehui`; UPDATE `proxy` SET `name`='mysql' WHERE (`id`='1') AND (`name`='my') LIMIT 1 |
  14. | mysql-bin.000003 | 784 | Xid | 1 | 811 | COMMIT /* xid=190 */ |
  15. | mysql-bin.000003 | 811 | Query | 1 | 882 | BEGIN |
  16. | mysql-bin.000003 | 882 | Query | 1 | 995 | use `codehui`; UPDATE `test` SET `name`='mysql' WHERE `id`='1' |
  17. | mysql-bin.000003 | 995 | Xid | 1 | 1022 | COMMIT /* xid=192 */ |
  18. | mysql-bin.000003 | 1022 | Query | 1 | 1109 | drop database codehui |
  19. | mysql-bin.000003 | 1109 | Rotate | 1 | 1152 | mysql-bin.000004;pos=4 |
  20. +------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
  21. 15 rows in set (0.00 sec)

到这里是不很兴奋,看到了备份之后执行的所有的”增删改”记录。
通过分析,造成数据库破坏的pos点区间是介于 1022—1109 之间(这是按照日志区间的pos节点算的),只要恢复到1109前就可。

7、恢复凌晨1点的备份数据,也就是刚才手动备份的数据

  1. root@ba586179fe4b:/# cd /opt/backup/
  2. root@ba586179fe4b:/opt/backup# ls
  3. codehui.bak.sql.gz mysql-bin.000003
  4. root@ba586179fe4b:/opt/backup# gzip -d codehui.bak.sql.gz
  5. root@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < codehui.bak.sql

这样就恢复了截至当日凌晨(1:00)前的备份数据都恢复了,之后的数据通过binlog日志mysql-bin.000003进行恢复。

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | codehui |
  7. | mysql |
  8. | performance_schema |
  9. +--------------------+
  10. 4 rows in set (0.00 sec)
  11. mysql> use codehui;
  12. Reading table information for completion of table and column names
  13. You can turn off this feature to get a quicker startup with -A
  14. Database changed
  15. mysql> show tables;
  16. +-------------------+
  17. | Tables_in_codehui |
  18. +-------------------+
  19. | demo |
  20. | proxy |
  21. | test |
  22. +-------------------+
  23. 3 rows in set (0.00 sec)
  24. mysql> select * from test;
  25. +----+------+
  26. | id | name |
  27. +----+------+
  28. | 1 | code |
  29. | 2 | php |
  30. +----+------+
  31. 2 rows in set (0.00 sec)

8、从binlog日志恢复数据

恢复命令的语法格式:

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=codehui 指定只恢复codehui数据库(一台主机上往往有多个数据库,只限本地log日志)
  • 不常用选项:
    • -u —user=name 连接到远程主机的用户名
    • -p —password[=name] 连接到远程主机的密码
    • -h —host=name 从远程主机上获取binlog日志
    • —read-from-remote-server 从某个MySQL服务器上读取binlog日志

小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;

A、 完全恢复(需要手动编辑mysql-bin.000003,将那条drop语句剔除掉)

温馨提示:在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱!

  1. root@ba586179fe4b:/opt/backup# mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sql
  2. root@ba586179fe4b:/opt/backup# vi /opt/backup/000003.sql #删除里面的drop语句
  3. # 删掉drop语句前后的# at 到 /*!*/之间的内容
  4. root@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < /opt/backup/000003.sql

查看数据,已经恢复了

  1. mysql> use codehui;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  4. Database changed
  5. mysql> select * from test;
  6. +----+--------+
  7. | id | name |
  8. +----+--------+
  9. | 1 | mysql |
  10. | 2 | php |
  11. | 3 | java |
  12. | 4 | golang |
  13. | 5 | shell |
  14. +----+--------+
  15. 5 rows in set (0.00 sec)
  16. # 然后删除codehui数据库,测试第二种方法
  17. mysql> drop database codehui;
  18. Query OK, 3 rows affected (0.06 sec)
  19. # 重新导入凌晨1点的备份数据,
  20. root@ba586179fe4b:/opt/backup# mysql -uroot -p123456 -v < codehui.bak.sql

B、 指定pos结束点恢复(部分恢复):

—stop-position=571 pos结束节点(按照事务区间算,是571)

  • 注意:
    • 此pos结束节点介于”test”表原始数据与更新”name=’mysql’”之前的数据,这样就可以恢复到 更改”name=’mysql’”之前的数据了。

操作如下

  1. root@ba586179fe4b:/opt/backup# mysqlbinlog --stop-position=571 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui
  2. mysql> use codehui;
  3. Reading table information for completion of table and column names
  4. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> select * from test;
  7. +----+--------+
  8. | id | name |
  9. +----+--------+
  10. | 1 | code |
  11. | 2 | php |
  12. | 3 | java |
  13. | 4 | golang |
  14. | 5 | shell |
  15. +----+--------+
  16. 5 rows in set (0.00 sec)

C、 指定pos点区间恢复(部分恢复):

更新 “name=’mysql’” 这条数据,日志区间是Pos[882] —> End_log_pos[995],按事务区间是:Pos[811] —> End_log_pos[1022]

单独恢复 “name=’mysql’” 这步操作,可这样:
按照binlog日志区间单独恢复:

  1. root@ba586179fe4b:/opt/backup# mysqlbinlog --start-position=882 --stop-position=995 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui

按照事务区间单独恢复

  1. root@ba586179fe4b:/opt/backup# mysqlbinlog --start-position=811 --stop-position=1022 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui

如果要恢复区间内的多条日志,按事务区间恢复就可以。

  1. mysql> use codehui;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  4. Database changed
  5. mysql> select * from test;
  6. +----+--------+
  7. | id | name |
  8. +----+--------+
  9. | 1 | mysql |
  10. | 2 | php |
  11. | 3 | java |
  12. | 4 | golang |
  13. | 5 | shell |
  14. +----+--------+
  15. 5 rows in set (0.00 sec)

查看数据库恢复了”name=’mysql’”,这样就恢复了删除前的数据状态了。

D、 也可指定时间区间恢复(部分恢复):除了用pos点的办法进行恢复,也可以通过指定时间区间进行恢复,按时间恢复需要用mysqlbinlog命令读取binlog日志内容,找时间节点。

  1. # 起始时间点
  2. --start-datetime="YYYY-MM-DD H:I:S"
  3. # 结束时间点
  4. --stop-datetime ="YYYY-MM-DD H:I:S"
  5. # 用法举例
  6. mysqlbinlog --start-position=811 --start-datetime="YYYY-MM-DD H:I:S" --stop-datetime="YYYY-MM-DD H:I:S" --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui