记录docker compose简单搭建MYSQL主从复制架构

准备环境：

两台机器：

192.168.10.253	主节点
192.168.10.252	从节点

均安装了docker和docker compose 环境，安装参考

MySQL 版本：8.0.27

一.主节点

服务器创建如下文件及文件夹：

docker-compose.yml

version: '3.6'
services:
  mysql:
    image: 'mysql:8.0.27'
    container_name: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
    ports:
      - '3306:3306'
    volumes:
      - './conf/my.cnf:/etc/mysql/my.cnf'
      - './logs:/logs'
      - './data:/var/lib/mysql'

 conf文件夹的配置文件my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
secure-file-priv=/var/lib/mysql
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
event_scheduler = 1
skip-character-set-client-handshake
skip-name-resolve
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 1500
innodb_flush_sync = 1
lower_case_table_names = 1 # 忽略表名大小写
log-bin=mysql-bin # 开启 binlog，binlog文件名
binlog-format=MIXED # 选择 MIXED 模式
server_id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复
expire-logs-days = 7 #二进制日志保留7天，防止磁盘爆满

启动主节点：

docker-compose up -d

进入mysql 容器内部：

进入容器内：
    docker exec -it mysql /bin/bash

登录mysql：
    mysql -uroot -p

创建同步数据的用户：
    mysql> CREATE USER 'slave' @'%' IDENTIFIED BY 'pwd123';

授予用户权限:
    mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave' @'%';

    mysql> FLUSH PRIVILEGES;

tips:
使用如下语句查询
    mysql> use mysql;
    mysql> SELECT plugin FROM `user` where user = 'slave';

发现使用的插件显示的是 caching_sha2_password, 我们需要改为 mysql_native_password,不然会连接不上。

mysql> ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd123';

查看主节点状态：
     mysql> show MASTER status;

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1175 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

记录下这两个值，后续在配置slave节点时需要用到

至此，主节点配置完毕。

二.从节点

服务器创建如下文件及文件夹：

 docker-compose.yml

version: '3.6'
services:
  mysql:
    container_name: mysql-slave
    restart: always
    image: 'mysql:8.0.27'
    ports:
      - "3306:3306"
    volumes:
      - ./conf/mysql:/etc/mysql
      - ./logs:/logs
      - ./data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: 'slave'

conf文件夹的配置文件my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
secure-file-priv=/var/lib/mysql
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
event_scheduler = 1
skip-character-set-client-handshake
skip-name-resolve
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 1500
lower_case_table_names = 1 # 忽略表名大小写
server_id=2
relay_log=relay_log
read_only=on
#replicate_wild_do_table=levi.% # %表示同步levi库下的所有表

启动从节点：

docker-compose up -d

进入容器内部：

#进入mysql-slave容器
    docker exec -it mysql-slave /bin/bash

#进入mysql
    mysql -uroot -p
    mysql>

# 停止从节点
    stop slave;


#关联master节点
change master to master_host='192.168.10.253', master_user='slave' ,master_password='pwd123', master_port=3306,master_log_file='mysql-bin.000003', master_log_pos=1175,master_connect_retry=30;

#启动slave
    start slave;

# 查看从节点状态：
    show slave status;

| Slave_IO_Running | Slave_SQ                    L_Running |
| Yes              | Yes                                   |

确保这两项为 Yes，即表示成功

若出现从服务器连不上的情况，有可能是防火墙或者端口没开。

查看想开的端口是否已开：
$ firewall-cmd --query-port=3306/tcp
提示yes表示已开通，提示no表示未开通。

开放端口号：
$ firewall-cmd --zone=public --add-port=3306/tcp --permanent

 使配置生效一定要重新载入配置
$ firewall-cmd --reload

永久关闭防火墙：systemctl disable firewalld.service

成功之后，可以看到，在主节点建库、建表、插入数据，从库会同步过来