Using Amoeba to realize mysql read-write separation

Using amoeba to separate mysql reading and writing

1. What is amoeba?

Amoeba (amoeba) project focuses on the development of distributed database proxy. Located between Client and DB Server(s). Transparent to clients. It has load balancing, high availability, sql filtering, separation of reading and writing, routing related queries to the target database, and concurrent request for consolidation results of multiple databases.

Main solutions:

• reduce the complex multi database structure caused by data segmentation

• provide segmentation rules and reduce the impact of data segmentation rules on applications

• reduce db connections to clients

• read write separation

2. Why use Amoeba

At present, there are several schemes to realize the separation of master-slave reading and writing of mysql:

1. Through program implementation, there are many ready-made codes on the Internet, which are more complex. If you add slave servers, you need to change the codes of multiple servers.

2. It is realized through MySQL proxy. Because the master-slave read-write separation of MySQL proxy is realized through Lua script, at present, the development of lua script can not keep up with the rhythm, and there is no perfect ready-made script. Therefore, it is risky to use it in the production environment. According to many people on the Internet, the performance of MySQL proxy is not high.

3. Develop the interface by yourself. This scheme has high threshold and high development cost, which is not affordable for ordinary small companies.

4. It is implemented by using Alibaba's open source project Amoeba, which has load balancing, high availability, sql filtering, read-write separation, routing related queries to the target database, and very simple installation and configuration. Domestic open source software should be supported. It is currently in use. Don't publish too many conclusions. Publish conclusions after everything is tested. Ha ha!

3. amoeba installation

1. First, install the jdk directly using the rpm package
2. Download the corresponding version of amoeba https://sourceforge.net/projects/amoeba/ , you can decompress it directly
3. Configure amoeba's profile

dbServers.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

		<!-- 
			Each dbServer needs to be configured into a Pool,
			If you need to configure multiple dbServer with load balancing that can be simplified by the following configu
ration:			 add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with n
ame factoryConfig			 such as 'multiPool' dbServer   
		-->
		
	<dbServer name="abstractServer" abstractive="true">
		<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
			<property name="connectionManager">${defaultManager}</property>
			<property name="sendBufferSize">64</property>
			<property name="receiveBufferSize">128</property>
				
			<!-- mysql port -->
			<property name="port">3306</property>
			
			<!-- mysql schema -->
			<property name="schema">msb</property>
			
			<!-- mysql user -->
			<property name="user">root</property>
			
			<property name="password">123456</property>
		</factoryConfig>

		<poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
			<property name="maxActive">500</property>
			<property name="maxIdle">500</property>
			<property name="minIdle">1</property>
			<property name="minEvictableIdleTimeMillis">600000</property>
			<property name="timeBetweenEvictionRunsMillis">600000</property>
			<property name="testOnBorrow">true</property>
			<property name="testOnReturn">true</property>
			<property name="testWhileIdle">true</property>
		</poolConfig>
	</dbServer>

	<dbServer name="writedb"  parent="abstractServer">
		<factoryConfig>
			<!-- mysql ip -->
			<property name="ipAddress">192.168.85.11</property>
		</factoryConfig>
	</dbServer>
	
	<dbServer name="slave"  parent="abstractServer">
		<factoryConfig>
			<!-- mysql ip -->
			<property name="ipAddress">192.168.85.12</property>
		</factoryConfig>
	</dbServer>
	<dbServer name="myslave" virtual="true">
		<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
			<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
			<property name="loadbalance">1</property>
			
			<!-- Separated by commas,such as: server1,server2,server1 -->
			<property name="poolNames">slave</property>
		</poolConfig>
	</dbServer>
</amoeba:dbServers>

amoeba.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

	<proxy>
	
		<!-- service class must implements com.meidusa.amoeba.service.Service -->
		<service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">
			<!-- port -->
			<property name="port">8066</property>
			
			<!-- bind ipAddress -->
			<!-- 
			<property name="ipAddress">127.0.0.1</property>
			 -->
			
			<property name="connectionFactory">
				<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
					<property name="sendBufferSize">128</property>
					<property name="receiveBufferSize">64</property>
				</bean>
			</property>
			
			<property name="authenticateProvider">
				<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
					
					<property name="user">root</property>
					
					<property name="password">123456</property>
					
					<property name="filter">
						<bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
							<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
						</bean>
					</property>
				</bean>
			</property>
			
		</service>
		
		<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
			
			<!-- proxy server client process thread size -->
			<property name="executeThreadSize">128</property>
			
			<!-- per connection cache prepared statement size  -->
			<property name="statementCacheSize">500</property>
			
			<!-- default charset -->
			<property name="serverCharset">utf8</property>
			
			<!-- query timeout( default: 60 second , TimeUnit:second) -->
			<property name="queryTimeout">60</property>
		</runtime>
		
	</proxy>
	
	<!-- 
		Each ConnectionManager will start as thread
		manager responsible for the Connection IO read , Death Detection
	-->
	<connectionManagerList>
		<connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">
			<property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>
		</connectionManager>
	</connectionManagerList>
	
		<!-- default using file loader -->
	<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
		<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
	</dbServerLoader>
	
	<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
		<property name="ruleLoader">
			<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
				<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
				<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
			</bean>
		</property>
		<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
		<property name="LRUMapSize">1500</property>
		<property name="defaultPool">writedb</property>
		
		<property name="writePool">writedb</property>
		<property name="readPool">myslave</property>
		<property name="needParse">true</property>
	</queryRouter>
</amoeba:configuration>
4. Start amoeba
/root/amoeba-mysql-3.0.5-RC/bin/launcher

4. Test amoeba

--Tested sql
--In installation amoeba Log on to your server mysql
mysql -h192.168.85.13 -uroot -p123 -P8066
--Respectively in master,slave,amoeba Login on mysql
use msb
select * from user;
--stay amoeba Insert data on
insert into user values(2,2);
--stay master and slave View the data in the table separately
select * from user;
--take master Upper mysql If the service stops and you continue to insert data, you will find that the insertion is unsuccessful, but you can query
--take master Upper msyql Service on, stop slave Upper mysql,It was found that the insertion was successful, but it could not be queried

Tags: Database MySQL

Posted on Mon, 06 Dec 2021 22:39:12 -0500 by ironmonk3y