Mycat 读写分离
实验环境
主机名 | IP地址 | 系统 | 软件 | 备注 |
mysql-master | 200.200.2.10 | CentOS7.5 | mysql5.6 | 主库 |
mysql-slave | 200.200.2.11 | CentOS7.5 | mysql5.6 | 从库 |
mycat | 200.200.2.12 | CentOS7.5 | mycat | 中间件 |
数据库主从复制请参考前边的文章进行配置
mycat服务器
安装jdk环境
tar xf jdk-8u60-linux-x64.tar.gz mv jdk1.8.0_60/ /usr/local/java8
配置java环境
vim /etc/profile.d/java.sh cat /etc/profile.d/java.sh export JAVA_HOME=/usr/local/java8 export CLASSPATH=$JAVA_HOME/lib/ export PATH=$PATH:$JAVA_HOME/bin 加载环境变量 source /etc/profile
查看java环境版本
java -version
下载mycat
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
解压源码包
tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz mv mycat/ /usr/local/
创建程序用户
useradd -M -s /sbin/nologin mycat
chwon -R mycat:mycat /usr/local/mycat/
添加mycat命令到环境
vim /etc/profile.d/mycat.sh cat /etc/profile.d/mycat.sh export MYCAT_HOME=/usr/local/mycat export PATH=$PATH:$MYCAT_HOME/bin 加载环境变量 source /etc/profile.d/mycat.sh
更改配置文件
vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="HEADSHOW" checkSQLschema='flase' dataNode='dn1'></schema> 定义一个逻辑库HEADSHOW 节点在dn1 <dataNode name="dn1" dataHost="localhost1" database="abc" /> 映射成为一个真实的库名 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 与上边的localhost进行管理 最大连接数1000 最小连接数10 balance 0是不开始 1是wirehost内部进行随机负载,2是dataNode内部进行随机负载 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> 查看哪些用户 <!-- can have multi write hosts --> <writeHost host="200.200.2.10" url="200.200.2.10:3306" user="root" 写入的数据写入到主库200.200.2.10 3306端口 登陆用户为root用户 password="123456"> 登陆密码为123456 <!-- can have multi read hosts --> <readHost host="200.200.2.11" url="200.200.2.11:3306" user="root" password="123456" /> 读的数据将从从库200.200.2.11中查询 登陆用户为root密码是123456 </writeHost> <writeHost host="hostS1" url="localhost:3316" user="root" 备用 password="123456" /> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> <!-- <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost> </dataHost> <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat> <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql> <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost> <dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost> <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </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>
vim /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="useSqlStat">1</property> <property name="useGlobleTableCheck">0</property> <property name="sequnceHandlerType">2</property> <property name="processorBufferPoolType">0</property> <property name="useHandshakeV10">1</property> <property name="useSqlStat">0</property> <property name="useGlobleTableCheck">0</property> <property name="serverPort">3310</property> 端口号 <property name="managerPort">9066</property> 端口号 <property name="maxStringLiteralLength">65535</property> <property name="sequnceHandlerType">0</property> <property name="backSocketNoDelay">1</property> <property name="frontSocketNoDelay">1</property> <property name="processorExecutor">16</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">1</property> <!-- 单位为m --> <property name="memoryPageSize">1m</property> <!-- 单位为k --> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <!-- 单位为m --> <property name="systemReserveMemorySize">384m</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">true</property> </system> <!-- 全局SQL防火墙设置 --> <!-- <firewall> <whitehost> <host host="127.0.0.1" user="mycat"/> <host host="127.0.0.2" user="mycat"/> </whitehost> <blacklist check="false"> </blacklist> </firewall> --> <user name="root"> <property name="password">123456</property> <property name="schemas">HEADSHOW</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> <user name="mycat"> <property name="password">123456</property> <property name="schemas">HEADSHOW</property> <property name="readOnly">true</property> </user> </mycat:server>
启动mycat服务
mycat start
下载mariadb,访问测试
yum -y install mariadb mysql -uroot -p123456 -h 127.0.0.1 -P9066
测试方法
将主从复制关闭,在mycat中插入数据
查看主库中是否存在数据,mycat查询数据是否存在
主库中存在数据则证明通过mycat写入功能正常开启
mycat中查询不到数据则mycat读取是从从服务器上进行读取