MySQL数据库运维之读写分离03

头像
夏日寒冰
    阅读 7 分钟
    7
    上篇文章详细介绍了MySQL中间件MyCat的搭建及读写分离操作。在实际场景中，面对大容量的数据，在数据库层面中通常的解决方案有两种，第一种是读写分离，第二种就是数据库的分库和分表了，本篇文章将基于MyCat中间件，详细介绍一下通过MyCat综合实现分库、分表及读写分离操作！微信搜索公众号：”菜鸟封神记“，定期分享一线大厂常用技术干货。

    一、基础回顾

    上两篇文章中已经介绍了Mycat的基本使用，但MyCat的核心工作原理有必要此处再强调一下，利于理解后面其他内容，如下：

    1. 客户端连接MyCat代理，并发送MySQL的操作语句
    2. MyCat拦截用户发送的SQL语句
    3. 拦截到SQL语句之后，会通过内部的一系列解析器完成解析，并做一些特定的分析，比如：分片分析、路由分析、读写分离分析、缓存分析等
    4. 分析完成之后，根据分析出的结果，比如SQL操作需要路由到哪个数据库实例，哪个数据库，哪个表。然后将SQL语句发送到需要路由到的库或者表
    5. 数据库实例执行完SQL操作之后，将结果返回到MyCat代理，MyCat代理对结果做一些适当的处理，然后再将结果返回给发送SQL语句的客户端。至此，一次数据库操作完成！

    二、环境准备

    1、数据库软件

    MySQL5.6.39

    2、MyCat中间件

    Mycat-server-1.6

    3、服务器环境

    CenterOS6.9虚拟机五台

    4、服务器划分

    • 192.168.0.4（第一台主库mysql-server01）
    • 192.168.0.5（第一台从库mysql-server02）
    • 192.168.0.6（MyCat代理服务器mysql-proxy）
    • 192.168.0.7（第二台主库mysql-server03）
    • 192.168.0.8（第二台从库mysql-server04）

    三、分库分表及读写分离配置

    1、安装MySQL数据库，并配置主从复制，安装及配置主从同步请参考之前的文章，有详细讲解，此处不再赘述

    • mysql-server02同步mysql-server01
    • mysql-server04同步mysql-server03

    2、配置mysql-proxy，进入到MyCat的安装目录，然后修改server.xml

    #备份原文件
[root@mysql-proxy ~]# cd /usr/local/mycat/conf/
[root@mysql-proxy conf]# cp server.xml server.xml.bak

    添加如下两个用户，一个为只读用户，一个为读写用户：

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">test_db</property>
                <!--
                <privileges check="false">
                        <schema name="test_db" dml="0110">
                                <table name="student" dml="0000"></table>
                                <table name="t_user" dml="1111"></table>
                        </schema>
                </privileges>
                -->
        </user>
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">test_db</property>
                <property name="readOnly">false</property>
        </user>
</mycat:server>

    3、配置schema.xml配置文件

    #备份原文件
[root@mysql-proxy conf]# cp schema.xml schema.xml.bak
[root@mysql-proxy conf]# vim schema.xml

    写入如下内容：

    <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100">
                <table name="t_user" primaryKey="id" dataNode="wb_node01,wb_node02" rule="sharding-by-mod" />
        </schema>
        <dataNode name="wb_node01" dataHost="wb_host01" database="test_db" />
        <dataNode name="wb_node02" dataHost="wb_host02" database="test_db" />
        <dataHost name="wb_host01" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="mysql-server01" url="192.168.0.4:3306" user="root" password="root">
                        <readHost host="mysql-server02" url="192.168.0.5:3306" user="root" password="root" />
                </writeHost>
                <writeHost host="mysql-server02" url="192.168.0.5:3306" user="root" password="root" />
        </dataHost>
        <dataHost name="wb_host02" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="mysql-server03" url="192.168.0.7:3306" user="root" password="root">
                        <readHost host="mysql-server04" url="192.168.0.8:3306" user="root" password="root" />
                </writeHost>
                <writeHost host="mysql-server04" url="192.168.0.8:3306" user="root" password="root" />
        </dataHost>
</mycat:schema>

    部分内容解释：

    • dataNode节点表示实际的物理节点，此处使用了两组主从，dataNode中的节点分表表示两台从服务器
    • schema节点中的table表示需要拆分的表，rule表示分表策略的名称，与rule.xml中的tableRule节点中的name属性对应

    4、修改rule.xml，配置分表策略

    [root@mysql-proxy conf]# cp rule.xml rule.xml.bak
[root@mysql-proxy conf]# vim rule.xml

    配置分表策略，如下是按照主键ID取模进行分表：

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="sharding-by-mod">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- 表示数据节点的数量 -->
                <property name="count">2</property>
        </function>
</mycat:rule>

    5、启动mycat服务，并插入两条数据

    [root@mysql-proxy conf]# cd /usr/local/mycat/bin/
[root@mysql-proxy bin]# ./mycat start

    使用user用户连接mycat，然后插入两条数据，如下：

    #连接mycat服务
[root@mysql-proxy bin]# mysql -uuser -puser -P8066 -h127.0.0.1

#查看数据库
mysql> show databases;
+----------+
| DATABASE |
+----------+
| test_db  |
+----------+

#在t_user表中插入两条记录
mysql> use test_db;
mysql> insert into t_user(id,name,age,address) values(1,"wangbin",24,"Shan'Xi");
mysql> insert into t_user(id,name,age,address) values(2,"binwang",21,"Bei'Jing");

    注意：上述插入数据时需要自己提供id，可以使用序列的方式或者ID生成器自己生成，此处暂时不介绍，如果不提供ID，会插入失败。
    6、分别登陆mysql-server01和mysql-server03查看插入的数据
    登陆mysql-server01，查看如下：

    [root@mysql-server01 ~]# mysql -uroot -proot -P3306 -h127.0.0.1 test_db
#查数据
root@127.0.0.1 test_db> select * from t_user;
+----+----------+------+---------+
| id | name     | age  | address |
+----+----------+------+---------+
|  2 | binwang |   21 | BeiJing |
+----+----------+------+---------+
1 row in set (0.00 sec)

    登陆mysql-server03，查看如下：

    [root@mysql-server03 ~]# mysql -uroot -proot -h127.0.0.1 -P3306 test_db
#查数据
root@127.0.0.1 test_db> select * from t_user;
+----+----------+------+---------+
| id | name     | age  | address |
+----+----------+------+---------+
|  1 | wangbin |   24 | Shan'Xi |
+----+----------+------+---------+
1 row in set (0.00 sec)

    从上述结果看到，id=1的数据和id=2的数据分别被分到mysql-server01组合mysql-server03组中。至此，基于MyCat的分库分表已经配置完成，读写分离可以通过监控MyCat的实时日志验证，日志位置为：/usr/local/mycat/logs/wrapper.log，可以自行查看，之前文章介绍过，此处不再赘述。
    7、查看节点当前状态

    [root@mysql-proxy bin]# mysql -uuser -puser -P9066 -h127.0.0.1
mysql> show @@datanode;
+-----------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME      | DATHOST           | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+-----------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| wb_node01 | wb_host01/test_db |     0 | mysql |      0 |   10 | 1000 |     140 |          0 |        0 |       0 |            -1 |
| wb_node02 | wb_host02/test_db |     0 | mysql |      0 |   10 | 1000 |     139 |          0 |        0 |       0 |            -1 |
+-----------+-------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

    可以看到，节点目前都处于存活状态。

    以上内容介绍了使用ID取模的方式借助MyCat中间件实现数据库的分库分表及读写分离过程。文章中概念内容介绍的比较少，因为在MyCat的官网上有更详细的概念描述。如有不明白，可下方评论。欢迎转发！

    后续更多文章将更新在个人小站上，欢迎查看。

    另外提供一些优秀的IT视频资料，可免费下载！如需要请查看https://www.592xuexi.com

    mysql
    夏日寒冰
    318 声望86 粉丝

    忠实的技术爱好者，追求极致，喜欢总结一些自己用过的技术点，与他人交流分享。

    « 上一篇
    MySQL数据库运维之读写分离02
    下一篇 »
    MySQL数据库运维之MySQL高可用01

    引用和评论

    推荐阅读
    头像
    面试常问的Spring源码，出maven版本了

    夏日寒冰阅读 596

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 529

    头像
    Mysql梳理1

    cashew赞 1阅读 2.2k

    头像
    MySQL 内存异常增长分析

    zbdba赞 1阅读 466

    头像
    mysql梳理2

    cashew赞 1阅读 1.6k

    头像
    MySQL使用内存的计算方法

    zbdba赞 1阅读 383

    头像
    如何高效使用索引——10个索引优化最佳指南

    Chat2DB阅读 970

    0 条评论
    得票最新