blank的编程之路


  • 首页

  • 归档

  • 搜索
consul atomikos mybatisplus druid nexus nas named bind mysqldump acme.sh Dockerfile rsync keepalived swarm kibana ubuntu linux scp bugfix https ssl certbot curl gogs adminer harbor yum portainer python kubernetes idea java springboot maven docker-compose redis nginx mysql brew git chocolatey jenkins elasticsearch docker haproxy rabbitmq centos

Docker docker-compose mysql 主从同步

发表于 2019-11-13 | 分类于 运维 | 4 | 阅读次数 1309

准备工作

master/slavedocker 版本
192.168.1.11Docker 19.03.4

单机伪集群方式 基于 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
  • 本文作者: blank
  • 本文链接: https://blankhang.com/2019/11/13/201911131748
  • 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!
# consul # atomikos # mybatisplus # druid # nexus # nas # named # bind # mysqldump # acme.sh # Dockerfile # rsync # keepalived # swarm # kibana # ubuntu # linux # scp # bugfix # https # ssl # certbot # curl # gogs # adminer # harbor # yum # portainer # python # kubernetes # idea # java # springboot # maven # docker-compose # redis # nginx # mysql # brew # git # chocolatey # jenkins # elasticsearch # docker # haproxy # rabbitmq # centos
swarm mysql (mariadb)集群 高可用
POST 请求 被 nginx 301 后 变 GET
© 2023 blank
Everything is energy and everything has a frequency