docker-compose整合mysql主从(脚本一键生成)

秋博容

2023-12-01

本文是通过docker-compose在linux配置mysql一主二从高可用，也可以通过该下面的脚本直接创建。前提条件是系统中需要安装docker和docker-compose。

配置配置信息

配置配置文件、数据和日志文件路径

配置master目录：

mkdir -p master/conf;
mkdir -p master/data;
mkdir -p master/log;
mkdir -p master/mysql-files;

配置slave1目录：

mkdir -p slave1/conf;
mkdir -p slave1/data;
mkdir -p slave1/log;
mkdir -p slave1/mysql-files;

配置slave2目录：

mkdir -p slave2/conf;
mkdir -p slave2/data;
mkdir -p slave2/log;
mkdir -p slave2/mysql-files;

mkdir -p /conf.d/stream

配置my.cnf

在master/conf/配置master的my.cnf：

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-master-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
binlog_expire_logs_seconds=2592000  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062

在slave1/conf/配置slave1的my.cnf：

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
binlog_expire_logs_seconds=2592000  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062  
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin 
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## slave设置为只读（具有super权限的用户除外）
read_only=1

在slave2/data/配置slave2的my.cnf：

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=103
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave2-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
binlog_expire_logs_seconds=2592000  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062  
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin 
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## slave设置为只读（具有super权限的用户除外）
read_only=1

配置nginx.conf

user  nginx;
worker_processes  auto;

error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;


events {
    worker_connections  1024;
}

# 添加stream模块，实现tcp反向代理
stream {
    include /opt/nginx/stream/conf.d/*.conf; #加载 /opt/nginx/stream/conf.d目录下面的所有配置文件
}

创建mysql-net网络

docker network create mysql-net

配置docker-compose.yml

MASTER_HOST修改为当前主机对应的ip。

version: '3'
services:
  mysql-slave-lb:
    restart: always
    image: nginx:alpine
    container_name: mysql-slave-lb
    ports:
      - 3307:3307
    volumes:
      - ./conf.d/stream:/opt/nginx/stream/conf.d
      - ./nginx.conf:/etc/nginx/nginx.conf
    networks: 
      - alex_net
    depends_on:
      mysql-master:
        condition: service_healthy
      mysql-slave1:
        condition: service_healthy
      mysql-slave2:
        condition: service_healthy

  mysql-master:
    restart: always
    image: mysql:latest
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      MASTER_SYNC_USER: "sync_admin" #设置脚本中定义的用于同步的账号
      MASTER_SYNC_PASSWORD: "123456" #设置脚本中定义的用于同步的密码
      ADMIN_USER: "root" #当前容器用于拥有创建账号功能的数据库账号
      ADMIN_PASSWORD: "123456"
      TZ: "Asia/Shanghai" #解决时区问题
    ports:
      - 3336:3306
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 10s
      timeout: 10s
      retries: 5
      start_period: 10s
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: 50m
    networks: 
      - alex_net
    volumes:
      - ./master/data:/var/lib/mysql:rw
      - ./master/conf/my.cnf:/etc/mysql/my.cnf:rw
      - ./master/log:/var/log/mysql:rw
      - ./master/mysql-files:/var/lib/mysql-files:rw

  mysql-slave1:
    restart: always
    image: mysql:latest
    container_name: mysql-slave1
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      SLAVE_SYNC_USER: "sync_admin" #用于同步的账号，由master创建
      SLAVE_SYNC_PASSWORD: "123456"
      ADMIN_USER: "root"
      ADMIN_PASSWORD: "123456"
      MASTER_HOST: "mysql-master" #master地址，开启主从同步需要连接master
      TZ: "Asia/Shanghai" #设置时区
    ports:
      - 3316:3306
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 10s
      timeout: 10s
      retries: 5
      start_period: 10s
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: 50m
    networks: 
      - alex_net
    depends_on:
      mysql-master:
        condition: service_healthy
    volumes:
      - ./slave1/data:/var/lib/mysql:rw
      - ./slave1/conf/my.cnf:/etc/mysql/my.cnf:rw
      - ./slave1/log:/var/log/mysql:rw
      - ./slave1/mysql-files:/var/lib/mysql-files:rw

  mysql-slave2:
    restart: always
    image: mysql:latest
    container_name: mysql-slave2
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      SLAVE_SYNC_USER: "sync_admin"
      SLAVE_SYNC_PASSWORD: "123456"
      ADMIN_USER: "root"
      ADMIN_PASSWORD: "123456"
      MASTER_HOST: "mysql-master"
      TZ: "Asia/Shanghai"
    ports:
      - 3326:3306
    healthcheck:
      test: [ "CMD", "mysqladmin" ,"ping", "-h", "localhost" ]
      interval: 10s
      timeout: 10s
      retries: 5
      start_period: 10s
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: 50m
    networks: 
      - alex_net
    depends_on:
      mysql-master:
        condition: service_healthy
    volumes:
      - ./slave2/data:/var/lib/mysql:rw
      - ./slave2/conf/my.cnf:/etc/mysql/my.cnf:rw
      - ./slave2/log:/var/log/mysql:rw
      - ./slave2/mysql-files:/var/lib/mysql-files:rw

networks:
  alex_net:
    external: true

最后的效果图

tree

最后配置的目录如下。

.
├── conf.d
│   └── stream
├── docker-compose.yml
├── master
│   ├── conf
│   │   └── my.cnf
│   ├── data
│   ├── log
│   └── mysql-files
├── nginx.conf
├── slave1
│   ├── conf
│   │   └── my.cnf
│   ├── data
│   ├── log
│   └── mysql-files
└── slave2
    ├── conf
    │   └── my.cnf
    ├── data
    ├── log
    └── mysql-files

启动docker-compose

docker-compose up -d

配置mysql主从

master中添加slave1、slave2用户

运行如下代码，然后之前设置的密码：123456，进入mysql master。

docker exec -it mysql-master mysql -u root -p

执行如下代码添加slave1、slave2用户。

#在主机MySQL里执行授权命令 
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; 
 
#此语句必须执行。否则见下面。 
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

#在主机MySQL里执行授权命令 
CREATE USER 'slave2'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%'; 
 
#此语句必须执行。否则见下面。 
ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 
flush privileges;

#查询master的状态,记住file和position对应的信息，为后来添加从到主做准备。
show master status;

+------------------------------+----------+--------------+------------------+-------------------+
| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------+
| mall-mysql-master-bin.000003 |    40130 |              | mysql            |                   |
+------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave1中配置主从

运行如下代码，然后之前设置的密码：123456，进入mysql master。

docker exec -it mysql-slave1 mysql -u root -p

$host替换为配置master的ip，master_log_file就是之前master对应的file信息，master_log_pos就是之前position对应的信息。

change master to master_host='mysql-master', master_user='slave1', master_password='123456', master_port=3306, master_log_file='mall-mysql-master-bin.000003', master_log_pos=1965, master_connect_retry=30;

启动slave。

start slave;

查看slave信息，如果Slave_IO_Running、Slave_SQL_Running两个信息是yes代表搭建从库成功。

show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mall-mysql-master-bin.000003
          Read_Master_Log_Pos: 40130
               Relay_Log_File: bc55a10d23de-relay-bin.000002
                Relay_Log_Pos: 38503
        Relay_Master_Log_File: mall-mysql-master-bin.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: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 40130
              Relay_Log_Space: 38720
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
                  Master_UUID: 0d212aaf-665e-11ed-968d-0242ac120003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

slave2中配置主从

运行如下代码，然后之前设置的密码：123456，进入mysql master。

docker exec -it mysql-slave2 mysql -u root -p

$host替换为配置master的ip，master_log_file就是之前master对应的file信息，master_log_pos就是之前position对应的信息。

change master to master_host='mysql-master', master_user='slave2', master_password='123456', master_port=3306, master_log_file='mall-mysql-master-bin.000003', master_log_pos=1965, master_connect_retry=30;

启动slave。

start slave;

查看slave信息，如果Slave_IO_Running、Slave_SQL_Running两个信息是yes代表搭建从库成功。

show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mall-mysql-master-bin.000003
          Read_Master_Log_Pos: 40130
               Relay_Log_File: bc55a10d23de-relay-bin.000002
                Relay_Log_Pos: 38503
        Relay_Master_Log_File: mall-mysql-master-bin.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: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 40130
              Relay_Log_Space: 38720
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
                  Master_UUID: 0d212aaf-665e-11ed-968d-0242ac120003
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

通过脚本直接创建mysql主从

#!/bin/bash
pd=123456

echo "删除原来的文件"
rm -rf master/data/*
rm -rf slave1/data/*
rm -rf slave2/data/*

echo "删除之前的mysql容器"
docker rm -f mysql-master
docker rm -f mysql-slave1
docker rm -f mysql-slave2
docker rm -f mysql-slave-lb
 
docker-compose up -d
 
echo "master添加用户"
docker exec -it mysql-master mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CREATE USER 'slave1'@'%' IDENTIFIED BY '$pd'; \
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; \
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '$pd'; "
docker exec -it mysql-master mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CREATE USER 'slave2'@'%' IDENTIFIED BY '$pd'; \
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%'; \
ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '$pd'; "
 
echo "获取master position"
master_status=`docker exec -it mysql-master mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "show master status\G"`
echo "master_status: $master_status"
master_log_file=`echo "$master_status" | awk  'NR==3{print substr($2,1,length($2)-1)}'`
master_log_pos=`echo "$master_status" | awk 'NR==4{print $2}'`
master_log_file="'""$master_log_file""'"

echo "配置slave1"

docker exec -it mysql-slave1 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_PORT=3306,MASTER_USER='slave1',MASTER_PASSWORD='$pd',MASTER_LOG_FILE=$master_log_file,MASTER_LOG_POS=$master_log_pos;"
docker exec -it mysql-slave1 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "start slave;"
docker exec -it mysql-slave1 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "show slave status\G;"

echo "配置slave2"
## Setting Up Replication Slaves
docker exec -it mysql-slave2 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e \
"CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_PORT=3306,MASTER_USER='slave2',MASTER_PASSWORD='$pd',MASTER_LOG_FILE=$master_log_file,MASTER_LOG_POS=$master_log_pos;"
docker exec -it mysql-slave2 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "start slave;"
docker exec -it mysql-slave2 mysql -uroot -p$pd -S /var/lib/mysql/mysql.sock -e "show slave status\G;"

mysql定时备份

因为我这面的数据库由于之前没有重视安全性，被黑客黑了，数据都丢失了。所以在这里添加数据库数据定时配置，备份数据。在当前目录创建mysql.sh文件，然后添加如下信息$ip修改为mysql数据库对应的ip地址、端口号。

#!/bin/bash
#备份路径
BACKUP=/usr/local/soft/mysql/backup

#当前时间
DATETIME=$(date +%Y-%m-%d)

echo "===备份开始==="
 
#数据库名称
finance=alex_finance
user=alex_user
nacos=nacos
miaosha=alex_miaosha
oss=alex_oss

#数据库地址
HOST=$ip

PORT=3306

#数据库用户名
DB_USER=root

#数据库密码
DB_PW=123456

#创建备份目录
[ ! -d "${BACKUP}" ] && mkdir -p "${BACKUP}"
echo "开始备份 ${finance}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW -q -R --databases ${finance}" > $BACKUP/${finance}_`date +%F`.sql
echo "结束备份 ${finance}"

echo "开始备份 ${user}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${user}" > $BACKUP/${user}_`date +%F`.sql
echo "结束备份 ${user}"

echo "开始备份${nacos}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${nacos}" > $BACKUP/${nacos}_`date +%F`.sql
echo "结束备份${nacos}"

echo "开始备份${miaosha}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${miaosha}" > $BACKUP/${miaosha}_`date +%F`.sql
echo "结束备份${miaosha}"

echo "开始备份${oss}"
docker exec -i mysql-master sh -c "exec mysqldump -uroot -p$DB_PW --databases ${oss}" > $BACKUP/${oss}_`date +%F`.sql
echo "结束备份${oss}"
#删除十天前的备份文件
find $BACKUP -mtime +9 -name "*.sql" -exec rm -rf {} \;

echo "===导出成功==="

执行如下代码，添加备份定时。

crontab -e

设置每天01:01执行定时。

01 01 * * * /usr/local/soft/mysql/mysql.sh

$root修改为mysql对应的root， $password修改为mysql对应的数据库密码。然后执行如下命令补数据。

docker exec -i mysql-master mysql -u$root -p$password < /usr/local/soft/mysql/backup/alex_finance_2022-12-30.sql

总结

至此，通过docker-compose搭建mysql一主二从完成。

Q&A

当执行开始slave，报如下错误时，运行重新创建slave即可。

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

重新设置slave

reset slave;
start slave;

docker-compose整合mysql主从(脚本一键生成)

配置配置信息

配置配置文件、数据和日志文件路径

配置my.cnf

配置nginx.conf

创建mysql-net网络

配置docker-compose.yml

最后的效果图

启动docker-compose

配置mysql主从

master中添加slave1、slave2用户

slave1中配置主从

slave2中配置主从

通过脚本直接创建mysql主从

mysql定时备份

总结

Q&A

相关阅读

相关文章

相关问答

相关文档