MySQL中间件之Mycat读写分离-高可用离实现

  • 基于上篇的环境,或者重新部署一套环境加入如下配置并修改下IP地址、端口号、用户密码参数即可使用。

MySQL中间件之Mycat介绍部署与读写分离实现

0122

配置读写分离高可用:

修改mycat配置文件:(mycat-02节点)
cp /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml_rw
vim /usr/local/mycat/conf/schema.xml
#增加一组可写数据库实例,对应的是mycat-01节点的库,mycat-01与mycat-02的3307实例互为主从,所以mycat-01节点也有olda库的数据
#在mycat-02节点的可写数据库实例组没有宕机时,mycat-01节点的可写数据库实例组是只读功能,当mycat-02数据库宕掉,mycat-01节点的3307实例组就会接管写操作

<?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="olda" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="mycat-02" url="10.0.0.20:3307" user="root" password="redhat">
        <readHost host="mycat-02" url="10.0.0.20:3309" user="root" password="redhat" />
    </writeHost>
    <writeHost host="mycat-01" url="10.0.0.10:3307" user="root" password="redhat">
        <readHost host="mycat-01" url="10.0.0.10:3309" user="root" password="redhat" />
    </writeHost>
    </dataHost>
</mycat:schema>
重启mycat:(mycat-02节点)
mycat restart
查看后端数据库读写节点:
mysql> show @@datasource;
+----------+----------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME     | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+----------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | mycat-02 | mysql | 10.0.0.20 | 3307 | W    |      0 |    1 | 1000 |     419 |         2 |          3 |
| dn1      | mycat-01 | mysql | 10.0.0.10 | 3307 | W    |      0 |    9 | 1000 |    1515 |       463 |          4 |
| dn1      | mycat-02 | mysql | 10.0.0.20 | 3309 | R    |      0 |    9 | 1000 |    1467 |       422 |          0 |
| dn1      | mycat-01 | mysql | 10.0.0.10 | 3309 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
+----------+----------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
测试mycat读写分离:(mycat-02节点)
mysql -uroot -p123456 -h127.0.0.1 -P8066 -e "show variables like 'server_id';"
带有一点负载均衡的功能:(mycat-02节点)

测试可读数据库高可用:(mycat-02节点)

正常状态插入数据

关闭mycat-02节点3307实例(主):(mycat-02节点)

依然可以插入数据,因为mycat-01节点3307数据库实例组已经被调度成写节点了

mycat-01节点查询刚刚插入的数据:(mycat-01节点)
mysql -uroot -p123456 -h10.0.0.20 -P8066 -e "use TESTDB;select * from stu where id='504';"

MySQL-8.0-Mycat版本:

  • 如果是MySQL-8.0版本之后,要在mycat节点的my.cnf配置文件中的 client标签下加入 default-auth=mysql_native_password

  • 或者直接命令行加参数

    mysql -uroot -p123456 -h172.18.1.100 -P8066 --default-auth=mysql_native_password
  • 当前环境为 4个MySQL实例,双主互为主从,还有两个slave从节点。( GTID模式 )

修改mycat配置文件:
[root@db-mycat conf]# cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="course" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
    <dataNode name="dn1" dataHost="node1" database="course" />
    <dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db01" url="172.18.1.76:3306" user="root" password="123456">
        <readHost host="db03" url="172.18.1.78:3306" user="root" password="123456" />
    </writeHost>
    <writeHost host="db02" url="172.18.1.77:3306" user="root" password="123456">
        <readHost host="db04" url="172.18.1.79:3306" user="root" password="123456" />
    </writeHost>
    </dataHost>
</mycat:schema>
查看后端数据库读写节点:
mysql> show @@datasource;
+----------+------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE  | HOST        | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | db01 | mysql | 172.18.1.76 | 3306 | W    |      0 |   10 | 1000 |      23 |         0 |          0 |
| dn1      | db02 | mysql | 172.18.1.77 | 3306 | W    |      0 |    1 | 1000 |      18 |         5 |          0 |
| dn1      | db03 | mysql | 172.18.1.78 | 3306 | R    |      0 |    3 | 1000 |      21 |         5 |          0 |
| dn1      | db04 | mysql | 172.18.1.79 | 3306 | R    |      0 |    4 | 1000 |      24 |         8 |          0 |
+----------+------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
4 rows in set (0.01 sec)
测试mycat读写分离:(mycat-02节点)
  • 当前server_id=1的节点为主节点,server_id=2的master节点是备主库,所以备主库也暂时作为读库使用
  • 因为 balance="1"
mysql -uroot -p123456 -h127.0.0.1 -P8066 -e "show variables like 'server_id';"

「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论