Mycat实现MySQL主从复制和读写分离(双主双从)
java1234
共 6301字,需浏览 13分钟
·
2021-05-25 21:09
点击上方蓝色字体,选择“标星公众号”
优质文章,第一时间送达
1 机器准备
机器名 | ip | 角色 |
wyl01 | 192.168.52.128 | master1 ,mycat |
wyl02 | 192.168.52.129 | slave1 |
wyl03 | 192.168.52.130 | master2 |
wyl04 | 192.168.52.131 | slave2 |
2 mysql双主双从安装
2.1 mysql的安装
2.2 验证
3 mycat的安装
3.1 mycat的配置文件
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">false</property>
</user>
schema.xml配置文件内容
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="test01" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="192.168.52.128:3306" user="root" password="123456">
<readHost host="slave1" url="192.168.52.129:3306" user="root" password="123456" />
</writeHost>
<writeHost host="hostM2" url="192.168.52.130:3306" user="root" password="123456">
<readHost host="slave2" url="192.168.52.131:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
3.2 验证服务
4 验证读写分离
4.1 验证写操作
# 在wyl01 mycat服务器登陆,创建一个表
[root@wyl01 mycat]# mysql -uroot -P8066 -p123456 -h127.0.0.1
MySQL [(none)]> use TESTDB;
Database changed
MySQL [TESTDB]> create table hello (id int not null primary key,hostname varchar(100),date DATE);
Query OK, 0 rows affected (0.02 sec)
MySQL [TESTDB]> insert into hello(id,hostname,date) values(1,@@hostname,20190801);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hello(id,hostname,date) values(2,@@hostname,20190802);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hello(id,hostname,date) values(3,@@hostname,20190803);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into hello(id,hostname,date) values(4,@@hostname,20190804);
Query OK, 1 row affected (0.01 sec)
# 检查一下实际的4台数据库服务中是否有hello的这个表,以及表里是否有数据,在操作正确的情况下,数据都是有的。
# 在wyl01 mycat服务器登陆,创建一个表
[root@wyl01 mycat]# mysql -uroot -P8066 -p123456 -h127.0.0.1
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> select * from hello;
+----+----------+------------+
| id | hostname | date |
+----+----------+------------+
| 1 | wyl03 | 2019-08-01 |
| 2 | wyl03 | 2019-08-02 |
| 3 | wyl03 | 2019-08-03 |
| 4 | wyl03 | 2019-08-04 |
+----+----------+------------+
4 rows in set (0.01 sec)
MySQL [TESTDB]>
4.2 验证读操作
5 主从切换
5.1 故障演示
5.2 恢复master节点
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:
https://blog.csdn.net/wyl9527/article/details/98752020
粉丝福利:Java从入门到入土学习路线图
👇👇👇
👆长按上方微信二维码 2 秒
感谢点赞支持下哈
评论