docker-compose部署MySQL主从

【内容转载于 https://zhuanlan.zhihu.com/p/650314645】

1.配置主库

a.添加主库配置文件master.cnf，配置主库server-id

[mysqld]
server-id=100

b.添加master.sh，用于在主库创建用户

#!/bin/bash
set -e
## create user for sync
MASTER_SYNC_USER=${MASTER_SYNC_USER:-sync_admin}
MASTER_SYNC_PASSWORD=${MASTER_SYNC_PASSWORD:-sync_admin123456}
ROOT_USER="root"
ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD:-admin123456}
SYNC_ALLOW_HOST=${SYNC_ALLOW_HOST:-%}
CREATE_SYNC_USER_SQL="CREATE USER '$MASTER_SYNC_USER'@'$SYNC_ALLOW_HOST' IDENTIFIED WITH mysql_native_password BY '$MASTER_SYNC_PASSWORD';"
GRANT_SYNC_PRIVILEGES_SQL="GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$MASTER_SYNC_USER'@'$SYNC_ALLOW_HOST';"
FLUSH_SYNC_PRIVILEGES_SQL="FLUSH PRIVILEGES;"
mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "$CREATE_SYNC_USER_SQL"
mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "$GRANT_SYNC_PRIVILEGES_SQL"
mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "$FLUSH_SYNC_PRIVILEGES_SQL"
## 如下用户是为了给程序添加可读可写的账号，不是用于主从同步的。
R_W_USER=${R_W_USER:-u_rw}
R_W_USER_PASSWORD=${R_W_USER_PASSWORD:-urw_pwd123456}
R_W_USER_HOST=${R_W_USER_HOST:-%}
R_W_DATABASE=${R_W_DATABASE:-*}
CREATE_R_W_USER_SQL="CREATE USER '$R_W_USER'@'$R_W_USER_HOST' IDENTIFIED WITH mysql_native_password BY '$R_W_USER_PASSWORD';"
GRANT_R_W_PRIVILEGES_SQL="GRANT XA_RECOVER_ADMIN,CREATE,DROP,INSERT,UPDATE,DELETE,SELECT ON $R_W_DATABASE.* TO '$R_W_USER'@'$R_W_USER_HOST';"
FLUSH_R_W_PRIVILEGES_SQL="FLUSH PRIVILEGES;"
mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "$CREATE_R_W_USER_SQL"
mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "$GRANT_R_W_PRIVILEGES_SQL"
mysql -u"$ROOT_USER" -p"$ROOT_PASSWORD" -e "$FLUSH_R_W_PRIVILEGES_SQL"

2.配置从库

a.添加从库配置文件slave.cnf

[mysqld]
server-id=100

b.编写脚本slave.sh，运行SQL将主库的信息配置到从库; 添加只读用户

#!/bin/bash

set -e

## config for sync
MASTER_HOST="${MASTER_HOST:-127.0.0.1}"
MASTER_PORT="${MASTER_PORT:-3306}"
MASTER_SYNC_USER="${MASTER_SYNC_USER:-sync_admin}"
MASTER_SYNC_PASSWORD="${MASTER_SYNC_PASSWORD:-sync_admin123456}"

SLAVE_ADMIN_USER="root"
SLAVE_ADMIN_PASSWORD="${MYSQL_ROOT_PASSWORD:-admin123456}"

sleep 10

RESULT=`mysql -h$MASTER_HOST -p$MASTER_PORT -u"$MASTER_SYNC_USER" -p"$MASTER_SYNC_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`
LOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`
LOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`

SYNC_SQL="""
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='$MASTER_HOST',
  SOURCE_PORT=$MASTER_PORT,
  SOURCE_USER='$MASTER_SYNC_USER',
  SOURCE_PASSWORD='$MASTER_SYNC_PASSWORD',
  SOURCE_LOG_FILE='$LOG_FILE_NAME',
  SOURCE_LOG_POS=$LOG_FILE_POS,
  SOURCE_CONNECT_RETRY=10;
"""
START_SYNC_SQL="START REPLICA;"
STATUS_SQL="SHOW REPLICA STATUS\G;"

mysql -u"$SLAVE_ADMIN_USER" -p"$SLAVE_ADMIN_PASSWORD" -e "$SYNC_SQL"
mysql -u"$SLAVE_ADMIN_USER" -p"$SLAVE_ADMIN_PASSWORD" -e "$START_SYNC_SQL"
mysql -u"$SLAVE_ADMIN_USER" -p"$SLAVE_ADMIN_PASSWORD" -e "$STATUS_SQL"

##  如下用户是为了给程序添加只读的账号，不是用于主从同步的。
R_USER=${R_USER:-u_rw}
R_USER_PASSWORD=${R_USER_PASSWORD:-urw_pwd123456}
R_USER_HOST=${R_USER_HOST:-%}
R_DATABASE=${R_DATABASE:-*}

CREATE_R_USER_SQL="CREATE USER '$R_USER'@'$R_USER_HOST' IDENTIFIED WITH mysql_native_password BY '$R_USER_PASSWORD';"
GRANT_R_PRIVILEGES_SQL="GRANT XA_RECOVER_ADMIN,SELECT ON $R_DATABASE.* TO '$R_USER'@'$R_USER_HOST';"
FLUSH_R_PRIVILEGES_SQL="FLUSH PRIVILEGES;"

mysql -u"$SLAVE_ADMIN_USER" -p"$SLAVE_ADMIN_PASSWORD" -e "$CREATE_R_USER_SQL"
mysql -u"$SLAVE_ADMIN_USER" -p"$SLAVE_ADMIN_PASSWORD" -e "$GRANT_R_PRIVILEGES_SQL"
mysql -u"$SLAVE_ADMIN_USER" -p"$SLAVE_ADMIN_PASSWORD" -e "$FLUSH_R_PRIVILEGES_SQL"

3.编写编写docker-compose.yml

编写docker-compose.yaml。在docker-compose.yaml中，配置了：

1. 一个主库mysql-master，

• 该服务在网络shardingSphere中-，在该网络中的ip为固定值192.168.0.100
• 服务映射到宿主机的port是13306
• 配置文件 master.cnf 映射到容器内：/etc/my.cnf
• 脚本master.sh 映射到容器中的/docker-entrypoint-initdb.d/master.sh。在/docker-entrypoint-initdb.d/目录下的脚本会在mysqld启动后运行

2. 一个从库mysql-slave

• 从库depend_on: mysql-master,因此，但mysql-master启动成功后，slave容器才会启动
• 该服务在网络shardingSphere中，在该网络中的ip为固定值192.168.0.101，与master在一个网络中
• 服务映射到宿主机的port是13307
• 配置文件 slave.cnf 映射到容器内：/etc/my.cnf
• 脚本slave.sh 映射到容器中的/docker-entrypoint-initdb.d/slave.sh。在/docker-entrypoint-initdb.d/目录下的脚本会在mysqld启动后运行

3. 创建网络shardingSphere，mysql-master和mysql-slave都在这个网络中。
   完整的docker-compose.yaml如下：

[root@vm03 mysql_multi]# cat docker-compose.yml 
services:
  mysql-master:
    image: "mysql:latest"
    ports:
      - "13306:3306"
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: admin123456
      MYSQL_ROOT_HOST: '%'
      MYSQL_DATABASE: 'db_hr'
      MYSQL_ALLOW_EMPTY_PASSWORD: 'no'
      MASTER_SYNC_USER: syncuser
      MASTER_SYNC_PASSWORD: sync123456
      SYNC_ALLOW_HOST: '%'
      R_W_USER: rw_user
      R_W_USER_PASSWORD: rw123456
      R_W_USER_HOST: '%'
      R_W_DATABASE: '*'
    volumes:
      - ./master.sh:/docker-entrypoint-initdb.d/master.sh #启动容器时执行的脚本
      - ./master.cnf:/etc/my.cnf
      - ./master-data:/var/lib/mysql
    restart: always
    networks:
      shardingSphere:
        ipv4_address: 192.168.0.100
  mysql-slave:
    image: "mysql:latest"
    ports:
      - "13307:3306"
    container_name: mysql-slave
    environment:
      MYSQL_ROOT_PASSWORD: admin123456
      MYSQL_DATABASE: 'db_hr'
      MYSQL_ALLOW_EMPTY_PASSWORD: 'no'
      MASTER_HOST: 192.168.0.100
      MASTER_PORT: 3306
      MASTER_SYNC_USER: syncuser
      MASTER_SYNC_PASSWORD: sync123456
      R_USER: r_user
      R_USER_PASSWORD: r123456
      R_USER_HOST: '%'
    volumes:
      - ./slave.sh:/docker-entrypoint-initdb.d/slave.sh
      - ./slave.cnf:/etc/my.cnf
      - ./slave-data:/var/lib/mysql
    restart: always
    networks:
      shardingSphere:
        ipv4_address: 192.168.0.101
    depends_on:
      - mysql-master
networks:
  shardingSphere:
    ipam:
      driver: default
      config:
        - subnet: "192.168.0.0/24"

4. 启动服务

运行sudo docker compose up -d启动服务

[root@vm03 mysql_multi]# ls
docker-compose.yml  master.cnf  master.sh  slave.cnf  slave.sh
[root@vm03 mysql_multi]# docker-compose up -d
[+] Running 2/3
 ⠋ Network mysql_multi_shardingSphere  Created                                                           1.0s 
 ✔ Container mysql-master              Started                                                           0.4s 
 ✔ Container mysql-slave               Started                                                           0.8s 
[root@vm03 mysql_multi]# docker-compose ps
NAME           IMAGE          COMMAND                   SERVICE        CREATED         STATUS         PORTS
mysql-master   mysql:latest   "docker-entrypoint.s…"   mysql-master   6 seconds ago   Up 5 seconds   33060/tcp, 0.0.0.0:13306->3306/tcp, :::13306->3306/tcp
mysql-slave    mysql:latest   "docker-entrypoint.s…"   mysql-slave    6 seconds ago   Up 4 seconds   33060/tcp, 0.0.0.0:13307->3306/tcp, :::13307->3306/tcp

验证

1.在master端创建table，添加数据

#######进入master容器###############
[root@vm03 mysql_multi]# docker exec -it mysql-master sh
#######进入SQL command###############
sh-4.4# mysql -uroot -padmin123456
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 11
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> use db_hr;
Database changed
mysql> show tables;
Empty set (0.00 sec)
#######创建表：t_sub_company，并向表中添加数据###############
mysql> CREATE TABLE `t_sub_company` (
    ->   `sub_company_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `address` varchar(255) DEFAULT NULL,
    ->   `name` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`sub_company_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> insert into t_sub_company (name) value ("company1"),("company2");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from t_sub_company;
+----------------+---------+----------+
| sub_company_id | address | name     |
+----------------+---------+----------+
|              1 | NULL    | company1 |
|              2 | NULL    | company2 |
+----------------+---------+----------+
2 rows in set (0.00 sec)
mysql> 

2.在slave端查看同步过来的表及数据

######## 进入slave容器###############
[root@vm03 mysql_multi]# docker exec -it mysql-slave sh
#######进入SQL command###############
sh-4.4# mysql -uroot -padmin123456
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 14
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> use db_hr;
Database changed
####### 主库没有创建table时，从库中table为空##########
mysql> show tables;
Empty set (0.01 sec)
 ##### 主库进行create table操作后，可以看到表同步到了slave。
mysql> show tables;
+-----------------+
| Tables_in_db_hr |
+-----------------+
| t_sub_company   |
+-----------------+
1 row in set (0.01 sec)
 ##### 数据同步到了slave。
mysql> select * from t_sub_company;
+----------------+---------+----------+
| sub_company_id | address | name     |
+----------------+---------+----------+
|              1 | NULL    | company1 |
|              2 | NULL    | company2 |
+----------------+---------+----------+
2 rows in set (0.00 sec)

一些有用的SQL

---- 在slave中配置master的host, port等信息----
mysql> CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='$MASTER_HOST',
  SOURCE_PORT=$MASTER_PORT,
  SOURCE_USER='$MASTER_SYNC_USER',
  SOURCE_PASSWORD='$MASTER_SYNC_PASSWORD',
  SOURCE_LOG_FILE='$LOG_FILE_NAME',
  SOURCE_LOG_POS=$LOG_FILE_POS,
  SOURCE_CONNECT_RETRY=10;
----启动同步------
mysql> START REPLICA;
----停止同步----
mysql> STOP REPLICA;
----查看同步状态。加\G显示出的结果可读性强。----
mysql> SHOW REPLICA STATUS\G;
----查看主库binlog的信息-----
mysql> SHOW MASTER STATUS\G;
----同步数据库d1
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);