Mysql middleware amoeba implements mysql read/write splitting _ MySQL

Mysql middleware amoeba implements mysql read/write separation 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 (XML-based configuration files, which are easier to write rules using SQLJEP syntax than MySQL Proxy based on lua scripts ). This article will implement a simple amoeba configuration

Lab environment:

192.168.1.121 is the amoeba server and provides read/write splitting.

Master server whose 192.168.1.141 is mysql

192.168.1.142 is the slave server of mysql.

1. configure the mysql master server

Edit/etc/my. cnf and provide the following configuration

Log_bin = index

Server_id = 1

Authorize on the master server

Mysql> grant replication slave, replication client on user @ '192. 168.1.142 'identified by "192 ";

Mysql> flush privileges;

2. configure mysql slave service

Edit/etc/my. cnf and provide the following configuration

Server_id = 10

Relay_log = relay

Enter the mysql command line interface

Mysql> change master toMASTER_HOST = "192.168.1.141", MASTER_USER = "user", MASTER_PASSWORD = "123456", MASTER_LOG_FILE = "index.000004", MASTER_LOG_POS = 429;

Mysql> start slave;

If you can see Slave_IO_Running: Yes and Slave_ SQL _Running: Yes, the master-slave configuration is successful.

3. install amoeba on 192.168.1.121

Amoeba is developed in java, so jdk must be installed first.

[Root @ localhost ~] # Chmod + x jdk-6u43-linux-x64.bin

[Root @ localhost ~] #./Jdk-6u43-linux-x64.bin

# Vi/etc/profile. d/java. sh

Export JAVA_HOME =/root/jdk1.6.0 _ 43/bin

Export PATH = $ JAVA_HOME/bin: $ PATH

# Source/etc/profile. d/java. sh

Install amoeba

# Wget http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/download

# Mkdir/usr/local/amoeba

# Tar xf amoeba-mysql-binary-2.2.0.tar.gz-C/usr/local/amoeba

Go to the amoeba directory and you will see several directories.

Bechmark: stress testing

Bin: Script File

Conf: configuration file

Lib: Library

There are many configuration files in the conf file. the read/write splitting effect is achieved here. you only need two files: amoeba. xml and dbserver. xml. The configuration to be modified in the amoeba. xml file is as follows:

3306 # Port number used to connect to amoeba

0.0.0.0 # Listener address

$ {ClientConnectioneManager}

128

64

Root # Account used to connect to amoeba

Amoeba # Password used to connect to amoeba

$ {Amoeba. home}/conf/access_list.conf

$ {Amoeba. home}/conf/rule. xml

$ {Amoeba. home}/conf/ruleFunctionMap. xml

$ {Amoeba. home}/conf/functionMap. xml

1500

# No default route is required

Test1 # Write the route to test1.

Test2 # Read route to test2

True

The configuration to be modified in the dbserver. xml file is as follows:

$ {Defamanager manager}

64

128

3306 # Backend mysql Port

Amoeba # Default database connection for backend mysql

Root # Connect to the backend mysql account

Amoebapass # Password used to connect to the backend mysql

192.168.1.141 # Backend MySQL ip address

192.168.1.142

Grant permissions to amoeba on the backend proxy mysql and perform the following operations on 192.168.1.141:

Grant all on *. * to 'root' @ '192. 168.1.121 'identified by 'amoebapass)

After all the items are configured, you can start amoeba. run:/usr/local/amoeba/bin/amoeba and the following error may occur:

The stack size specified is too small, Specify at least 160 k cocould not create the Java virtual machine.

Modify the amoeba file, vi/usr/local/amoeba/bin/amoeba, and find the following file:

DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss128k"

Modify it:

DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k

Run/usr/local/amoeba/bin/amoeba again. if amoeba start | stop appears, you can start amoeba.

#/Usr/local/amoeba/bin/amoeba start

After the startup is successful, install a mysql client on the server where amoeba is installed to test

# Yum install mysql

# Mysql-uroot-pamoeba-h192.168.1.121

If the connection is successful and the statement is successfully created, it can be displayed on the back-end proxy mysql to prove that the amoeba configuration is successful. Of course, if you want to see whether the master-slave splitting has been fully implemented, you can analyze and view the packet capture.