一、环境说明
192.168.10.134 master1,slave2
192.168.10.135 slave1 , master2
192.168.10.136 mycat
二、配置方法
1、配置134、135 主机互为主从,见文章https://editor.csdn.net/md/?articleId=115414687
2、134 授权136 主机的访问读写权限
在主库执行授权信息,从库会自动同步:
grant all on . to “mycat-proxy”@“192.168.10.136”
identified by “cui0116”;
3、mycat安装、并配置 如何读写 134、135
- 安装:
yum install java-1.8.0-openjdk -y
tar xf Mycat-server-1.6.7.1-release-20190627191042- linux.tar.gz
mv mycat /usr/local/mycat
- 配置:
修改mycat的配置文件:server.xml 的user 块,property 定义的是mycat 服务8066 端口的连接用户名、密码,默认的数据库名:
<user name="root" defaultAccount="true">
<property name="password">123456</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>
说明:schema name=“TESTDB” dml=“0110” 指定了逻辑库TESTDB 的权限,即mycat对该库的操作权限
xxxx 权限位说明:
第一位:insert
第二位:update
第三位:select
第四位:delete
修改配置文件 schema.cnf
<?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">
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="jfedu_tb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
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.10.134:3306" user="mycat-proxy"
password="cui0116" />
<writeHost host="hostM2" url="192.168.10.135:3306" user="mycat-proxy"
password="cui0116">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.10.135:3306" user="mycat-proxy" password="cui0116" />
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
</dataHost> -->
</mycat:schema>
说明:
-
<dataNode name=“dn1” dataHost=“localhost1”
database=“jfedu_tb”指定了 逻辑库名TESTDB与真实库名jfedu_tb的关系
-
<dataHost name=“localhost1” maxCon=“1000” minCon=“10” balance=“0” writeType=“0”
指定 jfedu_tb 数据库的读写均衡方式
balance 枚举值:
-
0,表示不进行读写分离,所有的读写都在当前writeHost进行;
-
1,所有读操作都随机发送到当前的writeHost对应的readHost和备用的 writeHost;
-
2,所有的读操作随机在readHost和writeHost上分发;
-
3,所有的select随机分发到writeHost对应的readHost,writeHost不参 与读均衡。
writeType: 写均衡设置
-
0,所有写操作发送到配置的第一个writeHost,主库宕机后切到还生存的第二个writeHost,重新启动后,写操作依然在备用数据库进行,切换记录在配置文件中:dnindex.properties
-
1,所有写操作都随机的发送到配置的writeHost。
< writeHost **>和<readHost **> 指定了写、读数据库的列表
启动mycat
[root@node3 logs]# …/bin/mycat start
三、验证
连接mycat
[root@node3 logs]# mysql -h127.0.0.1 -P 8066 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
MySQL [(none)]> 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 [TESTDB]> show tables;
+--------------------+
| Tables_in_jfedu_tb |
+--------------------+
| jfedu |
+--------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from jfedu ;
+------+----------+
| id | name |
+------+----------+
| 3 | xiaoming |
| 12 | |
| 23 | |
| 11 | xiaohong |
| 11 | xiaohong |
| 232 | sds |
+------+----------+
6 rows in set (0.00 sec)
MySQL [TESTDB]>