MyCat读写分离、分库分表

一、准备工作

1. 两台内网工作机。我是一台window、一台mac os。两台都装了版本5.7.15的mysql。版本最好一样！

2. 指定mac os主机为master、window主机为slave。在master主机装好mycat，版本5.6.29。我装在当前用户底下的Application/mycat。

    /Users/用户名/Application/mycat

    先备份一下mycat/conf文件夹，防止修改配置出错可以替换回来。

3. 记录下master主机ip:10.0.40.122、slave主机ip:10.0.40.176

二、做mysql的主从复制

1. 先来个参考连接https://www.cnblogs.com/gl-developer/p/6170423.html

2. mac os装的mysql，找不到my.cnf配置文件的解决方法，参考https://blog.csdn.net/jyongchong/article/details/77862819

（顺道提下在window底下mysql的配置文件是my.ini）

3. 对master主机的mysql的配置文件进行操作，进入命令行：

    3.1 找到主数据库的配置文件my.cnf，我的在/etc/my.cnf,在[mysqld]部分插入如下两行：

    [mysqld]

    log-bin=mysql-bin    #开启二进制日志

    server-id=1    #设置server-id

    3.2 重启下master主机的mysql

    创建用于同步的用户账号

    mysql -h127.0.0.1 -uroot -p123456 -P3306 各自的连接用户与密码不一样，按实际的输入

    创建用户并授权：用户：johnson 密码：123456

    mysql>create user 'johnson'@'10.0.40.176' IDENTIFIED BY '123456';    #创建用户

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'johnson'@'10.0.40.176';    #分配权限

    mysql>flush privileges; #刷新权限

    3.3查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

    mysql > SHOW MASTER STATUS;

    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000003 | 73 | test | manual,mysql |
    +------------------+----------+--------------+------------------+

4. 对slave主机的 mysql配置进行修改

   4.1 同样找到my.ini配置文件，添加server-id

   [ mysqld ]

    server-id=2 #设置server-id，必须唯一

    重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

    mysql> CHANGE MASTER TO

     > MASTER_HOST='10.0.40.122',

    > MASTER_USER='johnson'，
    > MASTER_PASSWORD='123456'，
    > MASTER_LOG_FILE='mysql-bin.000003',
    > MASTER_LOG_POS=73;

   4.2 启动slave同步进程：

   mysql>start slave;

   4.3 查看slave状态：

   mysql > show slave status\G;

   *************************** 1 . row ***************************

   Slave_IO_State: Waiting for master to send event

    Master_Host: 10.0.40.122    

    Master_Host: 10.0.40.122

Master_User: johnson

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000013

Read_Master_Log_Pos: 11662

Relay_Log_File: mysqld-relay-bin.000022

Relay_Log_Pos: 11765

Relay_Master_Log_File: mysql-bin.000013

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

    4.4 在master主机mysql建立两个数据库，分别为yii2、yii2-1。这时slave主机的mysql也自动复制了两个相对应的数据库

    4.5 进入slave主机的mysql,创建给mycat连接的用户

    mysql>create user 'jackie'@'10.0.40.122' identified by '123456';

    mysql>grant select,insert,update,delete on *.* to 'jackie'@'10.0.40.122';     #分配权限

    mysql>flush privileges;    #刷新权限

三、对mycat进行配置

1. 把原来mycat/conf/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">

                <!-- auto sharding by id (long) -->

                <table name="tb_user" dataNode="dn1,dn2" rule="mod-long"/>

        </schema>

        <dataNode name="dn1" dataHost="localhost1" database="yii2" />

        <dataNode name="dn2" dataHost="localhost1" database="yii2-1" />

        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>

                <!-- can have multi write hosts -->

                <writeHost host="hostM1" url="127.0.0.1:3306" user="root"

                                   password="123456">

                        <!-- can have multi read hosts -->

                        <readHost host="hostS1" url="10.0.40.176:3306" user="jackie" password="123456" />

                </writeHost>

        <!--    <writeHost host="hostM2" url="10.0.40.176:3306" user="jackie"

                                   password="123456"></writeHost>       -->

        </dataHost>

</mycat:schema>

2. 把原来 mycat/conf/rule.xml 繁杂的代码替换以下代码， 以下相对简单理解

<?xml version="1.0" encoding="UTF-8"?>

<!-- - - Licensed under the Apache License, Version 2.0 (the "License");

        - you may not use this file except in compliance with the License. - You

        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0

        - - Unless required by applicable law or agreed to in writing, software -

        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT

        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the

        License for the specific language governing permissions and - limitations

        under the License. -->

<!DOCTYPE mycat:rule SYSTEM "rule.dtd">

<mycat:rule xmlns:mycat="http://io.mycat/">

        <tableRule name="mod-long">

                <rule>

                        <columns>id</columns>

                        <algorithm>mod-long</algorithm>

                </rule>

        </tableRule>

        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">

        <!--  how many data nodes  -->

                <property name="count">2</property>

        </function>

</mycat:rule>

这个例子是对tb_user的id字段进行取模分表，在schema.xml里tb_user的数据放在两个dataNode里。所以规则函数里<property name="count">2</property>。

3.创建数据表

3.1 目前mycat有两个端口：8066数据端口和9066管理端口，登录方式类似于MySQL的服务端登录。

 查看Application/mycat/conf/server.xml代码，找到

 <user name="root">

 <property name="password"> 123456 </property>

 <property name="schemas"> TESTDB </property>

 这个就是连接的用户root，密码123456，进入mycat能访问到的数据库TESTDB

3.2 启动mycat: Application/mycat/bin/mycat start

     停止mycat: Application/mycat/bin/mycat stop

     重启mycat: Application/mycat/bin/mycat restart

 3.3 打开命令终端进入mycat数据端，mycat是装在master主机上

 mysql -h127.0.0.1 -uroot -p123456 -P8066

caisimingdeMac-mini:~ caisiming$ cd ~

caisimingdeMac-mini:~ caisiming$ Application/mycat/bin/mycat start

Starting Mycat-server...

caisimingdeMac-mini:~ caisiming$ mysql -h127.0.0.1 -uroot -p123456 -P8066

 再新开一个命令终端进入mycat管理端

 mysql -h127.0.0.1 -uroot -p123456 -P9066

3.4 切换到mycat数据端

mysql> use TESTDB;

Database changed

mysql> CREATE TABLE `tb_user` (

    ->   `id` int(4) unsigned NOT NULL AUTO_INCREMENT,

    ->   `user_name` varchar(30) NOT NULL,

    ->   `user_pwd` varchar(50) NOT NULL,

    ->   `phone` int(11) DEFAULT NULL,

    ->   `email` varchar(50) DEFAULT NULL,

    ->   `sex` tinyint(1) NOT NULL DEFAULT '1',

    ->   `into_time` int(10) NOT NULL,

    ->   `status` tinyint(1) NOT NULL DEFAULT '1',

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

到数据库yii2、yii2-1查看，同时生成了一张tb_user表。从库也相应生成表

3.5 插入5条数据

mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(1,'johnson-1','123456','1524896651');

Query OK, 1 row affected (0.07 sec)

mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(2,'johnson-2','123456','1524896651');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(3,'johnson-3','123456','1524896651');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(4,'johnson-4','123456','1524896651');

Query OK, 1 row affected (0.06 sec)

mysql> insert into tb_user(id,user_name,user_pwd,into_time) values(5,'johnson-5','123456','1524896651');

Query OK, 1 row affected (0.01 sec)

看yii2.tb_user有两条数据，yii2-1.tb_user有三条数据。实现分库分表

4. 读写分离，在schema.xml里设置balance="1",所有读操作都发送到了当前的writeHost对应的readhost和备用的writehost上

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>

                <!-- can have multi write hosts -->

                <writeHost host="hostM1" url="127.0.0.1:3306" user="root"

                                   password="123456">

                        <!-- can have multi read hosts -->

                        <readHost host="hostS1" url="10.0.40.176:3306" user="jackie" password="123456" />

                </writeHost>

可以看到readHost的url是连接slave主机上的，所以select操作是对从库进行的。

验证，master上的主库数据不变，对slave上的从库的数据做修改。查询一下数据select * from tb_user,看看数据是不是跟着变

5.进入mycat管理端，可以查查连接状态

caisimingdeMac-mini:~ caisiming$ mysql -h127.0.0.1 -uroot -p123456 -P9066

mysql> show @@heartbeat;

+--------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+

| NAME   | TYPE  | HOST        | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |

+--------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+

| hostM1 | mysql | 127.0.0.1   | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2018-04-28 14:47:59 | false |

| hostS1 | mysql | 10.0.40.176 | 3306 |       1 |     0 | idle   |       0 | 2,2,3        | 2018-04-28 14:47:59 | false |

+--------+-------+-----------

RS_CODE = 1 代表正常状态

= -1 连接出错

= -2 连接超时

= 0 初始化状态