mysql二进制日志详解

mysql二进制日志详解

一、什么是二进制日志

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

开启二进制日志对性能的开销很小，带来的好处远大于坏处。

二、开启和设置二进制日志

1、查看二进制日志状态

默认情况下二进制日志是关闭的。

系统变量log_bin的值为OFF表示没有开启二进制日志，ON表示开启了二进制日志，如下所示：

mysql> show variables like 'log_bin';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | OFF                                |
+---------------------------------+------------------------------------+
1 rows in set (0.00 sec)

2、开启二进制日志

（1）修改配置文件并重启mysql服务

如果需要开启二进制日志，则必须在配置文件中[mysqld]下面添加log-bin [=DIR[filename]] 。

DIR参数指定二进制文件的存储路径；
filename参数指定二级制文件的文件名。 
其中filename可以任意指定，但最好有一定规范。
系统变量log_bin是静态参数，不能动态修改的（因为它不是Dynamic Variable）。

内容如下所示：

server-id = 1              # mysql5.7必须加，否则mysql服务启动报错
log-bin = mysql_bin_log    # 路径及命名，默认在data下
expire_logs_days = 10      # 过期时间,二进制文件自动删除的天数,0代表不删除
max_binlog_size = 100M     # 单个日志文件大小

操作步骤：

Linux下的配置文件为/etc/my.cnf，Windows下的配置文件为my.ini。

[root@192 ~]# vim /etc/my.cnf
[mysqld]
​
# 省略部分内容
server-id = 1                     # mysql5.7必须加，否则mysql服务启动报错
log-bin = mysql_bin_log           # 路径及命名，默认在data下
expire_logs_days = 10             # 过期时间,二进制文件自动删除的天数,0代表不删除
max_binlog_size = 100M            # 单个日志文件大小
​
[root@192 ~]# systemctl restart mysqld.service
[root@192 ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 二 2020-06-16 17:47:34 CST; 35s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 78724 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 78701 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 78726 (mysqld)
    Tasks: 27
   CGroup: /system.slice/mysqld.service
           └─78726 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
​
6月 16 17:47:25 my_oracle systemd[1]: Starting MySQL Server...
6月 16 17:47:34 my_oracle systemd[1]: Started MySQL Server.

（2）查看二进制日志状态

重启MySQL后，你就会发现log_bin变为了ON，二进制日志（binary log）默认放在数据目录下（系统变量datadir下）。

show variables like 'log_bin%';

mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /var/lib/mysql/mysql_bin_log       |
| log_bin_index                   | /var/lib/mysql/mysql_bin_log.index |
| log_bin_trust_function_creators | OFF                                |
| log_bin_use_v1_row_events       | OFF                                |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)

（3）查看当前服务器所有的二进制日志文件

show binary logs;

mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql_bin_log.000001 |       120 |
+----------------------+-----------+
1 rows in set (0.00 sec)

或者：

show master logs;

mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql_bin_log.000001 |       120 |
+----------------------+-----------+
1 rows in set (0.00 sec)

（4）查看当前二进制日志文件状态

show master status;

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000001 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

三、切换二进制日志

执行 flush logs 可以刷新切换二进制文件。

每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。

1、重启MySQL服务切换日志

（1）重启MySQL服务器前

查看二进制日志状态，如下所示：

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000001 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

（2）重启MySQL服务

service mysql restart

[root@192 ~]# service mysql restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@192 ~]# 

（3）查看日志

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000002 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2、切换二进制日志并查看

执行flush logs刷新，切换二进制文件，并查看二进制日志状态。如下所示：

flush logs;

show master status;

mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)
​
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000003 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

切换二进制日志时，你会看到这些number会不断递增。另外，除了这些二进制日志文件外，你会看到还生成了一个mysql-bin.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

[root@192 ~]# ll /var/lib/mysql/
-rw-rw----. 1 mysql mysql      171 4月  10 11:25 mysql_bin_log.000001
-rw-rw----. 1 mysql mysql      143 4月  10 11:25 mysql_bin_log.000002
-rw-rw----. 1 mysql mysql      143 4月  10 11:25 mysql_bin_log.000003
-rw-rw----. 1 mysql mysql       92 4月  10 11:25 mysql_bin_log.index
​
[root@192 ~]# cat /var/lib/mysql/mysql_bin_log.index 
./mysql_bin_log.000001
./mysql_bin_log.000002
./mysql_bin_log.000003

四、查看二进制日志

1、查看当前日志

show binlog events；

mysql> show binlog events;
+----------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+----------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql_bin_log.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.42-log, Binlog ver: 4 |
| mysql_bin_log.000001 | 120 | Rotate      |         1 |         171 | mysql_bin_log.000002;pos=4            |
+----------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

2、查看指定日志

（1）模拟产生二进制日志

建库、建表、插入数据

mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
​
mysql> use demo;
Database changed
​
mysql> create table student(stuNo int, stuName varchar(25));
Query OK, 0 rows affected (0.01 sec)
​
mysql> insert into student values(1001,'John');
Query OK, 1 row affected (0.00 sec)

（2）查看日志

show binlog events in 'mysql_bin_log.000002';

mysql> show binlog events in 'mysql_bin_log.000002';
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
| Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                                             |
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
| mysql_bin_log.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.42-log, Binlog ver: 4                            |
| mysql_bin_log.000002 | 120 | Query       |         1 |         214 | create database demo                                             |
| mysql_bin_log.000002 | 214 | Query       |         1 |         340 | use `demo`; create table student(stuNo int, stuName varchar(25)) |
| mysql_bin_log.000002 | 340 | Query       |         1 |         419 | BEGIN                                                            |
| mysql_bin_log.000002 | 419 | Query       |         1 |         532 | use `demo`; insert into student values(1001,'John')              |
| mysql_bin_log.000002 | 532 | Xid         |         1 |         563 | COMMIT /* xid=15 */                                              |
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
6 rows in set (0.00 sec)

show binlog events in 'mysql_bin_log.000002' from 419;

mysql> show binlog events in 'mysql_bin_log.000002' from 419;
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                                |
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| mysql_bin_log.000002 | 419 | Query      |         1 |         532 | use `demo`; insert into student values(1001,'John') |
| mysql_bin_log.000002 | 532 | Xid        |         1 |         563 | COMMIT /* xid=15 */                                 |
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
2 rows in set (0.00 sec)

show binlog events in 'mysql_bin_log.000002' from 419 limit 1;

mysql> show binlog events in 'mysql_bin_log.000002' from 419 limit 1;
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                                |
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| mysql_bin_log.000002 | 419 | Query      |         1 |         532 | use `demo`; insert into student values(1001,'John') |
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
1 row in set (0.00 sec)
​
mysql> show binlog events in 'mysql_bin_log.000002' from 419 limit 2;
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                                |
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| mysql_bin_log.000002 | 419 | Query      |         1 |         532 | use `demo`; insert into student values(1001,'John') |
| mysql_bin_log.000002 | 532 | Xid        |         1 |         563 | COMMIT /* xid=15 */                                 |
+----------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
2 rows in set (0.00 sec)

五、使用二进制日志恢复数据库

如果开启了二进制日志，出现了数据丢失，可以通过二进制日志恢复数据库，语法如下：

mysqlbinlog [option] filename | mysql -u user -p passwd

option的参数主要有两个 --start-datetime --stop-datetime 和 start-position --stop-position ,前者指定恢复的时间点，后者指定恢复的位置（位置指的是二进制文件中 # at 580 580就是位置），原理就是把记录的语句重新执行了一次。如果恢复了两次。会产生重复数据。

1、按时间点恢复数据

（1）从日志开头截止到某个时间点的恢复

mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p

例如：

mysqlbinlog [--no-defaults] --stop-datetime=’2020-03-18 10:30:26’ /var/lib/mysql/mysql_bin_log.000005 | mysql -uroot -p

（2）从某个时间点到日志结尾的恢复

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p

例如：

mysqlbinlog [--no-defaults] --start-datetime=’2020-01-10 8:20:20’ /var/lib/mysql/mysql_bin_log.000005 | mysql -uroot -p

（3）从某个时间点到某个时间点的恢复

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p

例如：

mysqlbinlog [--no-defaults] --start-datetime=’2010-11-10 8:20:20’ --stop-datetime=’2020-03-18 10:30:26’ /var/lib/mysql/mysql_bin_log.000005 | mysql -uroot -p

2、按位置恢复数据

（1）从某个位置到日志结尾的恢复

/usr/local/mysql/bin/mysqlbinlog --start-position='275' /var/lib/mysql/mysql_bin_log.000005 | mysql -uroot -p
Enter password: 
[root@bogon ~]# 

（2）从日志开头位置到日志结尾的恢复

/usr/local/mysql/bin/mysqlbinlog --stop-position='465' /var/lib/mysql/mysql_bin_log.000005 | mysql -uroot -p
Enter password: 
[root@bogon ~]# 

（3）从某个位置到某个位置的恢复

/usr/local/mysql/bin/mysqlbinlog --start-position='4' --stop-position='120' /var/lib/mysql/mysql_bin_log.000005 | mysql -uroot -p
Enter password: 
[root@bogon ~]# 

六、删除二进制日志

1、删除某个日志之前的所有二进制日志文件

purge binary logs to xxx; 表示删除某个日志之前的所有二进制日志文件，这个命令会修改index中相关数据。

如下所示：

mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql_bin_log.000001 |       143 |
| mysql_bin_log.000002 |       586 |
| mysql_bin_log.000003 |       171 |
| mysql_bin_log.000004 |       171 |
| mysql_bin_log.000005 |       120 |
+----------------------+-----------+
5 rows in set (0.00 sec)
​
mysql> purge binary logs to 'mysql_bin_log.000002';
Query OK, 0 rows affected (0.03 sec)
​
mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql_bin_log.000002 |       586 |
| mysql_bin_log.000003 |       171 |
| mysql_bin_log.000004 |       171 |
| mysql_bin_log.000005 |       120 |
+----------------------+-----------+
4 rows in set (0.00 sec)

查看日志清单：

[root@192 ~]# cat /var/lib/mysql/mysql_bin_log.index 
./mysql_bin_log.000002
./mysql_bin_log.000003
./mysql_bin_log.000004
./mysql_bin_log.000005

2、清除某个时间点以前的二进制日志文件

mysql> purge binary logs before '2020-03-10 10:10:00';
Query OK, 0 rows affected (0.00 sec)

3、清除7天前的二进制日志文件

mysql> purge master logs before date_sub( now( ), interval 7 day);
Query OK, 0 rows affected (0.00 sec)

4、清除所有的二进制日志文件（当前不存在主从复制关系）

reset之后，从000001开始生成全新空日志。

mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql_bin_log.000002 |       586 |
| mysql_bin_log.000003 |       171 |
| mysql_bin_log.000004 |       171 |
| mysql_bin_log.000005 |       120 |
+----------------------+-----------+
4 rows in set (0.00 sec)
​
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
​
mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql_bin_log.000001 |       120 |
+----------------------+-----------+
1 row in set (0.00 sec)

5、自动清理二进制日志文件

另外，我们也可以设置expire_logs_days参数，设置自动清理，其默认值为0,表示不启用过期自动删除功能，如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除，自动删除工作通常发生在MySQL启动时或flush日志时。

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 10    |
+------------------+-------+
1 row in set (0.00 sec)

七、停止二进制日志

可以通过修改配置文件停止二进制日志功能，但是需要重启数据库，mysql提供了语句可以在线停止二进制功能。

set sql_log_bin = 0       # 停止二进制日志功能
set sql_log_bin = 1       # 开启二进制日志功能