使用amoeba实现mysql读写分离
本文章来源于：
http://www.ywlinux.com/post/60.html
欢迎访问查看，并点赞评论，谢谢！
实现环境
本次实现共4台设备；2台mysql+1台amoeba+1台访问测试机器；
机器安排
192.168.29.139 mysql-主库
192.168.29.140 mysql-从库
2台mysql已经实现mysql主从同步；
mysql主从同步参考下面网站：
http://www.ywlinux.com/post/59.html
192.168.29.133 amoeba
192.168.29.128 测试机
连接示意图：
测试机（web服务器）----> amoeba（读写分离192.168.29.133）----> mysql-主库（负责写 192.168.29.139）
----> mysql-从库（负责读 192.168.29.140）
关于系统中的账户密码说明：
amoeba 连接数据库使用用户名：amoeba，密码：dongbo；
mysql主从同步使用账号：用户名：tongbu，密码：dongbo；
mysql主从服务器root账号：用户名：root，密码：dongbo；
amoeba的登录账号：用户名：admin，密码：dongbo；
系统环境
[root@amoeba ~]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@amoeba ~]# uname -m
x86_64
[root@amoeba ~]# uname -r
2.6.32-358.el6.x86_64
[root@amoeba ~]# uname -a
Linux amoeba 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
主库上创建一个用户：
[root@mysql-master ~]# mysql -uroot -pdongbo
mysql> grant select,insert,update,delete on *.* to 'amoeba'@'192.168.29.%' identified by 'dongbo';
在从库上回收权限
mysql> revoke insert,update,delete on *.* from 'amoeba'@'192.168.29.%';
mysql> flush privileges;
amoba介绍
Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）
1.在Amoeba服务器下载安装jdk
jdk包可以到下面链接下载，因为文件比较大，建议下载后再上传到服务器；
http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
mkdir /home/dongbo/tools
cd /home/dongbo/tools
rz jdk-8u101-linux-x64.tar.gz 本地上传jdk
tar xf jdk-8u101-linux-x64.tar.gz
mv jdk1.8.0_101/ /usr/local/
ln -sv /usr/local/jdk1.8.0_101/ /usr/local/jdk
cat >>/etc/profile.d/java.sh <注释的范围，是否注视了配置文件）
vi amoeba.xml
11 8066 #修改amoeba启动端口
15 192.168.29.133 #修改为amoeba服务器ip地址
30 admin #登录amoeba账户
32 dongbo #定义amoeba登录密码
启动amoeba
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start
此时可以测试一下账户密码是否可以登录amoeba
到主从服务器连接amoeba测试是否可以正常连接：
[root@mysql-node1 ~]# mysql -u admin -pdongbo -h 192.168.29.133 -P 8066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1878327798
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0
mysql> exit
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba stop
修改dbServers.xml，设置数据库、登陆MySQL的帐号和密码（注意<-- -->注释的范围，是否注视了配置文件）
vi dbServers.xml
20 3306 #数据库端口
22
23 ywlinux #哪个库
25
26 amoeba #库的用户
28 注释的范围，是否注视了配置文件）
45
46
47
48 192.168.29.139
49
50
51
52
53
54
55 192.168.29.140
56
57
修改dbServers.xml，设置ROUNDROBIN（轮询策略）；（注意<-- -->注释的范围，是否注视了配置文件）
59
60
61
62 1
63
64
65 Slave1 #如果有多个库都列在slave1后，进行轮训
修改amoeba.xml，设置读写分离，修改queryRouter标签下的；（注意<-- -->注释的范围，是否注视了配置文件）
115 Master #修改amoeba指向后端节点的数据库
118 Master
119 virtualslave
启动amoeba
报错：
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start
[1] 23375
[root@amoeba conf]# Error: JAVA_HOME environment variable is not set.
[1]+ Exit 1 /usr/local/amoeba/bin/amoeba
解决：
[root@amoeba conf]# vi /etc/profile
[root@amoeba conf]# tail -5 /etc/profile
export JAVA_HOME=/usr/local/jdk
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba conf]# source /etc/profile
[root@amoeba conf]# amoeba start
The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
解决：
vi /usr/local/amoeba/bin/amoeba
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"
启动amoeba
[root@amoeba amoeba]# /usr/local/amoeba/bin/amoeba start &
[1] 24480
[root@amoeba amoeba]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2016-06-05 01:04:22,545 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2016-06-05 01:04:23,148 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2016-06-05 01:04:23,160 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /192.168.29.133:13243.
[root@amoeba amoeba]# ps aux|grep amoeba
root 24593 3.4 6.4 1079552 65356 pts/2 Sl 01:27 0:01 /usr/local/jdk/bin/java -server -Xms256m -Xmx256m -Xss512k -Damoeba.home=/usr/local/amoeba -Dclassworlds.conf=/usr/local/amoeba/bin/amoeba.classworlds -classpath /usr/local/amoeba/lib/classworlds-1.0.jar org.codehaus.classworlds.Launcher start
测试连接
在其他服务器上连接amoba测试：
[root@mysql-master ~]# mysql -u admin -p -h 192.168.29.133 -P 9066
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| ywlinux |
+--------------------+
5 rows in set (0.97 sec)
如果报错：
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1423556186
Current database: *** NONE ***
ERROR 2013 (HY000): Lost connection to MySQL server during query
[root@amoeba amoeba]# java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:633)
at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:633)
at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
查看amoeba日志
[root@amoeba amoeba]# cat /usr/local/amoeba/logs/root.log
2016-06-05 02:04:40,234 INFO net.BackendConnectionFactory - open socket channel to server[192.168.29.140:3306] success!
2016-06-05 02:04:40,237 ERROR net.MysqlServerConnection - handShake with /192.168.29.140:3306 error:Access denied for user 'amoeba'@'192.168.29.133' (using password: NO),hashCode=724198718
2016-06-05 02:04:40,304 INFO net.BackendConnectionFactory - open socket channel to server[192.168.29.139:3306] success!
2016-06-05 02:04:40,306 ERROR net.MysqlServerConnection - handShake with /192.168.29.139:3306 error:Access denied for user 'amoeba'@'192.168.29.133' (using password: NO),hashCode=1390753379
2016-06-05 02:04:43,238 INFO net.BackendConnectionFactory - open socket channel to server[192.168.29.140:3306] success!
2016-06-05 02:04:43,242 ERROR net.MysqlServerConnection - handShake with /192.168.29.140:3306 error:Access denied for user 'amoeba'@'192.168.29.133' (using password: NO),hashCode=71137882
上面的提示是'amoeba'@'192.168.29.133' 使用空密码来访问mysql/192.168.29.140:3306 ，所以可能是配置文件中的密码被注释掉了；
验证读写分离
在192.168.29.128 上连接amoeba
[root@zabbix ~]# mysql -uadmin -pdongbo -h 192.168.29.133 -P 9066
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| ywlinux |
+--------------------+
mysql> use ywlinux;
mysql> insert into dongbo values(1),(2),(3);
Query OK, 3 rows affected (0.66 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看amoeba上数据ywlinux.dongbo；
mysql> select * from ywlinux.dongbo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
查看mysql主库上也有，从库上也一样有
将从库停止同步；
mysql> slave stop;
Query OK, 0 rows affected (0.05 sec)
在amoeba上重新插入新数据并读取测试：
mysql> insert into dongbo values(5),(6);
Query OK, 2 rows affected (1.31 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from ywlinux.dongbo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
在启动从库；
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
在amoeba上查看同步情况；
mysql> select * from ywlinux.dongbo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
+------+
到此使用amoeba实现mysql主从读写分离已经完成；
本文章来源于：
http://www.ywlinux.com/post/60.html
欢迎访问查看，并点赞评论，谢谢！
实现环境
本次实现共4台设备；2台mysql+1台amoeba+1台访问测试机器；
机器安排
192.168.29.139 mysql-主库
192.168.29.140 mysql-从库
2台mysql已经实现mysql主从同步；
mysql主从同步参考下面网站：
http://www.ywlinux.com/post/59.html
192.168.29.133 amoeba
192.168.29.128 测试机
连接示意图：
测试机（web服务器）----> amoeba（读写分离192.168.29.133）----> mysql-主库（负责写 192.168.29.139）
----> mysql-从库（负责读 192.168.29.140）
关于系统中的账户密码说明：
amoeba 连接数据库使用用户名：amoeba，密码：dongbo；
mysql主从同步使用账号：用户名：tongbu，密码：dongbo；
mysql主从服务器root账号：用户名：root，密码：dongbo；
amoeba的登录账号：用户名：admin，密码：dongbo；
系统环境
[root@amoeba ~]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@amoeba ~]# uname -m
x86_64
[root@amoeba ~]# uname -r
2.6.32-358.el6.x86_64
[root@amoeba ~]# uname -a
Linux amoeba 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
主库上创建一个用户：
[root@mysql-master ~]# mysql -uroot -pdongbo
mysql> grant select,insert,update,delete on *.* to 'amoeba'@'192.168.29.%' identified by 'dongbo';
在从库上回收权限
mysql> revoke insert,update,delete on *.* from 'amoeba'@'192.168.29.%';
mysql> flush privileges;
amoba介绍
Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、高可用性等需求。与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）
1.在Amoeba服务器下载安装jdk
jdk包可以到下面链接下载，因为文件比较大，建议下载后再上传到服务器；
http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
mkdir /home/dongbo/tools
cd /home/dongbo/tools
rz jdk-8u101-linux-x64.tar.gz 本地上传jdk
tar xf jdk-8u101-linux-x64.tar.gz
mv jdk1.8.0_101/ /usr/local/
ln -sv /usr/local/jdk1.8.0_101/ /usr/local/jdk
cat >>/etc/profile.d/java.sh <注释的范围，是否注视了配置文件）
vi amoeba.xml
11 8066 #修改amoeba启动端口
15 192.168.29.133 #修改为amoeba服务器ip地址
30 admin #登录amoeba账户
32 dongbo #定义amoeba登录密码
启动amoeba
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start
此时可以测试一下账户密码是否可以登录amoeba
到主从服务器连接amoeba测试是否可以正常连接：
[root@mysql-node1 ~]# mysql -u admin -pdongbo -h 192.168.29.133 -P 8066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1878327798
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0
mysql> exit
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba stop
修改dbServers.xml，设置数据库、登陆MySQL的帐号和密码（注意<-- -->注释的范围，是否注视了配置文件）
vi dbServers.xml
20 3306 #数据库端口
22
23 ywlinux #哪个库
25
26 amoeba #库的用户
28 注释的范围，是否注视了配置文件）
45
46
47
48 192.168.29.139
49
50
51
52
53
54
55 192.168.29.140
56
57
修改dbServers.xml，设置ROUNDROBIN（轮询策略）；（注意<-- -->注释的范围，是否注视了配置文件）
59
60
61
62 1
63
64
65 Slave1 #如果有多个库都列在slave1后，进行轮训
修改amoeba.xml，设置读写分离，修改queryRouter标签下的；（注意<-- -->注释的范围，是否注视了配置文件）
115 Master #修改amoeba指向后端节点的数据库
118 Master
119 virtualslave
启动amoeba
报错：
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start
[1] 23375
[root@amoeba conf]# Error: JAVA_HOME environment variable is not set.
[1]+ Exit 1 /usr/local/amoeba/bin/amoeba
解决：
[root@amoeba conf]# vi /etc/profile
[root@amoeba conf]# tail -5 /etc/profile
export JAVA_HOME=/usr/local/jdk
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba conf]# source /etc/profile
[root@amoeba conf]# amoeba start
The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
解决：
vi /usr/local/amoeba/bin/amoeba
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k"
启动amoeba
[root@amoeba amoeba]# /usr/local/amoeba/bin/amoeba start &
[1] 24480
[root@amoeba amoeba]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2016-06-05 01:04:22,545 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2016-06-05 01:04:23,148 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2016-06-05 01:04:23,160 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /192.168.29.133:13243.
[root@amoeba amoeba]# ps aux|grep amoeba
root 24593 3.4 6.4 1079552 65356 pts/2 Sl 01:27 0:01 /usr/local/jdk/bin/java -server -Xms256m -Xmx256m -Xss512k -Damoeba.home=/usr/local/amoeba -Dclassworlds.conf=/usr/local/amoeba/bin/amoeba.classworlds -classpath /usr/local/amoeba/lib/classworlds-1.0.jar org.codehaus.classworlds.Launcher start
测试连接
在其他服务器上连接amoba测试：
[root@mysql-master ~]# mysql -u admin -p -h 192.168.29.133 -P 9066
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| ywlinux |
+--------------------+
5 rows in set (0.97 sec)
如果报错：
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1423556186
Current database: *** NONE ***
ERROR 2013 (HY000): Lost connection to MySQL server during query
[root@amoeba amoeba]# java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:633)
at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
at com.meidusa.amoeba.net.poolable.GenericObjectPool.borrowObject(GenericObjectPool.java:381)
at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:633)
at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
at com.meidusa.amoeba.mysql.net.MysqlClientConnection$2.run(MysqlClientConnection.java:291)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
查看amoeba日志
[root@amoeba amoeba]# cat /usr/local/amoeba/logs/root.log
2016-06-05 02:04:40,234 INFO net.BackendConnectionFactory - open socket channel to server[192.168.29.140:3306] success!
2016-06-05 02:04:40,237 ERROR net.MysqlServerConnection - handShake with /192.168.29.140:3306 error:Access denied for user 'amoeba'@'192.168.29.133' (using password: NO),hashCode=724198718
2016-06-05 02:04:40,304 INFO net.BackendConnectionFactory - open socket channel to server[192.168.29.139:3306] success!
2016-06-05 02:04:40,306 ERROR net.MysqlServerConnection - handShake with /192.168.29.139:3306 error:Access denied for user 'amoeba'@'192.168.29.133' (using password: NO),hashCode=1390753379
2016-06-05 02:04:43,238 INFO net.BackendConnectionFactory - open socket channel to server[192.168.29.140:3306] success!
2016-06-05 02:04:43,242 ERROR net.MysqlServerConnection - handShake with /192.168.29.140:3306 error:Access denied for user 'amoeba'@'192.168.29.133' (using password: NO),hashCode=71137882
上面的提示是'amoeba'@'192.168.29.133' 使用空密码来访问mysql/192.168.29.140:3306 ，所以可能是配置文件中的密码被注释掉了；
验证读写分离
在192.168.29.128 上连接amoeba
[root@zabbix ~]# mysql -uadmin -pdongbo -h 192.168.29.133 -P 9066
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| ywlinux |
+--------------------+
mysql> use ywlinux;
mysql> insert into dongbo values(1),(2),(3);
Query OK, 3 rows affected (0.66 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看amoeba上数据ywlinux.dongbo；
mysql> select * from ywlinux.dongbo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
查看mysql主库上也有，从库上也一样有
将从库停止同步；
mysql> slave stop;
Query OK, 0 rows affected (0.05 sec)
在amoeba上重新插入新数据并读取测试：
mysql> insert into dongbo values(5),(6);
Query OK, 2 rows affected (1.31 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from ywlinux.dongbo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
在启动从库；
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
在amoeba上查看同步情况；
mysql> select * from ywlinux.dongbo;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
+------+
到此使用amoeba实现mysql主从读写分离已经完成；
编辑回复