Shulou(Shulou.com)06/01 Report--

The following is about how to configure mysql read-write separation through the amoeba tool. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, and I'm sure you'll benefit from reading this article on how to configure mysql read-write separation through amoeba tools.

First, background introduction:

Amoeba (amoeba) project, focusing on the development of distributed database proxy. Located between Client and DB Server (s). Transparent to the client. It has load balancing, high availability, sql filtering, read-write separation, routing related query to the target database, and concurrent requests for merging results of multiple databases. To build Amoeba read-write separation, you first need to know the master-slave configuration of MySQL. The specific architecture diagram is shown below:

Second, configure the required environment:

Amoeba for mysql:192.168.1.28

Master: 192.168.1.247

Slave: 192.168.1.245

Database:yazi passwd:root/123456

JDK1.8.0_51

Third, the specific construction and configuration steps are as follows:

1. Configure mysql master-slave replication. Refer to the configuration of mysql master-slave replication in the previous section.

2. Install and configure jdk1.8.0_51 and environment variables

(1), install the jdk command: rpm-ivh jdk-8u51-linux-x64.rpm

[root@localhost install] # lltotal 481168 Murray RW Murray Ruby Murray. 1 root root 8850470 Nov 2 22:43 apache-tomcat-7.0.63.tar.gz-rw-r--r--. 1 root root 137808216 Jan 13 15:09 jdk-8u51-linux-x64.rpm-rw-r--r--. 1 root root 33203321 Nov 20 11:31 mysql-5.6.25.tar.gz-rw-r--r--. 1 root root 312845162 Nov 13 15:19 mysql-5.6.27-linux-glibc2.5-x86_ 64.tar.gz [root @ localhost install] # rpm-ivh jdk-8u51-linux-x64.rpm Preparing... # [100%] 1:jdk1.8.0_51 # [100%] Unpacking JAR files... Rt.jar... Jsse.jar... Charsets.jar... Tools.jar... Localedata.jar... Jfxrt.jar... Plugin.jar... Javaws.jar... Deploy.jar...

(2) configure environment variables:

[root@localhost install] # vi / etc/profile

# / etc/profile# System wide environment and startup programs, for login setup# Functions and aliases go in / etc/bashrc# It's NOT a good idea to change this file unless you know what you# are doing. It's much better to create a custom.sh shell script in# / etc/profile.d/ to make custom changes to your environment, as this# will prevent the need for merging in future updates.pathmunge () {case ": ${PATH}:" in *: "$1": *); *) if ["$2" = "after"] Then PATH=$PATH:$1 else PATH=$1:$PATH fi esac} if [- x / usr/bin/id]; then if [- z "$EUID"]; then # ksh workaround EUID= `id-u`UID= `id-ru`fi USER= "`id-un`" LOGNAME=$USER MAIL= "/ var/spool/mail/$USER" fi# Path manipulationif ["$EUID" = "0"] Then pathmunge / sbin pathmunge / usr/sbin pathmunge / usr/local/sbinelse pathmunge / usr/local/sbin after pathmunge / usr/sbin after pathmunge / sbin afterfiHOSTNAME= `/ bin/hostname 2 > / dev/ null`HISTSIZE = 1000if ["$HISTCONTROL" = "ignorespace"]; then export HISTCONTROL=ignorebothelse export HISTCONTROL=ignoredupsfiexport PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL# By default, we want umask to get set. This sets it for login shell# Current threshold for system reserved uid/gids is 20 You could check uidgid reservation validity in# / usr/share/doc/setup-*/uidgid fileif [$UID-gt] & & ["`id-gn `" = "`id-un`"]; then umask 002else umask 022fifor I in / etc/profile.d/*.sh; do if [- r "$I"]; then if ["${- # * I}"! = "$-"] Then. "$I" else. "$I" > / dev/null 2 > & 1 fi fidoneunset iunset-f pathmungeexport JAVA_HOME=/usr/java/jdk1.8.0_51export JRE_HOME=/usr/java/jdk1.8.0_51/jreexport PATH=$JAVA_HOME/bin:$PATHexport CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

(3) configuration takes effect and verification:

[root@FileServerA logs] # source / etc/profile [root@FileServerA logs] # java-versionjava version "1.8.0mm 51" Java (TM) SE Runtime Environment (build 1.8.0_51-b16) Java HotSpot (TM) 64-Bit Server VM (build 25.51-b03, mixed mode)

3. Install and configure amoeba

(1) download amoeba

(http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip)

(2) the compressed package can be decompressed without installation.

[root@localhost install] # unzip amoeba-mysql-3.0.5-RC-distribution.zip Archive: amoeba-mysql-3.0.5-RC-distribution.zip creating: amoeba-mysql-3.0.5-RC/ creating: amoeba-mysql-3.0.5-RC/lib/ inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-core-3.0.5-RC.jar inflating: amoeba-mysql-3.0.5-RC/lib/ Log4j-1.2.12.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-lang-2.4.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-logging-1.1.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-beanutils-1.8.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-collections-3.2.1.jar Inflating: amoeba-mysql-3.0.5-RC/lib/ognl-3.0.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/javassist-3.11.0.GA.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-pool-1.2.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-pool-1.6.jar inflating: amoeba-mysql-3.0. 5-RC/lib/slf4j-api-1.6.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/slf4j-log4j12-1.6.2.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-bean-1.3.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-util-1.2.0.jar inflating: amoeba-mysql-3.0.5-RC/ Lib/dom4j-1.6.1.jar inflating: amoeba-mysql-3.0.5-RC/lib/xml-apis-1.0.b2.jar inflating: amoeba-mysql-3.0.5-RC/lib/oro-2.0.8.jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-digester-1.8.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-io-2.2.0. Jar inflating: amoeba-mysql-3.0.5-RC/lib/commons-cli-1.2.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-common-runtime-1.3.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-core-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-asm-3.0.6.RELEASE.jar inflating: Amoeba-mysql-3.0.5-RC/lib/spring-context-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-aop-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/aopalliance-1.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/spring-beans-3.0.6.RELEASE.jar inflating: amoeba-mysql-3. 0.5-RC/lib/spring-expression-3.0.6.RELEASE.jar inflating: amoeba-mysql-3.0.5-RC/lib/plexus-classworlds-2.4.2-HEXNOVA.jar inflating: amoeba-mysql-3.0.5-RC/lib/toolkit-net-benchmark-1.2.0.jar inflating: amoeba-mysql-3.0.5-RC/lib/amoeba-mysql-3.0.5-RC.jar creating: amoeba-mysql-3 .0.5-RC/bin/ inflating: amoeba-mysql-3.0.5-RC/bin/benchmark inflating: amoeba-mysql-3.0.5-RC/bin/launcher inflating: amoeba-mysql-3.0.5-RC/bin/mkdirhier inflating: amoeba-mysql-3.0.5-RC/bin/shutdown inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.bat inflating: amoeba-mysql-3.0.5-RC/bin/launcher. Bat inflating: amoeba-mysql-3.0.5-RC/bin/benchmark.classpath inflating: amoeba-mysql-3.0.5-RC/bin/launcher.classpath creating: amoeba-mysql-3.0.5-RC/conf/ inflating: amoeba-mysql-3.0.5-RC/conf/access_list.conf inflating: amoeba-mysql-3.0.5-RC/conf/amoeba.dtd inflating: amoeba-mysql-3.0.5-RC/conf/amoeba. Xml inflating: amoeba-mysql-3.0.5-RC/conf/dbserver.dtd inflating: amoeba-mysql-3.0.5-RC/conf/dbServers.xml inflating: amoeba-mysql-3.0.5-RC/conf/function.dtd inflating: amoeba-mysql-3.0.5-RC/conf/functionMap.xml inflating: amoeba-mysql-3.0.5-RC/conf/log4j.dtd inflating: amoeba-mysql-3.0.5-RC/conf/log4j. Xml inflating: amoeba-mysql-3.0.5-RC/conf/rule.dtd inflating: amoeba-mysql-3.0.5-RC/conf/rule.xml inflating: amoeba-mysql-3.0.5-RC/conf/ruleFunctionMap.xml creating: amoeba-mysql-3.0.5-RC/benchmark/ inflating: amoeba-mysql-3.0.5-RC/benchmark/context.xml inflating: amoeba-mysql-3.0.5-RC/benchmark/objectMap.dtd Inflating: amoeba-mysql-3.0.5-RC/benchmark/query.xml inflating: amoeba-mysql-3.0.5-RC/jvm.properties [root@localhost install] # mv amoeba-mysql-3.0.5-RC/ usr/local/

(3) detailed description of the configuration file of amoeba:

Amoeba.xml: defines the front-end control file, including the front-end port, user name and password

DbServers.xml: defines the configuration file of the back-end node. Set up the database, login mysql account and password

A, configure the amoeba.xml file

[root@FileServerA conf] # vim amoeba.xml 8066 / / * * Port number * * / / 128 64 root 123456 "amoeba.xml" 91L 3099C 30BET 6-41.8% 8066 / / set other ports 128 64 Lqb / / * username and password of the remote access agent * * / 123456 / / * username and password * * / / ${amoeba.home} / conf/access_list.conf 128 500 utf8 60 com.meidusa.toolkit.net.AuthingableConnectionManager ${amoeba.home} / conf/dbServers.xml ${amoeba.home} / conf/rule.xml ${amoeba.home} / conf/ruleFunctionMap.xml ${amoeba.home} / conf/functionMap.xml 1500 Resource pool written by master master / / * / viplqb / / * resource pool written * * / / true

B, configure the dbServers.xml file

[root@FileServerA conf] # vim dbServers.xml ${defaultManager} 64 128 3306 / / * set mysql access port number * * / / Yazi / / * set mysql to access the default library * * / / root / / * Log in to mysql account and password * * / / 123456 500500 1 600000 600000 true / / * Set master and ip addresses * / / 192.168.1.247 / / * set slave1 and ip addresses * * / * if there are multiple slave hosts Copy this configuration and continue to add the ip address * * / 192.168.1.245 1 / / * * by polling * * / / Slave1 / / * all hosts from the library should write * * / /

(4) start amoeba and check to see if there is any error.

[root@FileServerA conf] # / usr/local/amoeba-mysql-3.0.5-RC/bin/launcher & [1] 34774 [root@FileServerA conf] # log4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016-07-04 13 root@FileServerA conf 4614 INFO net.ServerableConnectionManager-Server listening on 0.0.0.0/0.0.0.0:8066.Java HotSpot (TM) 64-Bit Server VM warning: ignoring option PermSize=16m Support was removed in 8.0Java HotSpot (TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m Support was removed in 8.0 2016-07-04 13:47:53 [INFO] Project Name=Amoeba-MySQL, PID=32445 Starting...log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml2016-07-04 13 Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016 47 Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016 53604 INFO context.MysqlRuntimeContext-07-04 13 Fringe 47 Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016 53789 INFO net.ServerableConnectionManager -Server listening on 0.0.0.0Universe 0.0.0.0pur8066. 2016-07-04 14:22:55 [INFO] ignore signal:HUPJava HotSpot (TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0Java HotSpot (TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m Support was removed in 8.0 2016-07-04 17:31:08 [INFO] Project Name=Amoeba-MySQL, PID=34779 Starting...log4j:WARN log4j config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/log4j.xml2016-07-04 17 INFO net.ServerableConnectionManager 31mer 08868 INFO context.MysqlRuntimeContext-Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARN ip access config load completed from file:/usr/local/amoeba-mysql-3.0.5-RC/conf/access_list.conf2016-07-04 17purl 31v 09128 -Server listening on 0.0.0.0Universe 0.0.0.0pur8066.

(5) Log in with other CVM:

[root@mysqlmaster] # mysql-ulqb-p-h292.168.1.28-P8066 Enter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 965356058Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | | yazi | +-+ 5 rows in set (0.01 sec) mysql >

Note: when configuring this file, amoeba.xml, other hosts can be accessed through the proxy, as described above, there is no problem with amoeba.xml configuration.

(6) verify read-write separation, which requires the configuration of the dbServers.xml file, followed by verification of read-write separation.

(a) create a table on the main library 247 before stopping synchronization

Mysql > use yazi;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables +-+ | Tables_in_yazi | +-+ | sxit | | test | +-+ 2 rows in set (0.00 sec) mysql > create table lqb (id int (10), name varchar (10), address varchar (20)); Query OK, 0 rows affected (0.02 sec)

(B) View on slave library 245 and stop synchronization on slave:

Mysql > use yazi;Database changedmysql > show tables;+-+ | Tables_in_yazi | +-+ | lqb | | sxit | | test | +-+ 3 rows in set (0.00 sec) mysql > stop slave->; Query OK, 0 rows affected (0.00 sec)

(C) insert a different piece of data on each master and slave

Insert (1) in the main library (1)

Insert on the library (2)

Do the following on the main library:

Mysql > insert into lqb values; Query OK, 1 row affected (0.00 sec) mysql > select * from lqb +-+ | id | name | address | +-+ | 1 | zhangsan | master | +-+ 1 row in set (0.00 sec)

Do the following on the slave library:

Mysql > insert into lqb values; Query OK, 1 row affected (0.00 sec) mysql > select * from lqb +-+ | id | name | address | +-+ | 2 | zhangsan | slave | +-+ 1 row in set (0.00 sec)

(d) Log in to amoeba CVM to view the read operation: 245slave CVM is displayed

Mysql > select * from lqb

+-+

| | id | name | address | |

+-+

| | 2 | zhangsan | slave |

+-+

1 row in set (0.00 sec)

(e) Test the following write operations on the amoeba CVM to view the data on the 245 slave database.

Mysql > insert into lqb values; Query OK, 1 row affected (0.00 sec) mysql > select * from lqb +-+ | id | name | address | +-+ | 2 | zhangsan | slave | +-+ 1 row in set (0.00 sec)

If you query on the main library 247, you can see that it has been inserted.

Mysql > select * from lqb +-+ | id | name | address | +-+ | 1 | zhangsan | master | | 3 | wanger | test_write | +-+ 2 rows in set (0.00 sec)

Do you query 245 from the library, or just now?

Mysql > select * from lqb;+-+ | id | name | address | +-+ | 2 | zhangsan | slave | +-+ 1 row in set (0.00 sec)

At this point, the database read-write separation has been configured, in the formal production environment, the definition of the read-write host needs to be changed, because the database uses the most read operations. We can give read or query to never processing, similarly, we can also add multiple slave hosts. Let it automatically read the database from different hosts.

Is there anything you don't understand about the above method of configuring mysql read-write separation through the amoeba tool? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.