MySQL read-write separation using amoeba for MySQL

Amoeba for MySQL is committed to MySQL's distributed database front-end agent layer, which mainly serves as the query routing function when the application layer accesses MySQL, focusing on the development of distributed database proxies. is located between the client and DB Server (s). Transparent to the client. With load balancing, high availability, query filtering, read/write separation, routing-related query to the target database, multiple database merge results can be requested concurrently. On amoeba you can perform high-availability, load-balancing, and data slicing functions for multiple data sources. Currently in the production line of many enterprises to use.
WWW.CENTOS.BZ here to use amoeba for MySQL to realize the read and write separation of MySQL, to alleviate the pressure of the primary database server, the following is the implementation of the schema diagram:

MySQL master-slave replication configuration

Because of the read and write separation, so one is responsible for the MySQL write operation, the other is responsible for the MySQL read operation, so we here use MySQL's master-slave replication is suitable. For this configuration, please visit:
Https://www.centos.bz/2011/07/linux-mysql-replication-sync-configure/

Java Environment Configuration

The amoeba framework was developed based on Java SE1.5, and it is recommended to use the Java SE 1.5 version. Currently, amoeba is proven to work correctly in JavaTM se 1.5 and Java SE 1.6 (which may include other unverified versions).
Java SE 1.6:http://www.oracle.com/technetwork/java/javase/downloads/jdk-6u32-downloads-1594644.html
I download jdk-6u32-linux-i586-rpm.bin here.
After the download is complete, the SH jdk-6u32-linux-i586-rpm.bin begins installation and will be installed into the/USR/JAVA/JDK1.6.0_32 directory.

Installation of Amoeba

amoeba:http://sourceforge.net/projects/amoeba/
Here are the installation steps:

1. Cd/tmp

2. Mkdir/usr/local/amoeba

3. wget http://softlayer.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz

4. Tar xzf amoeba-mysql-binary-2.1.0-rc5.tar.gz-c/usr/local/amoeba

Configuring User Environment variables

1. VI ~/.bash_profile

Set to:

1. Path= $PATH: $HOME/bin:/usr/local/amoeba/bin

2. Java_home=/usr/java/jdk1.6.0_32

4. Export Java_home

5. Export PATH

Immediate effect:

1. SOURCE ~/.bash_profile

Amoeba for MySQL configuration

Configuring the amoeba for MySQL read/write separation mainly involves two files:
1,/usr/local/amoeba/conf/dbservers.xml
This file defines how the database is connected by the amoeba proxy, such as the most basic: Host IP, port, username and password used by amoeba, and so on.
2,/usr/local/amoeba/conf/amoeba.xml
This file defines the relevant configuration for the Amoeba agent.

Dbservers.xml file Configuration

Abstractserver configuration:

2. <dbserver name= "Abstractserver" abstractive= "true" >

4. <factoryconfig class= "Com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory" >

6. <property name= "Manager" >${defaultManager}</property>

8. <property name= "Sendbuffersize" >64</property>

10. <property name= "Receivebuffersize" >128</property>

14. <!--MySQL Port--

16. <property name= "Port" >3306</property>

20. <!--MySQL Schema--

22. <property name= "schema" >dbname</property>

26. <!--mysql User--

28. <property name= "User" >root</property>

32. <!--mysql password--

34. <property name= "Password" >root</property>

38. </factoryConfig>

42. <poolconfig class= "Com.meidusa.amoeba.net.poolable.PoolableObjectPool" >

44. <property name= "Maxactive" >500</property>

46. <property name= "Maxidle" >500</property>

48. <property name= "Minidle" >10</property>

50. <property name= "Minevictableidletimemillis" >600000</property>

52. <property name= "Timebetweenevictionrunsmillis" >600000</property>

54. <property name= "Testonborrow" >true</property>

56. <property name= "Testwhileidle" >true</property>

58. </poolConfig>

60. </dbServer>

This section defines the port of the real MySQL server, the database name, the MySQL user, and the password.
Master-slave Database definition:

2. <dbserver name= "Master" parent= "Abstractserver" >

4. <factoryConfig>

6. <!--mysql IP--

8. <property name= "IpAddress" >192.168.0.1</property>

10. </factoryConfig>

12. </dbServer>

14. <dbserver name= "Slave1" parent= "Abstractserver" >

16. <factoryConfig>

18. <!--mysql IP--

20. <property name= "IpAddress" >192.168.0.2</property>

22. </factoryConfig>

24. </dbServer>

26. <dbserver name= "Slave2" parent= "Abstractserver" >

28. <factoryConfig>

30. <!--mysql IP--

32. <property name= "IpAddress" >192.168.0.3</property>

34. </factoryConfig>

36. </dbServer>

38. <dbserver name= "Virtualslave" virtual= "true" >

40. <poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >

42. <!--Load balancing Strategy:1=roundrobin, 2=weightbased, 3=ha-->

44. <property name= "LoadBalance" >1</property>

48. <!--separated by Commas,such As:server1,server2,server1--

50. <property name= "Poolnames" >Slave1,Slave2</property>

52. </poolConfig>

54. </dbServer>

This section defines the primary server, from the server, and from the server connection pool. Only the database addresses are defined here, and their users and passwords are the settings in the abstractserver above. Note the user that is used to connect to the real MySQL server must have remote connection permissions.

Amoeba.xml Configuration

Amoeba Connection Verification Configuration:

2. <property name= "Authenticator" >

4. <bean class= "Com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator" >

8. <property name= "User" >root</property>

12. <property name= "Password" >root</property>

16. <property name= "Filter" >

18. <bean class= "Com.meidusa.amoeba.server.IPAccessController" >

20. <property name= "Ipfile" >${amoeba.home}/conf/access_list.conf</property>

22. </bean>

24. </property>

26. </bean>

28. </property>

This defines the user and password used to authenticate when connecting to amoeba.
Read/write Separation configuration:

1. <property name= "Defaultpool" >Master</property>

2. <property name= "Writepool" >Master</property>

3. <property name= "Readpool" >virtualSlave</property>

Defaultpool: The default database node is configured, and some statements except Select\update\insert\delete are executed in Defaultpool.
Writepool: The database Write library is configured, usually with master, as it is configured as a previously defined master database.
Readpool: A database read library is configured, usually with a database pool of slave or slave, as in this case the Virtualslave database pool before it is configured.

Amoeba start

Start command:

1. Amoeba start

This command starts at the foreground, outputs information at startup, checks for no error messages, interrupts, and runs in the background:

1. Amoeba Start &

FAQs

1. Unable to connect properly?
First, based on the information that executes the amoeba start output, exclude the configuration file, then verify that the MySQL user has permission to connect remotely, and then check that the database configuration file for the site is set correctly.
2, how to configure the site database connection file?
The default port should be 8066, the user and password are set in Amoeba.xml.

MySQL read-write separation using amoeba for MySQL