基于mycat配置MySQL读写分离的详细教程

架构图

一、配置mysql主从

二、安装mycat

1、配置java环境

[root@mysql ~]# tar -xf jdk-8u77-linux-x64.gz

[root@mysql ~]#mkdir /usr/local/java

[root@mysql ~]#mv jdk1.8.0_77/ /usr/local/java/

[root@mysql ~]#vim /etc/profile

export java_home=/usr/local/java/jdk1.8.0_77

export jre_home=$java_home/jre

export classpath=.:$java_home/lib/dt.jar:$java_home/lib/tools.jar:$jre_home/lib/rt.jar

export path=$path:$java_home/bin:$jre_home/bin

[root@mysql ~]#source /etc/profile

[root@mysql ~]# java -version

java version "1.8.0_77"

java(tm) se runtime environment (build 1.8.0_77-b03)

java hotspot(tm) 64-bit server vm (build 25.77-b03, mixed mode)

说明此时java环境已经部署成功

2、安装mycat

下载mycat包并安装(https://dl.mycat.io/1.6-release/mycat-server-1.6-release-20161028204710-linux.tar.gz)

[root@mysql ~]# wget https://dl.mycat.io/1.6-release/mycat-server-1.6-release-20161028204710-linux.tar.gz

[root@mysql ~]# tar -xf mycat-server-1.6-release-20161028204710-linux.tar.gz

[root@mysql ~]# mv mycat/ /usr/local/

[root@mysql mycat]# cd /usr/local/mycat/

[root@mysql mycat]# vim /etc/profile

export mycat_home=/usr/local/mycat

export path=$path:$mycat_home/bin

[root@mysql mycat]# source /etc/profile

此时安装完毕

3、配置mycat

[root@mysql mycat]# cd conf/

修改schema.xml文件

[root@mysql mycat]# vimschema.xml

一下备注部分为分表，因目前没需要所以没有对分表进行配置

writetype="0" dbtype="mysql" dbdriver="native" switchtype="1" slavethreshold="100">

select user();

修改server.xml[root@mysql conf]# vim server.xml

0

123456

testdb

456

testdb

true

4、启动mycat，并查看是否启动[root@mysql conf]# /usr/local/mycat/bin/mycat start

[root@mysql conf]# netstat -anptu |grep java

tcp 0 0 127.0.0.1:32000 0.0.0.0:* listen 3063/java

tcp6 0 0 :::42155 :::* listen 3063/java

tcp6 0 0 :::34457 :::* listen 3063/java

tcp6 0 0 :::1984 :::* listen 3063/java

tcp6 0 0 :::8066 :::* listen 3063/java

tcp6 0 0 :::9066 :::* listen 3063/java

tcp6 0 0 192.168.1.76:36158 192.168.1.127:3306 established 3063/java

tcp6 0 0 192.168.1.76:35258 192.168.1.128:3306 established 3063/java

设置验证环境并验证

(1)、登陆mycat，建表并插入数据

[root@localhost ~]# mysql -uroot -h192.168.1.76 -p8066 -p

enter password:

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 4

server version: 5.6.29-mycat-1.6-release-20161028204710 mycat server (openclounddb)copyright (c) 2000, 2018, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or its

affiliates. other names may be trademarks of their respective

owners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> use testdb;

reading table information for completion of table and column names

you can turn off this feature to get a quicker startup with -adatabase changedmysql> create table company(id int not null primary key,name varchar(50),addr varchar(255));query ok, 0 rows affected (0.01 sec)

mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(2000002, 'alex', '2018-06-08',500.0,3);

query ok, 1 row affected (0.42 sec)

mysql> select * from travelrecord;

+---------+---------+------------+------+------+

| id | user_id | traveldate | fee | days |

+---------+---------+------------+------+------+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+---------+---------+------------+------+------+

3 rows in set (0.01 sec)

(2)、登陆主服务器并验证刚插入的数据

[root@localhost ~]# mysql -uroot -p123456

warning: using a password on the command line interface can be insecure.

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 42

server version: 5.6.39-log source distributioncopyright (c) 2000, 2018, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or its

affiliates. other names may be trademarks of their respective

owners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.reading table information for completion of table and column names

you can turn off this feature to get a quicker startup with -adatabase changed mysql> select * from travelrecord;

+---------+---------+------------+------+------+

| id | user_id | traveldate | fee | days |

+---------+---------+------------+------+------+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+---------+---------+------------+------+------+

1 rows in set (0.00 sec)mysql> flush privileges;

query ok, 0 rows affected (0.01 sec)同理登陆从服务器并在从服务器上插入数据以便验证读写分离

[root@localhost ~]# mysql -uroot -p123456

warning: using a password on the command line interface can be insecure.

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 36

server version: 5.6.39-log source distributioncopyright (c) 2000, 2018, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or its

affiliates. other names may be trademarks of their respective

owners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> use liuys;

reading table information for completion of table and column names

you can turn off this feature to get a quicker startup with -adatabase changed

mysql> select * from travelrecord;

+---------+---------+------------+------+------+

| id | user_id | traveldate | fee | days |

+---------+---------+------------+------+------+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+---------+---------+------------+------+------+

1 rows in set (0.00 sec)mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(2000001, 'alice', '2017-08-08',500.0,3);

query ok, 1 row affected (0.42 sec)

mysql> select * from travelrecord;

+---------+---------+------------+------+------+

| id | user_id | traveldate | fee | days |

+---------+---------+------------+------+------+

| 2000001 | alice | 2017-08-08 | 500 | 3 |

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+---------+---------+------------+------+------+

回到主服务器查看时，肯定无法查到该条数据

mysql> select * from travelrecord;

+---------+---------+------------+------+------+

| id | user_id | traveldate | fee | days |

+---------+---------+------------+------+------+

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+---------+---------+------------+------+------+

1 rows in set (0.00 sec)

当我们回到mycat服务器时，又可以看到数据

[root@localhost ~]# mysql -uroot -h192.168.1.76 -p8066 -p

enter password:

welcome to the mysql monitor. commands end with ; or \g.

your mysql connection id is 4

server version: 5.6.29-mycat-1.6-release-20161028204710 mycat server (openclounddb)copyright (c) 2000, 2018, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or its

affiliates. other names may be trademarks of their respective

owners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> use testdb;

reading table information for completion of table and column names

you can turn off this feature to get a quicker startup with -adatabase changed

mysql> select * from travelrecord;

+---------+---------+------------+------+------+

| id | user_id | traveldate | fee | days |

+---------+---------+------------+------+------+

| 2000001 | alice | 2017-08-08 | 500 | 3 |

| 2000002 | alex | 2018-06-08 | 500 | 3 |

+---------+---------+------------+------+------+

3 rows in set (0.01 sec)

至此mycat读写分离已部署完毕