MySQL中Binlog的常用设置

获取mysql镜像

➜  ~  docker pull mysql
➜  ~  docker images
REPOSITORY                                                                TAG                 IMAGE ID            CREATED             SIZE
mysql                                                                     latest              d435eee2caa5        3 weeks ago         456MB

启动mysql

➜  ~  docker run -itd --name docker-mysql-master -v /Users/chenyuan/Data/docker/mysql-data-master:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -p 33061:3306 mysql
88820868af121cbac02f48a8c8e5c9eae5c6cf7241eefd3646634e14526a940f
➜  ~  docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS                                NAMES
88820868af12        mysql               "docker-entrypoint.s…"   About a minute ago   Up About a minute   33060/tcp, 0.0.0.0:33061->3306/tcp   docker-mysql-master
➜  ~  docker exec -it 88820868af12 bash
root@88820868af12:/#
root@88820868af12:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

确定挂载的mysql-data文件

➜  mysql-data-master  ll
total 356592
drwxr-x---   12 chenyuan  staff   384B Dec 19 17:46 #innodb_temp
-rw-r-----    1 chenyuan  staff   1.2K Dec 19 17:46 88820868af12.err
-rw-r-----    1 chenyuan  staff    56B Dec 19 17:45 auto.cnf
-rw-r-----    1 chenyuan  staff   2.9M Dec 19 17:46 binlog.000001
-rw-r-----    1 chenyuan  staff   155B Dec 19 17:46 binlog.000002
-rw-r-----    1 chenyuan  staff    32B Dec 19 17:46 binlog.index
-rw-------    1 chenyuan  staff   1.6K Dec 19 17:45 ca-key.pem
-rw-r--r--    1 chenyuan  staff   1.1K Dec 19 17:45 ca.pem
-rw-r--r--    1 chenyuan  staff   1.1K Dec 19 17:45 client-cert.pem
-rw-------    1 chenyuan  staff   1.6K Dec 19 17:45 client-key.pem
-rw-r-----    1 chenyuan  staff   5.3K Dec 19 17:46 ib_buffer_pool
-rw-r-----    1 chenyuan  staff    48M Dec 19 17:46 ib_logfile0
-rw-r-----    1 chenyuan  staff    48M Dec 19 17:45 ib_logfile1
-rw-r-----    1 chenyuan  staff    12M Dec 19 17:46 ibdata1
-rw-r-----    1 chenyuan  staff    12M Dec 19 17:46 ibtmp1
drwxr-x---    8 chenyuan  staff   256B Dec 19 17:46 mysql
-rw-r-----    1 chenyuan  staff    29M Dec 19 17:46 mysql.ibd
drwxr-x---  105 chenyuan  staff   3.3K Dec 19 17:45 performance_schema
-rw-------    1 chenyuan  staff   1.6K Dec 19 17:45 private_key.pem
-rw-r--r--    1 chenyuan  staff   452B Dec 19 17:45 public_key.pem
-rw-r--r--    1 chenyuan  staff   1.1K Dec 19 17:45 server-cert.pem
-rw-------    1 chenyuan  staff   1.6K Dec 19 17:45 server-key.pem
drwxr-x---    3 chenyuan  staff    96B Dec 19 17:46 sys
-rw-r-----    1 chenyuan  staff    12M Dec 19 17:46 undo_001
-rw-r-----    1 chenyuan  staff    10M Dec 19 17:46 undo_002
➜  mysql-data-master  pwd
/Users/chenyuan/Data/docker/mysql-data-master

Binlog配置

查看binlog日志的地方，通过命令查看。因为没有设置过所以看不到。

mysql> mysql> show variables like '%datadir%';
Empty set (0.01 sec)

root@88820868af12:/etc/mysql# pwd
/etc/mysql
root@88820868af12:/etc/mysql# ls -l
total 12
drwxrwxr-x 1 root root 4096 Nov 23 01:48 conf.d
-rw-rw-r-- 1 root root 1174 Nov 23 01:48 my.cnf
-rw-r--r-- 1 root root 1469 Sep 20 09:04 my.cnf.fallback

# 后面设置好后，就能看到了。
mysql> show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

当前容器提交为镜像

到这里遇到一个非常好玩的事情，就是获取的mysql镜像是一个非常干净的容器，常用的命令都没有。比如：yum、ifconfig、cat等。所以我需要把当前的容器打一个镜像包。并且docker run的时候要挂载一个本地的目录，避免待会儿需要上传一些工具包。

docker commit [OPTIONS] CONTAINER [REPOSITORY[:TAG]]
-a :提交的镜像作者；
-c :使用Dockerfile指令来创建镜像；
-m :提交时的说明文字；
-p :在commit时，将容器暂停。

➜  ~  docker commit -a "chengcheng222e@sina.com" -m "created by vernon" 88820868af12  mysql-versnon:v1
sha256:e9691f399c321ea221b48e6142e9501f0ee69964fa4be687ac189f8444d75d66
➜  ~  docker images
REPOSITORY                                                                TAG                 IMAGE ID            CREATED             SIZE
mysql-versnon                                                             v1                  e9691f399c32        13 seconds ago      456MB
mysql                                                                     latest              d435eee2caa5        3 weeks ago         456MB

➜  Tools docker run -itd --name docker-mysql-master -v /Users/chenyuan/Data/docker/mysql-data-master:/var/lib/mysql -v /Users/chenyuan/Tools:/root/tools -e MYSQL_ROOT_PASSWORD=root -p 33061:3306 mysql-versnon:v1

这里注意一下，因为docker pull mysql镜像中的shell里面命令太少了，非常的不方便。这里建议大家还是利用dockefile的方式或者是docker pull centos的方式来安装mysql，不然你会怀疑人生。

通过dockerfile的方式：

FROM centos:7
MAINTAINER 2019-09-27 chenyuan chengcheng222e@sina.com

# Linux lib
RUN yum install -y tar
RUN yum install -y unzip
RUN yum install -y initscripts

# Software space
RUN mkdir -p ~/tools/
COPY jdk1.8.0_45.tar.gz ~/tools/
COPY mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz ~/tools/

# JDK
WORKDIR ~/tools/
RUN tar -zxvf jdk1.8.0_45.tar.gz
RUN mv jdk1.8.0_45 /opt/
RUN ln -s /opt/jdk1.8.0_45/bin/* /usr/local/sbin/
ENV JAVA_HOME /opt/jdk1.8.0_45
ENV JRE_HOME ${JAVA_HOME}/jre
ENV CLASSPATH .:${JAVA_HOME}/lib:${JRE_HOME}/lib
ENV PATH ${JAVA_HOME}/bin:$PATH

# MySQL
RUN cd ~/tools/
RUN yum -y install numactl
RUN yum -y install libaio
RUN yum -y install pwgen
RUN yum install -y perl-Data-Dumper
RUN tar -zxvf mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz
RUN mv mysql-5.6.45-linux-glibc2.12-x86_64 /usr/local/mysql
RUN groupadd mysql
RUN useradd -g mysql mysql
RUN chown -R mysql /usr/local/mysql
RUN chgrp -R mysql /usr/local/mysql

RUN cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
COPY my.cnf /etc/my.cnf
RUN /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
ENV PATH $PATH:/usr/local/mysql/bin
EXPOSE 3306

my.conf配置

修改my.conf的内容

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock

server-id = 1
log-bin=mysql-bin

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Binlog格式

Binlog的格式也有三种：STATEMENT、ROW、MIXED 。

STATMENT模式：基于SQL语句的复制（statement-based replication, SBR），每一条会修改数据的sql语句会记录到binlog中。
- 优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
- 缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
基于行的复制（row-based replication, RBR）：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。
- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
- 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
混合模式复制（mixed-based replication, MBR）：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

设置Binglog

通过show variables的方式来查看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   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.00 sec)

从上面可以看出，没有开启binlog日志，那么我们接下来开启binlog。

在/etc/my.cnf里面开启binlog配置。

1 2	server-id = 1 log-bin=mysql-bin

再次查看：

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.01 sec)

查看binlog_format

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
# 默认的格式就是 STATEMENT

确定binlog日志文件

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.45-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

创建表

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> CREATE TABLE `person` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `first_name` varchar(20) DEFAULT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   `gender` char(1) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.04 sec)
INSERT INTO test.person (id, first_name, age, gender) VALUES (1, 'Bob', 25, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (2, 'Jane', 20, 'F');
INSERT INTO test.person (id, first_name, age, gender) VALUES (3, 'Jack', 30, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (4, 'Bill', 32, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (5, 'Nick', 22, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (6, 'Kathy', 18, 'F');
INSERT INTO test.person (id, first_name, age, gender) VALUES (7, 'Steve', 36, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (8, 'Anne', 25, 'F');

查看binlog

mysql> show binlog events in 'mysql-bin.000001';
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                                         |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.45-log, Binlog ver: 4                                                                                                                                                                        |
| mysql-bin.000001 |  120 | Query       |         1 |         386 | use `test`; CREATE TABLE `person` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| mysql-bin.000001 |  386 | Query       |         1 |         465 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 |  465 | Query       |         1 |         619 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (1, 'Bob', 25, 'M')                                                                                                                 |
| mysql-bin.000001 |  619 | Xid         |         1 |         650 | COMMIT /* xid=14 */                                                                                                                                                                                          |
| mysql-bin.000001 |  650 | Query       |         1 |         729 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 |  729 | Query       |         1 |         884 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (2, 'Jane', 20, 'F')                                                                                                                |
| mysql-bin.000001 |  884 | Xid         |         1 |         915 | COMMIT /* xid=15 */                                                                                                                                                                                          |
| mysql-bin.000001 |  915 | Query       |         1 |         994 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 |  994 | Query       |         1 |        1149 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (3, 'Jack', 30, 'M')                                                                                                                |
| mysql-bin.000001 | 1149 | Xid         |         1 |        1180 | COMMIT /* xid=16 */                                                                                                                                                                                          |
| mysql-bin.000001 | 1180 | Query       |         1 |        1259 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 | 1259 | Query       |         1 |        1414 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (4, 'Bill', 32, 'M')                                                                                                                |
| mysql-bin.000001 | 1414 | Xid         |         1 |        1445 | COMMIT /* xid=17 */                                                                                                                                                                                          |
| mysql-bin.000001 | 1445 | Query       |         1 |        1524 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 | 1524 | Query       |         1 |        1679 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (5, 'Nick', 22, 'M')                                                                                                                |
| mysql-bin.000001 | 1679 | Xid         |         1 |        1710 | COMMIT /* xid=18 */                                                                                                                                                                                          |
| mysql-bin.000001 | 1710 | Query       |         1 |        1789 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 | 1789 | Query       |         1 |        1945 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (6, 'Kathy', 18, 'F')                                                                                                               |
| mysql-bin.000001 | 1945 | Xid         |         1 |        1976 | COMMIT /* xid=19 */                                                                                                                                                                                          |
| mysql-bin.000001 | 1976 | Query       |         1 |        2055 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 | 2055 | Query       |         1 |        2211 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (7, 'Steve', 36, 'M')                                                                                                               |
| mysql-bin.000001 | 2211 | Xid         |         1 |        2242 | COMMIT /* xid=20 */                                                                                                                                                                                          |
| mysql-bin.000001 | 2242 | Query       |         1 |        2321 | BEGIN                                                                                                                                                                                                        |
| mysql-bin.000001 | 2321 | Query       |         1 |        2476 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (8, 'Anne', 25, 'F')                                                                                                                |
| mysql-bin.000001 | 2476 | Xid         |         1 |        2507 | COMMIT /* xid=21 */                                                                                                                                                                                          |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
26 rows in set (0.01 sec)

常用命令

show variables like 'binlog_format'
set globle binlog_format='MIXED'
show variables like 'log_bin'
show binary logs

遇到问题:

[mysql@c738746e9623 support-files]$ ./mysql.server start
Starting MySQL... ERROR! The server quit without updating PID file (/usr/local/mysql/data/c738746e9623.pid).

log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
server-id=1

# 修改为
server-id = 1
log-bin=mysql-bin

参考地址：

https://www.cnblogs.com/grey-wolf/p/10437811.html

如果大家喜欢我的文章，可以关注个人订阅号。欢迎随时留言、交流。

简栈文化服务订阅号