mysql讀寫分離策略

標籤：style   blog   http   io   color   os   ar   使用   sp   

為mysql做讀寫分離有兩個步驟：第一步為mysql配置主從複製；第二步用mysql-proxy為mysql做讀寫分離。

 

mysql主從複製

為了保證mysql主從複製搭建成功，最好保證兩台機器上mysql的版本一致。 在mysql中輸入如下命令查看版本資訊。

select version();

我使用的兩台mysql地址分別為192.168.10.42和192.168.10.49。42上的mysql作為master使用，49上的mysql作為slave使用。

主要資料庫配置

登入192.168.10.42，找到mysql的設定檔my.cnf。開啟my.cnf在[mysqld]下面加入如下內容

log-slow-queries=mysql-slow.loglog-error=mysql.errlog-bin=mysql-binserver-id=1

上面的配置主要是為了開啟主要資料庫的二進位日誌功能，在添加時最好先瀏覽一下my.cnf這個檔案，因為有的屬性可能已經存在了。添加完成後儲存退出，然後重啟mysql。

 

service mysqld restart

 

mysql重啟完成後，以root登入進去，給從資料庫分配主要資料庫的複製許可權，命令如下

 

grant replication slave on *.* to ‘user‘@‘192.168.10.49‘ identified by `password`;

 

其中“*.*”表示從資料庫能夠複製主要資料庫上的哪些庫和哪些表，“user”和“password”是主要資料庫分配給從資料庫的使用者名稱和密碼，根據需求自己設定，這裡僅作樣本使用。設定從資料庫時要用到這個使用者名稱和密碼。

 

許可權分配後輸入如下命令可以查看到剛才設定的使用者名稱，密碼和ip地址資訊。

 

select host,user,password from mysql.user;

 

然後查看一下主要資料庫的狀態資訊，輸入如下命令

show master status;

記住其中的File和Position欄位的值，配置從資料庫時會使用到。

 

從資料庫配置

 

登入到191.168.10.49，找到mysql的設定檔my.cnf，在[mysqld]下面加入如下內容

log-slow-queries=mysql-slow.loglog-error=mysql.errlog-bin=mysql-binserver-id=10

添加前最好瀏覽下這個檔案的原有配置，如果已經配置夠就不用再配置了。儲存退出後，重啟mysql服務

 

service mysqld restart

 

測試從資料庫是否能用主要資料指派的使用者名和密碼登入到主要資料庫。

mysql -uuser -ppassword -h192.168.10.42

如果不能登入，先檢查一下能否ping通。如果能夠ping桶，再檢查一下192.168.10.42上的防火牆是否配置了3306連接埠。沒有,則在iptables中添加3306連接埠的允許存取策略，然後重啟iptables。

 

-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

 

重啟iptables

 

service iptables restart

 

這樣，應該就可以登陸到主要資料庫了。

以root登入從資料庫，配置slave資訊

 

change master to master_host=‘192.168.10.42‘, master_user=‘user‘ , master_password=‘password‘, master_log_file=‘mysql-bin.000005‘, master_log_pos=3065;

 

然後開啟從資料庫的slave狀態。

 

slave start;

 

查看slave資訊。

 

show slave status\G;

 

如果Slave_IO_Running與Slave_SQL_Running為YES，則slave狀態開啟成功。否則，根據mysqld.log日誌查看具體原因。

到此，mysql的主從複製就設定好了。在主要資料庫上添加刪除幾條資料，可以在從資料庫上同樣的操作。停掉從資料庫的slave狀態

 

slave stop;

 

再往主要資料庫中添加刪除資料，這些操作就不會影響到從資料庫了。

為mysql做讀寫分離功能

 

mysql的讀寫分離，使用mysql-proxy來實現。而mysql-proxy而依託lua指令碼，所以機器上密碼安裝lua。

 

安裝mysql-proxy的機器為192.168.29.132。

 

作業系統為ubuntu14.1。

 

mysql-proxy的版本為mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz。

 

因為我們使用的是ubuntu所以安裝lua的方式就很簡單了。

 

apt-get install lua

 

可以根據自己的系統選擇相應的方式安裝lua，保證lua命令能夠執行就可以了。mysql-proxy的版本也要根據自己的作業系統進行相應的選擇。

 

把mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz上傳到/home/mysql_proxy目錄下，解壓

 

tar zxvf mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gzcd mysql-proxy-0.8.2-linux-glibc2.3-x86-64bitcp share/doc/mysql-proxy/rw-splitting.lua ./vi rw-splitting.lua

 

找到如下片段

if not proxy.global.config.rwsplit then        proxy.global.config.rwsplit = {                min_idle_connections = 4,                max_idle_connections = 8,                is_debug = false        }end

為測試使用把4和8改為1。在生產環境中根據需求可做相應的修改。修改後如

 

現在就可以啟動mysql-proxy了，命令如下

 

./bin/mysql-proxy --proxy-read-only-backend-addresses=192.168.10.49:3306 --proxy-backend-addresses=192.168.10.42:3306 --proxy-lua-script=rw-splitting.lua

 

控制台沒有錯誤輸出則啟動正常。

mysql-proxy的偵聽連接埠為4040，所以在192.168.29.132這台機器上要開放4040連接埠。在iptables檔案中添加如下內容

 

-A INPUT -p tcp -m state --state NEW -m tcp --dport 4040 -j ACCEPT

 

重啟iptables

 

service iptables restart

 

輸入如下命令，可以查看連接埠偵聽狀況。

netstat -anp | grep 4040

由於mysql-proxy架設在192.168.29.132這台機器上，還需要在主要資料庫中配置這台機器的許可權，命令如下。

 

grant all on *.* to ‘user‘@‘192.168.29.132‘ identified by ‘password‘;

 

現在mysql讀寫分離就完成了，結構圖如下

 

應用程式串連到mysql-proxy上，所有的寫請求通過紅線發送到master mysql上，所有的讀請求通過藍線發送到slave mysql上，master和slave之間通過紫線同步資料。

現在就可以測試讀寫分離機制是否起作用了。通過如下命令串連到mysql-proxy上。然後停掉192.168.10.49上的slave狀態。

 

mysql -uuser -ppassword -h192.168.29.132 -P4040

 

在mysql-proxy上，通過sql插入幾條資料。可以看到42上的mysql中有新增的資料，49上的mysql沒有新增資料。然後在mysql-proxy上select一下，可以看到選出來的結構沒有剛才插入的幾條資料。

至此，mysql的讀寫分離就完成了。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

mysql讀寫分離策略