Mysql主從復制和讀寫分離方案分析
引子
最近在研究Web服務端負載均衡方面的技術，參考網上資料，總體思路可以分為如下幾類：
1.應用服務器集群，典型的代表就是Nginx+Tomcat實現負載均衡；
2.數據庫集群。
本文主要關注數據庫集群。
實現思路
1.應用層解決方案
通過應用層對數據源做路由來實現讀寫分離，項目是SpringMVC+myBatis，SQL路由交給Spring，通過AOP或者Annotation由代碼顯示的控制Datasource。
優點是路由策略的擴展性和可控性較強。
缺點是耦合到Spring；需要加入控制代碼。
2.中間件解決方案
通過mysql中間件做主從集群，Mysql Proxy、Amoeba、Atlas等中間件貌似都能符合需求。
優點是與應用層解耦。
缺點是增加一個服務維護的風險點，性能及穩定性待測試，需要支持代碼強制主從和事務。
3.驅動解決方案
Mysql自帶的ReplicationDriver提供主從庫訪問的驅動，是通過保持多個數據源的鏈接并根據ReadOnly True/False來選擇數據源。相當于應用層解決方案的一個現有實現，擴展性更弱。并且貌似不能使用其他驅動。由于耦合較高暫不考慮。
三種實現思路關鍵技術
1.在應用層使用Spring對數據源做路由，關鍵字：Spring AOP；
2.增加中間代理層，Amoeba就屬于這種情況，此外還有Mysql官方提供的Mysql Proxy；
3.在驅動層使用Mysql提供的主從庫訪問驅動，直接與數據庫連接驅動耦合，擴展性弱，目前還未做原型嘗試。
綜合上述分析，考慮到需要與應用層解耦，現采用中間件解決方案，使用Amoeba做SQL路由，實現數據庫讀寫分離。
既然選擇使用Amoeba，讓我們先了解什么是Amoeba？它能做什么？要怎么做？最后再看看它不能做什么。
Amoeba
Amoeba是什么
Amoeba(變形蟲)項目,該開源框架于2008年開始發布一款Amoeba for Mysql軟件。詳細資料可參閱Amoeba官方文檔（國內或許不能訪問）。
Amoeba能做什么
Amoeba致力于MySQL的分布式數據庫前端代理層，它主要在應用層訪問MySQL的時候充當SQL路由功能，專注于分布式數據庫代理層 （Database Proxy）開發。座落與 Client、DB Server(s)之間，對客戶端透明。具有負載均衡、高可用性、SQL過濾、讀寫分離、可路由相關的到目標數據庫、可并發請求多臺數據庫合并結果。 通過Amoeba你能夠完成多數據源的高可用、負載均衡、數據切片的功能。
Amoeba不能做什么
既然知道Amoeba能為我們解決什么問題，也要做到Amoeba不擅長的事情。這樣在具體項目技術方案選擇時，方能權衡考慮。Amoeba對于以下幾點暫時無能為力：
1.目前還不支持事務；
2.暫時不支持存儲過程，官方說近期會支持；
3.不適合從Amoeba導數據的場景或者對大數據量查詢的query并不合適，比如一次請求返回10w以上甚至更多數據的場合；
4.暫時不支持分庫分表，amoeba目前只做到分數據庫實例，每個被切分的節點需要保持庫表結構一致。
若實際項目中所需要的功能正式Amoeba的短板，建議使用Mysql Proxy作為中間件，或者在應用層通過程序控制數據源，手動實現數據庫讀寫分離。
原型環境
1.服務器A
IP: 1XX.XX.XX.181
運行Mysql主數據庫和Amoeba。
2.服務器B
IP: 1XX.XX.XX.182
運行Mysql從數據庫。
3.服務器C
IP: 1XX.XX.XX.183
運行Mysql從數據庫。
OS版本。
[root@chenllcentos ~]# cat /etc/redhat-release CentOS release 6.5 (Final)
具體實現
Mysql數據庫讀寫分離的具體實現主要包括兩個部分配置，即數據主從復制和Amoeba代理，現分別進行介紹。
主從復制
為什么要進行主從復制呢，其實很容易理解，因為數據要同步啊。
查看服務器A是否已經安裝Mysql數據庫。
[root@chenllcentos ~]# rpm -aq | grep mysql
若無消息顯示，則進行Mysql安裝，否則跳過此步驟。
yum install -y mysql-server mysql mysql-devel mysql-libs
Mysql安裝完畢，默認開機不啟動Mysql服務。
[root@chenllcentos ~]# chkconfig --list | grep mysqld mysqld 0:關閉 1:關閉 2:關閉 3:關閉 4:關閉 5:關閉 6:關閉
現在我們更改下配置，讓Mysql開機啟動。
[root@chenllcentos ~]# chkconfig mysqld on [root@chenllcentos ~]# chkconfig --list | grep mysqld mysqld 0:關閉 1:關閉 2:啟用 3:啟用 4:啟用 5:啟用 6:關閉
接下來，設置Mysql賬戶密碼。
[root@chenllcentos ~]# mysqladmin -u root password 'yourpassword'
此時,可以用剛才設置的賬戶密碼登陸數據庫。
[root@chenllcentos ~]# mysql -uroot -pyourpassword
至此，Mysql數據庫安裝成功。同樣的，對服務器B和服務器C安裝Mysql數據庫，此處略去。接下來，開始進行數據庫主從復制的配置。
1.主數據庫配置
修改主數據庫配置文件my.cnf。
[root@chenllcentos ~]# vi /etc/my.cnf
新增如下標注內容：
[mysqld] max_connections=1000 binlog-ignore-db=mysql #新增 binlog-ignore-db=information_schema #新增 log-bin=mysql-bin #新增 server-id=1 #新增 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
關于新增的幾項配置，有什么作用呢？其中binlog-ignore-db用來指定忽略同步的數據庫，未指定的默認都進行主從復制。log-bin 指定數據庫操作日志，主從復制的過程本質就是從數據庫在主數據庫讀取該日志文件，并且再執行一次。server-id只要滿足在數據庫集群中不重復即可。
保存退出，重啟Mysqld服務，使配置生效。額外提個原則，凡是修改到配置文件，最好都重啟該配置相關的程序或服務。
[root@chenllcentos ~]# service mysqld restart 停止 mysqld： [確定] 正在啟動 mysqld： [確定]
登陸主數據庫。
[root@chenllcentos ~]# mysql -uroot -pyourpassword
查看主數據庫master狀態。
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000015 Position: 106 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema
可以看出，Binlog_Ignore_DB顯示的信息就是剛才我們在配置文件所配置的信息。此外，還有兩個重要的參數需要記下：mysql-bin.000015和106。從數據庫就是根據這兩個參數，完成主從復制，以達到數據同步的效果。
從數據庫要讀取主數據庫日志文件，需要主數據開放授權用戶。
mysql> GRANT REPLICATION SLAVE ON *.* to 'slave'@'1XX.XX.XX.182' identified by 'root' mysql> GRANT REPLICATION SLAVE ON *.* to 'slave1'@'1XX.XX.XX.183' identified by 'root'
進行從數據庫配置時，將使用到這兩個授權用戶。
出于數據安全性考慮，Mysql提供訪問權限控制，若以主機的方式遠程訪問數據庫，需要開啟相應權限。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'1XX.XX.XX.181' IDENTIFIED BY 'root' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'1XX.XX.XX.182' IDENTIFIED BY 'root' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'1XX.XX.XX.183' IDENTIFIED BY 'root' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;
最后，還需要修改iptables，對數據庫端口3306放行。
[root@chenllcentos ~]# vi /etc/sysconfig/iptables
新增如下語句：
# 放行Mysql端口 -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
至此，完成主數據庫配置。接下來，讓我們進行從數據庫配置。
2.從數據庫配置
從數據庫配置相對主數據配置相對簡單，主要包括配置文件修改和主從復制設置。現以服務器B為例進行說明。
修改從數據庫配置文件。
[mysqld] max_connections=1000 server-id=2 #新增 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
設置主從數據庫同步點。
mysql> change master to master_host='1XX.XX.XX.181',master_user='slave',master_password='root',master_log_file='mysql-bin.000015',master_log_pos=106;
還記得mysql-bin.000015和106這兩個參數嗎？沒錯，就是我們在主數據庫查看master狀態所顯示的信息。
啟動主從復制。
mysql> slave start;
查詢slave狀態。
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1XX.XX.XX.181 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000015 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000005 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000015 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: 106 Relay_Log_Space: 758 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:
只有當Slave_IO_Running和Slave_SQL_Running都顯示Yes時，才表示主從復制配置成功。否則失敗，檢查上述配置過程。
服務器C從數據庫的配置過程類似，此處略去。
主從復制驗證
首先，在主數據建立一個demo數據庫，看兩個從數據庫是否會自動進行復制。
在服務器A登錄主數據庫，查看現有數據庫。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+
現在，新增一個測試數據庫demo。
mysql> create database demo;
接下來，分別登錄服務器B和服務器C的從數據庫，查詢數據庫。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | demo | | mysql | | test | +--------------------+
可以發現，當主數據庫發生改動，從數據庫會相應同步，并且同步的過程是異步進行的。因此，可以驗證我們配置的主從復制已經生效。
Amoeba數據庫代理
Amoeba作為數據庫代理，以中間件的形式存在，拓撲圖如下所示：
圖片來源于Amoeba官網。
目前Amoeba for Mysql最新版本為amoeba-mysql-3.0.5-RC-distribution.zip。
安裝過程很簡單，只需要將zip壓縮包解壓至/usr/local/即可。若沒有安裝zip和unzip，可以通過centOS yum安裝。
[root@chenllcentos ~]# yum -y install zip unzip
接下來，解壓Amoeba壓縮包。
[root@chenllcentos ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip [root@chenllcentos ~]# cp -rf amoeba-mysql-3.0.5-RC /usr/local
啟動Amoeba。
[root@chenllcentos ~]# /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher
但是提示出現fatal exception：
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啟動失敗，要如何修改呢？
其實Amoeba已經考慮到這個問題，并將JVM參數配置寫在屬性文件里。現在，讓我們通過該屬性文件修改JVM參數。
修改jvm.properties文件JVM_OPTIONS參數。
[root@chenllcentos ~]# vi /usr/local/amoeba-mysql-3.0.5-RC/jvm.properties
將內容：
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
替換為：
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"
再次啟動Amoeba。
[root@chenllcentos ~]# /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher
若使用Amoeba完成讀寫分離，需要分別對dbServers.xml和amoeba.xml兩個配置文件進行配置。與在應用層實現讀寫分離不同，使用Amoeba實現讀寫分離只需要修改配置文件，并不會產生硬編碼耦合，有利于系統擴展和維護。
首先是配置dbServers.xml，主要是配置真實Mysql數據庫連接信息。
<?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節點abstractive="true"，包含Mysql的公共配置信息，其他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 -->
<!-- Mysql默認端口 -->
<property name="port">3306</property>
<!-- mysql schema -->
<!-- 默認連接的數據庫，若不存在需要事先創建，否則Amoeba啟動報錯 -->
<property name="schema">test</property>
<!-- mysql user -->
<property name="user">root</property>
<property name="password">root</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>
<!-- master節點繼承abstractServer -->
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- master數據庫主機地址 -->
<property name="ipAddress">1XX.XX.XX.181</property>
</factoryConfig>
</dbServer>
<!-- slave節點繼承abstractServer -->
<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- slave數據庫主機地址 -->
<property name="ipAddress">1XX.XX.XX.182</property>
</factoryConfig>
</dbServer>
<!-- slave1節點繼承abstractServer -->
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<!-- slave1數據庫主機地址 -->
<property name="ipAddress">1XX.XX.XX.183</property>
</factoryConfig>
</dbServer> 1XX.XX.XX.181
1XX.XX.XX.185
<!-- 配置數據庫讀取連接池 --> <dbServer name="readPool" 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,slave1</property> </poolConfig> </dbServer>
</amoeba:dbServers>
可以看出，對dbServers.xml文件的配置，主要就是對dbServer節點的配置。其中，readPool節點需要特別注意，因為Amoeba實現讀寫分離就是根據它來實現。
接下來是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">1XX.XX.XX.181</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">root</property>
<property name="password">aroot</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">readPool</property>
<property name="needParse">true</property>
</queryRouter>
</amoeba:configuration> 在amoeba.xml中，主要完成連接信息和SQL路由配置。在queryRouter節點中，通過配置writePool和readPool可以實現讀寫分離。
配置完成后，重啟Amoeba。
[root@chenllcentos ~]# /usr/local/amoeba-mysql-3.0.5-RC/bin/shutdown [root@chenllcentos ~]# /usr/local/amoeba-mysql-3.0.5-RC/bin/launcher
至此，Mysql主從復制和使用Amoeba實現數據庫讀寫分離全部配置完成。
讀寫分離驗證
接下來，進行簡單測試，驗證以上配置是否能夠正確運行。
登錄master主數據庫。
[root@chenllcentos ~]# mysql -uroot -pyourpassword -h1XX.XX.XX.181 -P8066
額外說明下，此處的yourpassword是連接Amoeba的密碼，也就是在amoeba.xml配置文件中配置的密碼，與Mysql密碼不同，需要注意。
登陸后，此時會提示以下信息。
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution
說明已經成功連接Mysql代理Amoeba。
為了驗證Amoeba讀寫分離配置是否生效，我們做一個簡單的測試。
先在181服務器master服務器上創建一個表。
mysql> create table sxit (id int(10) ,name varchar(10));
而后，分別停止服務器B和服務器C兩個從數據庫的主從復制，便于數據庫操作觀察。
登陸服務器B從數據庫。
[root@chenllcentos ~]# mysql -uroot -pyourpassword
停止從數據庫主從復制。
mysql> slave stop;
登陸服務器C從數據庫。
[root@chenllcentos ~]# mysql -uroot -pyourpassword
停止從數據庫主從復制。
mysql> slave stop;
在主數據庫插入。
mysql> insert into sxit values('1','zhangsan'); 在從數據庫B插入。
mysql> insert into sxit values('2','lisi'); 在從數據庫C插入。
mysql> insert into sxit values('3','john'); 登陸到amoeba服務器,進行讀寫分離的測試:
[root@chenllcentos ~]# mysql -uroot -pyourpassword -h1XX.XX.XX.181 -P8066 mysql> use test; mysql> select * from sxit; +------+------+ | id | name | +------+------+ | 2 | lisi | +------+------+ mysql> select * from sxit; +------+------+ | id | name | +------+------+ | 3 | john | +------+------+
重復執行多次，發現始終只顯示從數據庫的數據，說明如果進行數據庫讀操作，Amoeba只將讀數據SQL命令路由至從數據庫。
登錄主數據庫。
[root@chenllcentos ~]# mysql -uroot -pyourpassword mysql> use test; mysql> select * from sxit; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+
可以驗證，使用Amoeba對Mysql讀寫分離成功。若此時開啟從數據庫主從復制，則可以進行Mysql集群和負載均衡。
小結
使用Amoeba做數據庫代理，對于應用層來說是透明的。所謂透明，可以這么簡單理解，是否使用代理，在應用層編碼上是沒有任何區別的，即使用代理的情況下，應用層和數據層能夠保持高度解耦。