amoeba mysql读写分离测试

在搭建完成后，需要对搭建的环境进行读写分离测试；

1.1部署环境：

masterDB：192.*.*.193

slaveDB：192.*.*.194

amoeba:192.*.*.195

在这之前我们在193和194部署了主从复制：193为master，194为slave；

信息分别如下：

193：

mysql> show master status;
+------------------+----------+--------------+----------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 |
+------------------+----------+--------------+----------------------------------+
| mysql-bin.000005 |      107 |              | information_schema,cluster,mysql |
+------------------+----------+--------------+----------------------------------+

193：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.101.218.193
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000014
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 559
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 193
1 row in set (0.00 sec)

1.2测试开始：

在195登录：

mysql -h196.*.*.195 -p123456 -P8066

Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MySQL Community Server (GPL)

则登录成功；

在启动amoeba进入bin目录：

./launcher

[root@localhost bin]# ./launcher
 2017-11-01 02:09:18 [INFO] Project Name=Amoeba-MySQL, PID=53966 , starting...
log4j:WARN log4j config load completed from file:/usr/local/amoeba/amoeba/conf/log4j.xml
2017-11-01 02:09:18,954 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
log4j:WARN ip access config load completed from file:/usr/local/amoeba/amoeba/conf/access_list.conf
2017-11-01 02:09:19,198 INFO  net.ServerableConnectionManager - Server listening on /10.101.218.195:8066.
 2017-11-01 02:35:45 [INFO] Project Name=Amoeba-MySQL, PID=54054 , starting...
log4j:WARN log4j config load completed from file:/usr/local/amoeba/amoeba/conf/log4j.xml
2017-11-01 02:35:46,024 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
log4j:WARN ip access config load completed from file:/usr/local/amoeba/amoeba/conf/access_list.conf
2017-11-01 02:35:46,270 INFO  net.ServerableConnectionManager - Server listening on /10.101.218.195:8066.
 2017-11-01 02:51:27 [INFO] Project Name=Amoeba-MySQL, PID=54097 , starting...
log4j:WARN log4j config load completed from file:/usr/local/amoeba/amoeba/conf/log4j.xml
2017-11-01 02:51:27,734 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
log4j:WARN ip access config load completed from file:/usr/local/amoeba/amoeba/conf/access_list.conf
2017-11-01 02:51:27,972 INFO  net.ServerableConnectionManager - Server listening on /10.101.218.195:8066.

1.3在193上创建测试使用数据库：id=“003”

amoeba_db;

创建表porduct；

mysql> select * from product;
+-----+-------+-------+
| id  | name  | price |
+-----+-------+-------+
| 001 | ????  |  1000 |
| 002 | apple |  5000 |
| 003 | pear  |  3000 |
+-----+-------+-------+
3 rows in set (0.00 sec)

在194上查询：

mysql> select * from product;
+-----+-------+-------+
| id  | name  | price |
+-----+-------+-------+
| 001 | ????  |  1000 |
| 002 | apple |  5000 |
+-----+-------+-------+

在195上查询：

mysql> select * from product;
+-----+-------+-------+
| id  | name  | price |
+-----+-------+-------+
| 001 | ????  |  1000 |
| 002 | apple |  5000 |
+-----+-------+-------+
2 rows in set (0.00 sec)

由于我们在插入第三条数据的时候关闭了slave，故194上未同步003号数据；我们在195上查询，只读到了两条数据：说明查询的是194上的数据而非193的数据；

自此，读写已经分离；

另外，我们停止193的mysql服务，在195仍然可以读到两条数据；但是写入的时候失败：

mysql> insert into product  values("004","apple",5000);
ERROR 1044 (42000): Amoeba could not connect to MySQL server[1.*.*193:3306],Connection refused

测试完毕。

参考文献：http://www.cnblogs.com/liuyisai/p/6009379.html

http://heylinux.com/archives/1004.html