binlog介绍

binlog是Mysql sever数据库维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句（查询的日志不记录），并以事务的形式保存在磁盘中。

MySQL Server 有四种类型的日志:

1. Error Log

   错误日志，记录 mysqld 的一些错误信息。

2. General Query Log

   一般查询日志，记录 mysqld 正在做的事情，比如客户端的连接和断开、来自客户端每条 Sql Statement 记录信息；例如：查看客户端到底传了什么信息给服务端，这个日志就非常管用了，不过它非常影响性能

3. Binary Log

   包含了一些事件，这些事件描述了数据库的改动，如建表、数据改动等，也包括一些潜在改动，比如DELETE FROM ran WHERE bing = luan;

   Binlog 就有了两个重要的用途——复制和恢复。比如主从表的复制，和备份恢复。

4. 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'

MySQL binlog的三种工作模式

1. Row level

   日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

   优点：能清楚的记录每一行数据修改的细节

   缺点：数据量太大

2. Statement level（默认）

   每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

   优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能

   缺点：容易出现主从复制不一致

3. Mixed（混合模式）

   结合了Row level和Statement level的优点

重启mysql

注意：对mysql的/etc/my.cnf文件的修改，需要重启MySQL服务！

centos下重启命令：

[root@mini1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

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

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                             |
+---------------------------------+--------------------------------+

提示

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)

查看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'

mysqlbinlog: unknown variable 'default-character-set=utf8'

直接cd进到刚才binlog存储的目录，发现使用mysqlbinlog工具查看的时候，出现了个错误。mysqlbinlog: unknown variable 'default-character-set=utf8'

原因是：mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令

解决办法：

1. 把my.cnf文件中client下的default-character-set=utf8修改成character-set-server = utf8

   然后重启MySQL服务永久生效

2. 无需重启环境，使用--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*/;

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 (opens new window)

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

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

创建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