MySQL8.0.18配置多主一从

目录

• 1. 现实背景

• 2. 约定

• 3. 配置 master

• 3.1. 配置启动参数

• 3.2. 重启服务使参数生效

• 3.3. 以 root 身份登录，创建用户，赋予密码，授权，刷新权限

• 4. 配置 slave 服务器

• 4.1. 配置启动参数

• 4.2. 重启服务使参数生效

• 5. 配置多主一从

• 5.1. 查看 master 状态

• 5.2. 配置 slave 与 master 的关联

• 5.3. 准备表

• 5.4. 启动 slave，查看 slave 状态

1. 现实背景

现有 4 台主机，均能够自动地采集数据，并存入其 MySQL 数据库中，另有 1 台专门用于处理数据的高配置主服务器。这 5 台机器经常不在同一个网段下，但希望，一旦处于同一个网段下时，4 台用于采集数据的主机能够自动地向主服务器汇集数据，为此配置环境。

2. 约定

• slave，主服务器

• master1， 用于采集数据的某一台主机

• master2， 用于采集数据的某一台主机

• master3， 用于采集数据的某一台主机

• master4， 用于采集数据的某一台主机

3. 配置 master

3.1. 配置启动参数

多台 master 只需确保 server-id 不一致即可，其他根据自身需求配置。

[mysqld]
# 服务器标识符, 确保每台服务器标识符都不一样
server-id = 1000

# master 机必须开启 log_bin
# mysql-bin 为自定义名字，会生成诸如 mysql-bin.index、mysql-bin.000001 等文件
log_bin=mysql-bin

# 二进制日志过期时间（单位：天），默认值为 0，即不过期
expire_logs_days = 0

# 错误日志
log-error=/var/lib/mysql/mysql-error.log

# 单个 log_bin 文件最大值，达到最大值之后新建文件后缀自增，如 mysql-bin.000002
max_binlog_size = 100M

# mysql 安装路径
basedir=/var/lib/mysql

# mysql 数据路径
datadir=/var/lib/mysql

# master 记录操作的数据库
binlog_do_db=replication

# master 忽略的数据库
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
binlog_ignore_db=sys
binlog_ignore_db=mysql

# 二进制日志保存模式
binlog_format=MIXED

# blob 类型的最大存储值（单位：字节、B）
# 1048576 B = 1MB
max_allowed_packet=1048576

# 密码复杂度配置，需要插件
# 密码长度至少为 0
# validate_password_length=8

# 大小写同时存在的最少数目
# validate_password_mixed_case_count=1

# 密码至少存在的数字数目
# validate_password_number_count=1

# 密码至少存在的特殊字符数目
# validate_password_special_char_count=1

innodb_flush_log_at_trx_commit=0

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

3.2. 重启服务使参数生效

3.3. 以 root 身份登录，创建用户，赋予密码，授权，刷新权限

创建用户 replication，同时赋予密码：

create user 'replication'@'％' identified with mysql_native_password by 'JINGhuaSHUIyue123,.';

如果创建用户失败，可能已经存在用户，不紧要的话可以删除该用户：

drop user 'replication'@'％';

如果不希望删除重建用户，只希望修改密码：

alter user 'replication'@'％' identified with mysql_native_password by 'JINGhuaSHUIyue123,.';

赋予用户 replication slave 权限：

grant replication slave on *.* to 'replication'@'％';

保证 replication slave 权限立即生效，刷新权限：

flush privileges;

4. 配置 slave 服务器

4.1. 配置启动参数

[mysqld]
# 服务器标识符, 确保每台服务器标识符都不一样
server-id = 2000

# mysql 安装路径
basedir=D:\mysql

# mysql 数据路径
datadir=D:\mysql\data

# slave 复制的数据库
replicate_do_db=test

# slave 忽略的数据库
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys

# slave 网络超时重连间隔（单位：秒）
slave_net_timeout=60

[mysql]

default-character-set=utf8

[client]

default-character-set=utf8

4.2. 重启服务使参数生效

5. 配置多主一从

5.1. 查看 master 状态

以 root 身份登陆 master1，需要留意其中的 file、position：

show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000006 |      155 | test         | information_schema,performance_schema,sys,mysql |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

以 root 身份登陆 master1，需要留意其中的 file、position：

show master status;

+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000005 |      155 | test         | information_schema,performance_schema,sys,mysql |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

说明：启动 MySQL 会强制生成新的 log-bin，因此位置均为 155。

5.2. 配置 slave 与 master 的关联

查看是否有其他残余的配置：

show slave status\G;

停止 slave，清除残余配置：

stop slave;

reset slave all;

根据 master1 的 file，position 配置 replication 通道“master1”

change master to
master_host = '112.124.1.100',
master_user = 'replication',
master_port = 3306,
master_password = 'replication',
master_log_file = 'mysql-bin.000006',
master_log_pos = 155,
master_connect_retry = 15,
master_retry_count = 0
for channel 'master1';

根据 master2 的 file，position 配置 replication 通道“master2”

change master to
master_host = '192.168.1.139',
master_user = 'replication',
master_port = 3306,
master_password = 'JINGhuaSHUIyue123,.',
master_log_file = 'mysql-bin.000005',
master_log_pos = 155,
master_connect_retry = 15,
master_retry_count = 0
for channel 'master2';

• master_connect_retry：连接失败，重试间隔（单位：秒）

• master_retry_count：连接失败重试次数，0 为无限次

5.3. 准备表

启动前，在三台机器的数据库中使用 DDL 语句定义好表结构，且表结构保持一致，确保主从复制前的一致性，否则会出错！

5.4. 启动 slave，查看 slave 状态

start slave for channel 'master1';
start slave for channel 'master2';

show slave status\G;

注意 Slave_IO_Running 和 Slave_Slave_Running 需要均显示为 Yes，才表示成功，否则留意错误提示。

来源：https://juejin.cn/post/6975853919029198879