MySQL之主从复制集群搭建_mysql集群主从复制

简述

这篇文章主要记录使用docker compose搭建MySQL主从复制集群搭建，方便后续进行本地测试开发。

这篇文章主要介绍一主一从的搭建过程。

主从架构，可以缓解MySQL的数据存储以及访问的压力。

一. 主从复制原理

原理图如下：

步骤：

• 当master主服务器上的数据发生改变时，则将其改变写入二进制（binlog）事件日志文件中；
• slave从服务器会在一定时间间隔内对master主服务器上的二进制日志进行探测，探测其是否发生过改变（通过二进制文件的大小是否不同来进行判断，日志文件改变了的大小也可以叫作偏移），如果探测到master主服务器的二进制事件日志发生了改变，则开始一个I/O Thread请求master二进制事件日志；
• 同时master主服务器为每个I/O Thread启动一个dump thread，用于向其发送二进制事件日志；
• slave从服务器将接收到的二进制事件日志写到自己本地的（relay日志）中继日志文件中；
• slave从服务器将启动SQL Thread从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；
• 最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次唤醒；

主从复制的过程会有很小的延迟，基本没有影响

二. 配置文件

搭建主从集群时候，需要两个注意点（Docker不需要）：

• MySQL版本必须一致；
• 节点之间时间需要同步；

2.1. 环境目录

.
├── conf
│   ├── master
│   │   └── my.cnf  ==> master的配置文件
│   └── slave
│       └── my.cnf  ==> slave的配置文件
├── data
│   ├── master ==> master的数据目录
│   └── slave  ==> slave的数据目录
└── docker-compose.yml
1
2
3
4
5
6
7
8
9
10

2.2. docker compose文件

version: '3'
services:
  # master节点
  mysql-cluster:
    image: mysql:latest
    container_name: mysql-cluster
    restart: always
    ports:
      - 33306:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    volumes:
      - ./data/master:/var/lib/mysql
      - ./conf/master:/etc/mysql/conf.d
    networks:
      - one-to-one
  # slave节点    
  mysql-slave:
    image: mysql:latest
    container_name: mysql-slave
    restart: always
    ports:
      - 33307:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    volumes:
      - ./data/slave:/var/lib/mysql
      - ./conf/slave:/etc/mysql/conf.d   
    networks:
      - one-to-one
networks:
  one-to-one:
    driver: bridge
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

2.3. 数据库配置文件

master的my.cnf：

[mysqld]
# 服务器唯一ID，默认是1，一般取IP最后一段
server-id=1
# 开启二进制日志
log-bin=mysql-bin
# 是否只读； 1:只读 0:可读可写
read-only=0
# 指定忽略哪个库不同步
binlog-ignore-db=mysql
# 设置binlog格式 statement｜mixed｜row（canal）
binlog_format=row
# 设置需要同步的数据库 binlog_do_db = 数据库名； 
# 如果是多个同步库，就以此格式另写几行即可。
# 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
# binlog_do_db=test

# 确保binlog日志写入后与硬盘同步
# sync_binlog = 1
# 跳过所有的错误，继续执行复制操作
# slave-skip-errors = all 

# 密码问题配置
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
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

slave的my.cnf：

[mysqld]
# 服务器唯一ID，默认是1，一般取IP最后一段
server-id=2
# 开启二进制日志
log-bin=mysql-bin
# 是否只读； 1:只读 0:可读可写
read-only=1
# 指定忽略哪个库不同步
binlog-ignore-db=mysql
# 设置binlog格式 statement｜mixed｜row（canal）
binlog_format=row
# 设置需要同步的数据库 binlog_do_db = 数据库名； 
# 如果是多个同步库，就以此格式另写几行即可。
# 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
# binlog_do_db=test

# 确保binlog日志写入后与硬盘同步
sync_binlog = 1
# 跳过所有的错误，继续执行复制操作
slave-skip-errors = all 

# 密码问题配置
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
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

2.4. 启动

执行up命令启动docker容器：

docker compose up -d
1

查看执行状态：

2.5. 注意

上面MySQL提供了一个sync_binlog参数来控制数据的binlog写到磁盘的频率！（MySQL在每次提交事务的时候把二进制日志的内容同步到磁盘上）这个参数有下面几个配置：

• sync_binlog=0：默认值，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓冲刷新；此时性能最好，但是风险最大，一旦系统宕机，在binlog_cache中的所有binlog信息都会被丢失；
• sync_binlog>0：表示没sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓冲写入日志；当sync_binlog=1是最安全的，表示每次事务提交，MySQL都会把binlog写入日志，但是此时性能损耗最大（虽然可以通过group cimmit缓解，但是刷新的频率过高对IO性能影响也是非常大）。

在生产环境中sync_binlog一般设置为0或者100，这样牺牲一定的一致性，可以获得更高的并发和性能。

group commit可以参看这篇文章：http://keithlan.github.io/2018/07/24/mysql_group_commit/

三. 授权配置

上面启动之后主从复制集群并没有搭建成功，下面我们还需要配置一些数据！

3.1. master配置

接着先进去master的容器内部：

docker exec -it mysql-cluster bash
1

登陆MySQL，创建一个账号（这个账号主要用户主从复制），并授权：

root@45504f93e66d:/# mysql -uroot -p123456 # 登陆

mysql> create user 'long'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> grant replication slave on *.* to 'long'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> grant replication client on *.* to 'long'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

接着查看master的binlog日志状态：

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 2096
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

这里需要记住File和Position两个值，后面会用到

3.2. slave配置

这里我们还是和上面一样先进去slave容器内部：

docker exec -it mysql-slave bash
1

接着登陆MySQL的控制台，主从服务器进行连接：

mysql> change replication source to source_host='192.168.31.174', source_port=33306, source_user='long', source_password='123456', source_log_file='mysql-bin.000003', source_log_pos=1047;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> start replica; # 开启主从复制，8.0.22之前版本执行：start slave
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5
6
7
8

这里我们稍微介绍下SQL的各个参数的含义：

• source_host：主数据库的主机地址
• source_port：主数据库的端口，不设置默认为3306
• source_user：主数据库被授予同步复制权限的用户名
• source_password：对应的用户密码
• source_log_file：在主数据库执行命令show master statua查询的File的二进制日志文件名称
• source_log_pos：在主数据库执行命令show master statua查询的Position的二进制日志文件记录位置

接着我们查看一些主从连接状态：show slave status\G;

8.0.22之前的版本执行 show slave status;

3.3. 验证

这里我们只需要在主库中创建一个database，建一张表，插入一条数据，在从库中查看如果有就说明成功了！

3.4. 常用命令

下面列举一些主从常用命令：

• 启动主从复制：start replica ｜ start slave
• 停止主从复制：stop replica ｜ stop slave
• 重启主从复制：restart replica ｜ restart slave

关闭主从复制配置：

• 从库关清除该从服务器和主服务器的关系，执行：
  • stop replica;
  • reset replica all；
• 主库也不维护主从关系，执行：
  • reset master;