MySQL - Detailed explanation of reading and writing separation based on Amoeba (picture and text)

Today, we continue the topic of MySQL, and today we bring you an article on a solution to realize MySQL read-write separation based on Amoeba, so let’s go directly to the topic of today.

1. Server planning

Host name	IP address	Node
liuyazhuang152	192.168.0.152	amoeba
liuyazhuang153	192.168.0.153	Write library
liuyazhuang154	192.168.0.154	Read library

2. Test plan

We installed amoeba on 192.168.0.152, and installed mysql on 192.168.0.153 and 192.168.0.154. There is no intersection between these two mysql servers. By configuring amoeba, 192.168. MySQL on 0.153 is configured as a writing database, and MySQL on 192.168.0.154 is configured as a reading database; our client connects to amoeba, and amoeba operates the database. We write data to the corresponding database through amoeba, and use query: The 192.168.0.153 server MySQL has inserted relevant data, but the MySQL server on 192.168.0.154 does not; then, we think that amoeba has helped our program complete the read-write separation function;

3. Install MySQL

Please refer to the blog post "MySQL-CentOS6.5 Compile and Install MySQL5.6.16 Detailed Code Introduction"

4. Install and configure Amoeba

1 , Download Amoeba

We can go to https://sourceforge.net/projects/amoeba/ to download Amoeba. What I downloaded here is amoeba-mysql-binary-2.2 .0.tar.gz

2. Install Amoeba

Amoeba is written in Java, so we need our server to configure the Java operating environment. I will not talk about how to configure Java here. Running environment

Since there are no files directly after Amoeba is decompressed, we need to create a folder in advance. I will create a folder under /usr/local: amoeba-mysql-binary-2.2.0

Then enter the following command on the command line

tar -zxvf amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba-mysql-binary-2.2.0

Extract Amoeba to the /usr/local/amoeba-mysql-binary-2.2.0 directory. At this time, we have completed the installation.

3. Configure Amoeba

We go to Amoeba’s conf directory and see the following Configuration file:

Here we focus on amoeba.xml and dbServers.xml files.

3.1 Configure amoeba.xml

We open the amoeba.xml file and find the following code:

We Configure the user name and password, of which 8066 is the default port of Amoeba. The configured effect is as follows:

## Find the following code:

Remove the

comment , the effect is as follows:

This configures the amoeba file. After we configure it, we access When using mysql, you need to access the port, user name, and password configured in the amoeba.xml file to connect to MySQL through Amoeba.

3.2 Configure dbServers.xml

Find the following code in the dbServers.xml file:

We are related here For the configuration of the database, please note: The configuration of these databases specifies the database, username, and password to be separated from reading and writing. The username and password are the username and password configured to access Amoeba on the terminal. The effect after my modification is as follows:

The username and password at this time need to be authorized by the MySQL server to the Amoeba server so that it can log in to the MySQL server.

Then we find the following code:

Modify it to the IP addresses of the two MySQL servers, as shown below:

#At this point, we have configured the read-write separation based on Amoeba.

4. Authorize Amoeba access in MySQL

Log in to two MySQL servers respectively and execute the following commands to authorize Amoeba

 grant all on lyz.* to lyz@192.168.0.152 identified by 'lyz';

The username and password here are what we are using The username and password configured in the dbServers.xml file. At this time, MySQL authorization is completed

5. Run Amoeba

We enter the bin directory of Amoeba and enter the following command on the command line to run Amoeba

./amoeba start &

Among them, & represents running in the background. At this time, we found that the startup failed: the information is as follows:

This means that the configured Java stack is too small , at least 228K. At this time, we open the amoeba script file in the bin directory and find the following code:

Change the 128K in the above picture to 256K (you can change it according to your Actual situation modification), as shown below:

此时我们重新输入命令

./amoeba start &

启动成功

6、测试

6.1 查询数据

我们先分别查询MySQL数据库表上的相关数据，未通过Amoeba操作数据库的时候，我们是新建的数据库lyz和表t1,此时192.168.0.153和192.168.0.154上MySQL数据表都应该为空，如下图：

6.2登录Amoeba

我们在192.168.0.152上输入如下命令登录Amoeba，通过Amoeba访问数据库

mysql -h192.168.0.152 -uroot -proot -P 8066

注意：我们这里输入的端口是8066，此端口是Amoeba配置的端口，我们需要登录Amoeba而不是直接登录MySQL

登录成功如下图所示：

6.3测试

  此时我们在终端查询数据表数据，显示为空，如下图：

我们在Amoeba服务器终端插入一条数据，如下：

同时，我们在Amoeba上查询数据如下：

数据为空。

我们登录192.168.0.153数据库，查看表数据如下：

如图，说明Amoeba向192.168.0.153库插入了数据。

我们在登录192.168.0.154数据库查看数据信息，如下：

说明Amoeba没有向192.168.0.154库插入数据，并从192.168.0.154库读取数据。

至此，基于Amoeba实现MySQL读写分离全部配置完成。

五、附录

./amoeba start & 启动amoeba
./amoeba stop  关闭amoeba

The above is the detailed content of MySQL - Detailed explanation of reading and writing separation based on Amoeba (picture and text). For more information, please follow other related articles on the PHP Chinese website!