MYSQL BINLOG 二进制日志

二进制日志BINLOG

BINGLOG日志对应于ORACLE的REDOLOG的功能

Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。

# 打开二进制日志功能.

# 在复制(replication)配置中,作为 MASTER 主服务器必须打开此项

# 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志.

log-bin=mysql-bin

查看系统变量log_bin，如果其值为OFF，表示没有开启二进制日志，如果需要开启二进制日志，则必须在my.cnf中[mysqld]下面添加log-bin [=DIR\[filename]] ，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名。 其中filename可以任意指定，但最好有一定规范。系统变量log_bin是静态参数，不能动态修改的（因为它不是Dynamic Variable）。如下所示：

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | OFF |

+---------------+-------+

1 row in set (0.00 sec)

mysql> set global log_bin=mysql_bin;

ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

4.1：修改my.cnf，在[mysqld]下面增加

log_bin=/u01/mysql_instance/mysql_log/mysql_bin_log

重启MySQL后，你就会发现log_bin变为了ON，二进制日志（binary log）默认放在数据目录下（系统变量datadir下），当然这里放在专门的目录下 同时要设置server-id=2

启动的时候遇到问题可以查看错误日志，这个日志类似于ORACLE告警日志

log_error | ./mysql-master.err

2018-03-22T09:16:11.819173Z mysqld_safe Starting mysqld daemon with databases from /u01/mysql_instance/mysql_master/mysql_data

2018-03-22T09:16:11.826964Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)

2018-03-22T09:16:11.827018Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2018-03-22T09:16:11.971237Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-03-22T09:16:11.971286Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2018-03-22T09:16:11.971292Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

2018-03-22T09:16:11.971328Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2018-03-22T09:16:11.971352Z 0 [Note] /u01/mysql_soft/mysql5.7.21/bin/mysqld (mysqld 5.7.21-log) starting as process 13549 ...

mysqld: File '/u01/mysql_instance/mysql_log/mysql_bin_log.index' not found (Errcode: 2 - No such file or directory)

2018-03-22T09:16:11.974486Z 0 [ERROR] Aborting

2018-03-22T09:16:11.974496Z 0 [Note] Binlog end

2018-03-22T09:16:11.974538Z 0 [Note] /u01/mysql_soft/mysql5.7.21/bin/mysqld: Shutdown complete

2018-03-22T09:16:11.982533Z mysqld_safe mysqld from pid file /u01/mysql_instance/mysql_master/mysql_data/mysql-master.pid ended

用红色标识的表示启动失败的原因所在。

修改成log_bin=/u01/mysql_instance/mysql_master/mysql_log/mysql_bin_log

[mysqldba@mysql-master ~]service mysqld start

Starting MySQL. [确定]

[mysqldba@mysql-master ~]mysql –uroot –p123456

如下所示：

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)

--默认目录

mysql> show variables like 'datadir';

+---------------+----------------------------------------------+

| Variable_name | Value |

+---------------+----------------------------------------------+

| datadir | /u01/mysql_instance/mysql_master/mysql_data/ |

+---------------+----------------------------------------------+

1 row in set (0.00 sec)

mysql> show variables like '%log_bin%';

+--------------+--------------------------------------------------------------+

| Variable_name| Value |---------------+-------------------------------------------------------------+

| log_bin | ON |

| log_bin_basename | /u01/mysql_instance/mysql_master/mysql_log/mysql_bin_log

| log_bin_index | /u01/mysql_instance/mysql_master/mysql_log/mysql_bin_log.index

| log_bin_trust_function_creators | OFF

| log_bin_use_v1_row_events | OFF

| sql_log_bin | ON

+------------+----------------------------------------------------------------+

6 rows in set (0.00 sec)

4.2 MySQL binlog的三种工作模式

binlog_format | ROW

（1）Row level

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点：能清楚的记录每一行数据修改的细节

缺点：数据量太大

（2）Statement level（默认）

每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能

缺点：容易出现主从复制不一致

（3）Mixed（混合模式）

结合了Row level和Statement level的优点

小仙认为 现在就使用默认的ROW 跟ORACLE REDOLOG 一致性会更容易学习

4.3 二进制日志切换方法

使用命令flush logs切换二进制日志，如下所示

mysql> show master status;

+----------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------------+----------+--------------+------------------+-------------------+

| mysql_bin_log.000001 | 154 | | | |

+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>flush logs;

Query OK, 0 rows affected (0.03 sec)

mysql> show master status;

+----------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------------+----------+--------------+------------------+-------------------+

| mysql_bin_log.000002 | 154 | | | |

+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

请注意，每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，你会看到这些number会不断递增。另外，除了这些二进制日志文件外，你会看到还生成了一个mysql_bin_log.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

mysql> show binary logs;

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| mysql_bin_log.000001 | 205 |

| mysql_bin_log.000002 | 154 |

+----------------------+-----------+

2 rows in set (0.00 sec)

4.4二进制日志删除方法

二进制日志的删除可以通过命令手工删除，也可以设置自动清理。下面简单介绍一下，如何删除二进制日志。

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

mysql> purge binary logs to 'mysql_bin_log.000002';

Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;

+----------------------+-----------+

| Log_na | File_size |

+----------------------+-----------+

| mysql_bin_log.000002 | 154 |

+----------------------+-----------+

1 row in set (0.00 sec)

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

mysql>purge binary logs before'2017-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)

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

mysql> show variables like 'expire_logs_days';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| expire_logs_days | 0 |

+------------------+-------+

1 row in set (0.00 sec)

mysql> set global expire_logs_days=7;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| expire_logs_days | 7 |

+------------------+-------+

1 row in set (0.00 sec)

其他参数

系统变量log_bin_trust_function_creators，默认为OFF，这个参数开启会限制存储过程、Function、触发器的创建。

系统变量sql_log_bin 用于控制会话级别二进制日志功能的开启或关闭，默认为ON，表示启用二进制日志功能。

系统变量binlog_cache_size表示为每个客户端分配binlog_cache_size大小的缓存，默认值32768。类似于ORACLE的LOGBUUFER，不过它是每个会话。二进制日志缓存使用的前提条件是服务器端使用了支持事务的引擎以及开启了bin log功能，它是MySQL用来提高binlog的效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多或多事务语句，写入量比较大，可适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

系统变量max_binlog_cache_size二进制日志能够使用的最大cache内存大小。当执行多语句事务时，max_binlog_cache_size 如果不够大，系统可能会报出“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。

系统变量max_binlog_stmt_cache_size

max_binlog_cache_size针对事务语句，max_binlog_stmt_cache_size针对非事务语句，当我们发现Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比较大时就需要考虑增大cache的大小

系统变量max_binlog_size， 表示二进制日志的最大值，一般设置为512M或1GB，但不能超过1GB。该设置并不能严格控制二进制日志的大小，尤其是二进制日志比较靠近为不而又遇到一根比较大事务时， 为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。

系统变量binlog_checksum用作复制的主从校检。 NONE表示不生成checksum，CRC-32表示使用这个算法做校检。

系统变量sync_binlog，这个参数对于Mysql系统来说是至关重要的，它不仅影响到二进制日志文件对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而是让Filesystem自行决定什么时候来做同步。MySQL中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但风险也是最大的。一旦系统崩溃（Crash），在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同时文件系统将Binlog文件缓存刷新到磁盘。

可以适当的调整sync_binlog， 在牺牲一定的一致性下，获取更高的并发和性能。

这个嘛 应该设置SYNC_BINLOG=1 提交一次就要算进磁盘中。这个跟ORACLE DIO直接IO 绕过系统缓存。

Log-slave-update该参数在搭建master=>slave=>slave的架构时，需要配置。

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的日志，默认值为空，则表示将所有库的日志同步到二进制日志。

4.4 查看二进制日志内容

方法1：使用show binlog events方式可以获取当前以及指定binlog的日志，不适宜提取大量日志。

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

方法2: 使用mysqlbinlog命令行查看日志内容(适宜批量提取日志)。

下面我们来通过实验验证一下，先看看show binlog events方式的测试:

1: 查看第一个binlog文件的内容(show binlog events)

mysql> slog events;

+----------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+----------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql_bin_log.000002 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |

| mysql_bin_log.000002 | 123 | Previous_gtids | 2 | 154 | |

+----------------------+-----+----------------+-----------+-------------+---------------------------------------+

2 rows in set (0.00 sec)

mysql> system mysqlbinlog /u01/mysql_instance/mysql_master/mysql_log/mysql_bin_log.000002;

mysqlbinlog: unknown variable 'default-character-set=utf8'

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。

两个方法可以解决这个问题

一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

二是用mysqlbinlog --no-defaults mysql-bin.000004 命令打开

mysqldba@mysql-master mysql_log]mysqlbinlog --no-defaults mysql_bin_log.000002 >test.sql;

[mysqldba@mysql-master mysql_log]ll

总用量 76K

-rw-r----- 1 mysqldba dba 191 3月 20 18:13 mysql-master-slow.log

-rw-r----- 1 mysqldba dba 59K 3月 22 16:37 mysql-master.log

-rw-r----- 1 mysqldba dba 154 3月 22 17:38 mysql_bin_log.000002

-rw-r----- 1 mysqldba dba 64 3月 22 17:44 mysql_bin_log.index

-rw-r--r-- 1 mysqldba dba 816 3月 22 18:05 test.sql

[mysqldba@mysql-master mysql_log]cat test.sql;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180322 17:38:09 server id 2 end_log_pos 123 CRC32 0xb91e4edf Start: binlog v 4, server v 5.7.21-log created 180322 17:38:09

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

gXmzWg8CAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

Ad9OHrk=

'/*!*/;

# at 123

#180322 17:38:09 server id 2 end_log_pos 154 CRC32 0xc8a98172 Previous-GTIDs

# [empty]

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[mysqldba@mysql-master mysql_log]