一、环境准备
master 192.168.100.10
slave 192.168.100.20
mycat 192.168.100.30
二、搭建主从复制
略
三、mycat安装
1、安装jdk
yum install java-1.8.0-openjdk*
2、下载mycat
wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
3、解压
tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
4、移动到/usr/local下
mv mycat /usr/local/
5、创建专门运行mycat账号
adduser mysql
6、将文件权限赋给mysql账号
groupadd mycat
chown mysql:mycat -R /usr/local/mycat
7、配置环境变量并添加
cat /etc/profile.d/mycat.sh
export MYCAT_HOME=/usr/local/mycat
export PATH=$MYCAT_HOME/bin:$PATH
source /etc/profile
echo $PATH
/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
8、启动mycat并验证
mycat start
netstat -anpt |egrep "9066|8066"
tcp6 0 0 :::9066 :::* LISTEN 7750/java
tcp6 0 0 :::8066 :::* LISTEN 7750/java
四、配置Mycat的读写分离
1、目录结构
tree -L 1 /usr/local/mycat/
/usr/local/mycat/
├── bin 各种命令,如启动
├── catlet
├── conf 配置文件
├── lib
├── logs 日志文件
├── tmlogs
└── version.txt
6 directories, 1 file
[root@db03 ~]#
2、修改连接配置文件
cp /usr/local/mycat/conf/server.xml{,.bak}
cat /usr/local/mycat/conf/server.xml
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
3、修改schema.xml文件
[root@db03 ~]# cat /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'/>
<dataNode name="dn1" dataHost="localhost1" database="full" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.100.10:3306" user="root"
password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.100.20:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
balance指的负载均衡类型,目前的取值有4种:
- balance=”0”, 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
- balance=”1”,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
- balance=”2”,所有读操作都随机的在writeHost、readhost上分发。
- balance=”3”,所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力
注意:balance=3只在1.4及其以后版本有,1.3没有。
writeType属性:
1.writeType=”0”,所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties.
2.writeType=”1”,所有写操作都随机地发送到配置的writeHost,1.5以后废弃不推荐。
switchType指的是切换的模式,目前的取值也有4种:
- switchType=’-1’ 表示不自动切换
- switchType=’1’ 默认值,表示自动切换
- switchType=’2’ 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
- switchType=’3’基于MySQLgalary cluster的切换机制(适合集群)(1.4.1),心跳语句为 show status like ‘wsrep%’。
4、重启服务
[root@db03 ~]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@db03 ~]# netstat -anpt |egrep '8066|9066’
tcp6 0 0 :::9066 :::* LISTEN 9577/java
tcp6 0 0 :::8066 :::* LISTEN 9577/java
五、验证
1、在master和slave上进行授权(允许root用户可以从mycat进行连接)
master:
mysql> grant all on . to ‘root’@‘192.168.100.30’ identified by ‘123456’;
mysql> flush privileges;
mysql> \q;
slave:
mysql> grant all on . to ‘root’@‘192.168.100.30’ identified by ‘123456’;
mysql> flush privileges;
mysql> \q;
2、登录mycat的管理端口
mysql -uroot -p123456 -h192.168.100.30 -P9066
mysql> show @@datasource;
mysql> show @@heartbeat;
mysql> \q
3、通过mycat验证数据的访问
3-1:在master上查看full库中t1表的数据
mysql -uroot -p
mysql> select * from full.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
mysql>
3-2: 在slave上停止主从同步后在查看full库中t1表的数据
mysql -uroot -p
mysql> stop slave;
mysql> select * from full.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
mysql> insert into full.t1 values(5),(6);
mysql> select * from full.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
mysql>
3-3: 登录到mycat的数据端口
mysql -uroot -p123456 -h192.168.100.30 -P8066
MySQL> select * from t1; # 数据为1.2.3.4.5.6,读取内容是slave上
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
MySQL> insert into t1 values(10);
Query OK, 1 row affected (0.11 sec)
MySQL> select * from t1; #slave上的数据
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.01 sec)
MySQL
3-4:在master上再次查看full库中t1表的数据
mysql -uroot -p
mysql> select * from full.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 10 |
+------+
5 rows in set (0.00 sec)
3-5:在slave上开启同步
MyCAT读写分离完成