【MySQL】启用MySQL的Binlog
BInlog（二进制日志）记录了所有的DDL语句和DML语句，但不包括数据查询语句。语句以“事件”的形式存在，它描述了数据的更改过程，此日志对数据恢复起着非常重要的作用。
一 Binlog的格式
Binlog的日志格式有3种，分别为Statement、Row和Mixed，可在启动时通过参数--binlog-format进行设置。
- Statement：日志中记录的是语句，每一条对数据修改的SQL语句都会记录在日志中，通过mysqlbinlog工具，可以看到每条语句的内容。主从复制时，从库将日志解析为原文本，并在从库重新执行一次。优点是日志记录清晰易读、日志量少，对I/O影响小；缺点是在某些情况下，从库复制会出错；
- ROW：将每一行的变更记录到日志中，而不是记录SQL语句，优点是会记录每一行数据的变化细节，不会出现某些情况下无法复制的情况；缺点是日志量大，对I/O影响较大；
- MIXED：组合了Statement和Row两种日志，默认是statement，但在一些特殊情况下采用Row来记录，Mixed格式利用了两种模式的优点，避开了它们的缺点。
二 启用Binlog
Mysql数据库版本：
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)Binlog默认情况下是关闭的，可通过下面参数查看：
mysql> show variables like '%log%bin%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_statements_unsafe_for_binlog | ON |
| sql_log_bin | ON |
+----------------------------------+-------+
7 rows in set (0.00 sec)可以通过以下方式启用BInlog，在my.ini的mysqld下增加下面两行：
log-bin=/var/lib/mysql/binlog/mysql-bin
server-id=1
再次查看参数：
mysql> show variables like '%log%bin%';
+----------------------------------+---------------------------------------+
| Variable_name | Value |
+----------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog/mysql-bin |
| log_bin_index | /var/lib/mysql/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_statements_unsafe_for_binlog | ON |
| sql_log_bin | ON |
+----------------------------------+---------------------------------------+
7 rows in set (0.01 sec)三 查看Binlog
Binlog的日志格式如果是Row，则会出现一串无法读取的字串，如图：
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)创建表并增加记录：
mysql> use test;
Database changed
mysql> create table t1 (id int ,name varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1,'Alen');
Query OK, 1 row affected (0.01 sec)Binlog日志为：
[root@strong mysql]# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000001 --查看Binlog内容
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180129 19:35:40 server id 1 end_log_pos 123 CRC32 0x62ebeb85 Start: binlog v 4, server v 5.7.21-log created 180129 19:35:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
DAdvWg8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAMB29aEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYXr62I=
'/*!*/;
# at 123
#180129 19:35:40 server id 1 end_log_pos 154 CRC32 0xb0a4f258 Previous-GTIDs
# [empty]
# at 154
#180129 19:39:59 server id 1 end_log_pos 219 CRC32 0xbbb8e212 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180129 19:39:59 server id 1 end_log_pos 336 CRC32 0x73fe23e9 Query thread_id=8 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1517225999/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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 t1 (id int ,name varchar(100)) --创建表操作
/*!*/;
# at 336
#180129 19:40:11 server id 1 end_log_pos 401 CRC32 0xef54b9f2 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 401
#180129 19:40:11 server id 1 end_log_pos 473 CRC32 0x59a6a2e9 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1517226011/*!*/;
BEGIN
/*!*/;
# at 473
#180129 19:40:11 server id 1 end_log_pos 521 CRC32 0xbab0d07c Table_map: `test`.`t1` mapped to number 110
# at 521
#180129 19:40:11 server id 1 end_log_pos 567 CRC32 0xfb0647ba Write_rows: table id 110 flags: STMT_END_F
BINLOG '
GwhvWhMBAAAAMAAAAAkCAAAAAG4AAAAAAAEABHRlc3QAAnQxAAIDDwIsAQN80LC6
GwhvWh4BAAAALgAAADcCAAAAAG4AAAAAAAEAAgAC//wBAAAABABBbGVuukcG+w==
'/*!*/;
# at 567
#180129 19:40:11 server id 1 end_log_pos 598 CRC32 0x1e7e6eac Xid = 157
COMMIT/*!*/;
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*/;此时需要加上-v或-vv读取，如图：
[root@strong mysql]# mysqlbinlog -v /var/lib/mysql/binlog/mysql-bin.000001 --加上-v参数查看Binlog内容
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180129 19:35:40 server id 1 end_log_pos 123 CRC32 0x62ebeb85 Start: binlog v 4, server v 5.7.21-log created 180129 19:35:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
DAdvWg8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAMB29aEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYXr62I=
'/*!*/;
# at 123
#180129 19:35:40 server id 1 end_log_pos 154 CRC32 0xb0a4f258 Previous-GTIDs
# [empty]
# at 154
#180129 19:39:59 server id 1 end_log_pos 219 CRC32 0xbbb8e212 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180129 19:39:59 server id 1 end_log_pos 336 CRC32 0x73fe23e9 Query thread_id=8 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1517225999/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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 t1 (id int ,name varchar(100)) --创建表操作
/*!*/;
# at 336
#180129 19:40:11 server id 1 end_log_pos 401 CRC32 0xef54b9f2 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 401
#180129 19:40:11 server id 1 end_log_pos 473 CRC32 0x59a6a2e9 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1517226011/*!*/;
BEGIN
/*!*/;
# at 473
#180129 19:40:11 server id 1 end_log_pos 521 CRC32 0xbab0d07c Table_map: `test`.`t1` mapped to number 110
# at 521
#180129 19:40:11 server id 1 end_log_pos 567 CRC32 0xfb0647ba Write_rows: table id 110 flags: STMT_END_F
BINLOG '
GwhvWhMBAAAAMAAAAAkCAAAAAG4AAAAAAAEABHRlc3QAAnQxAAIDDwIsAQN80LC6
GwhvWh4BAAAALgAAADcCAAAAAG4AAAAAAAEAAgAC//wBAAAABABBbGVuukcG+w==
'/*!*/;
### INSERT INTO `test`.`t1` --向表插入数据
### SET
### @1=1
### @2='Alen'
# at 567
#180129 19:40:11 server id 1 end_log_pos 598 CRC32 0x1e7e6eac Xid = 157
COMMIT/*!*/;
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*/;
[root@strong mysql]# mysql> set binlog_format='STATEMENT'; --设置Binlog格式为Statement
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)再次查看Binlog日志：
mysql> insert into t1 values(2,'Liu');
Query OK, 1 row affected (0.03 sec)Binlog日志为：
[root@strong ~]# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180129 19:35:40 server id 1 end_log_pos 123 CRC32 0x62ebeb85 Start: binlog v 4, server v 5.7.21-log created 180129 19:35:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
DAdvWg8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAMB29aEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AYXr62I=
'/*!*/;
# at 123
#180129 19:35:40 server id 1 end_log_pos 154 CRC32 0xb0a4f258 Previous-GTIDs
# [empty]
# at 154
#180129 19:39:59 server id 1 end_log_pos 219 CRC32 0xbbb8e212 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180129 19:39:59 server id 1 end_log_pos 336 CRC32 0x73fe23e9 Query thread_id=8 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1517225999/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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 t1 (id int ,name varchar(100))
/*!*/;
# at 336
#180129 19:40:11 server id 1 end_log_pos 401 CRC32 0xef54b9f2 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 401
#180129 19:40:11 server id 1 end_log_pos 473 CRC32 0x59a6a2e9 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1517226011/*!*/;
BEGIN
/*!*/;
# at 473
#180129 19:40:11 server id 1 end_log_pos 521 CRC32 0xbab0d07c Table_map: `test`.`t1` mapped to number 110
# at 521
#180129 19:40:11 server id 1 end_log_pos 567 CRC32 0xfb0647ba Write_rows: table id 110 flags: STMT_END_F
BINLOG '
GwhvWhMBAAAAMAAAAAkCAAAAAG4AAAAAAAEABHRlc3QAAnQxAAIDDwIsAQN80LC6
GwhvWh4BAAAALgAAADcCAAAAAG4AAAAAAAEAAgAC//wBAAAABABBbGVuukcG+w==
'/*!*/;
# at 567
#180129 19:40:11 server id 1 end_log_pos 598 CRC32 0x1e7e6eac Xid = 157
COMMIT/*!*/;
# at 598
#180129 19:49:44 server id 1 end_log_pos 663 CRC32 0xfc71131a Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 663
#180129 19:49:44 server id 1 end_log_pos 742 CRC32 0x585060c0 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1517226584/*!*/;
BEGIN
/*!*/;
# at 742
#180129 19:49:44 server id 1 end_log_pos 846 CRC32 0x128a929f Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1517226584/*!*/;
insert into t1 values(2,'Liu')
/*!*/;
# at 846
#180129 19:49:44 server id 1 end_log_pos 877 CRC32 0xa347089b Xid = 354
COMMIT/*!*/;
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*/;四 刷新Binlog
mysql> system ls -l /var/lib/mysql/binlog/
total 8
-rw-r-----. 1 mysql mysql 877 Jan 29 19:49 mysql-bin.000001
-rw-r-----. 1 mysql mysql 39 Jan 29 19:35 mysql-bin.index
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> system ls -l /var/lib/mysql/binlog/
total 20
-rw-r-----. 1 mysql mysql 924 Jan 29 19:55 mysql-bin.000001
-rw-r-----. 1 mysql mysql 201 Jan 29 19:55 mysql-bin.000002
-rw-r-----. 1 mysql mysql 201 Jan 29 19:55 mysql-bin.000003
-rw-r-----. 1 mysql mysql 154 Jan 29 19:55 mysql-bin.000004
-rw-r-----. 1 mysql mysql 156 Jan 29 19:55 mysql-bin.index
mysql> 五 删除Binlog
对于比较繁忙的OLTP系统，每天会生成很大的日志量，这些日志如果长期不清除，将会对磁盘带来很大的浪费，因此，需定期清除Binlog，删除的方法有以下几种：
1、执行Reset Master命令，该命令删除所有Binlog日志，新日志编号从000001开始：
mysql> system ls -l /var/lib/mysql/binlog/
total 20
-rw-r-----. 1 mysql mysql 924 Jan 29 19:55 mysql-bin.000001
-rw-r-----. 1 mysql mysql 201 Jan 29 19:55 mysql-bin.000002
-rw-r-----. 1 mysql mysql 201 Jan 29 19:55 mysql-bin.000003
-rw-r-----. 1 mysql mysql 154 Jan 29 19:55 mysql-bin.000004
-rw-r-----. 1 mysql mysql 156 Jan 29 19:55 mysql-bin.index
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> system ls -l /var/lib/mysql/binlog/
total 8
-rw-r-----. 1 mysql mysql 154 Jan 29 19:56 mysql-bin.000001
-rw-r-----. 1 mysql mysql 39 Jan 29 19:56 mysql-bin.indexmysql> system ls -l /var/lib/mysql/binlog/
total 40
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000001
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000002
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000003
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000004
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000005
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000006
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000007
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000008
-rw-r-----. 1 mysql mysql 154 Jan 29 19:56 mysql-bin.000009
-rw-r-----. 1 mysql mysql 351 Jan 29 19:56 mysql-bin.index
mysql> purge master logs to 'mysql-bin.000005';
Query OK, 0 rows affected (0.01 sec)
mysql> system ls -l /var/lib/mysql/binlog/
total 24
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000005
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000006
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000007
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000008
-rw-r-----. 1 mysql mysql 154 Jan 29 19:56 mysql-bin.000009
-rw-r-----. 1 mysql mysql 195 Jan 29 19:57 mysql-bin.indexmysql> system ls -l /var/lib/mysql/binlog/
total 24
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000005
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000006
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000007
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000008
-rw-r-----. 1 mysql mysql 154 Jan 29 19:56 mysql-bin.000009
-rw-r-----. 1 mysql mysql 195 Jan 29 19:57 mysql-bin.index
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-01-29 19:58:06 |
+---------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> system ls -l /var/lib/mysql/binlog/
total 36
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000005
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000006
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000007
-rw-r-----. 1 mysql mysql 201 Jan 29 19:56 mysql-bin.000008
-rw-r-----. 1 mysql mysql 201 Jan 29 19:59 mysql-bin.000009
-rw-r-----. 1 mysql mysql 201 Jan 29 19:59 mysql-bin.000010
-rw-r-----. 1 mysql mysql 201 Jan 29 19:59 mysql-bin.000011
-rw-r-----. 1 mysql mysql 154 Jan 29 19:59 mysql-bin.000012
-rw-r-----. 1 mysql mysql 312 Jan 29 19:59 mysql-bin.index
mysql> purge master logs before '2018-01-29 19:59:06';
Query OK, 0 rows affected (0.01 sec)
mysql> system ls -l /var/lib/mysql/binlog/
total 20
-rw-r-----. 1 mysql mysql 201 Jan 29 19:59 mysql-bin.000009
-rw-r-----. 1 mysql mysql 201 Jan 29 19:59 mysql-bin.000010
-rw-r-----. 1 mysql mysql 201 Jan 29 19:59 mysql-bin.000011
-rw-r-----. 1 mysql mysql 154 Jan 29 19:59 mysql-bin.000012
-rw-r-----. 1 mysql mysql 156 Jan 29 20:00 mysql-bin.index
mysql> 