运维做开发

这个屌丝很懒，什么也没留下！

热门标签

【运维】docker-compose部署mysql5.7、mysql 8、单点、双主

作者：运维做开发 | 2024-06-25 04:26:40

踩

docker-compose部署mysql5.7

一、docker-compose部署mysql 5.7

单机版mysql 5.7

## docker-compose config                  ## 检查当前目录docker-compose.yml文件配置是否正确
## docker-compose up -d                   ## -d 为后台启动
## docker-compose up -d --force-rereate  ## 强制重新创建容器,修改挂载文件后使用该命令启动,谨慎使用,调试时可使用,使用此命令可能造成数据丢失

version: "3"
services:
  mysql:
    image: docker.io/mysql:5.7
    container_name: mysql5.7
    restart: always
    command: [
      '--default-authentication-plugin=mysql_native_password',
      '--character-set-server=utf8mb4',
      '--collation-server=utf8mb4_general_ci',
      '--explicit_defaults_for_timestamp=true',
      '--lower_case_table_names=1'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PWD}
      MYSQL_INITDB_SKIP_TZINFO: "Asia/Shanghai"
      #MYSQL_DATABASE: data_sys
    volumes:
      #数据目录，要确保先创建好
      - ./mysql/data:/var/lib/mysql
      - ./mysql/logs:/var/log/mysql
      ##初始化的脚本，初始化我们存放的init.sql文件
      - ./mysql/initdb:/docker-entrypoint-initdb.d/
      - ./mysql/conf:/etc/mysql/conf.d
    ports:
      - 3306:3306
    healthcheck:
      test: mysqladmin ping -uroot -p${MYSQL_ROOT_PWD}
      #test: [ "CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-p123456" ]
      interval: 6s
      timeout: 5s
      retries: 10
    #network_mode: host

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

双主热备mysql5.7配置步骤

1、启动docker-compose并配置m1和m2的双主

启动（双主）

创建docker-compose.yml配置文件

version: "3"
services:
  mysql:
    image: docker.io/mysql:5.7
    container_name: mysql5.7mm
    restart: always
    command: [
      '--default-authentication-plugin=mysql_native_password',
      '--character-set-server=utf8mb4',
      '--collation-server=utf8mb4_general_ci',
      '--explicit_defaults_for_timestamp=true',
      '--lower_case_table_names=1'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PWD}
      MYSQL_INITDB_SKIP_TZINFO: "Asia/Shanghai"
      #MYSQL_DATABASE: data_sys
    volumes:
      #数据目录，要确保先创建好
      - ./mysql/data:/var/lib/mysql
      - ./mysql/logs:/var/log/mysql
      #初始化的脚本，初始化我们存放的init.sql文件
      - ./mysql/initdb:/docker-entrypoint-initdb.d/
      - ./mysql/my.cnf:/etc/mysql/my.cnf
    ports:
      - 3306:3306
    healthcheck:
      test: mysqladmin ping -uroot -p${MYSQL_ROOT_PWD}
      #test: [ "CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-p123456" ]
      interval: 6s
      timeout: 5s
      retries: 10
    #network_mode: host

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

创建my.cnf配置文件

mkdir mysql && cd mysql
vim my.cnf
1
2

my.cnf

[mysqld]
# binlog 配置
log-bin=mysql-bin
binlog_format=mixed

# server-id 配置,必须唯一
server-id = 2

# 确保binlog日志写入后与硬盘同步
sync_binlog = 1

# 如果当前实例既做主库又做从库次选线必须开启
log-slave-updates=ON

# 自增长ID
# 特殊说明 当该实例为双主的架构时要特殊配置 以避免自增id冲突的问题
auto_increment_offset = 1
auto_increment_increment = 2

# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
# binlog-ignore-db=mysql
# 要同步的数据库名
# replicate-do-db=test_db

# 跳过所有的错误，继续执行复制操作
slave-skip-errors = all  
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

分别在两台主机上启动

docker-compose up -d
1

进入m1和m2下执行下列命令来获取各自的master status 和同步账号

m1和m2:

docker exec -it mysql-m1 bash

mysql -uroot -p123456

# 查看m1 File和Position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

# 创建同步账号
mysql> grant replication slave,replication client on *.* to 'slave'@'%' identified by "pwd-ms-sync";
mysql> flush privileges;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2、设置主主

根据master状态拼接设置slave的命令

# 设置当前节点监听的master节点
change master TO master_host = '172.16.223.140',
    master_port = 3306,
    master_user = 'slave',
    master_password = 'pwd-ms-sync',
    master_log_file = 'mysql-bin.000003',
    master_log_pos = 620,
    master_connect_retry=30;

# 启动当前为从节点
start slave;
# 停止从节点
stop slave;

# 查看主从状态
show slave status\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

m1:

mysql> change master to master_host='mysql-m2',master_user='slave',master_password='pwd-ms-sync',master_port=3306,master_log_file='mysql-bin.000005', master_log_pos=154,master_connect_retry=30;

mysql> start slave;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-m2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 620
               Relay_Log_File: de7a84f1b7f1-relay-bin.000002
                Relay_Log_Pos: 786
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

m2:

mysql> change master to master_host='mysql-m1',master_user='slave',master_password='pwd-ms-sync',master_port=3306,master_log_file='mysql-bin.000005', master_log_pos=154,master_connect_retry=30;

mysql> start slave;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-m1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1086
               Relay_Log_File: 65322be4d8a9-relay-bin.000002
                Relay_Log_Pos: 786
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

如下两个配置为Yes表示从节点设置成功
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
1
2
3

参考文档：https://dogslee.top/2021/08/23/docker-compose%E6%90%AD%E5%BB%BAMySql%E4%B8%BB%E4%BB%8E%E5%92%8C%E5%8F%8C%E4%B8%BB/

二、docker-compose部署mysql 8

1、单机版mysql 8.0

## docker-compose config                  ## 检查当前目录docker-compose.yml文件配置是否正确
## docker-compose up -d                   ## -d 为后台启动
## docker-compose up -d --force-rereate  ## 强制重新创建容器,修改挂载文件后使用该命令启动,谨慎使用,调试时可使用,使用此命令可能造成数据丢失

version: "3"
services:
  mysql:
    image: mysql:8.0.33
    container_name: mysql8
    restart: always
    command: [
      '--default-authentication-plugin=mysql_native_password',
      '--character-set-server=utf8mb4',
      '--collation-server=utf8mb4_general_ci',
      '--explicit_defaults_for_timestamp=true',
      '--lower_case_table_names=1'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PWD}
      MYSQL_INITDB_SKIP_TZINFO: "Asia/Shanghai"
    volumes:
      #数据目录，要确保先创建好
      - ./mysql/data:/var/lib/mysql
      - ./mysql/logs:/logs
      ##初始化的脚本，初始化我们存放的init.sql文件
      - ./mysql/initdb:/docker-entrypoint-initdb.d/
      - ./mysql/conf:/etc/mysql/conf.d
    ports:
      - 3306:3306
    healthcheck:
      test: mysqladmin ping -uroot -p${MYSQL_ROOT_PWD}
      #test: [ "CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-p123456" ]
      interval: 6s
      timeout: 5s
      retries: 10
    #network_mode: host

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

2、双主热备mysql8配置步骤

分别在两台主机上启动，在两个节点上先执行完前两步
创建docker-compose.yml文件

version: "3"
services:
  mysql:
    image: mysql:8.0.33
    container_name: mysql8mm
    restart: always
    command: [
      '--default-authentication-plugin=mysql_native_password',
      '--character-set-server=utf8mb4',
      '--collation-server=utf8mb4_general_ci',
      '--explicit_defaults_for_timestamp=true',
      '--lower_case_table_names=1'
    ]
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PWD}
      MYSQL_INITDB_SKIP_TZINFO: "Asia/Shanghai"
      #MYSQL_DATABASE: data_sys
    volumes:
      #数据目录，要确保先创建好
      - ./mysql/data:/var/lib/mysql
      - ./mysql/logs:/var/log/mysql
      #初始化的脚本，初始化我们存放的init.sql文件
      - ./mysql/initdb:/docker-entrypoint-initdb.d/
      - ./mysql/my.cnf:/etc/my.cnf
    ports:
      - 3306:3306
    healthcheck:
      test: mysqladmin ping -uroot -p${MYSQL_ROOT_PWD}
      #test: [ "CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-p123456" ]
      interval: 6s
      timeout: 5s
      retries: 10
    #network_mode: host

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

创建my.cnf配置文件

mkdir mysql && cd mysql
vim my.cnf
1
2

my.cnf

[mysqld]
# binlog 配置
log-bin=mysql-bin
binlog_format=mixed

# server-id 配置,必须唯一
server-id = 2

# 确保binlog日志写入后与硬盘同步
sync_binlog = 1

# 如果当前实例既做主库又做从库次选线必须开启
log_replica_updates=ON 

# 自增长ID
# 特殊说明 当该实例为双主的架构时要特殊配置 以避免自增id冲突的问题
auto_increment_offset = 1
auto_increment_increment = 2

# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
# binlog-ignore-db=mysql
# 要同步的数据库名
# replicate-do-db=test_db
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

1)、创建同步用户

create user 'slave'@'%' identified by 'pwd-ms-sync';
1

2)、赋权同步权限

grant replication slave,replication client on *.* to 'slave'@'%';
flush privileges;
1
2

3)、先查master节点的状态

设置主主

两台机器互为主从，交换完成如下几步
先在master执行第3步，然后再在slave节点执行第4、5、6步

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1
2
3
4
5
6

4)、根据第3步的master状态拼接设置slave的命令

change master TO master_host = '172.16.223.140',
    master_port = 3306,
    master_user = 'slave',
    master_password = 'pwd-ms-sync',
    master_log_file = 'mysql-bin.000003',
    master_log_pos = 863,
    master_connect_retry=30;
1
2
3
4
5
6
7

5)、启动当前为从节点

start slave;
1

6)、查看主从状态

show slave status\G;
1

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.223.140
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 863
               Relay_Log_File: f45bf55e91c3-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如下两个配置为Yes表示从节点设置成功
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
1
2
3

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/运维做开发/article/detail/755007