MySQL5.7 多主一从(多源复制)同步配置

多主一从,也称为多源复制,数据流向:

  • 主库 1
    -> 从库 s
  • 主库 2 -> 从库 s
  • 主库 n
    -> 从库 s

应用场景

  • 数据汇总,可将多个主数据库同步汇总到一个从数据库中,方便数据统计分析。
  • 读写分离,从库只用于查询,提高数据库整体性能。

部署环境

注:使用 docker 部署 mysql 实例,方便快速搭建演示环境。但本文重点是讲解主从配置,因此简略描述 docker 环境构建 mysql
容器实例。

  • 数据库:MySQL
    5.7.x (相比 5.5,5.6 而言,5.7 同步性能更好,支持多源复制,可实现多主一从,主从库版本应保证一致)
  • 操作系统:CentOS 7.x
  • 容器:Docker 17.09.0-ce
  • 镜像:mysql:5.7
  • 主库 300:IP=192.168.10.212;
    PORT=4300; server-id=300; database=test3; table=user
  • 主库 400:IP=192.168.10.212;
    PORT=4400; server-id=400; database=test4; table=user
  • 主库 500:IP=192.168.10.212;
    PORT=4500; server-id=500; database=test5; table=user
  • 从库
    10345:IP=192.168.10.212; PORT=4345; server-id=10345; database=test3,test4,test5;
    table=user

配置约束

  • 主从库必须保证网络畅通可访问
  • 主库必须开启 binlog 日志
  • 主从库的
    server-id 必须不同

事前准备

  1. 关闭
    selinux
setenforce0
vim /etc/sysconfig/selinux
SELINUX=disabled

# 若不关闭,使用docker启动mysql5.7镜像容器时启动不起来,查看日志会有如下错误显示:
ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help"
mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help"
mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
  1. 安装并启动
    docker
yum install epel-release
yum -y install docker
systemctl start
  1. 创建目录
mkdir-p /datavol/mysql-300/{mysql,conf}
mkdir -p /datavol/mysql-400/{mysql,conf}
mkdir -p /datavol/mysql-500/{mysql,conf}
mkdir -p /datavol/mysql-10345/{mysql,conf}

【主库 300】操作及配置

配置
my.cnf

把该文件放到主库所在配置文件路径下:​​/datavol/mysql-300/conf​

[client]port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 300 #必须唯一
log_bin = mysql-bin #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test3 #要同步的数据库

binlog-ignore-db = mysql #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys


##########################
# character set
##########################
character-set-server = utf8mb4
collation-server

安装启动

[root@localhost ~]# docker run -d -p 4300:3306 --name=mysql-300 -v /datavol/mysql-300/conf:/etc/mysql/conf.d -v /datavol/mysql-300/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
5691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5691bac538e6 mysql:5.7 "docker-entrypoint..." 44 seconds ago Up 43 seconds 33060/tcp, 0.0.0.0:4300->3306/tcp mysql-300
[root@localhost ~]# docker exec -it mysql-300 /bin/bash
root@5691bac538e6:/# mysql -u root -p
Enter password:

注:若不熟悉
docker,可使用传统方式安装 mysql,效果相同。

创建授权用户

连接
mysql 主数据库,键入命令 mysql -u root -p,输入密码后登录数据库。创建用户用于从库同步复制,授予复制、同步访问的权限

mysql>'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql>'slave'@'%' identified by '123456';

log_bin
是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in

查看
master 状态

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 438
Binlog_Do_DB: test3
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【主库 400】配置及操作

配置
my.cnf

把该文件放到主库所在配置文件路径下:​​/datavol/mysql-400/conf​

[client]port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 400 #必须唯一
log_bin = mysql-bin #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test4 #要同步的数据库

binlog-ignore-db = mysql #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys


##########################
# character set
##########################
character-set-server = utf8mb4
collation-server

安装启动

[root@localhost ~]# docker run -d -p 4400:3306 --name=mysql-400 -v /datavol/mysql-400/conf:/etc/mysql/conf.d -v /datavol/mysql-400/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:4400->3306/tcp mysql-400
[root@localhost ~]# docker exec -it mysql-400 /bin/bash
root@19e93b6d93ca:/# mysql -u root -p
Enter password:

创建授权用户

创建用户用于从库同步复制,授予复制、同步访问的权限

mysql>'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql>'slave'@'%' identified by '123456';

log_bin
是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in

查看
master 状态

mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 438
Binlog_Do_DB: test4
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【主库 500】配置及操作

配置
my.cnf

把该文件放到主库所在配置文件路径下:​​/datavol/mysql-500/conf​

[client]port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 500 #必须唯一
log_bin = mysql-bin #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test5 #要同步的数据库

binlog-ignore-db = mysql #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys


##########################
# character set
##########################
character-set-server = utf8mb4
collation-server

安装启动

[root@localhost ~]# docker run -d -p 4500:3306 --name=mysql-500 -v /datavol/mysql-500/conf:/etc/mysql/conf.d -v /datavol/mysql-500/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:4500->3306/tcp mysql-500
[root@localhost ~]# docker exec -it mysql-500 /bin/bash
root@19e93b6d93ca:/# mysql -u root -p
Enter password:

创建授权用户

创建用户用于从库同步复制,授予复制、同步访问的权限

mysql>'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql>'slave'@'%' identified by '123456';

log_bin
是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in

查看
master 状态

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 438
Binlog_Do_DB: test5
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【从库 10345】配置及操作

配置
my.cnf

把该文件放到主库所在配置文件路径下:​​/datavol/mysql-10345/conf​

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 10345
master_info_repository = table
relay_log_info_repository = table

##########################
# character set
##########################

安装启动

[root@localhost ~]# docker run -d -p 4345:3306 --name=mysql-10345 -v /datavol/mysql-10345/conf:/etc/mysql/conf.d -v /datavol/mysql-10345/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:4345->3306/tcp mysql-10345
[root@localhost ~]# docker exec -it mysql-10345 /bin/bash
root@19e93b6d93ca:/# mysql -u root -p
Enter password:

设置【主库】信息

登录【从库
10345】,进入 mysql 命令行。

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.10.212',
MASTER_PORT=4300,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=438
for channel '300';
Query OK, 0 rows affected

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.10.212',
MASTER_PORT=4400,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=438
for channel '400';
Query OK, 0 rows affected

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.10.212',
MASTER_PORT=4500,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=438
for channel '500';
Query OK, 0 rows affected

mysql> start slave;
Query OK, 0 rows affected