本文介绍了如何配置Mycat数据库中间件，包括server.xml和schema.xml的设置，实现了用户权限分配和读写分离。通过启动Mycat并观察日志确认启动成功，然后在Mycat服务器上进行MySQL登录，创建并测试了读写分离，所有查询操作均分发到从节点执行。
本次实验用到四台机器，具体信息如下：
1)配置server.xml
<user name="root" defaultAccount="true">
<property name="password">mysql</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
配置用户root，密码为mysql，对TESTDB具有权限
2)配置schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="test" />
<dataNode name="dn2" dataHost="localhost1" database="dedecms" />
<dataNode name="dn3" dataHost="localhost1" database="xcredit" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<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="master1" url="192.168.8.57:3306" user="root"
password="mysql">
<!-- can have multi read hosts -->
<readHost host="slave" url="192.168.8.59:3306" user="root" password="mysql" />
</writeHost>
<writeHost host="master2" url="192.168.8.58:3306" user="root"
password="mysql" />
-
schema标签：schema name属性指定逻辑库名，dataNode属性指定下边的dataNode -
dataNode标签：dataHost指定下边的dataHost，database指定具体的database -
dataHost标签：balance指的负载均衡类型，switchType指的是切换的模式
balance指的负载均衡类型，目前的取值有4种：
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
2. balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡。
3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。
4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力
switchType指的是切换的模式，目前的取值也有4种：
1. switchType='-1' 表示不自动切换
2. switchType='1' 默认值，表示自动切换
3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
4. switchType='3'基于MySQL galary cluster的切换机制(适合集群)(1.4.1)，心跳语句为 show status like 'wsrep%'。
3)启动mycat，并观察日志
/usr/local/mycat/bin/mycat start
[root@oneproxy logs]# tail -f wrapper.log
STATUS | wrapper | 2018/08/25 10:32:39 | --> Wrapper Started as Daemon
STATUS | wrapper | 2018/08/25 10:32:40 | Launching a JVM...
INFO | jvm 1 | 2018/08/25 10:32:40 | Wrapper (Version 3.2.3)
INFO | jvm 1 | 2018/08/25 10:32:40 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2018/08/25 10:32:40 |
INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN No appenders could be found for logger (io.mycat.memory.MyCatMemory).
INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN Please initialize the log4j system properly.
INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN See
INFO | jvm 1 | 2018/08/25 10:32:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
4)在mycat服务器登录mysql
mysql -uroot -pmysql -P8066 -h192.168.8.63
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
5)测试读写分离
mysql> use TESTDB;
mysql> create table t4(hostname1 varchar(12),hostname2 varchar(12),hostname3 varchar(12));
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> select * from t4;
+-----------+-----------+-----------+
| hostname1 | hostname2 | hostname3 |
+-----------+-----------+-----------+
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
| master1 | master1 | master1 |
+-----------+-----------+-----------+
64 rows in set (0.00 sec)
插入的数据都是从master1插入的，通过查询mycat日志，发现所有的查询全部分发到了slave节点。
扫一扫
731
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
