mycat高可用

一、安装并配置java环境
1、安装jdk
tar xvf jdk-8u131-linux-x64.tar.gz
mkdir -p /usr/local/java
mv jdk1.8.0_131 /usr/local/java/
chown -R root:root /usr/local/java
2、配置环境变量
vim /etc/profile
export JAVA_HOME=/usr/local/java/jdk1.8.0_131
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

source /etc/profile
二、mycat的安装

tar vxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat /usr/local
groupadd mycat
useradd -g mycat mycat
passwd mycat
chown -R mycat.mycat /usr/local/mycat

vim /etc/profile
export MYCAT_HOME=/usr/local/mycat
source /etc/profile
三、配置mycat的配置文件

关于配置文件，conf目录下主要以下三个需要熟悉。
server.xml是Mycat服务器参数调整和用户授权的配置文件
schema.xml是逻辑库定义和表以及分片定义的配置文件
rule.xml是分片规则的配置文件

cd /usr/local/mycat/conf
1、vim wrapper.conf
wrapper.java.command=%JAVA_HOME%/bin/java
2、vim server.xml

 <property name="serverPort">8066</property> <property name="managerPort">9066</property>
 <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
 <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>


 <user name="test">
                <property name="password">test</property>
                <property name="schemas">TESTDB</property>
 </user>

3、vim schema.xml

</schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
                <dataNode name="dn1" dataHost="localhost1" database="db1" />
                <dataNode name="dn2" dataHost="localhost1" database="db2" />
                <dataNode name="dn3" dataHost="localhost1" database="db3" />

        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
         <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
        <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
        <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="server1" url="127.0.0.1:3306" user="root"
                                   password="westos">

4、启动mycat
/usr/local/mycat/bin/mycat start

/usr/local/mycat/bin/mycat stop
/usr/local/mycat/bin/mycat restart

四、测试
1、登陆数据库并创建表

mysql> create database db1;
mysql> create database db2;
mysql> create database db3;
create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
insert into travelrecord(id,user_id,traveldate,fee,days)  values(1,'Victor',20160101,100,10);
insert into travelrecord(id,user_id,traveldate,fee,days)  values(5000001,'Job',20160102,100,10);
insert into travelrecord(id,user_id,traveldate,fee,days)  values(10000001,'Slow',20160103,100,10);


mysql> use db1;
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> select * from travelrecord;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | Victor  | 2016-01-01 |  100 |   10 |
+----+---------+------------+------+------+
1 row in set (0.00 sec)
mysql> use db2;
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> select * from travelrecord;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 5000001 | Job     | 2016-01-02 |  100 |   10 |
+---------+---------+------------+------+------+
1 row in set (0.00 sec)
mysql> use db3;
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> select * from travelrecord;
+----------+---------+------------+------+------+
| id       | user_id | traveldate | fee  | days |
+----------+---------+------------+------+------+
| 10000001 | Slow    | 2016-01-03 |  100 |   10 |
+----------+---------+------------+------+------+
1 row in set (0.00 sec)

2、使用test登陆
mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> show tables；
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables；' at line 1
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company          |
| customer         |
| customer_addr    |
| employee         |
| goods            |
| hotnews          |
| orders           |
| order_items      |
| travelrecord     |
+------------------+
9 rows in set (0.00 sec)

mysql> select * from travelrecord;
+----------+---------+------------+------+------+
| id       | user_id | traveldate | fee  | days |
+----------+---------+------------+------+------+
|  5000001 | Job     | 2016-01-02 |  100 |   10 |
| 10000001 | Slow    | 2016-01-03 |  100 |   10 |
|        1 | Victor  | 2016-01-01 |  100 |   10 |
+----------+---------+------------+------+------+
3 rows in set (0.12 sec)

五、总结
1、通过mycat组件可以实现多个数据库的同步，并且只要配置好mycat其他数据库的操作就很简单，减少了配置难度和时间问题。
2、mycat 的server.xml 以及schema.xml 两个文件中的各参数意义很关键，是理解基本的mycat操作的核心。