Docker docker-compose mysql 主从同步

准备工作

单机伪集群方式 基于 docker-compose

mkdir -p /docker/mysql
# 准备 docker-compose.yml 文件
cat > /docker/mysql/docker-compose.yml <<EOF

version: '3.7'
services:
  mysql-master:
    build:
      context: ./
      dockerfile: master/Dockerfile
    # image: mysql:8
    container_name: mysql-master
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    privileged: true
    environment:
      MYSQL_ROOT_PASSWORD: "rootpass"
      #MYSQL_DATABASE: "test"
      #MYSQL_USER: "dbuser"
      #MYSQL_PASS: "dbpass"
    networks:
      - mysqlnet
    ports:
      - 3306:3306

  mysql-slave:
    build:
      context: ./
      dockerfile: slave/Dockerfile
    # image: mysql:8
    container_name: mysql-slave
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    privileged: true
    environment:
      MYSQL_ROOT_PASSWORD: "rootpass"
      #MYSQL_DATABASE: "test"
      #MYSQL_USER: "dbuser"
      #MYSQL_PASS: "dbpass"
    networks:
      - mysqlnet
    ports:
      - "3307:3306"

networks:
  mysqlnet:
    driver: overlay
    attachable: true
EOF

准备 master/slave 公用配置文件

cat > /docker/mysql/mysql-server-comm.cnf <<EOF
# mysql-server-comm.cnf 此文件为所有 mysql 数据库公用配置文件
[client]
default-character-set = utf8mb4

[mysql_safe]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysqld]
# MySQL 服务的唯一编号 几个 MySQL 服务 ID 需唯一(集群中)
server-id = 1 


# docker mysql image /etc/mysql/conf.d/docker.cnf已经有以下2个配置了
skip-host-cache
skip-name-resolve

# 修改时区为东8区
default-time-zone = '+8:00'

#pid-file      = /var/run/mysqld/mysqld.pid
#socket        = /var/run/mysqld/mysqld.sock
# 数据库的数据存放目录
datadir       = /var/lib/mysql
# 错误日志
log-error    = /var/lib/mysql/mysql-error.log

# 跳过密码登陆(取消注释重置 root 密码)
#skip-grant-tables

# 设置默认排序规则
collation-server         = utf8mb4_unicode_ci

# 客户端跟服务器字符编码不一致时拒绝连接
character-set-client-handshake = TRUE

# 指定初始化连接时的字符编码
init_connect          = 'SET NAMES utf8mb4' 
init_connect          = 'SET collation_connection = utf8mb4_unicode_ci'


# 时间格式的 0000 不报错处理
sql_mode = NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO


# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB

# 锁定等待时间由默认的50s 修改为500s
innodb_lock_wait_timeout = 500

# 事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = READ-COMMITTED

# 表名不分区大小写
lower_case_table_names=1

# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'

# 最大连接数
max_connections = 400

# 最大错误连接数
max_connect_errors = 1000

# TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true

# SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M

# 代表受信任的函数创建者，也就是使MySQL不对函数做出限制
log_bin_trust_function_creators = 1

# 旧密码插件
#default-authentication-plugin = mysql_native_password

# MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
# MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800

# 内部内存临时表的最大值 ，设置成128M。
# 比如大数据量的group by ,order by时可能用到临时表，
# 超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728


# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

# 检查未使用到索引的sql
log_queries_not_using_indexes = 1

# 针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5

# 作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1

# 慢查询执行的秒数，必须达到此值可被记录
long_query_time = 8

# 检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100


# mysql binlog日志文件保存的过期时间，过期后自动删除 7 天 = 7 * 24 * 60 * 60
binlog_expire_logs_seconds = 604800
EOF

准备 master 配置文件

mkdir -p /docker/mysql/master

cat > /docker/mysql/master/master.cnf <<EOF
# master.cnf 此文件为 mysql master 配置文件
[mysqld]
# MySQL 服务的唯一编号 几个 MySQL 服务 ID 需唯一(集群中)
server-id = 1 
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db = mysql  
# 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin = /var/lib/mysql/mysql-master1
#每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
max-binlog-size = 500M
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog-cache-size = 1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog-format = mixed
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave-skip-errors = 1062
EOF

准备 master 的 Dockerfile 文件

cat > /docker/mysql/master/Dockerfile <<EOF
FROM mysql:8
MAINTAINER [email protected]
ADD ./mysql-server-comm.cnf /etc/mysql/conf.d/mysql-server-comm.cnf
ADD ./master/master.cnf /etc/mysql/conf.d/master.cnf
EOF

准备 slave 配置文件

mkdir -p /docker/mysql/slave

cat > /docker/mysql/slave/slave.cnf <<EOF
# slave.cnf 此文件为 mysql slave 配置文件
[mysqld]
# MySQL 服务的唯一编号 几个 MySQL 服务 ID 需唯一(集群中)
server-id = 2
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db = mysql  
# 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin = /var/lib/mysql/mysql-slave1
# 从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数
log-slave-updates = 1
#每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
max-binlog-size = 500M
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog-cache-size = 1M  
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog-format = mixed
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave-skip-errors = 1062
# relay_log配置中继日志
relay-log = replicas-mysql-relay-bin  

# 防止改变数据(除了特殊的线程)
read-only = 1
EOF

准备 slave 的 Dockerfile 文件

cat > /docker/mysql/slave/Dockerfile <<EOF
FROM mysql:8
MAINTAINER [email protected]
ADD ./mysql-server-comm.cnf /etc/mysql/conf.d/mysql-server-comm.cnf
ADD ./slave/slave.cnf /etc/mysql/conf.d/slave.cnf
EOF

启动

cd /docker/mysql && docker-compose up -d --build

Building mysql-master
Step 1/4 : FROM mysql:8
8: Pulling from library/mysql
Status: Downloaded newer image for mysql:8
 ---> c8ee894bd2bd
Step 2/4 : MAINTAINER [email protected]
 ---> Running in b2c57eb5f28f
Removing intermediate container b2c57eb5f28f
 ---> 2080d446ec18
Step 3/4 : ADD ./mysql-server-comm.cnf /etc/mysql/conf.d/mysql-server-comm.cnf
 ---> f2a09455c821
Step 4/4 : ADD ./master/master.cnf /etc/mysql/conf.d/master.cnf
 ---> ee56b6cbe831

Successfully built ee56b6cbe831
Successfully tagged mysql_mysql-master:latest
Building mysql-slave
Step 1/4 : FROM mysql:8
 ---> c8ee894bd2bd
Step 2/4 : MAINTAINER [email protected]
 ---> Using cache
 ---> 2080d446ec18
Step 3/4 : ADD ./mysql-server-comm.cnf /etc/mysql/conf.d/mysql-server-comm.cnf
 ---> Using cache
 ---> f2a09455c821
Step 4/4 : ADD ./slave/slave.cnf /etc/mysql/conf.d/slave.cnf
 ---> 336d9198fe70

Successfully built 336d9198fe70
Successfully tagged mysql_mysql-slave:latest
Creating mysql-slave  ... done
Creating mysql-master ... done

# 检查服务运行状态
docker-compose ps
    Name                  Command               State                 Ports               
------------------------------------------------------------------------------------------
mysql-master   docker-entrypoint.sh --def ...   Up      0.0.0.0:3306->3306/tcp, 33060/tcp
mysql-slave    docker-entrypoint.sh --def ...   Up      0.0.0.0:3307->3306/tcp, 33060/tcp

# 检查服务运行日志
docker-compose logs

Attaching to mysql-slave, mysql-master
mysql-slave     | 2019-11-13 16:24:22+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
mysql-slave     | 2019-11-13 16:24:22+08:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
mysql-slave     | 2019-11-13 16:24:22+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
mysql-slave     | 2019-11-13 16:24:23+08:00 [Note] [Entrypoint]: Initializing database files
mysql-slave     | 2019-11-13 16:24:27+08:00 [Note] [Entrypoint]: Database files initialized
mysql-slave     | 2019-11-13 16:24:27+08:00 [Note] [Entrypoint]: Starting temporary server
mysql-slave     | 2019-11-13 16:24:29+08:00 [Note] [Entrypoint]: Temporary server started.
mysql-slave     | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
mysql-slave     | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
mysql-slave     | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
mysql-slave     | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
mysql-slave     | 
mysql-slave     | 2019-11-13 16:24:31+08:00 [Note] [Entrypoint]: Stopping temporary server
mysql-slave     | 2019-11-13 16:24:33+08:00 [Note] [Entrypoint]: Temporary server stopped
mysql-slave     | 
mysql-slave     | 2019-11-13 16:24:33+08:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
mysql-slave     | 
mysql-master    | 2019-11-13 16:24:22+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
mysql-master    | 2019-11-13 16:24:22+08:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
mysql-master    | 2019-11-13 16:24:22+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
mysql-master    | 2019-11-13 16:24:23+08:00 [Note] [Entrypoint]: Initializing database files
mysql-master    | 2019-11-13 16:24:28+08:00 [Note] [Entrypoint]: Database files initialized
mysql-master    | 2019-11-13 16:24:28+08:00 [Note] [Entrypoint]: Starting temporary server
mysql-master    | 2019-11-13 16:24:30+08:00 [Note] [Entrypoint]: Temporary server started.
mysql-master    | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
mysql-master    | Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
mysql-master    | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
mysql-master    | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
mysql-master    | 
mysql-master    | 2019-11-13 16:24:32+08:00 [Note] [Entrypoint]: Stopping temporary server
mysql-master    | 2019-11-13 16:24:35+08:00 [Note] [Entrypoint]: Temporary server stopped
mysql-master    | 
mysql-master    | 2019-11-13 16:24:35+08:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

到这里服务的运行都 OK 了 现在配置主从复制

使用 root 密码 rootpass 连接 master mysql 192.168.1.11:3306

docker run -it --rm mysql mysql -h192.168.1.11 -P3306 -uroot -prootpass

Unable to find image 'mysql:latest' locally
latest: Pulling from library/mysql
Digest: sha256:7345ce4ce6f0c1771d01fa333b8edb2c606ca59d385f69575f8e3e2ec6695eee
Status: Downloaded newer image for mysql:latest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
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.

# 记录下 File Position 的值会用到
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-master1.000003 |     155  |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 为了安全考量 新建一个只有复制权限的用户用来给 SLAVE 同步使用
CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY 'replicationPW';

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

FLUSH PRIVILEGES;

使用 root 密码 rootpass 连接 slave mysql 192.168.1.11:3307

docker run -it --rm mysql mysql -h192.168.1.11 -P3307 -uroot -prootpass
# 使用指定host/user/password/logfile/log_pos 连接 master
CHANGE MASTER TO
    MASTER_HOST='mysql-master',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='replicationPW',
    MASTER_LOG_FILE='mysql-master1.000003',
    MASTER_LOG_POS=155;

stop slave;
start slave;

#检查 同步状态
mysql> show slave status \G.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master1.000003
          Read_Master_Log_Pos: 2849
               Relay_Log_File: replicas-mysql-relay-bin.000002
                Relay_Log_Pos: 1457
        Relay_Master_Log_File: mysql-master1.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error:
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
1 row in set (0.00 sec)
show slave status;

# 表示同步成功且没有出错
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0

在 master sql 中插入测试数据

mysql> CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE `test`.`user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id主键',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)

INSERT INTO `test`.`user`(`username`, `password`, `age`) VALUES ('blank', '12345', 28)
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `test`.`user`;
+----+----------+----------+------+---------------------+
| id | username | password | age  | create_time         |
+----+----------+----------+------+---------------------+
|  1 | blank    | 12345    |   28 | 2019-11-13 16:50:54 |
+----+----------+----------+------+---------------------+
1 row in set (0.00 sec)

在 slave sql 中检查数据是否自动同步过来

mysql> SELECT * FROM `test`.`user`;
+----+----------+----------+------+---------------------+
| id | username | password | age  | create_time         |
+----+----------+----------+------+---------------------+
|  1 | blank    | 12345    |   28 | 2019-11-13 16:50:54 |
+----+----------+----------+------+---------------------+
1 row in set (0.00 sec)

Done!

swarm 方式

//TODO