MySQL双主双从与ProxySQL读写分离实战
目录
一、mysql双主双从（MM-SS）
mysql双主双从，顾名思义，就是有两个主服务器，两个（或多个）从服务器。单个主服务器如果故障，会影响全局的写入事件。双主服务器可以解决这个问题。
环境准备
在测试环境中，关闭所有主机的防火墙，selinux设置为关闭。所有主机已安装mysql5.7.17版本
| 主机名 | IP地址 |
| master1 | 192.168.4.50 |
| master2 | 192.168.4.51 |
| slave1 | 192.168.4.52 |
| slave2 | 192.168.4.53 |
搭建步骤
两个主服务器
接下来需要分别在各个服务器上设置对方为自己的主服务器。
1.编辑配置文件
打开主配置文件/etc/my.cnf。向其中写入：
master1:
[mysqld]
server_id=1
log-bin = mysql-bin
binlog-format = ROW
gtid_mode=ON
enforce_gtid_consistency=1
auto-increment-increment=2
auto-increment-offset=1
log-bin=mysql-bin
sync_binlog=1
master2
[mysqld]
server_id=2
log-bin = mysql-bin
binlog-format = ROW
gtid_mode=ON
enforce_gtid_consistency=1
auto-increment-increment=2
auto-increment-offset=2
log-bin=mysql-bin
sync_binlog=1
编辑完后记得重启mysql
上面的配置中设置了GTID，设置后，从服务器会自动与主服务器协商二进制日志的复制位置，不用在自行查看主服务器的配置。避免出现位置错误
GTID（全局事务标识符）是MySQL复制中的一种机制，它为每个事务生成一个全局唯一的ID，用于自动追踪和定位复制位置。使用GTID后，不再需要手动指定二进制日志文件名和位置，简化了主从切换和故障恢复的流程。当主库发生故障时，从库可以根据GTID自动找到正确的事务点继续复制，确保数据一致性和高可用性。
配置文件中auto-increment-increment（自增步长）和auto-increment-offset（自增偏移量）是GTID相关的重要配置项。这是为了避免双主服务器同时写入时自增主键冲突。每次新增事务时，GTID会按照自增步长增加，并根据来源服务器的不同偏移相对应的值。(Master1 只会生成 1, 3, 5, 7... 这样的奇数ID，而 Master2 只会生成 2, 4, 6, 8... 这样的偶数ID。)
下面是各字段的解释
| 字段名 | 描述 |
| server-id | 服务器的id。server-id必须唯一 |
| log-bin | 开启二进制日志 |
| binlog-format | 设置为ROW，表示使用行格式的二进制日志 |
| gtid_mode = ON | 开启GTID（全局事务标识符），这样可以自动跟踪复制位置，避免使用传统的文件名和位置。 |
| enforce-gtid-consistency = ON | 确保只有对GTID安全的语句被记录。 |
| 自增步长。这是为了避免双主服务器同时写入时自增主键冲突。每次自增这个数值（写主服务器数量） | |
| auto-increment-offset | 自增偏移量。每次自增时额外增加这个值。（写服务器序号） |
| sync_binlog | 确保二进制日志及时写入磁盘 |
2.创建用户
创建master1上创建用于复制的用户
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'192.168.4.%' IDENTIFIED BY 'Xthy@1234';master2上同理（可以沿用上面的命令）
3.设置主服务器
设置master1服务器的主服务器是master2
在master1上执行
CHANGE MASTER TO
MASTER_HOST='192.168.4.51',
MASTER_USER='rep',
MASTER_PASSWORD='Xthy@1234',
MASTER_AUTO_POSITION=1;
设置master2服务器的主服务器是master1
在master2上编辑
CHANGE MASTER TO
MASTER_HOST='192.168.4.50',
MASTER_USER='rep',
MASTER_PASSWORD='Xthy@1234',
MASTER_AUTO_POSITION=1;
4.启动
START SLAVE;检查启动状况
SHOW SLAVE STATUS\G;重点检查这两个字段。如果出错，大概率是刚刚敲的命令有误，可以重新配置一下
|
Slave_IO_Running: Yes Slave_SQL_Running: Yes |
这样就成功将服务器A设置为服务器B的主服务器了。
5.测试
创建一些临时数据，由于两个主服务器互为主从，在任意一方写入的数据都会同步到另一方
create database test;
use test;
create table user(id int);
insert into user (name) values ('aaa'),('bbb'),('ccc');
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 3 | bbb |
| 5 | ccc |
+----+------+
3 rows in set (0.00 sec)
到另一个主机上查询一下
select * from test.user;
这样即使数据没及时同步，主键也不会冲突
设置互为主从后，备份一下，一会儿需要导入到从服务器中
innobackupex --user=root --password='mysqlpassword' /xtrabackup/full/
tar -czf mysqlbackup.tar.gz /xtrabackup/full/2025-10-19_18-30-10/
scp mysqlbackup.tar.gz 192.168.4.52:/root
scp mysqlbackup.tar.gz 192.168.4.53:/root
从服务器配置
1.恢复主服务器备份
(工作目录在/root下)
tar -xf mysqlbackup.tar.gz
systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex xtrabackup/full/2025-10-19_18-30-10/ --apply-log
innobackupex xtrabackup/full/2025-10-19_18-30-10/ --copy-back
chown -R mysql:mysql /var/lib/mysql
systemctl restart mysqld
2.修改配置文件
编辑/etc/my.cnf,在其中分别写入：
slave1
[mysqld]
server-id=3
gtid_mode=on
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
slave2
[mysqld]
server-id=4
gtid_mode=on
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
重启服务
3.设置双主复制通道
多源复制通道是MySQL中允许一个从服务器同时从多个主服务器复制数据的技术，每个主服务器的数据流通过独立的“通道”进行管理。通道名称用于区分不同主库的数据来源，使得从库可以并行接收和处理多个主库的二进制日志事件。这种机制在双主或多主架构中非常有用，能够实现数据的集中备份、复杂拓扑结构的灵活管理，并支持按通道独立监控和控制复制状态。
--to master1
CHANGE MASTER TO
MASTER_HOST='192.168.4.50',
MASTER_USER='rep',
MASTER_PASSWORD='Xthy@1234',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'master1';
--to master2
CHANGE MASTER TO
MASTER_HOST='192.168.4.51',
MASTER_USER='rep',
MASTER_PASSWORD='Xthy@1234',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'master2';
由于之前设置了GTID，所以这里可以直接设置MASTER_AUTO_POSITION=1 而不是MASTER_LOG_FILE="master*****",MASTER_LOG_POS=***;
按照架构图，slave1应该设置master1通道，slave2应该设置master2通道。但是，一个服务器也可以同时配置多个主服务器的位置，且不影响原有功能
4.启动
START SLAVE;检查启动状况
SHOW SLAVE STATUS\G;和刚刚一样关注这两个字段
|
Slave_IO_Running: Yes Slave_SQL_Running: Yes |
设置完成后，可以插入数据测试一下
在任意主服务器插入数据后，其他服务器也会有数据
主服务器
其他服务器
二、ProxySQL读写分离
接下来我们实现读写分离。可以使用的软件很多，我们这里以ProxySQL为例
1.安装 ProxySQL
方法1：使用官方 YUM 仓库
wget https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql-2.5.4-1-centos7.x86_64.rpm
yum install -y proxysql-2.5.4-1-centos7.x86_64.rpm
方法2：配置官方仓库
如果上面的方案不可行，那可以试试配置一个官方仓库，内容如下
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
yum install -y proxysql安装成功后，启用服务
systemctl enable proxysql --now2.配置防火墙
proxySQL 需要占用6032（管理端口）,6033（代理端口），6080（web管理端口，如果启动了的话）。如果防火墙开启，则需开放这些端口。
firewall-cmd --permanent --add-port=6032/tcp
firewall-cmd --permanent --add-port=6033/tcp
firewall-cmd --permanent --add-port=6080/tcp
firewall-cmd --reload
systemctl stop firewalld
我使用的测试环境，防火墙已经关闭
systemctl stop firewalld3.proxySql的基础配置
3.1连接到管理界面
（如果没安装mysql，这里可以安装一个mariadb）
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt="ProxySQL> "
3.2.配置监控用户
在主服务器上创建监控用户。这个用户是用于服务器之间同步数据的。
GRANT USAGE ON *.* TO 'monitor'@'%' IDENTIFIED BY 'Monitor123!';
FLUSH PRIVILEGES;
然后回到之前连接到的管理界面，在其中配置监控用户：
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor123!'
WHERE variable_name='mysql-monitor_password';
3.3. 配置后端 MySQL 服务器
根据你的双主双从架构添加服务器。我们设定主服务器的hostgroup_id=10，从服务器的hostgroup_id=20。需要将服务器的信息在管理界面中写入到mysql_servers表中
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment) VALUES
(10, '192.168.4.50', 3306, 1000, 'Master1 - Write'),
(10, '192.168.4.51', 3306, 1000, 'Master2 - Write');
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment) VALUES
(20, '192.168.4.52', 3306, 100, 'Slave1 - Read'),
(20, '192.168.4.53', 3306, 100, 'Slave2 - Read');
3.4.配置Mysql用户（用于应用连接）
首先，在 MySQL 主服务器 上创建应用用户。创建的这个用户是用于外部来查询的代理用户。可以按需求配置。
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' IDENTIFIED BY 'AppUser123!';
FLUSH PRIVILEGES;
然后在 ProxySQL管理单元中配置这个用户：
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent, comment)
VALUES ('app_user', 'AppUser123!', 10, 1, 'Application user');
3.5.配置读写分离规则
这里需要按照之前定义的组修改，将相应的请求分发到相应的组（写到主服务器（写组），读到从服务器（从组））
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 10, 1, 'Write SELECT'), -- SELECT FOR UPDATE 路由到写组
(2, 1, '^SELECT', 20, 1, 'Read SELECT'), -- 普通 SELECT 路由到读组
(3, 1, '.*', 10, 1, 'All other to Write'); -- 其他所有语句路由到写组
3.6.保存配置
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL USERS TO DISK;LOAD MYSQL *** TO RUNTIME;
SAVE MYSQL *** TO DISK;
可以让配置立即生效，每一步都是持久化操作，并便于定位问题所在（当然运行的话只要复制后全部在管理界面粘贴就行）
4.测试
4.1.检查服务器状态
SELECT hostgroup_id, hostname, port, status, weight, comment
FROM runtime_mysql_servers
ORDER BY hostgroup_id, hostname;
正常输出应该显示所有服务器的 status 为 ONLINE（配置的时候有一台服务器坏了，重新配了一台）
4.2.检查连接监控
SELECT *
FROM monitor.mysql_server_ping_log
ORDER BY time_start_us DESC
LIMIT 10;
4.3测试应用连接
在客户端使用应用用户连接 ProxySQL 的代理端口（6033）
mysql -u app_user -pAppUser123! -h 服务器位置 -P 6033
SHOW DATABASES;
连接后可以进行测试查询
读写也正常
现在，可以直接连接proxySQL 服务器的6033端口，来实现读写分离、均衡负载地，对数据库进行访问
更多推荐
23
0- 0
已为社区贡献1条内容
所有评论(0)