MySQL二进制日志总结
2017-03-24 12:28 潇湘隐者 阅读(20303) 评论(6) 编辑 收藏 举报二进制日志简单介绍
MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。
官方文档关于二进制日志（binary log）的介绍如下：
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:
· For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
· Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 5.4.3, “The General Query Log”.
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.
二进制日志状态查看
系统变量log_bin的值为OFF表示没有开启二进制日志（binary log）。ON表示开启了二进制日志（binary log）
mysql> show variables like '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 |
+----------------------+-----+------------+-----------+-------------+-------------------------------------------------+
| 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 */ |
+----------------------+-----+------------+-----------+-------------+-------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
接下来，我们来看看使用mysqlbinlog命令如何查看二进制日志文件中的的内容
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> flush logs;
Query OK, 0 rows affected (0.01 sec)mysql> use gsp;Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changedmysql> insert into test values(303, 'kerry');
Query OK, 1 row affected (0.01 sec)mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| DB-Server-bin.000013 | 337 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> system mysqlbinlog /var/lib/mysql/DB-Server-bin.000013;
/*!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#170323 18:43:17 server id 1 end_log_pos 120 CRC32 0xabfd028f Start: binlog v 4, server v 5.6.20-enterprise-commercial-advanced-log created 170323 18:43:17# Warning: this binlog is either in use or was not closed properly.
BINLOG 'hU/UWA8BAAAAdAAAAHgAAAABAAQANS42LjIwLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAY8C
/as=
'/*!*/;# at 120#170323 18:43:46 server id 1 end_log_pos 197 CRC32 0xc5ff49de Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1490309026/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN/*!*/;# at 197#170323 18:43:46 server id 1 end_log_pos 306 CRC32 0x75c26223 Query thread_id=4 exec_time=0 error_code=0
use `gsp`/*!*/;
SET TIMESTAMP=1490309026/*!*/;
insert into test values(303, 'kerry')
/*!*/;# at 306#170323 18:43:46 server id 1 end_log_pos 337 CRC32 0x6ea837f4 Xid = 80
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;mysql>
2： 解析DB-Server-bin.000013并将内容输出到test.sql
[root@DB-Server ~]# mysqlbinlog /var/lib/mysql/DB-Server-bin.000013 > test.sql;[root@DB-Server ~]# more test.sql /*!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#170323 18:43:17 server id 1 end_log_pos 120 CRC32 0xabfd028f Start: binlog v 4, server v 5.6.20-enterprise-commercial-advanced-log created 170323 18:43:17# Warning: this binlog is either in use or was not closed properly.
BINLOG 'hU/UWA8BAAAAdAAAAHgAAAABAAQANS42LjIwLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAY8C
/as=
'/*!*/;# at 120#170323 18:43:46 server id 1 end_log_pos 197 CRC32 0xc5ff49de Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1490309026/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN/*!*/;# at 197#170323 18:43:46 server id 1 end_log_pos 306 CRC32 0x75c26223 Query thread_id=4 exec_time=0 error_code=0
use `gsp`/*!*/;
SET TIMESTAMP=1490309026/*!*/;
insert into test values(303, 'kerry')
/*!*/;# at 306#170323 18:43:46 server id 1 end_log_pos 337 CRC32 0x6ea837f4 Xid = 80
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysqlbinlog有许多参数，你可以分析、提取指定position位置的binlog日志；也可以按指定时间恢复数据；也可以导出的脚本文件；或者压缩处理等等，非常灵活强大。在这里不做详细展开，后面将单独的写一篇总结介绍。
开启二进制日志影响性能吗？
开启MySQL的二进制日志会影响服务器性能吗？答案是会有一些性能损耗，但是性能开销非常小（slightly slower），另外，开启binlog带来的好处要远远超过带来的性能开销。官方文档的介绍如下所示：
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.
参考资料：
https://dev.mysql.com/doc/refman/5.6/en/binary-log.html
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_log_bin_basename
http://pangge.blog.51cto.com/6013757/1319304
