二进制日志

由于事务日志严重依赖于存储引擎，比如MyISAM不支持事务，所以对于MyISAM来说就没有事务日志这个概念，只有InnoDB才有事务日志。事务日志内记录的内容，已提交的事务未提交的事务都需要记录到事务日志中，而二进制日志不同。

二进制日志又叫存档日志，记录那些已提交的确定的事件，记录导致数据改变或潜在导致数据改变的SQL语句。二进制日志不依赖于存储引擎。二进制日志详细的记录了数据库所有的增删改操作，所以二进制日志就相当于忠实的记录了数据库的所有的行为（增、删、改）。所以利用二进制日志可以分析了解数据库内数据变化的整个过程。因为二进制日志记录了数据库内所有数据的行为，所以可以通过“重放”日志文件中的事件来生成数据副本，也就是说通过重放二进制日志文件来生成新的数据库备份。基于安全考虑，强烈建议将二进制日志文件和数据文件进行分开存放。

二进制日志的记录格式

二进制日志有3种格式

  1. statement：基于语句记录，默认
  2. row:基于行记录，记录的是数据，日志量较大。
  3. mixed:混合模式，系统自行判断基于那种方式进行记录

在工作中，推荐基于row的方式进行记录

二进制日志的开启方式

二进制日志需要启动两个选项

1
2
sql_log_bin   #sql_log_bin为会话级的变量可以在MySQL内通过set命令来修改
log_bin       #log_bin为选项需要写在配置文件中。

sql_log_bin默认已经开启

1
2
3
4
5
6
7
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

由于是二进制安装的MySQL所以默认的配置文件中log_bin也是开启的

1
2
3
4
5
6
7
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

但是配置文件中默认定义的二进制日志是存放在和数据库一起，不复合我们的要求需要修改，将其更改至其他目录

1
2
[root@localhost ~]# vim /etc/mysql/my.cnf
log-bin=/data/bin/mysql-bin

为二进制日志创建存放目录，并修改目录的属主和属组

1
2
[root@localhost ~]# mkdir /data/bin
[root@localhost ~]# chown -R mysql.mysql /data/bin

然后重启服务

1
[root@localhost ~]# service mysql restart

再次查看二进制日志存放的目录，此时已经在目录下产生了二进制日志文件

1
2
3
4
[root@localhost ~]# ll /data/bin/
total 8
-rw-rw---- 1 mysql mysql 328 May  6 03:31 mysql-bin.000001
-rw-rw---- 1 mysql mysql  27 May  6 03:31 mysql-bin.index

二进制日志相关的变量

1
2
3
max_binlog_size=1073741824     #单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
sync_binlog=1|0    #设定是否启动二进制日志即时同步磁盘功能，默认为0由操作系统负责同步日志到磁盘，1.表示立即写入日志到磁盘
expire_logs_days=N  #二进制日志可以自动删除的天数，默认为0，不自动删除。

二进制日志相关的一些命令

SHOW MASTER LOGS:查看使用中的二进制日志文件列表及大小

1
2
3
4
5
6
7
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.00 sec)

SHOW MASTER STATUS:查看使用中的二进制日志

1
2
3
4
5
6
7
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

SHOW BINLOG EVENTS:查看二进制文件中记录的各种操作

1
2
3
4
5
6
7
8
9
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001';
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.23-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000001 | 256 | Gtid_list         |         1 |         285 | []                                             |
| mysql-bin.000001 | 285 | Binlog_checkpoint |         1 |         328 | mysql-bin.000001                               |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
3 rows in set (0.00 sec)

查看二进制日志中指定的位置的操作

1
2
3
4
5
6
7
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' from 285;
+------------------+-----+-------------------+-----------+-------------+------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info             |
+------------------+-----+-------------------+-----------+-------------+------------------+
| mysql-bin.000001 | 285 | Binlog_checkpoint |         1 |         328 | mysql-bin.000001 |
+------------------+-----+-------------------+-----------+-------------+------------------+
1 row in set (0.00 sec)

二进制日志的客户端命令工具

mysqlbinlog

1
mysqlbinlog [options] log_file...
option 说明
start-position 指定开始位置
stop-position 指定结束位置
start-datetime 指定开始的时间
stop-datetime 指定结束的时间
base64-output[=name] 使用base64格式导出
-v -vvv 显示详细信息 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
[root@localhost ~]# mysqlbinlog --start-position=8309 --stop-position=8639 /data/bin/mysql-bin.000001 -v    #-v选项可以将base64编码转换为可读的信息。
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190506  3:31:11 server id 1  end_log_pos 256 CRC32 0x0c261669 	Start: binlog v 4, server v 10.2.23-MariaDB-log created 190506  3:31:11 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
/znPXA8BAAAA/AAAAAABAAABAAQAMTAuMi4yMy1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD/Oc9cEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgFpFiYM
'/*!*/;
# at 8309
#190506  4:11:55 server id 1  end_log_pos 8351 CRC32 0x4cbdba04 	GTID 0-1-33 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=33*//*!*/;
# at 8351
#190506  4:11:55 server id 1  end_log_pos 8639 CRC32 0x8b15477f 	Query	thread_id=12	exec_time=0	error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1557087115/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=0, @@session.unique_checks=0, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=524288/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `toc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  `TID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

二进制日志的清理

1.重新生成一个二进制日志

FLUSH LOGS;重新生成二进制日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      8993 |
| mysql-bin.000002 |       385 |
+------------------+-----------+

MariaDB [(none)]>  FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      8993 |
| mysql-bin.000002 |       432 |
| mysql-bin.000003 |       385 |
+------------------+-----------+

2.清理二进制日志

PURGE BINARY LOGS TO ‘’:删除二进制文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      8993 |
| mysql-bin.000002 |       432 |
| mysql-bin.000003 |       432 |
| mysql-bin.000004 |       432 |
| mysql-bin.000005 |       432 |
| mysql-bin.000006 |       432 |
| mysql-bin.000007 |       432 |
| mysql-bin.000008 |       408 |
| mysql-bin.000009 |       389 |
| mysql-bin.000010 |       432 |
| mysql-bin.000011 |       385 |
+------------------+-----------+
11 rows in set (0.00 sec)

#删除旧的二进制文件，表示删除指定的二进制日志之前的日志。
MariaDB [(none)]> PURGE BINARY LOGS TO 'mysql-bin.000011';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       385 |      #mysql-bin.000011之前的日志已经删除。
+------------------+-----------+
1 row in set (0.00 sec)

3.重置二进制日志更新计数

RESET MASTER;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [(none)]> SHOW MASTER LOGS;     #查看下当前二进制日志计数
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       385 |        #当前二进制日计数为mysql-bin.000011
+------------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> RESET MASTER;         #重置计数
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER LOGS;      #再次查看日志计数
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |          #现在计数已经变为mysql-bin.000001
+------------------+-----------+
1 row in set (0.00 sec)

#MariaDB从10.1.6之后开始支持TO，RESET MASTER [TO #];重置二进制日志到指定的日志计数。