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

搞到这里,我放弃了、放弃了、弃了、了。实在是命令太少了,这不是我这次的重点,所以我还是去用我自己的dockerfile来启动一个镜像。省略掉中间的一些步骤~

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()函数, lastinsertid(),以及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配置。

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  

参考地址:

简栈文化服务订阅号