docker compose部署mysql主从复制（内含故障切换操作）

# 1、部署mysql master服务

# 准备部署的文件

# docker.cnf

[mysqld]
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-format = mixed
binlog-do-db = testdb

1
2
3
4
5
6

# docker-compose.yaml

version: '3'
services:
  mysql:
    image: mysql:5.7.35
    container_name: mysql
    restart: unless-stopped
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - ./database/mysql:/var/lib/mysql
      - ./docker.cnf:/etc/mysql/conf.d/docker.cnf
      - ./mysql-backup:/data/mysql-backup
    ports:
      - "13306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "test@123"
      TZ: Asia/Shanghai
    networks:
      - default
networks:
  default:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 启动master服务

docker compose up -d

# 2、部署mysql slave服务

# 目录结构

# init-slave.sh

# 等待主库 MySQL 启动并可访问
echo "Waiting for MySQL master to be available..."
until mysql -h "$MYSQL_MASTER_HOST" -P "$MYSQL_MASTER_PORT" -u "$MYSQL_BACKUP_USER" -p"$MYSQL_BACKUP_USER_PASSWORD" -e 'SELECT 1'; do
  echo "Waiting for MySQL master..."
  sleep 3
done

# 使用环境变量设置备份的数据库名（逗号分隔），并转换为空格分隔的格式
DB_NAMES_SPACED=$(echo "$MYSQL_BACKUP_DATABASES" | tr ',' ' ')

# 备份主库数据库并保存到临时文件
echo "Starting backup from master..."
mysqldump -h "$MYSQL_MASTER_HOST" -P "$MYSQL_MASTER_PORT" -u "$MYSQL_BACKUP_USER" -p"$MYSQL_BACKUP_USER_PASSWORD" --master-data --routines --set-gtid-purged=OFF --single-transaction --databases $DB_NAMES_SPACED > /tmp/full_backup.sql

# 从备份文件中提取 MASTER_LOG_FILE 和 MASTER_LOG_POS
MASTER_LOG_FILE=$(grep -oP "MASTER_LOG_FILE='\K[^']+" /tmp/full_backup.sql)
MASTER_LOG_POS=$(grep -oP "MASTER_LOG_POS=\K[0-9]+" /tmp/full_backup.sql)


# 恢复数据库到从库
echo "Restoring backup on slave..."
mysql -u root -p"$MYSQL_ROOT_PASSWORD" < /tmp/full_backup.sql

# 配置从库连接到主库
echo "Configuring slave..."
mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "CHANGE MASTER TO
  MASTER_HOST='$MYSQL_MASTER_HOST',
  MASTER_USER='$MYSQL_REPLICATION_USER',
  MASTER_PORT = $MYSQL_MASTER_PORT,
  MASTER_PASSWORD='$MYSQL_REPLICATION_PASSWORD',
  MASTER_LOG_FILE='$MASTER_LOG_FILE',
  MASTER_LOG_POS=$MASTER_LOG_POS;"

# 启动主从同步
echo "Starting slave replication..."
mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "START SLAVE;"

# 确认从库同步状态
echo "Checking slave status..."
mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SHOW SLAVE STATUS\G"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# my.cnf

[mysqld]
# MySQL服务运行的用户
user=mysql

# MySQL服务监听的端口
port=3306

# 最大连接数
max_connections=1000

# 默认的存储引擎
default-storage-engine=INNODB

# 服务器默认字符集
character-set-server=utf8mb4

# 服务器默认的校对规则
collation_server = utf8mb4_general_ci

# 禁用域名解析，提高性能，只允许IP地址进行客户端登录
skip-name-resolve=1

# 表名不区分大小写，1表示开启
lower_case_table_names=1

# 日志时间戳格式，SYSTEM表示使用系统时间
log_timestamps=SYSTEM

# 设置默认的时区
default-time-zone = '+8:00'


# 错误日志文件的存储路径
log-error=/var/lib/mysql/mysqld.log



# 从库不进行同步的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=sys
replicate-ignore-db=performance_schema

# 禁用SSL
skip_ssl

# MySQL服务的Unix套接字文件
socket=/var/run/mysqld/mysqld.sock

# 启用的SQL模式，定义了MySQL的行为
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 最大允许的数据包大小
max_allowed_packet=512M
innodb_buffer_pool_size = 1G
key_buffer_size = 128M
query_cache_size = 64M

[mysql]
# 客户端使用的Unix套接字文件
socket=/var/run/mysqld/mysqld.sock

# 客户端默认字符集
default-character-set = utf8mb4

[client]
# 客户端程序默认使用的字符集
default-character-set = utf8mb4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

# docker-compose.yaml

version: '3.5'

services:
  cmdb-db:
    image: mysql:5.7.35
    container_name: mysql-slave
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: "test@123"                        # 从库root密码
      MYSQL_REPLICATION_USER: "repluser"                        # 主从同步账户
      MYSQL_REPLICATION_PASSWORD: "sre@test"               # 同步账户密码
      MYSQL_MASTER_HOST: "192.168.153.134"                         # 主库IP地址
      MYSQL_MASTER_PORT: 13306                                   # 主库端口
      MYSQL_BACKUP_USER: "root"                                 #数据库备份用户主库root一般不让远程登录()
      MYSQL_BACKUP_USER_PASSWORD: "tentest@123               #数据库备份用户密码
      MYSQL_BACKUP_DATABASES: "testdb"            # 需要备份的数据库，需要主从同步的数据库（多个数据库逗号分隔）
      TZ: 'Asia/Shanghai'                                       # 时区
    command: --server-id=138 --relay-log=mysqld-relay-bin --replicate-do-db=testdb    #指定server-id； relay-log 需要在从库上，需要指定同步的数据库，
#--replicate-do-db=test01 --replicate-do-db=test02 --replicate-do-db=test03 多个库要分开写,有几个写几个
    volumes:
      - ./init-slave.sh:/docker-entrypoint-initdb.d/init-slave.sh  # 挂载初始化脚本
      - ./db-data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
      - /etc/localtime:/etc/localtime:ro
    ports:
      - 23306:3306  # 从库使用不同的端口（可选）
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-u", "root", "-ptest@123   interval: 30s
      timeout: 10s
      retries: 3
    networks:
      - default
networks:
  default:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 启动slave服务

docker compose up -d

# 查看主从复制状态

docker logs mysql-slave
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.153.134
                  Master_User: repluser
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 920
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testdb
          Replicate_Ignore_DB: mysql,information_schema,sys,performance_schema
           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: 920
              Relay_Log_Space: 528
              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: 1
                  Master_UUID: 13ed61ea-c34b-11ef-a985-0242ac130002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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:

# 3、mysql主节点宕机，从节点切换到主节点操作

在 MySQL 5.7.33 版本中，如果主节点（Master）宕机，你可以将备节点（Slave）提升为新的主节点。以下是详细的操作步骤：

# 1. 确认主节点宕机

首先，确认主节点确实无法恢复。如果主节点只是暂时不可用，建议等待其恢复，而不是立即切换。

# 2. 停止备节点的复制进程

在备节点上，停止复制进程，以确保备节点不再尝试从主节点同步数据。

STOP SLAVE;

# 3. 重置备节点的复制信息

重置备节点的复制信息，清除与旧主节点的关联。

RESET SLAVE ALL;

# 4. 将备节点提升为主节点

在备节点上，执行以下命令，将其提升为主节点。

SET GLOBAL read_only = OFF;

# 查看binlog日志是否开启

执行：

SHOW VARIABLES LIKE 'log_bin';

如果 log_bin 的值为 OFF，说明二进制日志未启用。你需要修改 MySQL 配置文件（通常是 my.cnf 或 my.ini），启用二进制日志：

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server_id = 138 # 确保 server_id 是唯一的

1
2
3

然后重启 MySQL 服务：

docker restart mysql-slave

重启后，再次执行 SHOW MASTER STATUS，应该会显示二进制日志信息。

# 5. 更新应用程序的连接信息

更新应用程序的数据库连接配置，使其指向新的主节点（原备节点）。

# 6. 配置其他备节点（如果有）

如果你有其他备节点，需要将它们重新配置为从新的主节点同步数据。

# 6.1 在新的主节点上创建复制用户

在新的主节点上，创建一个用于复制的用户。

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

1
2
3

# 6.2 在其他备节点上配置复制

在其他备节点上，停止复制进程，并重新配置为从新的主节点同步数据。

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='new_master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;
START SLAVE;

1
2
3
4
5
6
7
8

注意：MASTER_LOG_FILE 和 MASTER_LOG_POS 需要根据新的主节点的二进制日志位置进行设置。你可以通过以下命令查看新的主节点的当前二进制日志位置：

SHOW MASTER STATUS;

# 7. 验证复制状态

在其他备节点上，验证复制状态，确保它们正在从新的主节点同步数据。

SHOW SLAVE STATUS\G

确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，并且没有错误。

# 8. 监控和测试

监控新的主节点和其他备节点的状态，确保系统正常运行。进行必要的测试，确保应用程序能够正常访问数据库。

# 9. 恢复旧主节点（可选）

如果旧主节点能够恢复，你可以将其重新配置为新的备节点，从新的主节点同步数据。

# 9.1 在旧主节点上停止复制进程

STOP SLAVE;

# 9.2 重置旧主节点的复制信息

RESET SLAVE ALL;

# 9.3 配置旧主节点为新的备节点

CHANGE MASTER TO
    MASTER_HOST='new_master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=4;
START SLAVE;

1
2
3
4
5
6
7

# 10. 完成切换

至此，你已经成功将备节点切换为新的主节点，并重新配置了其他备节点。系统应该能够继续正常运行。

# 注意事项

在执行切换操作之前，确保你已经备份了所有重要数据。
在切换过程中，可能会有短暂的服务中断，建议在业务低峰期进行操作。
如果主从复制配置了 GTID（全局事务标识符），切换步骤会有所不同，需要根据 GTID 的配置进行调整。

通过以上步骤，你可以成功将备节点切换为新的主节点，并确保数据库系统的高可用性。

上次更新: 2024/12/27, 10:46:50

← innobackupex实现MySQL备份 mongodb相关概念→