mysql读写分离

jdk下载地址http://7dx.pc6.com/wwb5/jdk7u79linuxx64.tar.gz

mycat下载地址https://github.com/MyCATApache/Mycat-download/blob/master/1.4-RELEASE/Mycat-server-1.4-release-20151019230038-linux.tar.gz

搭建前准备：

主：172.25.67.2

从：172.25.67.3

mycat client：172.25.67.1

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

现在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

[html] view plain copy

1. <?xml version="1.0"?>  
2.         <!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
3.         <mycat:schema xmlns:mycat="http://org.opencloudb/">  
4.         <schema name="pcx_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="defaultDN"></schema>  
5.   
6.         <dataNode name="defaultDN" dataHost="dtHost" database="pcx_schema" />  
7.   
8.         <dataHost name="dtHost" maxCon="1000" minCon="10" balance="1"  
9.                         writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">  
10.   
11.                 <heartbeat>select user()</heartbeat>  
12.   
13.                 <writeHost host="hostM1" url="172.25.67.2:3306" user="root" password="redhat"></writeHost>  
14.                 <writeHost host="hostS1" url="172.25.67.33306" user="root" password="redhat" />  
15.         </dataHost>  
16. </mycat:schema>  

# vim server.xml

[html] view plain copy

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <!DOCTYPE mycat:server SYSTEM "server.dtd">  
3. <mycat:server xmlns:mycat="http://org.opencloudb/">  
4.         <system>  
5.         <property name="defaultSqlParser">druidparser</property>  
6.         </system>  
7.         <user name="root">  
8.                 <property name="password">redhat</property>  
9.                 <property name="schemas">pcx_schema</property>  
10.         </user>  
11.   
12.         <user name="user">  
13.                 <property name="password">redhat</property>  
14.                 <property name="schemas">pcx_schema</property>  
15.                 <property name="readOnly">true</property>  
16.         </user>  
17. </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


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-10.19|  100 |   10 |
|       2 | mysql-server | 2017-10.19 |  100 |   10 |
| 5000001 | mysql-server | 2017-10-19 |  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-10-19|  100 |   10 |
|       2 | mysql-server | |2017-10-19  100 |   10 |
| 5000001 | mysql-server | 2017-10-19 |  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-10-19|  100 |   10 |
|       2 | mysql-server |2017-10-19 |  100 |   10 |
| 5000001 | mysql-server |2017-10-19 |  100 |   10 |
+---------+--------------+------------+------+------+

我们再来看看主和从：

主：

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

从：

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