Read/write splitting/load balancing for mysql databases based on amoeba

For more blog posts, please note: Children without umbrellas must keep running.(Www.xuchanggang.cn)

I. Brief description of Amoeba: [from Baidu encyclopedia]

Amoeba is a proxy that uses MySQL as the underlying data storage and provides MySQL protocol interfaces for applications. It centrally responds to application requests and sends SQL requests to specific databases for execution based on user-defined rules. Based on this, Server Load balancer, read/write splitting, and high availability can be achieved. Compared with MySQL's official MySQL Proxy, the author emphasizes the convenience of amoeba configuration based on XML configuration files, and uses SQLJEP syntax to write rules, which is simpler than MySQL Proxy Based on lua scripts ). Amoeba is equivalent to a router for SQL requests. It aims to provide mechanisms for Server Load balancer, read/write splitting, and high availability, rather than fully implementing them. You need to use MySQL Replication and other mechanisms to implement replica synchronization and other functions. Amoeba also uses a pluggable mechanism for underlying database connection management and routing implementation. Third parties can develop more advanced policies to replace the author's implementation. This program is generally in line with the KISS Principle. Amoeba is currently used in production lines of many enterprises. Ii. Problems solved by Amoeba and current disadvantages: 1. main problems solved: (1 ). integrate complex data sources after data splitting (2 ). provides data sharding rules and reduces the impact of data sharding rules on the database (3 ). reducing database and client connection (4 ). read/write splitting routing 2. disadvantages: (1 ). currently, transactions are not supported (2 ). currently, stored procedures are not supported.) (3 ). it is not suitable for exporting data from amoeba or querying large data volumes. For example, if a request returns more than million data records ). currently, database sharding and table sharding are not supported. Currently, only database sharding instances are supported for amoeba. Each split node must maintain the same database and table structure. 3. mysql database read/write splitting configuration based on Amoeba. 1. test environment, amoeba requirements, software provided, brief schematic: (1 ). test environment: Red Hat 6.3 system amoeba server address: 192.168.1.104 mysql database: 192.168.1.102: 3306 (master- -Rw)/192.168.1.100: 3306 (slave -- r) Note: To facilitate the test of read/write splitting, I do not configure Master/slave here. (2 ). amoeba requirements: Amoeba is developed based on java, so if you want to run Amoeba, you must first install jdk to run (3 ). http://www.oschina.net/p/amoeba amoeba software: http://www.kuaipan.cn/file/id_119710994921422890.htm amoeba Reference Manual: http://docs.hexnova.com/amoeba/ amoeba project code: http://sourceforge.net/projects/amoeba/files/ (4 ). schematic: 650) this. width = 650; "src ="/e/u/themes/default/images/spacer.gif "style =" background: url ("/e/u/lang/zh-cn/images/localimage.png") no-repeat center; border: 1px solid # ddd; "alt =" spacer.gif "/> 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2222302R9-0.jpg "title =" amoeba.jpg "alt =" 222358593.jpg"/> 2. here we place amoeba-related software under the/tmp directory. Here we first configure JDK

# Decompress the JDK software to the/usr/local directory [root @ centos tmp] # tar-xf jdk-7u15-linux-x64.tar.gz-C/usr/local/# enter the corresponding directory, and create the java directory [root @ centos tmp] # cd/usr/local/[root @ centos local] # mkdir java # Move the extracted content to the java directory [root @ centos local] # mv jdk1.7.0 _ 15/* java # Set the java environment variable [root @ centos local] # echo 'export JAVA_HOME =/usr/local/Java'> ~ /. Bashrc [root @ centos local] #. ~ /. Bashrc # test whether jdk is correctly installed [root @ centos local] # java-version #********************** * ******** # Set JDK in the following method [when setting amoeba below, will overwrite, so the configuration file here, refer to the following amoeba configuration] [root @ centos java] # echo $ JAVA_HOME [root @ centos java] # vim/etc/profile # Add export JAVA_HOME =/usr/ logroovy/javaexport PATH = $ JAVA_HOME/bin: $ JAVA_HOME/jre/bin: $ PATH [root @ centos java] # source/etc/profile #************************* ******

3. Configure dbServer. xml in amoeba (backend mysql server connection configuration)

[Root @ centos java] # cd/tmp/# create the corresponding amoeba directory [root @ centos tmp] # mkdir/usr/local/amoeba # decompress the compressed package to the specified directory [root @ centos tmp] # tar-xf amoeba-mysql-binary-2.2.0.tar.gz-C/usr/local/amoeba [root @ centos tmp] # cd/usr/local/amoeba/[root @ centos amoeba] # cd conf /# Prepare to modify the configuration file #/* the following part is the annotation #/* Amoeba has a total of 7 configuration files, they are as follows: #/* Amoeba main configuration file ($ AMOEBA_HOME/conf/amoeba. xml) is used to configure basic parameters of the Amoeba service, such as the Amoeba host address, port, authentication method, user name, password, number of threads, timeout time, and other configurations. Set the file location. #/* Database server configuration file ($ AMOEBA_HOME/conf/dbServers. xml), used to store and configure information about the database server that the Amoeba proxy is used for, such as the host IP address, port, user name, and password. #/* The sharding rule configuration file ($ AMOEBA_HOME/conf/rule. xml) is used to configure the sharding rule. #/* The database function configuration file ($ AMOEBA_HOME/conf/functionMap. xml) is used to configure the database function processing method. Amoeba parses the database function using the method in this configuration file. #/* The configuration file of the sharding rule function ($ AMOEBA_HOME/conf/ruleFunctionMap. xml) is used to configure the processing method of user-defined functions used in the sharding rule. #/* Access rule configuration file ($ AMOEBA_HOME/conf/access_list.conf), used to authorize or prohibit some Server IP addresses from accessing Amoeba. #/* The log specification configuration file ($ AMOEBA_HOME/conf/log4j. xml) is used to configure the log output level and method of Amoeba. # Configure the backend mysql server to connect to [dbServer. xml] [root @ centos conf] # vim dbServers. xml ......................... (Omitted) <dbServer name = "abstractServer" effecactive = "true"> <factoryConfig class = "com.meidusa.amoeba.mysql.net. mysqlServerConnectionFactory "> <property name =" manager ">$ {defamanager manager} </property> <property name =" sendBufferSize "> 64 </property> <property name =" receiveBufferSize "> 128 </property> # Set the mysql database port <! -- Mysql port --> <property name = "port"> 3306 </property> # sets the default database. when connected to amoeba, the operation table must explicitly specify the database name, dbname is used. tablename: # You cannot use dbname to specify the default database, because the operation is scheduled to each backend dbserver. <! -- Mysql schema --> <property name = "schema"> kongzhong </property> # Set the account and password for connecting amoeba to the backend database server. You must create this user on the backend database server, and authorize amoeba connection <! -- Mysql user --> <property name = "user"> kongzhong123 </property> <! -- Mysql password --> <property name = "password"> kongzhong123 </property> </factoryConfig> <poolConfig class = "com.meidusa.amoeba.net. poolable. poolableObjectPool "> # maximum number of connections [Default] <property name =" maxActive "> 500 </property> # maximum number of idle connections [Default] <property name =" maxIdle "> 500 </ property> <property name = "minIdle"> 10 </property> <property name = "minEvictableIdleTimeMillis"> 600000 </property> <property name = "timeBetweenEvic TionRunsMillis "> 600000 </property> <property name =" testOnBorrow "> true </property> <property name =" testOnReturn "> true </property> <property name =" testWhileIdle "> true </property> </poolConfig> </dbServer> # sets a backend dbServer, the name is "master", which can be obtained at will, but in order to clarify its meaning, it is best to give words with special meanings, here is the master db server <dbServer name = "master" parent = "abstractServer"> <factoryConfig> # Set the IP address of this DB server <! -- Mysql ip --> <property name = "ipAddress"> 192.168.1.102 </property> </factoryConfig> </dbServer> # Set a backend mysql database, which is called slave, the name must be unique [N backend databases can be created here, as long as the following <dbServer>... </dbServer>] <dbServer name = "slave" parent = "abstractServer"> <factoryConfig> # Set the IP address of this DB server <! -- Mysql ip --> <property name = "ipAddress"> 192.168.1.100 </property> </factoryConfig> </dbServer> # specify a virtual dbServer, adding the dbserver defined above to this virtual dbserver is equivalent to forming a group [here we will read the database into a group] # Here we need to add name = "mul... "change to the name you want to get. This name also needs to be meaningful. <dbServer name =" ReadPool "virtual =" true "> <poolConfig class =" com. meidusa. amoeba. server. multipleServerPool "> <! -- Load balancing strategy: 1 = ROUNDROBIN, 2 = WEIGHTBASED, 3 = HA --> # select Scheduling Algorithm 1 as round robin 2 as weight 3 as HA here select 1 Round Robin <property name = "loadbalance"> 1 </property> <! -- Separated by commas, such as: server1, server2, server1 --> # Server Load balancer, slave1, and slave2 are used as two servers for scheduling. This is an analog weighted scheduling algorithm. # Note that the dbserver used here must have been defined and can be written multiple times, such as slave1, slave2 <property name = "poolNames"> slave </property> </poolConfig> </dbServer> </amoeba: dbServers>

4. Configure the Amoeba listening port [amoeba. xml]

[Root @ centos conf] # vim amoeba. xml ......................... (Omitted) <proxy> <! -- Service class must implements com. meidusa. amoeba. service. Service --> <service name = "Amoeba for Mysql" class = "com.meidusa.amoeba.net. ServerableConnectionManager"> <! -- Port --> # Set the amoeba listening port (if the default port is used here, you need to specify the port in the subsequent test, which is the port here) <property name = "port"> 3306 </property> <! -- Bind ipAddress --> <! -- # Set the listener interface. If this parameter is not set, listen to all IP addresses [select the default] <property name = "ipAddress"> 127.0.0.1 </property> --> ................ ......... (Omitted) <property name = "authenticator"> <bean class = "com. meidusa. amoeba. mysql. server. mysqlClientAuthenticator "> # Use the account set here to connect the client to amoeba (the account password here is irrelevant to the password used by amoeba to connect to the backend database server) <property name = "user"> kongzhong </property> # Use the password <property name = "password"> kongzhong </property> <prope Rty name = "filter"> <bean class = "com. meidusa. amoeba. server. IPAccessController "> <property name =" ipFile ">$ {amoeba. home}/conf/access_list.conf </property> </bean> </property> </service> .......... ............... (Omitted) <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 </Properties> <property name = "defaultPool"> master </property> <! --> # Remove the comments from the read/write splitting option commented out by default and change the readpool to ReadPool (this name is in front of dbServer. set a read database group in xml, which is used as a read-only pool) <property name = "writePool"> master </property> <property name = "readPool"> ReadPool </property> <property name = "needParse"> true </property> </queryRouter>

5. Configure amoeba and JAVA environment variables

[root@centos conf]# vim /etc/profileJAVA_HOME=/usr/locla/javaAMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin:$JAVA_HOME/bin:$JAVA_HOME/jre/bin[root@centos conf]# source /etc/profile

6. Start amoeba Testing

# If you confirm that the instance can be started properly, add>/dev/null. Otherwise, check the prompt [root @ centos conf] # amoeba start & >>/dev/null # Check the startup status [root @ centos conf]. # netstat-tulnp | grep java # stop amoeba test command availability [root @ centos conf] # amoeba stop

7. Perform the corresponding operations in the preceding settings on the two databases (1). operate on the master DB server

# Create database kongzhong; mysql> use kongzhong # create a table for testing mysql> create table t1 (name varchar (10 )); # insert test data mysql> insert into t1 values ('20140901'); # grant the amoeba user the permission to connect to the database mysql> grant all privileges on kongzhong. * to 'kongzhong123 '@' 192. 168.1.104 'identified by 'kongzhong123 '; mysql> flush privileges;

(2). operate on the slave DB server

# Create database kongzhong; mysql> use kongzhong # create a table for testing mysql> create table t1 (name varchar (10 )); # insert test data mysql> insert into t1 values ('20140901'); # grant the amoeba user the permission to connect to the database mysql> grant all privileges on kongzhong. * to 'kongzhong123 '@' 192. 168.1.104 'identified by 'kongzhong123 '; mysql> flush privileges;

8. log on to any machine for testing. The IP address points to the IP address of the server where amoeba is located.

# Log on to the corresponding database and query the current database (which everyone should understand is not detailed) [root @ client102 ~] # Mysql-ukongzhong-p-h192.168.1.104 [root @ client100 ~] # Mysql-ukongzhong-p-h192.168.1.104 [root @ client104 ~] # Mysql-ukongzhong-p-h192.168.1.104

9. Problems Encountered (1). Error 1:

The stack size specified is too small, Specify at least 160 kError: cocould not create the Java Virtual Machine. error: A fatal exception has occurred. program will exit. solution: [root @ centos bin] # cd/usr/local/amoeba/# modify the configuration file [root @ centos amoeba] # vim bin/amoeba DEFAULT_OPTS = "-server-Xms1024m -Xmx1024m-Xss128k "changed: DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k"

(2). Error 2:

Error: JAVA_HOME environment variable is not set. Solution: [root @ centos local] # echo 'export JAVA_HOME =/usr/local/Java'> ~ /. Bashrc [root @ centos local] #. ~ /. Bashrc

10. Considerations (1). Cancel log generation. Otherwise, the disk may be full.

[Root @ centos conf] # vim log4j. xml [this file is in the conf directory under the amoeba Directory] modify log4j. xml cancels the log file generation, and the disk is easily full.) <param name = "file" value = "$ {amoeba. home}/logs/project. log "/> to <param name =" file "value =" <! [CDATA [$ {amoeba. home}/logs/project. log>/dev/null]> "/>

(2). Performance Optimization

[Root @ centos amoeba] # vim bin/kernel = "-server-Xms256m-Xmx256m-Xss128k" to ult_opts = "-server-Xms512m-Xmx512m-Xmn100m-Xss1204k"

This article from the "no umbrella children must work hard to run" blog, please be sure to keep this source http://634871.blog.51cto.com/624871/1335129