MySQL5.7 多主一从(多源复制)同步配置
2022/9/17 2:19:59
本文主要是介绍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 必须不同
事前准备
- 关闭 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/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 = 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,效果相同。
创建授权用户
连接 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'; +---------------+-------+ | 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
把该文件放到主库所在配置文件路径下:/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 = 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.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> 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'; +---------------+-------+ | 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
把该文件放到主库所在配置文件路径下:/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 = 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.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> 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'; +---------------+-------+ | 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
把该文件放到主库所在配置文件路径下:/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 ########################## character-set-server = utf8mb4 collation-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.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
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: 0 Master_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: 0 Master_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: 0 Master_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 changed mysql> INSERT INTO user (id, name, age) VALUES (300, 'Tom', 18); Database changed mysql> SELECT * FROM user; +-----+------+-----+ | id | name | age | +-----+------+-----+ | 300 | Tom | 18 | +-----+------+-----+ 1 row in set (0.00 sec)
在【从库 10345】中查询 user 表数据:
mysql> use test3; Database changed mysql> 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: 0 mysql> 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 重新指定后,开始同步。
- 若是不需要同步,登录从库执行:stop slave;
- 若需要单独启动或停止某个同步通道,可使用如下命令: start slave for channel '300'; // 启动名称为 300 的同步通道 stop slave for channel '300'; // 停止名称为 300 的同步通道
- 通常多主一从,主库之间尽量不使用相同的 databaseName,避免冲突。
这篇关于MySQL5.7 多主一从(多源复制)同步配置的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-25如何部署MySQL集群资料:新手入门教程
- 2024-12-24MySQL集群部署资料:新手入门教程
- 2024-12-24MySQL集群资料详解:新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习:新手入门教程
- 2024-12-24部署MySQL集群入门:一步一步搭建指南
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南