多主一从,也称为多源复制,数据流向:mysql
主库1 -> 从库s
主库2 -> 从库s
主库n -> 从库s
应用场景
数据汇总,可将多个主数据库同步汇总到一个从数据库中,方便数据统计分析。
读写分离,从库只用于查询,提升数据库总体性能。
部署环境
注:使用docker部署mysql实例,方便快速搭建演示环境。但本文重点是讲解主从配置,所以简略描述docker环境构建mysql容器实例。linux
数据库: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必须不一样
事前准备
关闭selinux
setenforce 0
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!
安装并启动docker
yum install epel-release
yum -y install docker
systemctl start docker.service
建立目录
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/confsql
[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 = utf8mb4_unicode_ci
安装启动
[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,效果相同。docker
建立受权用户
链接mysql主数据库,键入命令mysql -u root -p,输入密码后登陆数据库。建立用户用于从库同步复制,授予复制、同步访问的权限数据库
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-