mysql Cluster and Read-Write Separation Implementation Scheme (Using mycat Middleware)

1.mycat Download

Official website: http://mycat.org.cn/about.html

2. Create a mycat 1.6 directory and upload the mycat service package

tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

3. Configuration of server. XML

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>

	</system>
	<!--Express mycat The login username of--> 
	<user name="root">
        <!--Express mycat The login password of ____________-->
		<property name="password">123456</property>
        <!--Express mycat Logical database name, you can customize-->
		<property name="schemas">ssm,ebus</property>
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">ssm,ebus</property>
		<property name="readOnly">true</property>
	</user>
</mycat:server>

4. Configuration of schema. XML

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="ssm" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
	
	</schema>
	<schema name="ebus" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
	
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="ssm" />
	<dataNode name="dn2" dataHost="localhost1" database="ebus" />

	<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="hostM1" url="10.1.10.83:3306" user="liuwei"
				   password="123456">			
		</writeHost>
		<writeHost host="hostS1" url="10.1.10.84:3316" user="liuwei"
				   password="123456" />
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	
</mycat:schema>

5. Attribute description in schema. XML

Note the following three properties of the dataHost node

balance， switchType， writeType

balance="0", without opening the read-write separation mechanism, all read operations are sent to the currently available writeHost.

balance="1". All readHost and stand by writeHost participate in load balancing of select statements. Simply put, when the dual master-slave mode (M1 - > S1, M2 - > S2, and M1 and M2 are mutually backed up), under normal circumstances, M2,S1,S2 all participate in load balancing of select statements.

balance="2", all reading operations are distributed randomly on writeHost and readhost.

balance="3" and all read requests are randomly distributed to readhost under writeHost, which does not bear reading pressure.

WritteType Represents Write Mode

WteType= "0" and all operations are sent to the first writehost of the configuration

WteType= "1" is randomly sent to all configured writehost s

WteType= "2", no write operation

switchType refers to the switching mode, and there are four currently available values:

switchType='-1'means no automatic switching

switchType='1'default value for automatic switching

switchType='2'determines whether to switch based on the status of MySQL master-slave synchronization. The heartbeat statement is show slave status.

switchType='3'switch mechanism based on MySQL galary cluster (suitable for cluster) (1.4.1), the heartbeat statement is show status like'wsrep%'.

6. Configure MYCAT_HOME

[root@myserver mycat]# vim /etc/profile

export MYCAT_HOME=/usr/soft/mycat1.6/mycat 
export PATH=$PATH:$MYCAT_HOME/bin

[root@myserver mycat]# source /etc/profile

7.mycat startup

[root@interBus-mysql-02 mycat]# pwd
/usr/soft/mycat1.6/mycat
[root@interBus-mysql-02 mycat]# 
Starting Mycat-server...

[root@interBus-mysql-02 mycat1.6]# ps aux | grep mycat
root     18795  0.0  0.0  19124   804 ?        Sl   17:03   0:01 /usr/soft/mycat1.6/mycat/bin/./wrapper-linux-x86-64 /usr/soft/mycat1.6/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/soft/mycat1.6/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root     18797  0.3  8.0 10097828 651120 ?     Sl   17:03   0:10 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/log4j-1.2-api-2.5.jar:lib/libwrapper-linux-x86-32.so:lib/jline-0.9.94.jar:lib/reflectasm-1.03.jar:lib/wrapper.jar:lib/log4j-1.2.17.jar:lib/dom4j-1.6.1.jar:lib/leveldb-0.7.jar:lib/sequoiadb-driver-1.12.jar:lib/Mycat-server-1.6-RELEASE.jar:lib/log4j-slf4j-impl-2.5.jar:lib/hamcrest-core-1.3.jar:lib/commons-collections-3.2.1.jar:lib/disruptor-3.3.4.jar:lib/hamcrest-library-1.3.jar:lib/velocity-1.7.jar:lib/commons-lang-2.6.jar:lib/libwrapper-linux-x86-64.so:lib/asm-4.0.jar:lib/univocity-parsers-2.2.1.jar:lib/leveldb-api-0.7.jar:lib/guava-19.0.jar:lib/druid-1.0.26.jar:lib/log4j-api-2.5.jar:lib/curator-framework-2.11.0.jar:lib/slf4j-api-1.6.1.jar:lib/ehcache-core-2.6.11.jar:lib/mysql-binlog-connector-java-0.4.1.jar:lib/kryo-2.10.jar:lib/mongo-java-driver-2.11.4.jar:lib/netty-3.7.0.Final.jar:lib/libwrapper-linux-ppc-64.so:lib/zookeeper-3.4.6.jar:lib/joda-time-2.9.3.jar:lib/curator-client-2.11.0.jar:lib/log4j-core-2.5.jar:lib/fastjson-1.2.12.jar:lib/mapdb-1.0.7.jar:lib/objenesis-1.2.jar:lib/curator-recipes-2.11.0.jar:lib/jsr305-2.0.3.jar:lib/minlog-1.2.jar -Dwrapper.key=9rd625drkvxzdhaz -Dwrapper.port=32001 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=18795 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start
root     19071  0.0  0.0 103312   876 pts/2    R+   17:59   0:00 grep mycat
[root@interBus-mysql-02 mycat1.6]#

8. Open the firewall settings for the corresponding ports of mycat

vi /etc/sysconfig/iptables

#SerrPort: Define the use port of mycat with a default value of 8066
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT
#ManagerrPort: Define the management port of mycat with the default value of 9066
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9066 -j ACCEPT

Restart the firewall with service iptables restart

9. Connect mycat via navicat

mycat's connection name and password are configured in the server.xml file

1. First log in using root

Add 333 records to the tanyuan table of database ssm and submit successfully

2. Log in using user(readonly) users

user users are read-only users and cannot write to databases

10. Configuration Reference for Implementing Read-Write Separation in Master-Slave Server

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

	<schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		<!-- auto sharding by id (long) -->
	<dataNode name="dn1" dataHost="localhost" database="test" />

	<dataHost name="localhost" 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="hostM1" url="120.77.153.204:3306" user="masterslave"
			password="123456">
			<!-- can have multi read hosts -->
			<!-- <readHost host="hostS2" url="120.77.148.74:3306" user="root" password="root" /> -->
		</writeHost>

		<writeHost host="hostS1" url="120.77.148.74:3306" user="root"
		                        password="root">
		                                                <!-- can have multi read hosts -->
                        <!-- <readHost host="hostS2" url="120.77.148.74:3306" user="root" password="root" /> -->
                </writeHost>
	</dataHost>

</mycat:schema>

Keywords: mycat MySQL xml Database

Added by MatthewJ on Mon, 24 Jun 2019 00:00:35 +0300

Programming VIP