当前位置：Gxlcms > 数据库问题 > MySQL二进制日志总结

MySQL二进制日志总结

时间：2021-07-01 10:21:17 帮助过：23人阅读

 ‘log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

 

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

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 | 478421400 |
| mysql-bin.000005 |      9653 |
| mysql-bin.000006 | 340631484 |
+------------------+-----------+
3 rows in set (0.00 sec)

 

当然你还可以使用下面命令查看

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 | 478421400 |
| mysql-bin.000005 |      9653 |
| mysql-bin.000006 |  340631484 |
+------------------+-----------+
3 rows in set (0.00 sec)
 
mysql>

 

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

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 373655406 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>

技术分享

 

二进制日志开启方法

 

查看系统变量log_bin，如果其值为OFF，表示没有开启二进制日志（binary log），如果需要开启二进制日志，则必须在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
mysql>

 

1：修改my.cnf，在[mysqld]下面增加log_bin=mysql_bin_log，重启MySQL后，你就会发现log_bin变为了ON，二进制日志（binary log）默认放在数据目录下（系统变量datadir下），如下所示：

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       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
 
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                                |
| sql_log_bin                     | ON                                 |
+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)
 
mysql>

 

2：如果在my.cnf里面只设置log_bin，但是不指定file_name，然后重启数据库。你会发现二进制日志文件名称为${hostname}-bin 这样的格式。如下所示：

[mysqld]

log_bin

 

mysql> show variables like ‘%log_bin%‘;
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON                                 |
| log_bin_basename                | /var/lib/mysql/DB-Server-bin       |
| log_bin_index                   | /var/lib/mysql/DB-Server-bin.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)
 
mysql>

 

 

3：当然你可以可以指定二进制日志的路径位置，如下所示：

log_bin=/mysql/bin_log/mysql_binlog

技术分享

 

二进制日志切换方法

 

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

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| DB-Server-bin.000002 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
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 |
+----------------------+----------+--------------+------------------+-------------------+
| DB-Server-bin.000003 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>

技术分享

 

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

[root@DB-Server mysql]# ls -lrt DB-Server-bin*
-rw-rw---- 1 mysql mysql 143 Mar 22 10:55 DB-Server-bin.000001
-rw-rw---- 1 mysql mysql 171 Mar 22 11:20 DB-Server-bin.000002
-rw-rw---- 1 mysql mysql 171 Mar 22 11:23 DB-Server-bin.000003
-rw-rw---- 1 mysql mysql 171 Mar 22 11:24 DB-Server-bin.000004
-rw-rw---- 1 mysql mysql 138 Mar 22 11:24 DB-Server-bin.index
-rw-rw---- 1 mysql mysql 120 Mar 22 11:24 DB-Server-bin.000006
-rw-rw---- 1 mysql mysql 171 Mar 22 11:24 DB-Server-bin.000005
[root@DB-Server mysql]# more DB-Server-bin.index 
./DB-Server-bin.000001
./DB-Server-bin.000002
./DB-Server-bin.000003
./DB-Server-bin.000004
./DB-Server-bin.000005
./DB-Server-bin.000006
[root@DB-Server mysql]#

技术分享

 

二进制日志删除方法

 

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

 

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

mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| DB-Server-bin.000001 |       143 |
| DB-Server-bin.000002 |       171 |
| DB-Server-bin.000003 |       171 |
| DB-Server-bin.000004 |       171 |
| DB-Server-bin.000005 |       171 |
| DB-Server-bin.000006 |       143 |
| DB-Server-bin.000007 |       143 |
| DB-Server-bin.000008 |       120 |
+----------------------+-----------+
8 rows in set (0.00 sec)
 
mysql> purge binary logs to ‘DB-Server-bin.000002‘;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| DB-Server-bin.000002 |       171 |
| DB-Server-bin.000003 |       171 |
| DB-Server-bin.000004 |       171 |
| DB-Server-bin.000005 |       171 |
| DB-Server-bin.000006 |       143 |
| DB-Server-bin.000007 |       143 |
| DB-Server-bin.000008 |       120 |
+----------------------+-----------+
7 rows in set (0.00 sec)
 
mysql> purge binary logs to ‘DB-Server-bin.000004‘;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| DB-Server-bin.000004 |       171 |
| DB-Server-bin.000005 |       171 |
| DB-Server-bin.000006 |       143 |
| DB-Server-bin.000007 |       143 |
| DB-Server-bin.000008 |       120 |
+----------------------+-----------+
5 rows in set (0.00 sec)
 
mysql>

技术分享

 

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)

 

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

 

mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| DB-Server-bin.000004 |       171 |
| DB-Server-bin.000005 |       171 |
| DB-Server-bin.000006 |       143 |
| DB-Server-bin.000007 |       143 |
| DB-Server-bin.000008 |       143 |
| DB-Server-bin.000010 |       143 |
| DB-Server-bin.000011 |       120 |
+----------------------+-----------+
7 rows in set (0.00 sec)
 
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show binary logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| DB-Server-bin.000010 |       120 |
+----------------------+-----------+
1 row in set (0.00 sec)
 
mysql>

技术分享

 

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

 

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

 

二进制日志相关参数

 

关于二进制日志（binary log）的一些相关参数，下面一一介绍，如有不足或疏漏之处，敬请指出：

 

1：系统变量log_bin_basename是MySQL 5.6.2开始引入的。 它表示二进制日志文件名。默认值为datadir + ‘/‘ + hostname + ‘-bin‘。 该参数不需要设置，也不能在my.cnf中设置，否则会报错（实验环境为MySQL 5.6.20，如有不对，敬请指出！）。

Holds the name and complete path to the binary log file. Unlike the log_bin system variable, log_bin_basename reflects the name set with the --log-bin server option 

mysql> show variables like ‘log_bin_basename‘;
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| log_bin_basename | /var/lib/mysql/DB-Server-bin |
+------------------+------------------------------+
1 row in set (0.00 sec)
 
mysql>

 

 

2：系统变量log_bin_index是MySQL 5.6.4开始引入的。 它表示二进制日志的索引文件。该参数可以在my.cnf中设置。 

mysql> show variables like ‘log_bin_index‘;
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| log_bin_index | /var/lib/mysql/DB-Server-bin.index |
+---------------+------------------------------------+
1 row in set (0.01 sec)
 
mysql>

 

 

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

 

mysql> show variables like ‘log_bin_trust_function_creators‘;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
 
mysql>

 

 

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

 

mysql> show variables like ‘sql_log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

 

5：系统变量expire_logs_days ，前面已经阐述。在此不做介绍。

 

 

mysql> show variables like ‘%binlog%‘;
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| binlogging_impossible_mode              | IGNORE_ERROR         |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
16 rows in set (0.00 sec)
 
mysql>

 

6：系统变量binlog_cache_size 表示为每个客户端分配binlog_cache_size大小的缓存，默认值32768。二进制日志缓存使用的前提条件是服务器端使用了支持事务的引擎以及开启了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）来缓存了。

 

mysql> show variables like ‘binlog_cache_size‘;
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)
 
mysql> 
mysql>  show status like ‘binlog%‘;
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Binlog_cache_disk_use      | 37      |
| Binlog_cache_use           | 1048300 |
| Binlog_stmt_cache_disk_use | 0       |
| Binlog_stmt_cache_use      | 2306    |
+----------------------------+---------+
4 rows in set (0.00 sec)
 
mysql>

技术分享

 

如上所示，可以通过查看Binlog_cache_disk_use 与 Binlog_cache_use来判断binlog_cache_size是否需要调整。

 

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

mysql> show variables like ‘max_binlog_cache_size‘;
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00 sec)
 
mysql>

 

8： 系统变量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_stmt_cache_size sets the size for the statement cache only; the upper limit for the transaction cache is governed exclusively by the max_binlog_cache_size system variable. 

mysql> show variables like ‘max_binlog_stmt_cache_size‘;
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
1 row in set (0.00 sec)
 
mysql>

 

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

 

mysql> show variables like ‘max_binlog_size‘;
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
 
mysql>

 

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

mysql> show variables like ‘binlog_checksum‘;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| binlog_checksum | CRC32 |
+-----------------+-------+
1 row in set (0.00 sec)
 
mysql>

 

 

11：系统变量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， 在牺牲一定的一致性下，获取更高的并发和性能。

 

mysql> show variables like ‘sync_binlog‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

 

 

12：系统变量binlog_format 指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式。这个参数主要影响主从复制。

 

mysql> show variables like ‘binlog_format‘;
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
 
mysql>

 

复制的模式有下面几种：

基于SQL语句的复制(statement-based replication, SBR)，

基于行的复制(row-based replication, RBR)，

混合模式复制(mixed-based replication, MBR)。

相应地，二进制日志的格式也有三种：STATEMENT，ROW，MIXED。这里限于篇幅和主题，不做展开介绍。后面会单独总结介绍。

 

查看二进制日志内容

 

方法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)

技术分享

 

2: 查看某个特定binglog文件的内容。 

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| DB-Server-bin.000011 |      120 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into test values(21, ‘kkk21‘);
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into test values(22, ‘kkk22‘);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|   10 | kerry |
|   20 | kern  |
|   21 | kkk21 |
|   22 | kkk22 |
+------+-------+
4 rows in set (0.00 sec)
 
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(12) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| DB-Server-bin.000012 |      552 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>  show binlog events in ‘DB-Server-bin.000012‘;
+----------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                                                 |
+----------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| DB-Server-bin.000012 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.20-enterprise-commercial-advanced-log, Binlog ver: 4 |
| DB-Server-bin.000012 | 120 | Query       |         1 |         197 | BEGIN                                                                |
| DB-Server-bin.000012 | 197 | Query       |         1 |         305 | use `gsp`; insert into test values(21, ‘kkk21‘)                      |
| DB-Server-bin.000012 | 305 | Xid         |         1 |         336 | COMMIT /* xid=46 */                                                  |
| DB-Server-bin.000012 | 336 | Query       |         1 |         413 | BEGIN                                                                |
| DB-Server-bin.000012 | 413 | Query       |         1 |         521 | use `gsp`; insert into test values(22, ‘kkk22‘)                      |
| DB-Server-bin.000012 | 521 | Xid         |         1 |         552 | COMMIT /* xid=47 */                                                  |
+----------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
7 rows in set (0.00 sec)
 
mysql>

技术分享

 

mysql> show binlog events in ‘DB-Server-bin.000012‘ from 336;
+----------------------+-----+------------+-----------+-------------+-------------------------------------------------+
| Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！