MySQL5.7多主一从同步配置
发表于：2024-11-23 作者：千家信息网编辑
千家信息网最后更新 2024年11月23日，这篇文章主要讲解了"MySQL5.7多主一从同步配置"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"MySQL5.7多主一从同步配置"吧！部署环境注：使
千家信息网最后更新 2024年11月23日MySQL5.7多主一从同步配置
这篇文章主要讲解了"MySQL5.7多主一从同步配置"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"MySQL5.7多主一从同步配置"吧！
部署环境
注：使用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必须不同
【主库300】操作及配置
配置my.cnf
[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-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 = MIXEDsync_binlog = 1expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。#binlog_cache_size = 128m#max_binlog_cache_size = 512m#max_binlog_size = 256Mbinlog-do-db = test3 #要同步的数据库binlog-ignore-db = mysql #不需要同步的数据库 binlog_ignore_db = information_schemabinlog_ignore_db = performation_schemabinlog_ignore_db = sys ########################### character set##########################character-set-server = utf8mb4collation-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.75691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad[root@localhost ~]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES5691bac538e6 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/bashroot@5691bac538e6:/# mysql -u root -pEnter password:
注：若不熟悉docker，可使用传统方式安装mysql，效果相同。
创建授权用户
连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限
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';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+1 row in set
查看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
[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-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 = MIXEDsync_binlog = 1expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。#binlog_cache_size = 128m#max_binlog_cache_size = 512m#max_binlog_size = 256Mbinlog-do-db = test4 #要同步的数据库binlog-ignore-db = mysql #不需要同步的数据库 binlog_ignore_db = information_schemabinlog_ignore_db = performation_schemabinlog_ignore_db = sys ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci
安装启动
[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.719e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7[root@localhost ~]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES19e93b6d93ca 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/bashroot@19e93b6d93ca:/# mysql -u root -pEnter password:
创建授权用户
创建用户用于从库同步复制，授予复制、同步访问的权限
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';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+1 row in set
查看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
[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-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 = MIXEDsync_binlog = 1expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。#binlog_cache_size = 128m#max_binlog_cache_size = 512m#max_binlog_size = 256Mbinlog-do-db = test5 #要同步的数据库binlog-ignore-db = mysql #不需要同步的数据库 binlog_ignore_db = information_schemabinlog_ignore_db = performation_schemabinlog_ignore_db = sys ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci
安装启动
[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.719e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7[root@localhost ~]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES19e93b6d93ca 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/bashroot@19e93b6d93ca:/# mysql -u root -pEnter password:
创建授权用户
创建用户用于从库同步复制，授予复制、同步访问的权限
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';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+1 row in set
查看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
[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-character-set = utf8mb4[mysqld]########################### summary###########################bind-address = 0.0.0.0#port = 3306#datadir=/datavol/mysql/data #数据存储目录########################### log bin##########################server-id = 10345master_info_repository = tablerelay_log_info_repository = table ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci
安装启动
[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.719e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7[root@localhost ~]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES19e93b6d93ca 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/bashroot@19e93b6d93ca:/# mysql -u root -pEnter password:
设置【主库】信息
登录【从库10345】，进入mysql命令行。
mysql> stop slave;Query OK, 0 rows affectedmysql> 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 affectedmysql> 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 affectedmysql> 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 affectedmysql> start slave;Query OK, 0 rows affected
stop slave; //停止同步
start slave; //开始同步
//必须和【主库】的信息匹配。
CHANGE MASTER TO
MASTER_HOST='192.168.10.212', //主库IP
MASTER_PORT=4300, //主库端口
MASTER_USER='slave', //访问主库且有同步复制权限的用户
MASTER_PASSWORD='123456', //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE='mysql-bin.000003',
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=438
for channel '300'; //定义通道名称
查看同步状态
mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.212 Master_User: slave Master_Port: 4300 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1860 Relay_Log_File: 0ad84f013600-relay-bin-300.000002 Relay_Log_Pos: 1742 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1860 Relay_Log_Space: 1960 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 300 Master_UUID: 4efae154-ebd4-11e8-bf7d-0242ac110007 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 300 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.212 Master_User: slave Master_Port: 4400 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 2461 Relay_Log_File: 0ad84f013600-relay-bin-400.000002 Relay_Log_Pos: 1187 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2461 Relay_Log_Space: 1405 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 400 Master_UUID: 8fb0aa81-ebd4-11e8-ac5b-0242ac110009 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 400 Master_TLS_Version: *************************** 3. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.212 Master_User: slave Master_Port: 4500 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 2151 Relay_Log_File: 0ad84f013600-relay-bin-500.000002 Relay_Log_Pos: 626 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2151 Relay_Log_Space: 844 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 500 Master_UUID: b87704d2-ebd5-11e8-a90c-0242ac11000a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 500 Master_TLS_Version: 3 rows in set (0.00 sec)
可以看见设置三个的主从同步通道的所有状态信息。
只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的。
如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。
mysql> show variables like 'log_error%';+---------------------+--------+| Variable_name | Value |+---------------------+--------+| log_error | stderr || log_error_verbosity | 3 |+---------------------+--------+2 rows in set
配置完成，则【从库10345】开始自动同步。
若需要单独启动或停止某个同步通道，可使用如下命令：
start slave for channel '300'; //启动名称为300的同步通道
stop slave for channel '300'; //停止名称为300的同步通道
验证数据同步
建库
使用root账号登录【主库300】，创建test3数据库
mysql> CREATE DATABASE test3;Query OK, 1 row affected (0.00 sec)mysql> USE test3;Database changed
建表
在【主库300】中创建user表
CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, `age` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
新增
在【主库300】中向user表插入一条数据：
mysql> use test3;Database changedmysql> INSERT INTO user (id, name, age) VALUES (300, 'Tom', 18);Database changedmysql> SELECT * FROM user;+-----+------+-----+| id | name | age |+-----+------+-----+| 300 | Tom | 18 |+-----+------+-----+1 row in set (0.00 sec)
在【从库10345】中查询user表数据：
mysql> use test3;Database changedmysql> SELECT * FROM user;+-----+------+-----+| id | name | age |+-----+------+-----+| 300 | Tom | 18 |+-----+------+-----+1 row in set (0.00 sec)
新增记录同步成功。
更新
在【主库300】中修改刚才插入的数据：
mysql> UPDATE user SET name='Peter' where id=300;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;+-----+-------+-----+| id | name | age |+-----+-------+-----+| 300 | Peter | 18 |+-----+-------+-----+1 row in set (0.00 sec)
在【从库10345】中查询user表数据：
mysql> select * from user;+-----+-------+-----+| id | name | age |+-----+-------+-----+| 300 | Peter | 18 |+-----+-------+-----+1 row in set (0.00 sec)
更新记录同步成功。
删除
在【主库300】中删除刚才更新的数据：
mysql> DELETE FROM user WHERE id=300;Query OK, 1 row affected (0.00 sec)mysql> select * from user;Empty set (0.00 sec)
在【从库10345】中查询user表数据：
mysql> select * from user;Empty set (0.00 sec)
删除记录同步成功。
注：【主库400】、【主库500】的验证操作与上述类似。
补充：
如果【主服务器】重启mysql服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。
如果某段时间内，【从数据库】服务器异常导致同步中断（可能是同步点位置不匹配），可以尝试以下恢复方法：进入【主数据库】服务器（正常），在bin-log中找到【从数据库】出错前的position，然后在【从数据库】上执行change master，将master_log_file和master_log_pos重新指定后，开始同步。
感谢各位的阅读，以上就是"MySQL5.7多主一从同步配置"的内容了，经过本文的学习后，相信大家对MySQL5.7多主一从同步配置这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
同步
数据
配置
数据库
服务
服务器
日志
用户
二进制
主从
文件
状态
通道
信息
权限
目录
存储
登录
成功
名称
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发是现代服务业
幼儿园网络安全宣传展板素材
县残联网络安全自查报告
学院机房网络安全评估方案
网络安全品牌对比
牟平区平台软件开发
数据库技术运用
海曙一站式软件开发项目
做研究 软件开发
软件开发模型-迭代增量
实用数据库软件
计算机网络技术叫专科吗
原神外服哪个服务器人多
监管科技互联网金融风险
租房的网络技术
个人网络安全怎么防
springboot数据库配置是长连接吗
今日头条软件开发招聘
大数据服务器搭建
360代理服务器认证
oa查找数据库密码
德鲁伊数据库连接空指针异常
万网服务器数据库
列宽数据库
网络安全 盛世光明
加密货币网络安全概念
工控软件开发业务咨询
有关网络安全方面的手抄报文字
php软件开发流程
中学生专题教育和网络安全
