MySQL dual-machine hot standby +amoeba based on master-slave replication for read-write separation and load balancing

Read and write separation refers to the client can only write on the primary server, only read from the server, of course, it is also to see the configuration, you can configure the Read function on the primary server, but only read from the server can not write, because from the server is based on Binlog to the primary server replication, if it is written from the server, Will cause the Lord to never agree on a result. MySQL realizes read and write separation, although the pressure of writing is not reduced, but the reading pressure is reduced by half in an instant.

Amoeba's Chinese meaning is amoeba, variant insect
Amoeba is a proxy server that uses MySQL as the underlying data store and provides the MySQL protocol interface to the application. It responds centrally to the application's requests, sending SQL requests to a specific database based on the rules that the user has set beforehand. This allows for load balancing, read-write separation , and high availability. Compared to MySQL's official MySQL proxy, the author emphasizes the convenience of the amoeba configuration (XML-based configuration file, which uses the SQLJEP syntax to write rules that are simpler than the LUA script -based MySQL proxy).
The amoeba is equivalent to a SQL-requested router that provides mechanisms for load balancing, read-write separation, and high availability, rather than fully implementing them. Users need to use a combination of MySQL replication and other mechanisms to achieve the functions such as replica synchronization. Amoeba also uses pluggable mechanisms for the underlying database connection management and routing implementations, and third parties can develop more advanced policy classes to replace the authors ' implementations. This procedure is generally more in line with the idea of the kiss principle .

1. Introduction to the Environment

master--ip:192.168.1.135 (master server)
Slave---ip:192.168.1.136 (from server)
amoeba--ip:192.168.1.137 (proxy server)
client--ip:192.168.1.138 (client)

2. Installing the JDK

#mkdir/amoeba
#tar –XVF jdk-7u40-linux-x64.tar.gz
# Vim/etc/profile
Java_home=/amoeba/jdk1.7.0_40
Export Java_home

Path= $JAVA _home/bin: $PATH
Export PATH

Classpath=.: $JAVA _home/lib/tools.jar: $JAVA _home/lib/dt.jar: $CLASSPATH
Export CLASSPATH

3, Installation Amoeba

#:mkdir/usr/local/amoeba
#:unzip amoeba-mysql-1.3.1-beta.zip-d/usr/local/amoeba
#:chmod-r +x/usr/local/amoeba/bin/
# java-version Java Version "1.7.0_40" Java (TM) SE Runtime Environment (build 1.7.0_40-b43)
Java HotSpot (TM) 64-bit Server VM (build 24.0-b56, Mixed mode)

4, the principal and subordinate authorization, increase the user

Mysql-->grant all privileges on * * to [e-mail protected] '% ' identified by ' amoeba123 ';
Mysql-->flush privileges;

===================================================================================5, Amoeba-related configuration

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

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

<!--
Amoeba Open Interface Related configuration
-
<server>
<!--proxy server bound Port--
<property name= "Port" >9006</property>

<!--proxy server bound IP--

<property name= "IpAddress" >192.168.1.137</property>

<!--proxy server Net IO Read thread Size--
<property name= "Readthreadpoolsize" >20</property>

<!--proxy Server client process thread size--
<property name= "Clientsidethreadpoolsize" >30</property>

<!--MySQL server data packet process thread size--
<property name= "Serversidethreadpoolsize" >30</property>

<!--socket Send and receive buffersize (UNIT:K)--
<property name= "Netbuffersize" >128</property>

<!--enable/disable Tcp_nodelay (disable/enable Nagle ' s algorithm). -
<property name= "Tcpnodelay" >true</property>

<!--Externally authenticated user name--
<property name= "User" >root</property>

<!--Externally authenticated passwords--

<property name= "Password" >aixocm</property>


<!--query timeout (default:60 second, timeunit:second)--
<property name= "QueryTimeout" >60</property>
</server>

<!--
Each ConnectionManager is started as a thread.
Manager is responsible for connection IO read/write/death detection
-
<connectionManagerList>
<connectionmanager name= "Defaultmanager" class= "Com.meidusa.amoeba.net.MultiConnectionManagerWrapper" >
<property name= "Submanagerclassname" >com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!--
Default value is avaliable processors
<property name= "Processors" >5</property>
-
</connectionManager>
</connectionManagerList>

<dbServerList>
<!--
A mysqlserver needs to be configured with a pool,
If more than one equal MySQL needs to be loadbalance,
The platform has already provided a load balancing capability of the ObjectPool:com.meidusa.amoeba.mysql.server.MultipleServerPool
The simple configuration is the attribute plus virtual= "true", which the pool does not allow to configure Factoryconfig
or write a objectpool yourself.
-



<!--
Master Licensing Related Settings
-
<dbserver name= "Server1" >

<!--Poolableobjectfactory Implementation class--
<factoryconfig class= "Com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory" >
<property name= "Manager" >defaultManager</property>

<!--real MySQL database Port--
<property name= "Port" >3306</property>

<!--real MySQL database IP--
<property name= "IpAddress" >192.168.1.135</property>
<property name= "schema" >test</property>

<!--user name to log in to MySQL--
<property name= "User" >amoeba</property>

<!--password to log in to MySQL--
<property name= "Password" >amoeba</property>


</factoryConfig>

<!--Objectpool Implementation class--
<poolconfig class= "Com.meidusa.amoeba.net.poolable.PoolableObjectPool" >
<property name= "Maxactive" >200</property>
<property name= "Maxidle" >200</property>
<property name= "Minidle" >10</property>
<property name= "Minevictableidletimemillis" >600000</property>
<property name= "Timebetweenevictionrunsmillis" >600000</property>
<property name= "Testonborrow" >true</property>
<property name= "Testwhileidle" >true</property>
</poolConfig>
</dbServer>




<!--
Slave database Licensing Related settings
-
<dbserver name= "Server2" >

<!--Poolableobjectfactory Implementation class--
<factoryconfig class= "Com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory" >
<property name= "Manager" >defaultManager</property>

<!--real MySQL database Port--
<property name= "Port" >3306</property>

<!--real MySQL database IP--
<property name= "IpAddress" >192.168.1.136</property>

<property name= "schema" >test</property>

<!--user name to log in to MySQL--
<property name= "User" >amoeba</property>

<!--password to log in to MySQL--
<property name= "Password" >amoeba</property>


</factoryConfig>

<!--Objectpool Implementation class--
<poolconfig class= "Com.meidusa.amoeba.net.poolable.PoolableObjectPool" >
<property name= "Maxactive" >200</property>
<property name= "Maxidle" >200</property>
<property name= "Minidle" >10</property>
<property name= "Minevictableidletimemillis" >600000</property>
<property name= "Timebetweenevictionrunsmillis" >600000</property>
<property name= "Testonborrow" >true</property>
<property name= "Testwhileidle" >true</property>
</poolConfig>
</dbServer>




<!--
Master Balanced Load
-
<dbserver name= "Master" virtual= "true" >
<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >
<!--load Balancing parameters 1=roundrobin, 2=weightbased, 3=ha-->
<property name= "LoadBalance" >1</property>

<!--the list of poolname participating in the pool load balancer is separated by commas--
<property name= "Poolnames" >server1</property>
</poolConfig>
</dbServer>



<!--
Slave Balanced Load
-
<dbserver name= "Slave" virtual= "true" >
<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >
<!--load Balancing parameters 1=roundrobin, 2=weightbased, 3=ha-->
<property name= "LoadBalance" >1</property>

<!--the list of poolname participating in the pool load balancer is separated by commas--
<property name= "Poolnames" >server1,server2</property>
</poolConfig>
</dbServer>



<!--
Read/write separation configuration
-
</dbServerList>

<queryrouter class= "Com.meidusa.amoeba.mysql.parser.MysqlQueryRouter" >
<property name= "Ruleconfig" >${amoeba.home}/conf/rule.xml</property>
<property name= "Functionconfig" >${amoeba.home}/conf/functionMap.xml</property>
<property name= "Rulefunctionconfig" >${amoeba.home}/conf/ruleFunctionMap.xml</property>
<property name= "Lrumapsize" >1500</property>

<property name= "Defaultpool" >master</property>
<property name= "Writepool" >master</property>
<property name= "Readpool" >slave</property>

<property name= "Needparse" >true</property>
</queryRouter>
</amoeba:configuration>

===================================================================================================

6. Start Amoeba

# Cd/usr/local/amoeba/bin
This startup is easy to see Nohup.log logs. Prevent prompt overflow
#:nohup bash-x Amoeba &
#: Restart Amoeba to kill the amoeba process first

#ps-ef|grep Amoeba|awk ' {print $} ' |xargs kill-9

#echo >nohup.out

#nohup bash-x Amoeba &

Common errors: 1
The stack size specified is too small, specify at least 228k
Error:could not create the Java Virtual machine.
ERROR:A Fatal exception has occurred. Program would exit.
Workaround:
# Vim/usr/local/amoeba/bin/amoeba
#DEFAULT_OPTS = "-server-xms256m-xmx256m-xss128k" comment
Default_opts= "-server-xms256m-xmx256m-xss256k" increased

7, test whether read-write separation

1. Build a Alvin table in master's test library
Mysql->use test;
Mysql->create table Alvin (ID int,name char (10));

2. See if the test library is synchronizing the Alvin Table (slave)
mysql-> use test;
Mysql->show tables;

3, in the Master (master) operation. Inserts a single piece of data.
Mysql-->insert into Alvin values (1, ' master ');

4, in from (slave) operation, insert a piece of data.
Mysql-->insert into Alvin values (2, ' slave ');

Note Because the master data is synchronized from. So here are 2 data, inserting 2 data on slave to be different from the main server content, so that better display polling balanced load viewing effect,

5. Operation on Amoeba Machine
In the 192.168.1.138 above test (can connect to the installation of Amoeba host can act as a client), this password is the configuration file inside the external authentication password root and password, open password, must be street port number, port number can be defined by themselves

#mysql –uroot–paixocm-h192.168.1.137-p9006
(1) Verify the Read first. We configure the file is Dr Polling, note this IP address to write:Amoeba IP address, not master is not slave, is through amoeba proxy login.
Mysql->use test;

Test polling succeeded. The Lord and the queries from each query.
(2) Verify write, normal. If all is written into the master, the data on both sides will be so. If one of them has a
Inconsistent words. Then the explanation is written from the inside. Because the master does not synchronize the data from. is a one-way data transfer.

Turn off the sync feature from server 136 first

#stop slave;

Then insert a piece of data through client 138

Then to the main server to query, you can see the data just inserted

finally to the query from the server, and do not see the data inserted, that is, the data is only written to the primary server, you can turn on the replication function from the server, start slave; re-query you can see the data from the server synchronization, do not believe you try!!


Common configurations:
Eight: Common configuration notation.
(1) If you load multiple libraries, the software determines which libraries have permissions based on your user authorization.
Grant all privileges on * * to [email protected] '% ' identified by ' amoeba123 '; All that is written here is to have permissions on all libraries (all libraries will be loaded automatically) .
the library name of the above configuration file, test. Based on this statement. If it is all, test it directly with the write test. A little easier.



(2) One primary one from the load notation one:
the Lord writes only. <property name= "Poolnames" >server1</property>(the primary balanced load configuration)
from read-only. <property name= "Poolnames" >server2</property>(from the Balanced load configuration)


(3) a master one from the load notation:
main Negative write: <property name= "Poolnames" >server1</property>
The Master is also responsible for reading, which is also responsible for reading. Ratio 1:1 the first reading. Read the second time from the. To write the word, only write the Lord. <property name= "Poolnames" >server1,server2</property>


(4) a master multi-slave load notation:
For example, a master. 3 units from. defined on the top. Server1 (master) server2 (slave1) Server3 (slave2) server4 (slave3)
1 Primary Write only: <property name= "Poolnames" >server1</property>
3 Read <property name= "Poolnames" from load >server2,server3,server4</property>
Weight: 1:1 3 Polling one at a time. Continuous cycle

(5) For example, I want to. slave1 weights a little higher. Other. 2 units from. Read every time. SLAVE1 read. 2 times .
<property name= "Poolnames" >server2,server2,server3,server4</property>

MySQL dual-machine hot standby +amoeba based on master-slave replication for read-write separation and load balancing