MySQL使用Mycat完成高可用和读写分离
大家好，我是anyux。本文介绍MySQL使用Mycat完成高可用和读写分离
Mycat高可用和读写分离
节点关系说明
| 读写主机 | 参数 | server_id | 备注 |
|---|---|---|---|
| writeHost | url="192.168.255.117:3307" | 7 | 写操作(主写节点) |
| readHost | url="192.168.255.117:3309" | 9 |
读操作 |
| writeHost | url="192.168.255.118:3307" | 1187 | 默认读操作(stand by writeHost) |
| readHost | url="192.168.255.118:3309" | 1189 |
读操作 |
当主写节点宕机后，stand by writeHost节点随机准备替换为主写节点
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--以上为xml头文件格式-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.255.117:3307" user="root" password="root">
<readHost host="db2" url="192.168.255.117:3309" user="root" password="root" />
</writeHost>
<writeHost host="db3" url="192.168.255.118:3307" user="root" password="root">
<readHost host="db4" url="192.168.255.118:3309" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
重启mycat
mycat restart
检查mycat
mysql -uroot -p123456 -h 127.0.0.1 -P8066 -e "select @@server_id\Gselect @@server_id\Gselect @@server_id\G;begin;select @@server_id\G;commit;begin;select @@server_id\G;commit;"
关闭主写节点
systemctl stop mysqld3307
检查数据库状态
mysql -uroot -p123456 -h 127.0.0.1 -P8066 -e "select @@server_id\Gselect @@server_id\Gselect @@server_id\G;begin;select @@server_id\G;commit;begin;select @@server_id\G;commit;"
可以看到，写操作切换到server_id：1187，读操作只有server_id:1189。说明旧主写节点宕机后，其对应的主从复制关系已停止，切换到另一个主从复制关系
启动旧写数据库节点,会自动加入到Mycat集群中
systemctl start mysqld3307
检查数据库状态
mysql -uroot -p123456 -h 127.0.0.1 -P8066 -e "select @@server_id\Gselect @@server_id\Gselect @@server_id\G;begin;select @@server_id\G;commit;begin;select @@server_id\G;commit;"
恢复后，旧的写节点变成stand by writeHost