1、mycat 软件安装
[root@phapxykdgis03 tmp]#groupadd mycat
[root@phapxykdgis03 tmp]#useradd mycat -g mycat
[root@phapxykdgis03 tmp]# tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@phapxykdgis03 tmp]# mv mycat /usr/local/
[root@phapxykdgis03 tmp]#chown -R mycat:mycat mycat
[root@phapxykdgis03 tmp]#cd /usr/local/mycat/conf
[root@phapxykdgis03 tmp]# mkdir backup
[root@phapxykdgis03 tmp]# mv * backup
然后把 rule.xml schema.xml server.xml wrapper.conf 拷贝回/usr/local/mycat/conf 目录
[root@phapxykdgis03 tmp]# ls
-rwxr-xr-x 1 mysql mysql 90 Sep 5 19:10 autopartition-long.txt
drwxr-xr-x 4 mysql mysql 4096 Sep 5 19:09 backup
-rwxr-xr-x 1 mysql mysql 46 Sep 4 17:39 dnindex.properties
-rwxr-xr-x 1 mysql mysql 4794 Sep 5 18:50 rule.xml
-rwxr-xr-x 1 mysql mysql 1191 Sep 5 19:12 schema.xml
-rwxr-xr-x 1 mysql mysql 963 Sep 5 10:16 server.xml
-rwxr-xr-x 1 mysql mysql 4182 Sep 4 16:17 wrapper.conf
--server.xml:是Mycat服务器参数调整和用户授权的配置文件。
--schema.xml:是逻辑库定义和表以及分片定义的配置文件。
--rule.xml: 是分片规则的配置文件,分片规则的具体一些参数信息单独存放为文件,也在这个目录下,配置文件修改需要重启MyCAT。
2、参数文件配置
[root@phapxykdgis03 conf]# vi server.xml
<user name="dba">
<property name="password">dba</property>
<property name="schemas">test</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="test" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="dba">
<property name="password">dba</property>
<property name="schemas">test</property>
<property name="readOnly">true</property>
</user>
[root@phapxykdgis03 conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test" checkSQLschema="true" sqlMaxLimit="100">
<table name="t1" primaryKey="ID" type="global" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="shard" database="db1" />
<dataHost name="shard" 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.17.118.164:3308" user="dba" password="dba">
<readHost host="hostS2" url="172.17.117.31:3306" user="dba" password="dba" />
<readHost host="hostS1" url="172.17.118.163:3307" user="dba" password="dba" />
</writeHost>
</dataHost>
</mycat:schema>
schema.xml schema的值与server.xml中schema的值是多对1的关系,server.xml中schema的值可以是多个、schema.xml schema的值是唯一,可以有多个schema。schema 相对于mysql中的database
dataNode 数据分片。一个dataNode标签就是一个独立的数据分片。
dataHost 物理主机
balance属性
balance负载均衡类型,目前的取值有3种:
(1) balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
(2) balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
(3) balance="2",所有读操作都随机的在writeHost、readhost上分发。
(4) balance="3",所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力,注意balance=3只在1.4及其以后版本有,1.3没有。
writeType属性
负载均衡类型,目前的取值有3种:
1. writeType="0", 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
2. writeType="1",所有写操作都随机的发送到配置的writeHost,1.5以后废弃不推荐。
switchType属性
- -1 表示不自动切换
- 1 默认值,自动切换
- 2 基于MySQL主从同步的状态决定是否切换
- 3 基于MySQL galary cluster的切换机制(适合集群)(1.4.1)心跳语句为 show status like ‘wsrep%’.
读写分离2种配置的区别
配置方式一
<writeHost host="hostM1" url="192.168.33.11:3306" password="123456" user="root"/>
<writeHost host="hostS2" url="192.168.33.12:3306" password="123456" user="root"/>
<writeHost host="hostS3" url="192.168.33.13:3306" password="123456" user="root"/>
配置方式二
<writeHost host="hostM1" url="192.168.33.11:3306" password="123456" user="root">
<readHost host="hostS2" url="192.168.33.12:3306" password="123456" user="root"/>
<readHost host="hostS3" url="192.168.33.13:3306" password="123456" user="root"/>
</writeHost>
在hostM1、hostS1、hostS2均正常工作时,写均在hostM1上。读会有如下区别:
balance 方式一 方式二
0 读–>hostM1 读–>hostM1
1 读–>hostS2 or hostS3 读–>hostS2 or hostS3
2 读–>hostM1 or hostS2 or hostS3 读–>hostM1 or hostS2 or hostS3
3 读–>hostM1 读–>hostS2 or hostS3
启动mycat
/usr/local/mycat/bin/mycat start
关闭mycat
/usr/local/mycat/bin/mycat stop
管理:
[root@phapxykdgis03 conf]# /mysql/mysql5635/bin/mysql -h127.0.0.1 -P9066 -udba -pdba -Dtest
mysql> show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 172.17.118.164 | 3308 | 1 | 0 | idle | 0 | 2,2,2 | 2018-11-12 18:02:56 | false |
| hostS2 | mysql | 172.17.117.31 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2018-11-12 18:02:56 | false |
| hostS1 | mysql | 172.17.118.163 | 3307 | 1 | 0 | idle | 0 | 1,1,1 | 2018-11-12 18:02:56 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.01 sec)
mysql> show @@datasource;
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 172.17.118.164 | 3308 | W | 0 | 10 | 1000 | 97 | 0 | 2 |
| dn1 | hostS2 | mysql | 172.17.117.31 | 3306 | R | 0 | 6 | 1000 | 112 | 21 | 0 |
| dn1 | hostS1 | mysql | 172.17.118.163 | 3307 | R | 0 | 6 | 1000 | 115 | 24 | 0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.01 sec)
测试:
[root@phapxykdgis03 conf]# /mysql/mysql5635/bin/mysql -h127.0.0.1 -P8066 -udba -pdba -Dtest -e 'insert into t1 values(7,"ccc");'
[root@phapxykdgis03 conf]# /mysql/mysql5635/bin/mysql -h127.0.0.1 -P8066 -udba -pdba -Dtest -e 'select @@port'
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3307 |
+--------+
[root@phapxykdgis03 conf]# /mysql/mysql5635/bin/mysql -h127.0.0.1 -P8066 -udba -pdba -Dtest -e 'select @@port'
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3306 |
+--------+