Mysql mycat读写分离
Mysql mycat读写分离:
下载网址:http://dl.mycat.io
Mycat官方网址:http://mycat.org.cn
说明:
10.0.1.2为mycat服务器,10.0.1.3、10.0.1.4为mysql服务器
yum install java-1.8.0-openjdk
tar zxfv Mycat-server-1.6.7.5-test-20200109231555-linux.tar.gz -C /space/
配置读写分离:
vi /space/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
<table name="travelrecord,address" dataNode="dn1" rule="auto-sharding-long" splitTableNames ="true"/>
</schema>
<dataNode name="dn1" dataHost="10.0.1.3" database="ming" />
<dataHost name="10.0.1.3" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="10.0.1.3:3306" user="ming"
password="123@abAB">
<readHost host="hostS1" url="10.0.1.4:3306" user="ming"
password="xxxxxx"/>
</writeHost>
</dataHost>
</mycat:schema>
:wq
vi /space/mycat/conf/server.xml(主要改user部分,其它不动)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property>
<property name="ignoreUnknownCommand">0</property>
<property name="useHandshakeV10">1</property>
<property name="removeGraveAccent">1</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sqlExecuteTimeout">300</property>
<property name="sequnceHandlerType">1</property>
<property name="sequnceHandlerPattern">(?:(\snext\s+value\s+for\sMYCATSEQ_(\w+))(,|)|\s)*)+</property>
<property name="subqueryRelationshipCheck">false</property>
<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">0</property>
<property name="memoryPageSize">64k</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">false</property>
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
<property name="parallExecute">0</property>
</system>
<user name="ming" defaultAccount="true">
<property name="password">xxxxxx</property>
<property name="schemas">ming</property>
<property name="defaultSchema">ming</property>
</user>
</mycat:server>
:wq
vi /space/mycat/conf/rule.xml
<!-- how many data nodes -->
<property name="count">1</property>
:wq
vi /space/mycat/conf/autopartition-long.txt
0-500M=0
#500M-1000M=1
#1000M-1500M=2
:wq
启动服务:
/space/mycat/bin/mycat start
查看log是否有报错(有的话根据报错提示处理):
cat /space/mycat/logs/wrapper.log
查看端口:
netstat -nplt | grep 8066 (8066是数据端口)
netstat -nplt | grep 9066 (9066是管理端口)
管理命令:
mysql -h 10.0.1.2 -u ming -p'xxxxx' -P9066
show @@help;
show @@version;
show @@datanode;
show @@heartbeat;
show @@datasource;
通过show @@datasource里不同mysql服务器的READ_LOAD和WRITE_LOAD次数可以看出是否有读写分离
读写: