案例 - [Docker] Docker 快速构建Mysql 主从架构 - 《Docker》

一、Mysql Master 节点相关设置
- 2.1、启动 master 节点
- 2.2、节点同步处理
  - 2.2.1、开启主从同步
  - 2.2.2、创建同步用户并授权
二、Mysql Slave 节点相关配置
- 2.1、启动 slave 节点
- 2.2、slave 节点同步处理
三、验证
四、主从数据同步处理

mysql 5.7 适用于快速构建测试环境主从架构

一、Mysql Master 节点相关设置

相关目录结构

部分文件及操作演示

2.1、启动 master 节点

2.1.1、docker-compose 脚本

version: '3'
services:
  mysql_master:
    container_name: mysql_master
    image: mysql:5.7
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      TZ: Asia/Shanghai
    ports:
      - 3308:3306
    volumes:
      - ./log:/var/log/mysql   ## log
      - ./data:/var/lib/mysql  ## 数据
      - ./config/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf  ## config

2.1.2、mysql 配置文件准备

mysqld.cnf相关配置如下：

[client]
default_character_set=utf8
[mysqld]
collation_server=utf8_general_ci
character_set_server=utf8
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir   = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=2048
max_allowed_packet=500M
server_id=101
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062

2.1.3、启动 Mysql Master 节点

# 在 docker-compose.yaml 所在路径执行命令
docker-compose up -d

2.1.4、测试连接 mysql Master 节点

## docker 查看mysql master 节点运行状态
docker ps | grep mysql_master

## 尝试连接 mysql master 节点
[root@ master]# docker exec -it mysql_master mysql -u root -p
Enter password: 
mysql>

2.2、节点同步处理

2.2.1、开启主从同步

## 进入到 mysql 控制台
root@ master]# docker exec -it mysql_master mysql -u root -p
Enter password: 
mysql> 
mysql> start slave;

2.2.2、创建同步用户并授权

## 创建用户
create user 'slave'@'%' identified by '123456';

## 授权
grant replication slave, replication client on *.* to 'slave'@'%';

二、Mysql Slave 节点相关配置

相关目录结构

部分文件及操作演示

2.1、启动 slave 节点

2.1.1、docker-compose.yaml 脚本

version: '3'
services:
  mysql_slave:
    container_name: mysql_slave
    image: mysql:5.7
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      TZ: Asia/Shanghai
    ports:
      - 3309:3306
    volumes:
      - ./log:/var/log/mysql   ## log
      - ./data:/var/lib/mysql  ## 数据
      - ./config/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf  ## config

2.1.2、mysql 配置文件准备

mysqld.cnf相关配置如下：

[client]
default_character_set=utf8
[mysqld]
collation_server=utf8_general_ci
character_set_server=utf8
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir   = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
max_connections=2048
server_id=102
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062

2.1.3、启动 mysql slave 节点

# 在 docker-compose.yaml 所在路径执行命令
docker-compose up -d

2.1.4、测试连接 mysql slave 节点

## docker 查看mysql slave 节点运行状态
docker ps | grep mysql_slave

## 尝试连接 mysql master 节点
[root@ master]# docker exec -it mysql_slave mysql -u root -p
Enter password: 
mysql>

2.2、slave 节点同步处理

2.2.1、查看 master 节点同步状态

[root@mine_test master]# docker exec -it mysql_master mysql -uroot -p
Enter password: 

mysql> show master logs;
+-----------------------+-----------+
| Log_name              | File_size |
+-----------------------+-----------+
| mall-mysql-bin.000001 |       177 |
| mall-mysql-bin.000002 |       177 |
| mall-mysql-bin.000003 |      1733 |
| mall-mysql-bin.000004 |       177 |
| mall-mysql-bin.000005 |       154 |
+-----------------------+-----------+
5 rows in set (0.02 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mall-mysql-bin.000005 |      154 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.2.2、slave 节点配置主从

## 配置主从同步
[root@mine_test slave]# docker exec -it mysql_slaver mysql -uroot -p
Enter password: 

mysql > change master to master_host='xx.xx.xx',master_user='slave',master_password='123456',master_port=3307,master_log_file='mall-mysql-bin.000005',master_log_pos=154,master_connect_retry=30;

## 开启主从配置
mysql > start slave;

2.2.3、查看主从复制状态

[root@mine_test slave]# docker exec -it mysql_slaver mysql -uroot -p
Enter password: 

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxxxxxxxxxxx
                  Master_User: slave
                  Master_Port: 3308
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: 8fbe62b7a804-relay-bin.000009
                Relay_Log_Pos: 325
        Relay_Master_Log_File: mall-mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

三、验证

略:master 节点操作，验证 slave 是否同步

四、主从数据同步处理

在进行扩展主从架构时，主库中通常都已存在运行数据，此时开启主从复制有两种方案

1、忽略旧数据，不处理，只同步新数据
2、对主库进行备份，从库使用主库数据启动并开启主从复制

如果是第一种，直接配置从库即可。
不过大部分场景下都是需要进行旧数据同步的场景。

1、备份主库数据，并记录备份时主库数据点

## 数据库备份脚本
docker exec [CONTAINER] /usr/bin/mysqldump -u username --password=xxx [DATABASE] > xxx.sql
## [DATABASE] 指定单表 or --all-databases 所有库备份

## 记录 master 节点状态
mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mall-mysql-bin.000005 |      466 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+

2、启动从节点并导入数据

## 启动从节点
略

## 创建数据库
略

## 导入数据
cat backup.sql | docker exec -i mysql_slave_2 /usr/bin/mysql -u root --password=123456 db_test

3、开启从节点复制

## 设置master 节点复制
mysql > change master to master_host='xxx.xxx.xxx.xxx',master_user='slave',master_password='123456',master_port=3308,master_log_file='mall-mysql-bin.000005',master_log_pos=466,master_connect_retry=30;

## 开启 slave
mysql > start slave;