mycat实现mysql读写分离

搭建前准备：

主：172.25.90.3

从：172.25.90.1

mycat client：172.25.90.2

主从上需要线配置好主从复制

现在mycat的主机上配置jdk:

jdk-7u79-linux-x64.tar.gz

解压到/usr/local/：# tar zxf jdk-7u79-linux-x64.tar.gz /usr/local/

做软链接：# ln -s jdk1.7.0_79/ java

# vim /etc/profile

export JAVA_HOME=/usr/local/java
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$PATH:$JAVA_HOME/bin

# source /etc/profile

配置mycat：

安装包：Mycat-server-1.4-beta-20150604171601-linux.tar.gz

解压到/usr/local:

# tar zxf Mycat-server-1.4-beta-20150604171601-linux.tar.gz -C /usr/local/

添加用户：

# useradd mycat

# passwd mycat

# chown mycat.mycat -R /usr/local/mycat/

添加环境变量：

# vim /etc/profile

export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin

# source /etc/profile

# cd /usr/local/conf

配置：

# vim schema.xml

<?xml version="1.0"?>
        <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
        <mycat:schema xmlns:mycat="http://org.opencloudb/">
        <schema name="pcx_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="defaultDN"></schema>

        <dataNode name="defaultDN" dataHost="dtHost" database="pcx_schema" />

        <dataHost name="dtHost" maxCon="1000" minCon="10" balance="1"
                        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select user()</heartbeat>

                <writeHost host="hostM1" url="172.25.90.3:3306" user="root" password="redhat"></writeHost>
                <writeHost host="hostS1" url="172.25.90.1:3306" user="root" password="redhat" />
        </dataHost>
</mycat:schema>

# vim server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
        </system>
        <user name="root">
                <property name="password">redhat</property>
                <property name="schemas">pcx_schema</property>
        </user>

        <user name="user">
                <property name="password">redhat</property>
                <property name="schemas">pcx_schema</property>
                <property name="readOnly">true</property>
        </user>
</mycat:server>

# vim log4j.xml

  <root>
    <level value="debug" />
    <appender-ref ref="FILE" />
     <!--<appender-ref ref="FILE" />-->
  </root>

启动mycat：

因为之前把环境变量配置到了/etc/profile里了，所以

# mycat console&

读写分离：

在远端登录mycat：

[kiosk@foundation90 Desktop]$ mysql -h172.25.90.2 -P 8066 -u root -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.4-beta-20150604171601 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+------------+
| DATABASE   |
+------------+
| pcx_schema |
+------------+
1 row in set (0.00 sec)

MySQL [(none)]> use pcx_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [pcx_schema]> show tables;
+----------------------+
| Tables_in_pcx_schema |
+----------------------+
| travelrecord         |
+----------------------+
1 row in set (0.01 sec)

MySQL [pcx_schema]> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
|       1 | mysql-server | 2017-06-23 |  100 |   10 |
|       2 | mysql-server | 2017-06-23 |  100 |   10 |
| 5000001 | mysql-server | 2017-06-23 |  100 |   10 |
+---------+--------------+------------+------+------+
3 rows in set (0.01 sec)

MySQL [pcx_schema]> delete from travelrecord where id='1';
Query OK, 1 row affected (0.11 sec)

一开始，主和从是同步的：

mysql> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
|       1 | mysql-server | 2017-06-23 |  100 |   10 |
|       2 | mysql-server | 2017-06-23 |  100 |   10 |
| 5000001 | mysql-server | 2017-06-23 |  100 |   10 |
+---------+--------------+------------+------+------+

现在间从机上的io_thread关闭
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.12 sec)

我们在远端对数据库修改：

MySQL [pcx_schema]> delete from travelrecord where id='1';
Query OK, 1 row affected (0.11 sec)

MySQL [pcx_schema]> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
|       1 | mysql-server | 2017-06-23 |  100 |   10 |
|       2 | mysql-server | 2017-06-23 |  100 |   10 |
| 5000001 | mysql-server | 2017-06-23 |  100 |   10 |
+---------+--------------+------------+------+------+

我们再来看看主和从：

主：

mysql> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
|       2 | mysql-server | 2017-06-23 |  100 |   10 |
| 5000001 | mysql-server | 2017-06-23 |  100 |   10 |
+---------+--------------+------------+------+------+
2 rows in set (0.00 sec)

从：

mysql> select * from travelrecord;
+---------+--------------+------------+------+------+
| id      | user_id      | traveldate | fee  | days |
+---------+--------------+------------+------+------+
|       1 | mysql-server | 2017-06-23 |  100 |   10 |
|       2 | mysql-server | 2017-06-23 |  100 |   10 |
| 5000001 | mysql-server | 2017-06-23 |  100 |   10 |
+---------+--------------+------------+------+------+
3 rows in set (0.00 sec)