MySQL的binlog日志
# binlog介绍
binlog是Mysql sever数据库维护的一种二进制
日志,与innodb引擎中的redo/undo log是完全不同的日志;其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句(查询的日志不记录),并以事务的形式保存在磁盘中。
MySQL Server 有四种类型的日志:
Error Log
错误日志,记录 mysqld 的一些错误信息。
General Query Log
一般查询日志,记录 mysqld 正在做的事情,比如客户端的连接和断开、来自客户端每条 Sql Statement 记录信息;例如:查看客户端到底传了什么信息给服务端,这个日志就非常管用了,不过它非常影响性能
Binary Log
包含了一些事件,这些事件描述了数据库的改动,如建表、数据改动等,也包括一些潜在改动,比如
DELETE FROM ran WHERE bing = luan;
Binlog 就有了两个重要的用途——复制和恢复。比如主从表的复制,和备份恢复。
Slow Query Log
慢查询日志,记录一些查询比较慢的 SQL 语句——这种日志非常常用,主要是给开发者调优用的。
# binlog使用
# 启用binlog
要启用binlog,必须在mysql的/etc/my.cnf
文件中进行配置。
编辑my.cnf文件
默认情况 MySQL 的 binlog是关闭状态的,所以需要先启动binlog。
修改 my.cnf
,然后在mysqld
选项下添加如下值:
[mysqld]
## binlog日志文件的前缀
log-bin=mysql-bin
## binlog的index文件的名称
log-bin-index=mysql-bin.index
## binlog的工作模式
binlog_format='ROW'
2
3
4
5
6
7
# MySQL binlog的三种工作模式
Row level
日志中会记录每一行数据被修改的情况,然后在slave端对相同的数据进行修改。
优点:能清楚的记录每一行数据修改的细节
缺点:数据量太大
Statement level(默认)
每一条被修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
优点:解决了 Row level下的缺点,不需要记录每一行的数据变化,减少bin-log日志量,节约磁盘IO,提高新能
缺点:容易出现主从复制不一致
Mixed(混合模式)
结合了Row level和Statement level的优点
# 重启mysql
注意:对mysql的/etc/my.cnf文件的修改,需要重启MySQL服务!
centos下重启命令:
[root@mini1 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
2
3
mac下重启命令:
# 启动MySQL服务
sudo /usr/local/MySQL/support-files/mysql.server start
# 停止MySQL服务
sudo /usr/local/mysql/support-files/mysql.server stop
# 重启MySQL服务
sudo /usr/local/mysql/support-files/mysql.server restart
2
3
4
5
6
7
8
Mac下也可以通过偏好设置里来启动和停止MySQL
# 查看binlog开启状态
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
2
3
4
5
6
7
8
9
10
11
提示
log_bin等于on表示成功开启了binlog
# 查看master状态
即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin.000020 | 56217764 | | | ad9a34e4-bd71-11e9-b5b2-00163e143f99:1-19076813 |
+------------------+----------+--------------+------------------+-------------------------------------------------+
1 row in set (0.03 sec)
2
3
4
5
6
7
# 查看binlog日志
[root@mini1 ~]# cd /var/lib/mysql/
[root@mini1 mysql]# ll
total 176152
-rw-rw----. 1 mysql mysql 56 Apr 19 17:56 auto.cnf
drwx------. 2 mysql mysql 4096 Jun 23 03:40 hive_metadata
-rw-rw----. 1 mysql mysql 79691776 Sep 27 00:07 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Sep 27 00:07 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Apr 19 17:56 ib_logfile1
drwx------. 2 mysql mysql 4096 Apr 19 17:56 mysql
-rw-rw----. 1 mysql mysql 120 Sep 27 00:07 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 Sep 27 00:07 mysql-bin.index
srwxrwxrwx. 1 mysql mysql 0 Sep 27 00:07 mysql.sock
drwx------. 2 mysql mysql 4096 Apr 19 17:56 performance_schema
[root@mini1 mysql]# mysqlbinlog mysql-bin.000001
mysqlbinlog: unknown variable 'default-character-set=utf8'
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysqlbinlog: unknown variable 'default-character-set=utf8'
直接cd进到刚才binlog存储的目录,发现使用mysqlbinlog工具查看的时候,出现了个错误。mysqlbinlog: unknown variable 'default-character-set=utf8'
原因是:mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8
这个指令
解决办法:
把my.cnf文件中client下的
default-character-set=utf8
修改成character-set-server = utf8
然后重启MySQL服务永久生效
无需重启环境,使用
--no-defaults
使用
mysqlbinlog --no-defaults mysql-bin.000001
的--no-defaults
选项来查看binlog日志
[root@mini1 mysql]# mysqlbinlog --no-defaults mysql-bin.000001
/*!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
#180927 0:07:30 server id 1 end_log_pos 120 CRC32 0xcbc08ea5 Start: binlog v 4, server v 5.6.40-log created 180927 0:07:30 at startup
ROLLBACK/*!*/;
BINLOG '
wq6rWw8BAAAAdAAAAHgAAAAAAAQANS42LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADCrqtbEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaWO
wMs=
'/*!*/;
# at 120
#180927 0:09:43 server id 1 end_log_pos 167 CRC32 0xb760697c Rotate to mysql-bin.000002 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# binlog的解析
对应MySQL的binlog
的解析,可先通过如下文章了解binlog
的组成。
MySQL Binlog解析(1) (opens new window)
MySQL Binlog解析(2) (opens new window)
实际工作中,可使用canal
、maxwell
、mysql_streamer
等工具来解析binlog
日志
# canal、maxwell、mysql_streamer对比
# Maxwell
maxwell 简介
Maxwell是一个能实时读取MySQL二进制日志binlog,并生成 JSON 格式的消息,作为生产者发送给 Kafka
Kinesis、RabbitMQ、Redis、Google Cloud Pub/Sub、文件或其它平台的应用程序。它的常见应用场景有ETL、维护
缓存、收集表级别的dml指标、增量到搜索引擎、数据分区迁移、切库binlog回滚方案等。官网 (opens new window)、GitHub (opens new window)
Maxwell主要提供了下列功能:
- 支持
SELECT * FROM table
的方式进行全量数据初始化 - 支持在主库发生failover后,自动恢复binlog位置(GTID)
- 可以对数据进行分区,解决数据倾斜问题,发送到kafka的数据支持database、table、column等级别的数据分区
- 工作方式是伪装为Slave,接收binlog events,然后根据schemas信息拼装,可以接受ddl、xid、row等各种event
# canal
canal 是阿里巴巴开源的由Java开发的一个MySQL的增量订阅&消费组件。分为服务端和客户端,拥有众多的衍生应用,性能稳定,功能强大;
canal 需要自己编写客户端来消费canal解析到的数据。
maxwell
相对于canal
的优势是使用简单,它直接将数据变更输出为json字符串,不需要再编写客户端。而canal可以自定义客户端,可以将获取到的增量数据直接发送到MQ(kafka)中。
# 附录
# binlog常用命令
## 查看binlog日志状态:即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
## 查看所有binlog日志列表
mysql> show master logs;
## 刷新binlog日志文件:刷新之后会新建一个新的Binlog日志
mysql> flush logs;
## 清空日志文件
mysql> reset master;
## 查看binlog日志文件
$ mysqlbinlog --no-defaults mysql-bin.000002
2
3
4
5
6
7
8
9
10
# my.cnf文件位置
默认情况下,MySQL将会按照给定的顺序从以下位置查找my.cnf文件
$ mysql --help or mysql --help | grep my.cnf
/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
/usr/local/mysql/my.cnf
~/.my.cnf
2
3
4
5
创建my.cnf文件
在5.7.18版本之前,MySQL默认在/usr/local/mysql/support-files
这个文件夹中提供了一个/usr/local/mysql/support-files/my-default.cnf
文件,将该文件copy至MySQL扫描的那几个目录即可。
但是:在5.7.18版本的MySQL之后,它不提供support-files
目录中的默认配置文件。因此,您可以在MySQL将要读取的位置手动创建my.cnf
文件,例如/etc/mysql/my.cnf
,并在文件中添加要添加的配置。
$ sudo vi /etc/my.cnf
# MySQL5.7的my.cnf文件配置示例
[client]
port = 3306
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
[mysqld]
##################
# summary
##################
bind-address = 0.0.0.0
port = 3306
basedir=/usr/local/mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir = /tmp
pid-file=/tmp/mysqld.pid
#skip-grant-tables
#skip-networking
explicit_defaults_for_timestamp=1
lower_case_table_names=1
table_open_cache = 8000
##################
# time out
##################
connect_timeout = 20
wait_timeout = 86400
##################
# connection
##################
max_connections = 2000
max_user_connections = 1900
max_connect_errors = 100000
max_allowed_packet = 1G
##################
# character set
##################
character-set-server = utf8mb4
collation-server = utf8mb4_bin
##################
# log bin
##################
server-id = 1
log_bin = mysql-bin
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7
binlog_cache_size = 128m
max_binlog_cache_size =512m
max_binlog_size =256M
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys
##################
# log relay
##################
relay_log = mysql-relay-bin
relay_log_purge = on
relay_log_recovery = on
max_relay_log_size = 1G
##################
# log error
##################
log_error=/var/log/mysqld_error.log
##################
# log slow
##################
slow_query_log = on
slow_query_log_file = /var/log/mysqld_slow.log
long_query_time = 2
log_queries_not_using_indexes = on
##################
# log general
##################
general_log = on
general_log_file = /var/log/mysqld_gener.log
##################
# thread pool
##################
#thread_handling=pool-of-threads
#thread_handling=one-thread-per-connection
#thread_pool_oversubscribe=8
##################
# innodb
##################
innodb_file_per_table=1
innodb_log_file_size=1024M
innodb_log_buffer_size=64M
##################
# password policy
# after init
##################
#validate_password_length=4
#validate_password_mixed_case_count=0
#validate_password_number_count=0
#validate_password_policy=0
#validate_password_special_char_count=0
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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118