docker-compose搭建 mysql8 主从

发表于 2023-05-07 分类于 Docker 阅读次数： Changyan：本文字数： 7.7k 阅读时长 ≈ 7 分钟

简介

我这里模拟了一台服务器，开启了两个mysql8的容器，一个作为主，一个作为从。

Master

/root/.docker/voldata/mysql8/master

编写主容器docker-compose.yml脚本

version: '3.9'
services:
  mysql8-master-ins:
    image: mysql:8.0.33
    container_name: mysql8-master
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
    deploy:
      resources:
        limits:
          cpus: "1"
          memory: 2g
      restart_policy:
        condition: on-failure
    privileged: true
    environment:
      MYSQL_ROOT_HOST: '%'
      MYSQL_ROOT_PASSWORD: Cn123456   #记得冒号与密码之间有个空格哈，这是yml的书写格式
      TZ: "Asia/Shanghai"
      MYSQL_USER: master
      MYSQL_PASSWORD: Cn123456
    command:
      --bind-address=172.18.0.100
      --default-authentication-plugin=mysql_native_password
      --max_connections=400
      --innodb_lock_wait_timeout=500
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --default-time-zone='+8:00'
      --explicit_defaults_for_timestamp=true
      --max_allowed_packet=128M
      --lower_case_table_names=1
      --expire-logs-days=7
    ports:
      - 3308:3306
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /root/.docker/voldata/mysql8/master/data:/var/lib/mysql
      - /root/.docker/voldata/mysql8/master/my.cnf:/etc/mysql/my.cnf
      - /root/.docker/voldata/mysql8/master/conf:/etc/mysql/conf.d
      - /root/.docker/voldata/mysql8/master/mysql-files:/var/lib/mysql-files
      - /root/.docker/voldata/mysql8/master/logs:/var/log/mysql
    networks:
      traefik:
        ipv4_address: 172.18.0.100
networks:
  traefik:
    external: true

编写主容器的my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
user=mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
datadir= /var/lib/mysql

# gtid:
server_id = 1                   #服务器id
gtid_mode = on                  #开启gtid模式
enforce_gtid_consistency = on   #强制gtid一致性，开启后对于特定create table不被支持


# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
#是否对sql语句大小写敏感，1表示不敏感  
lower_case_table_names = 1
#最大连接数
max_connections = 400
#最大错误连接数
max_connect_errors = 1000
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G  
max_allowed_packet=128M
# 设置时区
default-time_zone='+8:00'  
#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
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character_set_server = utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation_server = utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能） 
#transaction_isolation = READ-COMMITTED    

#忘记密码时使用
#skip-grant-tables
#设置协议认证方式
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
# innodb的dml操作的行级锁的等待时间
innodb_lock_wait_timeout = 500


  #binlog
#log_bin = mysql-binlog
log-bin = /var/lib/mysql/mysql-bin
log_slave_updates = on
binlog_format = row             #强烈建议，其他格式可能造成数据不一致
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 7

#relay log
skip_slave_start = 1

general_log = 1
general_log_file = /var/log/mysql/access.log
#数据库错误日志文件
#log-error = /var/lib/mysql/error.log  
log-error = /var/log/mysql/error.log
#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = /var/log/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


# Custom config should go here
# !includedir /etc/mysql/conf.d/

启动容器
```
cd master
docker-compose up -d
```

Slave

/root/.docker/voldata/mysql8/slave

编写从容器docker-compose.yml脚本

version: '3.9'
services:
  mysql8-slave-ins:
    image: mysql:8.0.33
    container_name: mysql8-slave
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
    deploy:
      resources:
        limits:
          cpus: "1"
          memory: 2g
      restart_policy:
        condition: on-failure
    privileged: true
    environment:
      MYSQL_ROOT_HOST: '%'
      MYSQL_ROOT_PASSWORD: Cn123456   #记得冒号与密码之间有个空格哈，这是yml的书写格式
      TZ: "Asia/Shanghai"
      MYSQL_USER: slave
      MYSQL_PASSWORD: Cn123456
    command:
      --bind-address=172.18.0.101
      --default-authentication-plugin=mysql_native_password
      --max_connections=400
      --innodb_lock_wait_timeout=500
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --default-time-zone='+8:00'
      --explicit_defaults_for_timestamp=true
      --max_allowed_packet=128M
      --lower_case_table_names=1
      --expire-logs-days=7
    ports:
      - 3309:3306
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /root/.docker/voldata/mysql8/slave/data:/var/lib/mysql
      - /root/.docker/voldata/mysql8/slave/my.cnf:/etc/mysql/my.cnf
      - /root/.docker/voldata/mysql8/slave/conf:/etc/mysql/conf.d
      - /root/.docker/voldata/mysql8/slave/mysql-files:/var/lib/mysql-files
      - /root/.docker/voldata/mysql8/slave/logs:/var/log/mysql
    networks:
      traefik:
        ipv4_address: 172.18.0.101
networks:
  traefik:
    external: true

编写从容器的my.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
user=mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
datadir= /var/lib/mysql

# gtid:
server_id = 2                   #服务器id
gtid_mode = on                  #开启gtid模式
enforce_gtid_consistency = on   #强制gtid一致性，开启后对于特定create table不被支持


# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
#是否对sql语句大小写敏感，1表示不敏感  
lower_case_table_names = 1
#最大连接数
max_connections = 400
#最大错误连接数
max_connect_errors = 1000
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G  
max_allowed_packet=128M
# 设置时区
default-time_zone='+8:00'
#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
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character_set_server = utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation_server = utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
# Disabling symbolic-links is recommended to prevent assorted security risks
# 禁用符号链接以防止各种安全风险  
symbolic-links=0

#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能） 
#transaction_isolation = READ-COMMITTED    

#忘记密码时使用
#skip-grant-tables
#设置协议认证方式
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
# innodb的dml操作的行级锁的等待时间
innodb_lock_wait_timeout = 500

 
#binlog
#log_bin = mysql-binlog
log-bin = /var/lib/mysql/mysql-bin  
log_slave_updates = on
binlog_format = row             #强烈建议，其他格式可能造成数据不一致
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 7

  #relay log
skip_slave_start = 1

read_only = on                   #设置只读

general_log = 1
general_log_file = /var/log/mysql/access.log
#数据库错误日志文件
#log-error = /var/lib/mysql/error.log  
log-error = /var/log/mysql/error.log
#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = /var/log/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


# Custom config should go here
# !includedir /etc/mysql/conf.d/

启动容器
```
cd slave
docker-compose up -d
```

新建的用户赋权限

给mysql的master账户和slave账户权限，其他权限各自按需添加

先进入主容器

docker exec -it mysql8-master bash;
mysql -uroot -pCn123456;
GRANT ALL ON *.* TO master; 
flush privileges;

从容器添加权限

docker exec -it mysql8-slave bash;
mysql -uroot -pCn123456;
GRANT REPLICATION CLIENT ON *.* TO slave;
GRANT REPLICATION SLAVE ON *.* TO slave;
GRANT SUPER ON *.* TO slave;
GRANT RELOAD ON *.* TO slave;
GRANT SELECT ON *.* TO slave;
flush privileges;

开启备份

进入从节点的mysql，使用slave账户连接主节点，开启备份:

docker exec -it mysql8-slave bash;
mysql -uslave -pCn123456;
CHANGE MASTER TO master_host='172.18.0.100', master_port=3306, master_user='master', master_password='Cn123456', master_auto_position=1;
START SLAVE;

查看状态

SHOW SLAVE STATUS\G