Mycat简单应用-读写分离
mycat的简介这里就不在赘述了,大家可以自行百度,这里只记录详细的过程。
使用mycat的一个架构:
环境:
主机名 | ip | 系统 | 软件版本 | 备注 |
master | 192.168.91.34 | RHEL7.4 | mycat1.6 | mycat服务器 |
node1 | 192.168.91.35 | RHEL7.4 | mysql5.7.26 | MySQL的主 |
node2 | 192.168.91.36 | RHEL7.4 | mysql5.7.26 | mysql的从 |
一,配置数据库的主从同步。
这里就不在赘述了,有问题的xiao伙伴可以之前的博客(https://www.cnblogs.com/winter1519/p/11260401.html)。
二,mycat中间件安装,配置,测试。
Mycat安装
mycat是使用java语言编写的,因此首先需要配置Java环境变量。
[root@master ~]# cd /usr/local/java/
[root@master java]# ll jdk1.8.0_131.zip
-rw-r--r-- 1 root root 188248218 Aug 16 2018 jdk1.8.0_131.zip
[root@master java]# unzip jdk1.8.0_131.zip
[root@master java]# ll
total 183840
drwxr-xr-x 8 root root 255 Feb 7 2018 jdk1.8.0_131
-rw-r--r-- 1 root root 188248218 Aug 16 2018 jdk1.8.0_131.zip
[root@master java]# vim /etc/profile
export JAVA_HOME=/usr/local/java/jdk1.8.0_131
export JRE_HOME=$JAVA_HOME/jre
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=./:JAVA_HOME/lib:$JRE_HOME/lib”
[root@master java]# source /etc/profile
[root@master java]# java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)
[root@master soft]# ll Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
-rw-r--r-- 1 root root 15662280 Jul 29 2019 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@master soft]# tar zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@master ~]# cd /usr/local/mycat/
[root@master mycat]# ll
total 12
drwxr-xr-x 2 root root 190 Jul 21 10:27 bin
drwxrwxrwx 2 root root 6 Mar 1 2016 catlet
drwxrwxrwx 4 root root 4096 Jul 21 11:15 conf
drwxr-xr-x 2 root root 4096 Jul 21 10:27 lib
drwxrwxrwx 2 root root 59 Jul 21 11:18 logs
-rwxrwxrwx 1 root root 217 Oct 28 2016 version.txt
mycat安装完成后,目录如下:
bin mycat命令,启动、重启、停止等
catlet catlet为Mycat的一个扩展功能
conf Mycat 配置信息,重点关注
lib Mycat引用的jar包,Mycat是java开发的
logs 日志文件,包括Mycat启动的日志和运行的日志。
为了使mycat的命令生效可以加入到环境变量中
[root@master mycat]# vim /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
[root@master mycat]# source /etc/profile
Mycat配置:
server.xml文件用来配置连接mycat的用户以及权限
<user name="thunder">
<property name="password">thunder@1234</property>
<property name="schemas">TESTDB</property>
</user>
参数 说明
user 用户配置节点
name 登录的用户名,也就是连接Mycat的用户名。
password 登录的密码,也就是连接Mycat的密码
schemas 数据库名,这里会和schema.xml中的配置关联,多个用逗号分开,例如需要这个用户需要管理两个数据库db1,db2,则配置db1,dbs
schema.xml是最主要的配置项,此文件关联mysql读写分离策略!读写分离、分库分表策略、分片节点都是在此文件中配置的!
MyCat作为中间件,它只是一个代理,本身并不进行数据存储,需要连接后端的MySQL物理服务器,此文件就是用来连接MySQL服务器的!
schemaxml文件中配置的参数解释
参数 说明
schema 数据库设置,此数据库为逻辑数据库,name与server.xml中schema对应
dataNode 分片信息,也就是分库相关配置
dataHost 物理数据库,真正存储数据的数据库
<?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='dtnode'>
</schema>
<dataNode name="dtnode" dataHost="dthost" database="test" />
<dataHost name="dthost" 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="node1" url="192.168.91.35:3306" user="mycat" password="kavl7kAkkle!"> mycat账号是中间件连接数据库的账号,所以的在数据库上建立对应的账号
<!-- can have multi read hosts -->
<readHost host="node2" url="192.168.91.36:3306" user="mycat" password="kavl7kAkkle!">
</readHost>
</writeHost>
<writeHost host="node2" url="192.168.91.36:3306" user="mycat" password="kavl7kAkkle!">
</writeHost>
</dataHost>
</mycat:schema>
[root@node1 ~]# mysql -uroot -pkavl7kAkkle!
mysql> create user mycat@'192.168.91.%' identified by 'kavl7kAkkle!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to mycat@'192.168.91.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mycat测试:
启动mycat
[root@master ~]# mycat start
Starting Mycat-server...
查看日志:
[root@master ~]# cd /usr/local/mycat/logs/
[root@master logs]# ll
total 80
-rw-r--r-- 1 root root 34603 Jul 22 10:49 mycat.log
-rw-r--r-- 1 root root 6 Jul 22 10:48 mycat.pid
-rw-r--r-- 1 root root 38605 Jul 22 10:49 wrapper.log
查看启动的端口
在客户机远程登录Mycat登录mysql(将mycat启动起来后,远程连接,默认端口是8066,逻辑库名和账号密码就是在schema.xml里配置的信息。代码里用jdbc方式连接)
[root@master logs]# mysql -uthunder -pthunder@1234 -P8066 -h 192.168.91.34
mysql: [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 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2019, 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>
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
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 -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| hke |
+----------------+
1 row in set (0.00 sec)
mysql> select * from hke;
+------+--------+
| id | name |
+------+--------+
| 1 | winter |
+------+--------+
1 row in set (0.00 sec)
读写分离测试:
mysql> insert into hke values(2,'lc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from hke;
+------+--------+
| id | name |
+------+--------+
| 1 | winter |
| 2 | lc |
+------+--------+
2 rows in set (0.00 sec)
mysql> delete from hke where id=2;
查看日志:
2020-07-22 11:10:27.565 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341))
- release connection MySQLConnection [id=6, lastTime=1595387427549, user=mycat, schema=test, old shema=test, borrowed=true,
fromSlaveDB=false, threadId=22302, charset=utf8, txIsolation=3, autocommit=true, attachment=dtnode{insert into hke values(2,'lc')},
respHandler=SingleNodeHandler [node=dtnode{insert into hke values(2,'lc')}, packetId=1], host=192.168.91.35, port=3306,
statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
2020-07-22 11:11:16.586 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341)) -
release connection MySQLConnection [id=13, lastTime=1595387476570, user=mycat, schema=test, old shema=test, borrowed=true,
fromSlaveDB=true, threadId=44581, charset=utf8, txIsolation=3, autocommit=true, attachment=dtnode{select * from hke},
respHandler=SingleNodeHandler [node=dtnode{select * from hke}, packetId=6], host=192.168.91.36, port=3306,
statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-07-22 11:17:19.014 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341))
- release connection MySQLConnection [id=2, lastTime=1595387839010, user=mycat, schema=test, old shema=test, borrowed=true,
fromSlaveDB=false, threadId=22305, charset=utf8, txIsolation=3, autocommit=true, attachment=dtnode{delete from hke where id=2},
respHandler=SingleNodeHandler [node=dtnode{delete from hke where id=2}, packetId=1], host=192.168.91.35, port=3306,
statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
从日志中可以看到读的数据分到了192.168.91.36上卖弄,写的数据分到了192.168.91.35上面。
这样就简单实现了读写分离。
更深入内容等着探索,有兴趣的可以一起