二进制日志
概念
-
记录对数据发生或潜在发生更改的SQL语句,并且是以二进制格式保存的日志
使用用途
-
查看数据库变更历史
-
数据库增量备份
-
数据库灾难恢复
-
MySQL复制(主从、主主复制)
二进制日志性能影响
-
日志即影响MySQL性能又占用大量磁盘空间。因此,往往需要做采样分析时才会打开
-
即使做采样分析,也最好仅在一台测试机上开启
-
二进制日志由于用途广泛,大多数情况下会开启。需要制定合理的备份计划和管理策略
开启二进制日志
方法一:不重启修改二进制日志配置
-
SET @@global.log_bin=1;
-
SET @@global.binlog_szie=37268;(单位:bytes)
-
其他参数可以通过以下命令查阅:
SHOW VARIABLES LIKE '%bin%';
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
|
mysql> show variables like
'%bin%'
;
+-----------------------------------------+------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_gtid_simple_recovery | OFF |
| 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 |
| log_bin | ON |
| log_bin_basename |
/data/3306/mysql-bin/mysql-bin
|
| log_bin_index |
/data/3306/mysql-bin/mysql-bin
.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+------------------------------------------+
26 rows
in
set
(0.00 sec)
|
常用二进制日志相关配置参数
-
log-bin=
#开启并指定二进制日志保存路劲及文件名,不设置则使用默认值。默认存放位置为数据库文件所目录下,名称为hostname-bin.xxxxx
-
max-binlog-size=500m
#设置单个二进制日志文件的最大值,默认1G,最大1G
-
binlog-do-db与binlog-ignore-db
#指定二进制日志文件记录哪些数据库操作
-
binlog-cache-size=100m
#设置二进制日志缓存大小
-
sync-binlog=N
#每隔N秒将缓存中的二进制日志记录写回硬盘。默认为0。不过,你经常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中,由于考虑到耐久性和一致性,则需要设置为1。同时,还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-ax=1(默认已开启)
暂停二进制日志
-
SET sql_log_bin={0|1};
查看二进制日志
-
mysqlbinlog host2-bin.000001
创建一个数据库并做相应修改,并查看二进制日志的变化
mysql> create database t1;
mysql> create table tt1(id int,name varchar(20),birthday date);
mysql> insert into tt1(id,name,birthday) values('1','demi','1991-10-12');
mysql> insert into tt1(id,name,birthday) values('2','hoai','1992-02-20');
1
2
3
4
5
6
7
8
9
10
|
mysql>
select
* from tt1;
+------+------+------------+
|
id
| name | birthday |
+------+------+------------+
| 1 | demi | 1991-10-12 |
| 2 | hoai | 1992-02-20 |
| 3 | wiss | 1991-07-14 |
| 4 | kime | 1993-06-18 |
+------+------+------------+
4 rows
in
set
(0.00 sec)
|
mysql> update tt1 set birthday='1991-06-28' where id=4;
1
2
3
4
5
6
7
8
9
10
|
mysql>
select
* from tt1;
+------+------+------------+
|
id
| name | birthday |
+------+------+------------+
| 1 | demi | 1991-10-12 |
| 2 | hoai | 1992-02-20 |
| 3 | wiss | 1991-07-14 |
| 4 | kime | 1991-06-28 |
+------+------+------------+
4 rows
in
set
(0.00 sec)
|
使用mysqlbinlog命令查看二进制日志的变化
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
[root@slave1 ~]
# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011
/*!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
#160314 18:34:51 server id 3 end_log_pos 120 CRC32 0xfd6a32fe Start: binlog v 4, server v 5.6.27-log created 160314 18:34:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
y5PmVg8DAAAAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADLk+ZWEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf4y
av0=
'/*!*/;
# at 120
#160314 21:03:56 server id 3 end_log_pos 208 CRC32 0x12f2847a Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457960636/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database t1
/*!*/;
# at 208
#160314 21:08:36 server id 3 end_log_pos 333 CRC32 0x806b0266 Query thread_id=4 exec_time=0 error_code=0
use `t1`/*!*/;
SET TIMESTAMP=1457960916/*!*/;
create table tt1(
id
int,name varchar(20),birthday
date
)
/*!*/;
# at 333
#160314 21:10:05 server id 3 end_log_pos 408 CRC32 0xc4950130 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961005/*!*/;
BEGIN
/*!*/;
# at 408
#160314 21:10:05 server id 3 end_log_pos 543 CRC32 0xab0bc88f Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961005/*!*/;
insert into tt1(
id
,name,birthday) values(
'1'
,
'demi'
,
'1991-10-12'
)
/*!*/;
# at 543
#160314 21:10:05 server id 3 end_log_pos 574 CRC32 0xacd9a973 Xid = 27
COMMIT/*!*/;
# at 574
#160314 21:10:40 server id 3 end_log_pos 649 CRC32 0xb6e8ef83 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961040/*!*/;
BEGIN
/*!*/;
# at 649
#160314 21:10:40 server id 3 end_log_pos 784 CRC32 0xef33bae2 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961040/*!*/;
insert into tt1(
id
,name,birthday) values(
'2'
,
'hoai'
,
'1992-02-20'
)
/*!*/;
# at 784
#160314 21:10:40 server id 3 end_log_pos 815 CRC32 0xc1ec540f Xid = 29
COMMIT/*!*/;
# at 815
#160314 21:11:00 server id 3 end_log_pos 890 CRC32 0xb4e3c45e Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961060/*!*/;
BEGIN
/*!*/;
# at 890
#160314 21:11:00 server id 3 end_log_pos 1025 CRC32 0xf8584b1d Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961060/*!*/;
insert into tt1(
id
,name,birthday) values(
'3'
,
'wiss'
,
'1991-07-14'
)
/*!*/;
# at 1025
#160314 21:11:00 server id 3 end_log_pos 1056 CRC32 0xe75877f8 Xid = 30
COMMIT/*!*/;
# at 1056
#160314 21:11:33 server id 3 end_log_pos 1131 CRC32 0x12b7b345 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961093/*!*/;
BEGIN
/*!*/;
# at 1131
#160314 21:11:33 server id 3 end_log_pos 1266 CRC32 0xab6f89cc Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961093/*!*/;
insert into tt1(
id
,name,birthday) values(
'4'
,
'kime'
,
'1993-06-18'
)
/*!*/;
# at 1266
#160314 21:11:33 server id 3 end_log_pos 1297 CRC32 0x2e4dbdb4 Xid = 31
COMMIT/*!*/;
# at 1297
#160314 21:14:04 server id 3 end_log_pos 1372 CRC32 0x571a3dda Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961244/*!*/;
BEGIN
/*!*/;
# at 1372
#160314 21:14:04 server id 3 end_log_pos 1489 CRC32 0xaee0efb9 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1457961244/*!*/;
update tt1
set
birthday=
'1991-06-28'
where
id
=4
/*!*/;
# at 1489
#160314 21:14:04 server id 3 end_log_pos 1520 CRC32 0xa7faed24 Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
修改二进制日志记录配置为ROW方式(ROW记录的二进制信息最为完整)
mysql> set @@global.binlog_format=ROW;
mysql> set binlog_format=ROW;
1
2
3
4
5
6
7
|
mysql> show variables like
'binlog_format'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row
in
set
(0.00 sec)
|
修改mysql数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> update tt1
set
birthday=
'1992-12-20'
where
id
=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
select
* from tt1;
+------+------+------------+
|
id
| name | birthday |
+------+------+------------+
| 1 | demi | 1992-12-20 |
| 2 | hoai | 1992-02-20 |
| 3 | wiss | 1991-07-14 |
| 4 | kime | 1991-06-28 |
+------+------+------------+
4 rows
in
set
(0.00 sec)
|
查看二进制日志的变化
[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011
1
2
3
4
5
6
7
8
9
10
11
12
13
|
BINLOG '
07zmVhMDAAAAMAAAAGYGAAAAAEcAAAAAAAEAAnQxAAN0dDEAAwMPCgI8AAe2N8
/Z
07zmVh8DAAAAPgAAAKQGAAAAAEcAAAAAAAEAAgAD
///4AQAAAARkZW1pTI8P
+AEAAAAEZGVtaZSR
D3vrjpU=
'/*!*/;
# at 1700
#160314 21:29:55 server id 3 end_log_pos 1731 CRC32 0xff29a00b Xid = 41
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011 -v
#加-v解析二进制日志
本文转自 HMLinux 51CTO博客,原文链接:http://blog.51cto.com/7424593/1751071