TheRiver | blog

You have reached the world's edge, none but devils play past here

0%

mysql binlog

Posted on Edited on
Symbols count in article: 9.8k Reading time ≈ 9 mins.

参考

MySQL实战45讲

mysql技术内幕

关于binary log那些事

查看配置信息

查看binlog是否打开以及配置信息：

1
2
3
4
5
6
7
8
9
10
11
12
MySQL [mysql]> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-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)

查看配置文件：

log_bin是打开binlog的开关.

1
2
3
4
5
6
7
8
9
10
[root@xxxx]# mysql --help | grep cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
[root@xxxx]# cat /etc/my.cnf
[mysqld]
...
 
log_bin = mysql-bin
binlog-format=STATEMENT 
expire_logs_days = 30

文件列表：

index是binlog的索引文件，记录所有的binlog日志文件。binlog日志是可续写的，不停有新的文件产生。而redo log是循环写的，固定文件个数，满了后从头开始。

1
2
3
4
5
6
7
8
9
10
11
[root@xxxx]# ll mysql-bin.*
-rw-r----- 1 mysql mysql   177 Jul 31 17:22 mysql-bin.000001
-rw-r----- 1 mysql mysql   177 Jul 31 17:26 mysql-bin.000002
-rw-r----- 1 mysql mysql  1321 Jul 31 17:36 mysql-bin.000003
-rw-r----- 1 mysql mysql 21167 Aug 20 10:50 mysql-bin.000004
-rw-r----- 1 mysql mysql    76 Jul 31 17:36 mysql-bin.index
[root@xxxx]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004

参数含义

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
+--------------------------------------------+---------------------------------------+
| Variable_name                              | Value                                 |
+--------------------------------------------+---------------------------------------+
| binlog_cache_size                          | 32768                                 |
| binlog_checksum                            | CRC32                                 |
| binlog_direct_non_transactional_updates    | OFF                                   |
| binlog_error_action                        | ABORT_SERVER                          |
| binlog_format                              | STATEMENT                             |
| binlog_group_commit_sync_delay             | 0                                     |
| binlog_group_commit_sync_no_delay_count    | 0                                     |
| binlog_gtid_simple_recovery                | ON                                    |
| 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                                 |
| binlog_transaction_dependency_history_size | 25000                                 |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER                          |
| innodb_api_enable_binlog                   | OFF                                   |
| innodb_locks_unsafe_for_binlog             | OFF                                   |
| log_bin                                    | ON                                    |
| log_bin_basename                           | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                              | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators            | OFF                                   |
| log_bin_use_v1_row_events                  | OFF                                   |
| log_statements_unsafe_for_binlog           | ON                                    |
| max_binlog_cache_size                      | 18446744073709547520                  |
| max_binlog_size                            | 1073741824                            |
| max_binlog_stmt_cache_size                 | 18446744073709547520                  |
| sql_log_bin                                | ON                                    |
| sync_binlog                                | 1                                     |
+--------------------------------------------+---------------------------------------+

max_binlog_size表示单个binlog日志文件的大小，默认是1g,超过后产生新文件

binlog_cache_size表示binlog在内存的缓冲区大小，默认32k,这个大小是基于会话的，所以不能太大。也不能太小，因为io压力会大

sync_binlog是否同步binlog到磁盘，就不写缓冲区了。高可用但是对磁盘io有压力

binlog_format

binlog_formatmysql5.1引入的，可设置的值有：statement, row, mixed.

1 statement: 记录逻辑日志
2 row: 记录表的行更改情况
3 mixed: 默认采用statement格式记录日志，但是在一些情况下会使用row格式，可能的情况有

  • 表的存储引擎为ndb,这时对表的dml操作
  • 使用了uuid(), user(), current_user(), found_user(), row_count()等不确定函数
  • 使用了insert delay语句
  • 使用了用户定义函数
  • 使用了临时表

对比：

statement: update tt set d = 4 where c = 4
row: update tt set d = 5 where c = 4;

show binlog events in ‘mysql-bin.000004’;

1
2
3
4
5
6
7
8
| mysql-bin.000004 | 20948 | Query          |         1 |       21029 | BEGIN                                                                                                                                                                                                                                                                             |
| mysql-bin.000004 | 21029 | Query          |         1 |       21136 | use `mysql`; update tt set d = 4 where c = 4                                                                                                                                                                                                                                      |
| mysql-bin.000004 | 21136 | Xid            |         1 |       21167 | COMMIT /* xid=645 */                                                                                                                                                                                                                                                              |
| mysql-bin.000004 | 21167 | Anonymous_Gtid |         1 |       21232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                              |
| mysql-bin.000004 | 21232 | Query          |         1 |       21305 | BEGIN                                                                                                                                                                                                                                                                             |
| mysql-bin.000004 | 21305 | Table_map      |         1 |       21353 | table_id: 146 (mysql.tt)                                                                                                                                                                                                                                                          |
| mysql-bin.000004 | 21353 | Update_rows    |         1 |       21415 | table_id: 146 flags: STMT_END_F                                                                                                                                                                                                                                                   |
| mysql-bin.000004 | 21415 | Xid            |         1 |       21446 | COMMIT /* xid=662 */

/usr/local/mysql/mysql/bin/mysqlbinlog mysql-bin.000004 -vv

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
BEGIN
/*!*/;
# at 21029
#200820 10:50:40 server id 1  end_log_pos 21136 CRC32 0xab574d07        Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1597891840/*!*/;
update tt set d = 4 where c = 4
/*!*/;
# at 21136
#200820 10:50:40 server id 1  end_log_pos 21167 CRC32 0xdd08f243        Xid = 645
COMMIT/*!*/;
# at 21167
#200820 11:26:28 server id 1  end_log_pos 21232 CRC32 0xe81ffc29        Anonymous_GTID  last_committed=76       sequence_number=77      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 21232
#200820 11:26:28 server id 1  end_log_pos 21305 CRC32 0x2e084ad1        Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1597893988/*!*/;
BEGIN
/*!*/;
# at 21305
#200820 11:26:28 server id 1  end_log_pos 21353 CRC32 0x13267696        Table_map: `mysql`.`tt` mapped to number 146
# at 21353
#200820 11:26:28 server id 1  end_log_pos 21415 CRC32 0xf078d9cb        Update_rows: table id 146 flags: STMT_END_F

BINLOG '
ZO09XxMBAAAAMAAAAGlTAAAAAJIAAAAAAAEABW15c3FsAAJ0dAADAwMDAAaWdiYT
ZO09Xx8BAAAAPgAAAKdTAAAAAJIAAAAAAAEAAgAD///4FAAAAAQAAAAEAAAA+BQAAAAEAAAABQAA
AMvZePA=
'/*!*/;
### UPDATE `mysql`.`tt`
### WHERE
###   @1=20 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=20 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3=5 /* INT meta=0 nullable=1 is_null=0 */
# at 21415
#200820 11:26:28 server id 1  end_log_pos 21446 CRC32 0xfa03d621        Xid = 662
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_rows_query_log_events

表示binlog日志中row格式下用不用打印执行语句，从前面的事件看，row格式下默认不打印执行语句的。这个参数默认是off:

查看并修改binlog_rows_query_log_events

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MySQL [mysql]> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF   |
+------------------------------+-------+
1 row in set (0.00 sec)

MySQL [mysql]> set binlog_rows_query_log_events = 'ON';
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | ON    |
+------------------------------+-------+
1 row in set (0.00 sec)

dml查看binlog打印结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MySQL [mysql]> update tt set d = 20 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [mysql]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |    21782 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MySQL [mysql]> update tt set d = 200 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [mysql]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |    22119 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
[root@xxxx ]# /usr/local/mysql/mysql/bin/mysqlbinlog mysql-bin.000004 -vv --start-position=21782 --stop-position=22119 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200731 17:36:11 server id 1  end_log_pos 123 CRC32 0xb2d914dd  Start: binlog v 4, server v 5.7.24-log created 200731 17:36:11 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
C+YjXw8BAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAL5iNfEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ad0U2bI=
'/*!*/;
# at 21782
#200820 14:37:40 server id 1  end_log_pos 21847 CRC32 0x78241dac        Anonymous_GTID  last_committed=78       sequence_number=79      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 21847
#200820 14:37:40 server id 1  end_log_pos 21920 CRC32 0xf4915dd7        Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1597905460/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
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 latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 21920
#200820 14:37:40 server id 1  end_log_pos 21978 CRC32 0x3308d5eb        Rows_query
# update tt set d = 200 where id = 2
# at 21978
#200820 14:37:40 server id 1  end_log_pos 22026 CRC32 0x06c85227        Table_map: `mysql`.`tt` mapped to number 146
# at 22026
#200820 14:37:40 server id 1  end_log_pos 22088 CRC32 0xd1e44ca1        Update_rows: table id 146 flags: STMT_END_F

BINLOG '
NBo+Xx0BAAAAOgAAANpVAACAACJ1cGRhdGUgdHQgc2V0IGQgPSAyMDAgd2hlcmUgaWQgPSAy69UI
Mw==
NBo+XxMBAAAAMAAAAApWAAAAAJIAAAAAAAEABW15c3FsAAJ0dAADAwMDAAYnUsgG
NBo+Xx8BAAAAPgAAAEhWAAAAAJIAAAAAAAEAAgAD///4AgAAAAIAAAAUAAAA+AIAAAACAAAAyAAA
AKFM5NE=
'/*!*/;
### UPDATE `mysql`.`tt`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3=20 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3=200 /* INT meta=0 nullable=1 is_null=0 */
# at 22088
#200820 14:37:40 server id 1  end_log_pos 22119 CRC32 0x45b08113        Xid = 670
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*/;

ending

----------- ending -----------