不講原理，直接上步驟：
1.下載MyCat，Mycat-server-1.4-release-20151019230038-linux.tar
2. 解壓到/usr/mycat目錄下：
[root@localhost mycat]# tar -xvf Mycat-server-1.4-release-20151019230038-linux.tar .
3.修改MyCat的配置文件 /usr/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="user" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile"/>
</schema>
<dataNode name="dn1" dataHost="master" database="db1" />
<dataNode name="dn2" dataHost="master" database="db2" />
<dataHost name="master" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"
switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="192.168.8.132:3306" user="root" password="root">
<readHost host="hostS1" url="192.168.8.130:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
注意，修改balance=1,否則不支持讀寫(xiě)分離。
由于MySQL的root用戶，默認(rèn)只支持localhost或者127.0.0.1訪問(wèn)，通過(guò)IP地址無(wú)法訪問(wèn)，需要修改，修改方法如下：
[root@localhost mycat]# mysql -u root -p
mysql> use mysql;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.07 sec)
在主從服務(wù)器都要運(yùn)行以上命令。
4.修改rule.xml
注意：role字段只能取值0和1
1 <tableRule name="sharding-by-intfile">
2 <rule>
3 <columns>role</columns>
4 <algorithm>hash-int</algorithm>
5 </rule>
6 </tableRule>
5.修改配置文件conf/partition-hash-int.txt
當(dāng)role取值為0時(shí)，路由到數(shù)據(jù)節(jié)點(diǎn)1（dn1）
當(dāng)role取值為1時(shí)，路由到數(shù)據(jù)節(jié)點(diǎn)2（dn2）
6.修改主從復(fù)制環(huán)境
在 http://www.aygfsteel.com/jacky9881/archive/2016/02/23/429403.html 搭建主從復(fù)制的環(huán)境做些修改。
在主服務(wù)器上新建數(shù)據(jù)庫(kù)db1和db2
[root@localhost mycat]# mysql -u root -p
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
修改 my.cnf文件
root@localhost mycat]#vi /etc/my.cnf
binlog-do-db=db1
binlog-do-db=db2
重啟MySQL服務(wù)器
[root@localhost mycat]# service mysqld restart
查看主服務(wù)狀態(tài)
[root@localhost mycat]# mysql -u root -p
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 106 | db1,db2 | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
記住File和Position的值。
修改從服務(wù)器
在從服務(wù)器上新建數(shù)據(jù)庫(kù)db1和db2
[root@localhost mycat]# mysql -u root -p
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.130', MASTER_PORT=3306,
MASTER_USER='admin', MASTER_PASSWORD='admin',MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=106;
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
確認(rèn)以下信息輸出：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
至此，主從復(fù)制環(huán)境修改成功。
7.結(jié)果確認(rèn)
在主服務(wù)器上，重啟Mycat服務(wù)器，然后連接Mycat服務(wù)器。
[root@localhost mycat]# ./bin/mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
[root@localhost mycat]#mysql -utest -ptest -h192.168.8.132 -P8066 -DTESTDB
mysql> create table `user` (`id` int not null auto_increment,`name` varchar (60),`password` varchar (20),`role` int not null,`email` varchar (30),`alertday` int,primary key (`id`));
Query OK, 0 rows affected (0.47 sec)
運(yùn)行成功，去從服務(wù)器的db1和db2，可以看到表已經(jīng)被創(chuàng)建了，說(shuō)明主從復(fù)制正常。
mysql> use db1;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user |
+---------------+
1 row in set (0.10 sec)
mysql> use db2;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| user |
+---------------+
1 row in set (0.00 sec)
在主服務(wù)器上運(yùn)行以下命令：
mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',0,'xxxx@xxx.com',30);
Query OK, 1 row affected (0.24 sec)
mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',1,'xxxx@xxx.com',30);
Query OK, 1 row affected (0.00 sec)
確認(rèn)分片規(guī)則有沒(méi)有起作用。
在從服務(wù)器上運(yùn)行如下命令，根據(jù)結(jié)果可以看出，role為0的記錄被路由到db1中，role為1的記錄被路由到db2中。
mysql> use db1;
Database changed
mysql> select * from user;
+----+-------+----------+------+--------------+----------+
| id | name | password | role | email | alertday |
+----+-------+----------+------+--------------+----------+
| 1 | admin | admin | 0 | xxxx@xxx.com | 30 |
+----+-------+----------+------+--------------+----------+
1 row in set (0.00 sec)
mysql> use db2;
Database changed
mysql> select * from user;
+----+-------+----------+------+--------------+----------+
| id | name | password | role | email | alertday |
+----+-------+----------+------+--------------+----------+
| 1 | admin | admin | 1 | xxxx@xxx.com | 30 |
+----+-------+----------+------+--------------+----------+
1 row in set (0.00 sec)
最后再確認(rèn)一下讀寫(xiě)分離有沒(méi)有真的實(shí)現(xiàn)。
在主服務(wù)上運(yùn)行
mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',1,'xxxx@xxx.com',30);
mysql> select * from user;
查看一下log文件
修改conf/log4j.xml, INFO=>DEBUG
02/29 03:48:05.414 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1456746485403, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=true, threadId=1857, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.8.130, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]