9. mysql (linux) read/write splitting, mysqllinux

9. mysql (linux) read/write splitting, mysqllinux

After Mysql master-slave mutual backup, in order to make full use of the two (or more) machines, the necessity of read/write splitting is highlighted. Advantages and disadvantages this is not the focus of the discussion!

There are three methods for Mysql read/write Splitting:

1. Mysql-proxy

2. Amoeba)

3. The other is rarely used.

Here we use amoeba. The first language is lua, and I heard that the performance is poor!

Required machines:

Write: 192.168.1.111

Read: 192.168.1.112 (Master/Slave has been set for the two machines; otherwise, the read/write splitting will be meaningless)

Amoeba: 192.168.1.112 (here, the machine installed by amoeba and mysql (read) are on one machine)

I. Preparations

1. install java support

1) download java SE 1.6

Address: http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html

The official website barely found version 1.6.

2) Installation

# Mkdir/usr/local/jdk

# Tar zxvf jdk-7u71-linux-x64.gz-C/usr/local/jdk

2. Install amoeba

1) download

Address: http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz/download

2) Installation

# Mkdir/usr/local/amoeba

# Tar zxvf amoeba-mysql-binary-2.1.0-RC5.tar.gz-C/usr/local/amoeba

# Chmod-R + x/usr/local/amoeba/bin

Ii. Configure the environment

1. Java

# Vi/etc/profile

# For java

Export JAVA_HOME =/usr/local/jdk

Export CLASSPATH =.: $ JAVA_HOME/jre/lib/rt. jar: $ JAVA_HOME/lib/dt. jar: $ JAVA_HOME/lib/tools. jar

Export PATH = $ JAVA_HOME/bin: $ PATH

2. Amoeba

# Vi/etc/profile

# For amoeba

Export AMOEBA_HOME =/usr/local/amoeba

Export PATH = $ PATH: $ AMOEBA_HOME/bin

After completion:

# Source/etc/profile

3. Check whether the test is successful.

Java:

# Java-version // display as follows

Java version "1.7.0 _ 71"
Java (TM) SE Runtime Environment (build 1.7.0 _ 71-b14)
Java HotSpot (TM) 64-Bit Server VM (build 24.71-b01, mixed mode)

Amoeba:

# Amoeba // display as follows

Amoeba start | stop

3. Configure amoeba

You need to configure two files: dbServer. xml and amoeba. xml.

1. Configure dbServer. xml and paste my configuration directly

# Vi dbServer. xml

Modify 26 rows
Original <property name = "user"> root </property>
Change <property name = "user"> amoeba </property> // you can use this account to connect two servers (read/write). This account can be used to connect the read server or the write server, if you do not need to add this user and authorize this ip address (you need to understand)
Modify lines 28, 29, and 30
28 <property name = "password"> amoeba </property> // password of the mysql server
Copy the code and paste it twice.
42 <dbServer name = "server1" parent = "abstractServer">
43 <factoryConfig>
44 <! -- Mysql ip -->
45 <property name = "ipAddress"> 127.0.0.1 </property>
46 </factoryConfig>
47 </dbServer>
The modified results are as follows: add two db server definitions and set the IP addresses of the three mysql servers respectively. The IP addresses use the virtual IP addresses of mysql-mmm.
42 <dbServer name = "Master" parent = "abstractServer">
43 <factoryConfig>
44 <! -- Mysql ip -->
45 <property name = "ipAddress"> 192.168.1.111 </property>
46 </factoryConfig>
47 </dbServer>
48
49 <dbServer name = "Slave1" parent = "abstractServer">
50 <factoryConfig>
51 <! -- Mysql ip -->
52 <property name = "ipAddress"> 192.168.1.112 </property>
53 </factoryConfig>
54 </dbServer>
Modify the bottom of the configuration file
64 <dbServer name = "ReadPool" virtual = "true"> // defines the name of the virtual node pool.
65 <poolConfig class = "com. meidusa. amoeba. server. MultipleServerPool">
66 <! -- Load balancing strategy: 1 = ROUNDROBIN, 2 = WEIGHTBASED, 3 = HA -->
67 <property name = "loadbalance"> 1 </property> // Server Load balancer Algorithm
68
69 <! -- Separated by commas, such as: server1, server2, server1 -->
70 <property name = "poolNames"> Slave1 </property> // Member of the virtual node pool
71 </poolConfig>
72 </dbServer>

2. Configure amoeba. xml

11 <propertyname = "port"> 8066 </property> // amoeba listening port, no need to modify
Modify row 30th
Original <property name = "user"> root </property>
Change <property name = "user"> amoeba </property> // modify the amoeba user name to amoeba.
Modify row 32nd
Original <property name = "password"> </property>
Change <property name = "password"> amoeba </property> // modify the amoeba user name to amoeba.

115 rows-120 rows

115 <property name = "defaultPool"> Master </property>
116
117 <property name = "writePool"> Master </property>
118 <property name = "readPool"> Slave1 </property>
119 <propertyname = "needParse"> true </property>
120 </queryRouter>

 

Iv. Test

1. Start amoeba

# Amoeba start &

Log4j: WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j. xml
11:20:15, 858 INFO context. MysqlRuntimeContext-Amoeba for Mysql current versoin = 5.1.45-mysql-amoeba-proxy-2.1.0-RC5
Log4j: WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
11:20:16, 061 INFO net. ServerableConnectionManager-Amoeba for Mysql listening on 0.0.0.0/0.0.0.0: 8066.
11:20:16, 066 INFO net. ServerableConnectionManager-Amoeba Monitor Server listening on/127.0.0.1: 37064.

After jumping out of the box, press ctrl + c and start it in the background!

2. Test

In 192.168.1.112

A. mysql-u amoeba-P8806-h 192.168.1.112-pamoeba // test whether amoeba is started properly

B. mysql-u amoeba-h192.168.1.111-pamoeba // test whether to log on to the database.

C. mysql-u amoeba-h192.168.1.112-pamoeba // test whether to log on to the read server.

If all three of them can be successfully logged on, continue the following test and fail. You need to check whether a. Has this user B. Has it authorized to log on with this IP address?

3. Test amoeba read/write

On 192.168.1.112 (read), mysql> stop slave; // disable master-slave Replication

# Mysql-u amoeba-P8806-h 192.168.1.112-pamoeba // log on to amoeba

Mysql> use test;

Mysql> insert into test1 (a) values (123 );

Mysql> select * from test1;

The inserted data is not found, indicating that the read and write operations have been separated.

V. Problems

1. Q: Jump Out Of amoeba start

The stack size specified too small, Specify at least 228 k

Error: cocould not create the Java Virtual Machine.

Error: A fatal exception has occurred. Program will exit.

Modify: vi/usr/local/amoeba/bin/amoeba

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

DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k"

 

2. Q: After logging on to amoeba, do the following data operations:

Cocould not create a validatedobject, cause: ValidateObject failed

A: Test on 192.168.1.112.

Mysql-u amoeba-h192.168.1.111-pamoeba

Mysql-u amoeba-h192.168.1.112-pamoeba

One or both of them cannot log on, create a user, and authorize this ip address

 

3. Q: An error occurred while connecting to MySQL: Host XXX is blocked because of role connectionerrors, unblock with 'mysqladmin flush-hosts '.

Cause: the MySQL server has received a large number of terminated connections from a host, so it decided to terminate and continue to receive connections from the host. The maximum number of connection errors allowed is max_connect_errors, the showvariables command can be used to query, generally 10.

A: log on to mysql and execute flush hosts (the local host is not allowed to log on !).

 

4. Q: A long string exists when amoeba is started.

A: Check the configuration file.

Note: you are welcome to give your suggestions, questions, and comments.