mysql双主双从-读写分离

不吃辣9

已于 2023-09-18 16:15:07 修改

阅读量115

点赞数

文章标签： mysql 数据库

于 2023-09-18 16:06:32 首次发布

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/qq_59337657/article/details/132965745

版权

目录

服务器的配置

主库的配置

从库的配置

两台主库相互复制

配置schema.xml文件

配置server .xml文件

启动并登录mycat：

查看读写分离是否成功

环境准备

服务器

五台服务器：

192.168.249.200 master1 软件：mysql

192.168.249.201 master2 软件：mysql

192.168.249.202 mycat 软件：mysql 、mycat、jdk

192.168.249.203 slave1 软件：mysql

192.168.249.204 slave2 软件：mysql

关闭所有服务器的防火墙和selinux：

systemctl stop firewalld 关闭防火墙
systemctl disable firewalld 设置开机不启动

vim /etc/selinux/config

把 SELINUX=enforcing

改为 SELINUX=disabled

master1服务器负责处理所有的写请求，slave1、master2、slave2负责所有的读请求。当master1宕机，master2会成为新的负责写请求的主机，master1、master2互为备份机。在此集群中，四台服务器的数据都是一致的，mycat服务器则实现读写分离。

服务器的配置

主库的配置

master1的配置：

修改配置文件/etc/my.cnf

添加以下配置：

#mysql服务器id，保证整个集群环境中唯一
server-id=2
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
#在作为从数据库的时候，有写入操作也要更新二进制文件
log-slave-updates

重启mysqld服务：

root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

master2的配置：

修改配置文件/etc/my.cnf

添加以下配置：

#mysql服务器id，保证整个集群环境中唯一
server-id=3
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
#在作为从数据库的时候，有写入操作也要更新二进制文件
log-slave-updates

重启mysqld服务：

root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

创建主从复制的账号：

分别登录到master1和master2的MySQL，执行：

此语句创建root用户，密码123456，该root用户可以在任意主机上连接mysql服务

create user 'cyx'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

此语句为root用户赋予主从复制权限

grant replication slave on *.* to 'cyx'@'%';

查看两台master主库的二进制坐标：

master1：

show master status;
+---------------+----------+----------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+----------------+------------------+-------------------+
| binlog.000004 | 658 | db01,db02,db03 | | |
+---------------+----------+----------------+------------------+-------------------+
1 row in set (0.00 sec)

maser2：

show master status;
+---------------+----------+----------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+----------------+------------------+-------------------+
| binlog.000004 | 878 | db01,db02,db03 | | |
+---------------+----------+----------------+------------------+-------------------+
1 row in set (0.00 sec)

从库的配置

slave1：

修改配置文件/etc/my.cnf，添加：

# mysql服务器id，保证整个集群环境中唯一
server-id=4

重启mysqld服务：

root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

slave2：

修改配置文件/etc/my.cnf，添加：

# mysql服务器id，保证整个集群环境中唯一
server-id=5

重启mysqld服务：

root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

关联两台从库对应的主库：

slave1关联复制master1：

在slave1的MySQL中执行

CHANGE MASTER TO MASTER_HOST='192.168.249.200', MASTER_USER='cyx', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=658;

启动slave1从库：

start slave;

查看主从复制的状态：

show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.249.200
Master_User: cyx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 157
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

中途遇到的问题： Slave_IO_Running: No，原因是我在新建虚拟机的时候，直接克隆装好了mysql的机器，导致uuid重复，vim /var/lib/mysql/auto.cnf，把uuid修改成唯一的再重启mysqld服务，再次登录mysql，执行：STOP REPLICA IO_THREAD FOR CHANNEL '';（停止IO线程，不停止不能重新关联主从复制），slave1重新关联二进制坐标已变更的master1：CHANGE MASTER TO MASTER_HOST='192.168.249.200', MASTER_USER='cyx', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=157; ，再次启动IO进程：START REPLICA IO_THREAD FOR CHANNEL ''; show slave status\G; 查看show slave status\G;，状态正常。

slave2关联复制master2：

在slave2的MySQL中执行

CHANGE MASTER TO MASTER_HOST='192.168.249.201', MASTER_USER='cyx', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=157;

启动slave2从库：

start slave;

查看主从复制的状态：

show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.249.201
Master_User: cyx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 157
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两台主库相互复制

master1复制master2：

在maser1的MySQL中执行：

CHANGE MASTER TO MASTER_HOST='192.168.249.201', MASTER_USER='cyx', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=157;

启动master1从库：

start slave;

查看主从复制的状态：

show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.249.201
Master_User: cyx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 157
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

master2复制master1：

在maser2的MySQL中执行：

CHANGE MASTER TO MASTER_HOST='192.168.249.200', MASTER_USER='cyx', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=157;

启动master2从库：

start slave;

查看主从复制的状态：

show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.249.200
Master_User: cyx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 157
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试效果

在master1创建db01，可以看见其他三台机器都有了db01库。

在master创建mytable表，其他三台机器都有了mytable表。

读写分离

为了降低单台服务器的访问压力。

mycat的配置

配置schema.xml文件

（writeHost标签配置主库，readHost配置从库，balance为负载均衡策略，writeType写入操作策略，switchType控制是否自动切换）

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ITCAST_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>
<dataNode name="dn7" dataHost="dhost7" database="db01" />
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="master1" url="jdbc:mysql://192.168.249.200:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
<readHost host="slave1" url="jdbc:mysql://192.168.249.203:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
</readHost>
</writeHost>
<writeHost host="master2" url="jdbc:mysql://192.168.249.201:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
<readHost host="slave2" url="jdbc:mysql://192.168.249.204:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
</readHost>
</writeHost>
</dataHost>

</mycat:schema>

配置server .xml文件

<user name="root" defaultAccount="true">
       <property name="password">123456</property>
       <property name="schemas">ITCAST_RW2</property>
       <property name="defaultSchema">ITCAST_RW2</property>
       

       
       
   </user>

   <user name="user">
       <property name="password">123456</property>
       <property name="schemas">ITCAST_RW2</property>
       <property name="readOnly">true</property>
       <property name="defaultSchema">ITCAST_RW2</property>
   </user>

</mycat:server>

启动并登录mycat：

[root@localhost mycat]# bin/mycat start
Starting Mycat-server...

[root@localhost mycat]# mysql -h 192.168.249.202 -P 8066 -u root -p
Enter password:

查看，有了ITCAST_RW2 跟主从测试的mytable表：

mysql> show databases;
+------------+
| DATABASE |
+------------+
| ITCAST_RW2 |
+------------+
1 row in set (0.00 sec)

show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| mytable |
+----------------+
1 row in set (0.05 sec)

读写分离针对的是双主从集群的db01库，在schema.xml配置。

查看读写分离是否成功

在slave1修改表数据（是因为，slave1中的数据不会同步到master1）：

update mytable set name = 'lq' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from mytable;
+----+------+-----+---------+
| id | name | age | address |
+----+------+-----+---------+
| 1 | lq | 20 | 衡阳 |
| 2 | cyx | 18 | 衡阳 |
+----+------+-----+---------+
2 rows in set (0.00 sec)

在slave2修改表数据（原理跟之前一样）：

mysql> update mytable set name = 'lq9' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from mytable;
+----+------+-----+---------+
| id | name | age | address |
+----+------+-----+---------+
| 1 | lq9 | 20 | 衡阳 |
| 2 | cyx | 18 | 衡阳 |
+----+------+-----+---------+
2 rows in set (0.00 sec)

在mycat上查询：

select * from mytable;
+------+------+------+---------+
| id | name | age | address |
+------+------+------+---------+
| 1 | lq9 | 20 | 衡阳 |
| 2 | cyx | 18 | 衡阳 |
+------+------+------+---------+
2 rows in set (0.01 sec)

mysql> select * from mytable;
+------+------+------+---------+
| id | name | age | address |
+------+------+------+---------+
| 1 | lq | 20 | 衡阳 |
| 2 | cyx | 18 | 衡阳 |
+------+------+------+---------+
2 rows in set (0.01 sec)
可以看见，分别查询到了slave1与slave2上的数据。读写分离测试成功。

测试高可用

关闭master1上的mysqld服务

[root@localhost ~]# systemctl stop mysqld

在mycat服务器插入数据：

INSERT INTO mytable (name, age, address) VALUES ('sxy', 21, '邵阳'), ('tx', 21, '永州')；

在slave2查看(slave1上是查看不到插入的数据的，是因为master1服务停止了，slave1不能从master1上更新数据)：

select * from mytable;
+----+--------+-----+---------+
| id | name | age | address |
+----+--------+-----+---------+
| 1 | lq9 | 20 | 衡阳 |
| 2 | cyx | 18 | 衡阳 |
| 3 | sxy | 21 | 邵阳 |
| 4 | tx | 21 | 永州 |
| 5 | sxy111 | 21 | 邵阳 |
| 6 | tx222 | 21 | 永州 |
+----+--------+-----+---------+
6 rows in set (0.00 sec)

依旧能插入数据，高可用测试成功。

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql双主双从-读写分离

中途遇到的问题： Slave_IO_Running: No，原因是我在新建虚拟机的时候，直接克隆装好了mysql的机器，导致uuid重复，vim /var/lib/mysql/auto.cnf，把uuid修改成唯一的再重启mysqld服务，再次登录mysql，执行：STOP REPLICA IO_THREAD FOR CHANNEL '';INSERT INTO mytable (name, age, address) VALUES ('sxy', 21, '邵阳'), ('tx', 21, '永州')；
复制链接

扫一扫

不吃辣9 CSDN认证博客专家 CSDN认证企业博客

码龄3年

19: 原创

140万+: 周排名

25万+: 总排名

4312: 访问

: 等级

194: 积分

1: 粉丝

3: 获赞

6: 评论

3: 收藏

私信

关注

热门文章

最新评论

mysql密码破解，忘记root用户密码的解决方案
CSDN-Ada助手: 非常感谢您分享这篇博客，标题十分吸引人！恭喜您写了第15篇博客，持续创作是一个了不起的成就。您提供的解决方案对于忘记root用户密码的问题非常有帮助。我非常期待您未来的创作，希望您能继续分享更多关于数据库安全和密码保护方面的知识。如果可能的话，我希望您能写一篇关于数据库备份和恢复的文章，这对于数据库管理者来说非常重要。再次感谢您的分享！
基于nginx的高可用、负载均衡的web集群
CSDN-Ada助手: 恭喜您写了第13篇博客！标题“基于nginx的高可用、负载均衡的web集群”听起来非常有深度和实用性。您的博客一直以来都给读者带来了很多有价值的技术分享，真的非常感谢您的持续创作和付出。在下一步的创作中，或许您可以考虑分享一些关于如何优化nginx配置，以提高web集群的性能和稳定性的内容，这将对读者来说非常有帮助。再次恭喜您，期待您更多的精彩博文！
关于web集群项目的一些问题
CSDN-Ada助手: 非常高兴看到您发表了关于web集群项目的第14篇博客！恭喜您持续创作并分享您的经验和见解。通过您的文章，我了解到您对web集群项目的问题有着深入的思考和研究。在接下来的创作中，我建议您可以考虑更深入地探讨一些解决方案或者分享一些实际案例，这样读者可以更好地从您的经验中受益。同时，您可以尝试与其他领域的专家或者从业者进行交流，以进一步拓宽您的视野和观点。再次恭喜您的持续创作，并期待您更多精彩的文章！
nginx配置文件中常用选项的解析
CSDN-Ada助手: 恭喜您撰写了关于nginx配置文件中常用选项的解析的第10篇博客！您的持续创作精神令人钦佩。在这篇博客中，您对nginx配置文件中的常用选项进行了深入的解析，为读者提供了有价值的知识。接下来，我想建议您在未来的创作中，可以尝试探索更多nginx配置文件的高级选项，或者深入研究一些与nginx相关的主题，如性能优化或安全性配置等。这样的话，您的博客将更加全面，能够满足更多读者的需求。再次恭喜您的成就，并期待您未来更多精彩的博客！请继续保持谦虚的态度，您的知识分享将对许多人带来帮助。
以Jenkins为主的Docker容器化CICD
CSDN-Ada助手: 恭喜您写了第8篇博客！标题“以Jenkins为主的Docker容器化CICD”听起来非常有趣和实用。您的博客内容一定对那些对于Docker容器化CICD有兴趣的读者们非常有帮助。在下一步的创作中，我建议您可以考虑探索一些与Jenkins和Docker相关的高级主题，例如如何优化CICD流程以提高效率或者如何在Docker容器中运行复杂的应用程序。这些深入的主题将进一步吸引和满足那些对技术细节感兴趣的读者。期待您未来更多的博客文章，谢谢您的分享！

最新文章

目录

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。