Example of using Amoeba to implement MySQL master/slave read/write splitting

Advantages of master-slave synchronization architecture

• The read work is handed over to the slave server, reducing the pressure on the master server.
• Back up data from the server to avoid affecting the primary server service during the backup process
• When the master server encounters a problem, you can switch to the slave server.

Amoeba introduction

The code is as follows:

Copy code

The Amoeba project, the open-source framework, launched an Amoeba for Mysql software in 2008. This software is dedicated to the MySQL distributed Database front-end Proxy layer. It acts as the SQL routing function when accessing MySQL at the application layer and focuses on the development of the distributed Database Proxy layer. It is located between the Client and DB Server (s) and transparent to the Client. Provides load balancing, high availability, SQL filtering, read/write splitting, routing-related to the target database, and concurrent requests to merge results from multiple databases. With Amoeba, you can achieve high availability, load balancing, and data slicing for multiple data sources. The software Amoeba for Mysql is mainly used this time.

 

Master-slave synchronization configuration of MySQL

Lab environment

• Server Type: Virtual Machine
• System: CentOS release 6.8 (Final)
• MySQL: 5.1.73
• Primary Database: 172.16.2.239
• Slave Database: 172.16.2.230

Configure the master database

1. This operation uses the yum service for installation. EnterYum-y install mysql(Note:-y indicates that 'y' is automatically selected during the installation process ')
2. Service mysql startStart MySQL service
3. Use it under the root accountMysqladmin-u root password 'newpassword'Set a password for the root account of mysql)
4. Mysql-u root-pEnter the password and log on
5. Grant replication salve on *. * to 'root' @ '192. 16.2.230 'identified by '20140901'(Add a remote account for synchronization from the database to the local machine. The root account is the account, the ip address is the slave database address, and 123456 is the logon password)
6. Select user, host, password from mysql. userCheck whether the account is successfully created, check whether the account and password are empty, and delete the account (note: This step is not required. The existence of the account and password will affect logon. This is to be ruled out before a problem occurs)
7. Vim/etc/my. cnf 
   
   (Server-id cannot be the same as other nodes. replicate_ignore_db sets databases that are not synchronized)
8. Exit MySQL,Service mysqld restartAfter restarting, log on to the server, view the server_id and File + position information, and record
   

Configure slave database

1. On 172.16.2.230, configure the Slave Database. For the installation process, refer to the master database (here, the default database is used as the master and slave database, so you can directly use yum to install the slave database, if there are changes to the master database to be consistent, use the dump backup and restore configuration on the slave database node)
2. Vim/etc/my. cnfEdit configuration file
    
   (Note: The server-id here must not be the same as the master database)
3. Service mysqld restartRestart database
4. Mysql-u root-p-h 172.16.2.239Verify the connection to the master database (the root here is the account authorized in the master database)
5. Log out of the remote connection and change to a local root account,Show variables like 'server _ id'Check whether the ID number is the same as that of the master database.
6. Slave stop; Disable synchronization service
7. Change master to master_host = '192. 16.2.239 ', master_user = 'root', master_password = '2016', master_log_file = 'master _ bin.20.19', master_log_pos = 172; (Note: master_user here is the authorized account, and log_file and pos are the records viewed on the previous master database. For details, see step 1 above. If the flush privileges command is used to refresh the database without synchronization in the master database, log_file will automatically + 1)
8. Slave star ** t to enable synchronization, ** show slave statusView status
   
   Check whether the values of Slave_IO_Running and shlave_ SQL _Running are Yes. If the values are NO, check the configuration.
9. After checking the status, you can create a table in the test database of the master database. After the table is written, you can view the table from the database. The synchronization is complete.

Install and configure Amoeba

1. Configure the JAVA environment

JDK needs to be downloaded here. The image can be downloaded and installed directly from yum, Yum-y install java-1.6.0-openjdkThe version 1.6 is used here. You can also directly use wget to download data to Oracle, but you only need to unzip the package to create a connection.
Vim/etv/profileConfigure java environment variables. Note that the download method and path are different.

Run Source profile, Use Java-verisonView
2. Install Amoeba

Wget https://sourceforge.net/projects/amoeba/files/Amoeba_for_mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/downloadmkdir/usr/local/amoeba-2.2.0tar xvf amoeba-mysql-binary-2.2.0.tar.gz-C/usr/local/amoeba-2.2.0

3. Configure Amoeba

Vim/usr/local/amoeba-2.2.0/conf/dbServers. xml 

• Port = 3306 indicates the port number of the master-slave database.
• Schema = test indicates the name of the amoeba proxy database.
• User = root and password are the account and password used by amoeba to log on to the master/slave server. Therefore, note that the account must have the permission to access two databases.

• The localhost here refers to the code of the master database, and the IP address below is the master database address
• Slave refers to the slave database code, and the ipAddress below is the slave database address
• Set the weights of the polling master and slave read to, which can effectively solve the problem of balance of the master and slave load.

Vim/usr/local/amoeba-2.2.0/conf/amoeba. xml 

• Port 8066 is the port listened to by the amoeba agent.
• User and password are the account information used to log on to the amoeba agent.

• LRUMapSize defines the number of SQL statement resolutions cached by Amoeba
• The default node is configured in the defaultPool. Some statements except SELELCT/UPDATE/INSERT/DELETEDE are executed on the node.
• WritePool is configured with the write database, usually the master database.
• ReadPool is configured with a read database, which can be a database pool or

This completes the Amoeba configuration (note: the parameter meanings of the configuration file are fully explained in the amoeba document)

Next, configure the environment variables, Vim/etc/profile. d/amoeba. sh

Export AMOEBA_HOME =/usr/local/amoeba-2.2.0/export PATH = $ AMOEBA_HOME/bin/: $ PATH

4. Start Amoeba

Run Amoeba start 
The stack size specified is too small, Specify at least 160 k
Cocould not create the Java virtual machine.
If the preceding error is reported, you need to edit the binary script because JAVA startup imposes a limit on the minimum value of the xss parameter.
Vim/usr/local/amoeba-2.2.0/bin/amoeba 
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss128k"
To
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k"
(Note: if the startup fails, check whether Amoeba Monitor Server shutdown completed exists !, If not, execute amoeba stop once)

5. Connect to amoeba

Connect to the amoeba database after normal startup, Mysql-u root-p-h 172.16.2.231-P8066

This is the prompt message after normal startup. If an error is prompted, there are many possibilities, but most of them are network (firewall is not off, master/slave databases are not started) permissions (the master/slave database does not provide amoeba account permissions, etc.) and configuration issues. Read more error prompts and logs to help you locate errors faster.

6. Test

There are many methods to test. The main idea is as follows:
-Create a table on amoeba and test whether to synchronize the master and slave nodes;
-Disable slave stop from database synchronization and perform write operations on amoeba to check whether only the master database is written. Insert one data entry on the master and slave databases respectively, perform read operations on amoba to view the displayed data.