MySQL5.7多主一从同步配置
短信预约 -IT技能 免费直播动态提醒
这篇文章主要讲解了“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 affectedstop 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多主一从同步配置这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/43.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
