欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-08 22:00:28
架构图 一、配置mysql主从 二、安装mycat 1、配置java环境 [root@mysql ~]# tar -xf jdk-8u77-linux-x64.gz [root@mysql ~]...

架构图

基于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读写分离已部署完毕