mysql+amoeba讀寫分離

mysql+amoeba讀寫分離
一 簡介：
Amoeba是一個以MySQL為底層數據存儲，并對應用提供MySQL協議接口的proxy。它集中地響應應用的請求，依據用戶事先設置的規則，將SQL請求發送到特定的數據庫上執行。基于此可以實現負載均衡、讀寫分離、高可用性等需求。與MySQL官方的MySQL Proxy相比，作者強調的是amoeba配置的方便（基于XML的配置文件，用SQLJEP語法書寫規則，比基于lua腳本的MySQL Proxy簡單）。
Amoeba相當于一個SQL請求的路由器，目的是為負載均衡、讀寫分離、高可用性提供機制，而不是完全實現它們。用戶需要結合使用MySQL的 Replication等機制來實現副本同步等功能。amoeba對底層數據庫連接管理和路由實現也采用了可插撥的機制，第三方可以開發更高級的策略類來替代作者的實現。這個程序總體上比較符合KISS原則的思想。
優勢
Amoeba主要解決以下問題：
a). 數據切分后復雜數據源整合
b). 提供數據切分規則并降低數據切分規則給數據庫帶來的影響
c). 降低數據庫與客戶端連接
d).?讀寫分離路由
不足
a)、目前還不支持事務
b)、暫時不支持存儲過程（近期會支持）
c)、不適合從amoeba導數據的場景或者對大數據量查詢的query并不合適（比如一次請求返回10w以上甚至更多數據的場合）
d)、暫時不支持分庫分表，amoeba目前只做到分數據庫實例，每個被切分的節點需要保持庫表結構一致：
二 準備
1三臺centos7系統（注意我是安裝的minimal的系統，需要通yum安裝一些依賴包如：,yum -y groupinstall development tools，現網建議安裝DVD版）
Amoeba:192.168.161.141
Msysql master:192.168.161.142
Mysql slave:192.168.161.143
2 Amoeba上安裝jdk-7u80-linux-x64.tar.gz、amoeba-mysql-3.0.5-RC-distribution.zip
3 Mysql上面安裝MySQL-client-5.6.6_m9-1.rhel5.x86_64.rpm、
MySQL-server-5.6.6_m9-1.rhel5.x86_64.rpm
三 關閉防火墻和selinux
systemctl stop firewalld
systemctl disable firewalld

setenforce 0

四 在161.142和161.143上面安裝mysql，并配置主從同步。
1 161.142和161.143上面都安裝mysql
rpm -ivh MySQL-server-5.6.6_m9-1.rhel5.x86_64.rpm
rpm -ivh MySQL-client-5.6.6_m9-1.rhel5.x86_64.rpm
2修改配置文件
Mysql master（161.142）：
[root@localhost home]# cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-ignore-db=information_schema #新增的配置,忽略information_schema 的同步

Mysql slave（161.143）：
[root@localhost home]# cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=2

3 啟動兩臺mysql，配置主從
（1）啟動mysql:
systemctl start mysql
（2）修改mysql的默認密碼：
mysqladmin -uroot password '123'
（3）進入161.142 mysql master：
[root@localhost home]# mysql -uroot -p123
mysql> show master status;
+------------------+----------+--------------+--------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------+-------------------+
| mysql-bin.000004 | 120 | | information_schema | |
+------------------+----------+--------------+--------------------+-------------------+
1 row in set (0.00 sec)
（4）進入161.143 mysql slave：
執行：
配置slave服務器：
change master to master_host='192.168.161.142',master_user='root',master_password='123',
master_log_file='mysql-bin.000004',master_log_pos=120;
啟動從服務器的復制功能：
start slave;
查看從服務器復制的狀態：
mysql> show slave status\G'
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.161.142
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000009
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 120
Relay_Log_Space: 623
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: 1
Master_UUID: f2e02c4b-f6c3-11e7-a14e-66ab28c66abe
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Slave_IO_Running: Yes，Slave_SQL_Running: Yes 兩個都為也是則沒有問題。

（5）測試：略
五 在161.141上面配置amoeba
1 161.141上面搭建Java環境
(1)解壓Java包
tar -xzvf jdk-7u80-linux-x64.tar.gz
（2）配置環境變量
修改配置文件：
Vi /etc/profile

JAVA_HOME=/home/jdk1.7.0_80
CLASSPATH=.:$JAVA_HOME/lib.tools.jar
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME CLASSPATH PATH

使配置文件立即生效：
source /etc/profile

驗證Java：
[root@localhost home]# java -version
java version "1.7.0_80"
Java(TM) SE Runtime Environment (build 1.7.0_80-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.80-b11, mixed mode)

2 配置amoeba配置文件
（1）下載
wget -c
https://ncu.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip

（2）解壓
unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/amoeba
（如沒有unzip命令請自行安裝：yum install -y unzip zip）

（3）給amoeba授權mysql遠程賬戶（不推薦使用root）
grant all on . to amoeba@"%" identified by "amoeba";
flush privileges;
（4）修改配置文件
Amoeba做讀寫分離只需要修改dbServers.xml、amoeba.xml 這兩個配置文件

[root@localhost amoeba-mysql-3.0.5-RC]# cd /usr/local/amoeba/amoeba-mysql-3.0.5-RC/conf/
[root@localhost conf]# ls
access_list.conf amoeba.dtd amoeba.xml dbserver.dtd dbServers.xml function.dtd functionMap.xml log4j.dtd log4j.xml rule.dtd ruleFunctionMap.xml rule.xml

修改dbServers.xml
[root@localhost conf]# cat dbServers.xml
<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

    <!-- 
        Each dbServer needs to be configured into a Pool,
        If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
         add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
         such as 'multiPool' dbServer   
    -->

<dbServer name="abstractServer" abstractive="true">
    <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
        <property name="connectionManager">${defaultManager}</property>
        <property name="sendBufferSize">64</property>
        <property name="receiveBufferSize">128</property>

        <!-- mysql port -->
        <property name="port">3306</property>

        <!-- mysql schema -->
        <property name="schema">test</property>

        <!-- mysql user -->
        <property name="user">amoeba</property>#設置amoeba連接后端數據庫服務器的賬號和密碼，因此需要在所有后端數據庫上創建該用戶，并授權amoeba服務器可連接

        <property name="password">amoeba</property>
    </factoryConfig>

    <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool">
        <property name="maxActive">500</property>
        <property name="maxIdle">500</property>
        <property name="minIdle">1</property>
        <property name="minEvictableIdleTimeMillis">600000</property>
        <property name="timeBetweenEvictionRunsMillis">600000</property>
        <property name="testOnBorrow">true</property>
        <property name="testOnReturn">true</property>
        <property name="testWhileIdle">true</property>
    </poolConfig>
</dbServer>

<dbServer name="master"  parent="abstractServer">
    <factoryConfig>
        <!-- mysql ip -->
        <property name="ipAddress">192.168.161.142</property>
    </factoryConfig>
</dbServer>

<dbServer name="slave"  parent="abstractServer">
    <factoryConfig>
        <!-- mysql ip -->
        <property name="ipAddress">192.168.161.143</property>
    </factoryConfig>
</dbServer>

<dbServer name="multiPool" virtual="true">
    <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
        <property name="loadbalance">1</property>

        <!-- Separated by commas,such as: server1,server2,server1 -->
        <property name="poolNames">slave</property>
    </poolConfig>
</dbServer>

</amoeba:dbServers>
[root@localhost conf]#

修改amoeba.xml
[root@localhost conf]# more amoeba.xml
<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<proxy>

    <!-- service class must implements com.meidusa.amoeba.service.Service -->
    <service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">
        <!-- port -->
        <property name="port">8066</property>

        <!-- bind ipAddress -->
        <!-- 
        <property name="ipAddress">127.0.0.1</property>
         -->

        <property name="connectionFactory">
            <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
                <property name="sendBufferSize">128</property>
                <property name="receiveBufferSize">64</property>
            </bean>
        </property>

        <property name="authenticateProvider">
            <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                <property name="user">amoeba</property>#提供客戶端連接amoeba時需要使用這里設定的賬號 (這里的賬號密碼和amoeba連接后端數據庫服務器的密碼無關)

                <property name="password">amoeba</property>

                <property name="filter">
                    <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
                        <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                    </bean>
                </property>
            </bean>
        </property>

    </service>

    <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">

        <!-- proxy server client process thread size -->
        <property name="executeThreadSize">128</property>

        <!-- per connection cache prepared statement size  -->
        <property name="statementCacheSize">500</property>

        <!-- default charset -->
        <property name="serverCharset">utf8</property>

        <!-- query timeout( default: 60 second , TimeUnit:second) -->
        <property name="queryTimeout">60</property>
    </runtime>

</proxy>

<!-- 
    Each ConnectionManager will start as thread
    manager responsible for the Connection IO read , Death Detection
-->
<connectionManagerList>
    <connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper">
        <property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property>
    </connectionManager>
</connectionManagerList>

    <!-- default using file loader -->
<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
    <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
    <property name="ruleLoader">
        <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
            <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
            <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
        </bean>
    </property>
    <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
    <property name="LRUMapSize">1500</property>
    <property name="defaultPool">master</property>

    <property name="writePool">master</property>
    <property name="readPool">slave</property> #這兩行原先的注釋要去掉

    <property name="needParse">true</property>
</queryRouter>

</amoeba:configuration>
（5）啟動amoeba
[root@server3 amoeba]# bin/launcher?
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.?
錯誤文字上看，應該是由于stack size太小，導致JVM啟動失敗，修改jvm.properties文件JVM_OPTIONS參數?
jvm.properties（/usr/local/amoeba/amoeba-mysql-3.0.5-RC）
將
? ? ? ? JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
改為
? ? ? ? JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
解決jdk7以上要求的啟動xss參數。

[root@server3 amoeba]# netstat -unlpt | grep java ##查看監聽的端口?
tcp 0 0 :::8066 :::* LISTEN 1506/java

啟動/關閉amoeba
? ? ? ? 最好先前臺啟動，檢查沒有錯誤之后再后臺啟動。
關閉
? ? ? ? # /usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/shutdown
啟動
? ? ? ? # /usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/launcher
后臺啟動并把日志保存到/var/log/amoeba.log
? ? ? ? # /usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/launcher > /var/log/amoeba.log 2>&1 &

（6）注意：
dbServers.xml、amoeba.xml 配置文件中的賬號密碼都是mysql數據庫中的賬號，填寫不對會出錯:
ERROR 1000 (42S02): Access denied for user 'amoeba'@'192.168.161.142:57952'(using password: YES)

六測試：
1 在161.142上面連接amoeba
（1）關掉master 數據庫161.142，測試是否只讀
systemctl stop mysql
[root@localhost etc]# mysql -uamoeba -pamoeba -h292.168.161.141 -P8066
mysql> use test;
Database changed
mysql> select * from b ;
+------+------+
| sf | ff |
+------+------+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
+------+------+
3 rows in set (0.01 sec)

mysql> insert into b values(5,5);
ERROR 1044 (42000): Amoeba could not connect to MySQL server[192.168.161.142:3306],Connection refused
mysql>

（2）關掉slave數據庫161.143，測試是否只能寫,不能讀
mysql> use test;
Database changed
mysql> select * from b ;
ERROR 1044 (42000): Amoeba could not connect to MySQL server[192.168.161.143:3306],Connection refused
mysql> insert into b values(5,5);
Query OK, 1 row affected (0.03 sec)
七 借鑒網址
http://blog.csdn.net/oufua/article/details/77373851
http://blog.csdn.net/sds15732622190/article/details/69262632
http://blog.csdn.net/Mryiyi/article/details/73521861