RHEL6.5下MySQL讀寫分離的實現

RHEL6.5下MySQL讀寫分離的實現

一、要求

1、配置兩台MySQL伺服器和一台Proxy 伺服器，實現MySQL代理的讀寫分離。

2、使用者只需要訪問MySQLProxy 伺服器，實際的SQL查詢、寫入操作交給背景2台MySQL伺服器來完成。

3、其中Master伺服器允許SQL查詢、寫入，Slave伺服器只允許SQL查詢。

二、方案

使用4台RHEL6.5虛擬機器，下所示。其中192.168.4.10、192.168.4.20分別作為MySQL主、從伺服器，是整個服務的後端；另一台192.168.4.30作為MySQLProxy 伺服器，是直接面向客戶的服務前端；客戶機192.168.4.100用作訪問測試。

對比兩種方式的讀寫分離效果——

1. MySQL主從複製：客戶機訪問Master伺服器來寫資料庫，客戶機訪問Slave服務 器來讀資料庫。這種情況下，需要用戶端自行區分向何處寫、從何處讀。
2. MySQL主從複製+代理：客戶機訪問Proxy伺服器，讀、寫請求交給Proxy識別，如果是寫資料庫操作則交給Master，如果是讀資料庫操作則交給Slave處理，具體由分配策略控制。這種情況下，無需用戶端區分讀、寫目標，而是由Proxy伺服器代勞了，從而降低了用戶端程式的複雜度。

以下是在mysql主從複製的基礎上實現的，mysql主從複製部分請參見另一篇文章《MySQL主從複製》

三、實現

1、部署mysql-proxyProxy 伺服器

1）安裝MySQL官方提供的mysql-proxy軟體包

MySQL官方提供了mysql-proxy的免安裝版本，解壓後即可使用。由於msyql-proxy使用了LUA指令碼語言，因此需要提前安裝好lua軟體包（RHEL6鏡像內已含）以確保其正常使用：

[root@proxy ~]# yum -y install lua

然後部署mysql-proxy軟體包：

[root@proxy 案頭]# tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz -C /usr/local/

[root@proxy 案頭]# cd /usr/local/     

[root@proxy local]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit/ mysql-proxy  //將檔案改短一點，方便訪問

切換到部署後的目錄，確認相關目錄、可執行程式、指令碼。一般情況下，使用bin目錄下的mysql-proxy指令碼來啟動代理服務：

[root@proxy local]# cd mysql-proxy/

[root@proxy mysql-proxy]# ls

bin  include  lib  libexec  licenses  share

[root@proxy mysql-proxy]# ls bin/ libexec/

bin/:                                      //服務指令碼目錄

mysql-binlog-dump  mysql-myisam-dump  mysql-proxy

 

libexec/:                                    //可執行程式目錄

mysql-binlog-dump  mysql-myisam-dump  mysql-proxy

2）準備讀寫分離的LUA策略指令碼

直接複製mysql-proxy提供的範例策略即可：

[root@proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./

3）啟動mysql-proxy代理服務

主要命令選項：

    -P（大寫）：指定代理監聽的IP地址、連接埠

    -r：指定讀伺服器的IP地址、連接埠

    -b：指定寫伺服器的IP地址、連接埠

    -s：指定lua指令檔

    --keepalive：如果服務進程崩潰，嘗試重啟此進程

 

[root@proxy mysql-proxy]# pwd

/usr/local/mysql-proxy

[root@proxy mysql-proxy]# bin/mysql-proxy -P 192.168.4.30:3306 \

> -b 192.168.4.10:3306 \

> -r 192.168.4.20:3306 \

> -s rw-splitting.lua &

[root@proxy mysql-proxy]# netstat -anpt | grep mysql  //確認監聽狀態

tcp  0  0  192.168.4.30:3306 0.0.0.0:*  LISTEN      16119/mysql-proxy 

對於用戶端來說，這台代理主機192.168.4.30就相當於一台可讀可寫的MySQL資料庫伺服器了。

為了每次開機啟動mysql-proxy代理服務可以將上面的命令寫到/etc/rc.local設定檔內：

 [root@proxy ~]# vim /etc/rc.local

 .. ..

 /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.4.30:3306 \

 -b 192.168.4.10:3306 \

 -r 192.168.4.20:3306 \

 -s rw-splitting.lua &

2、測試通過mysql-proxy的讀寫分離

1）在Master伺服器上授權使用者，允許其從192.168.4.0/24的客戶機遠端存取。

mysql>  GRANT all ON *.* TO user02@'192.168.4.%' IDENTIFIED BY 'pwd123';

Query OK, 0 rows affected (0.07 sec)

因為此前已配置mysql庫的主從同步，SLAVE上的使用者授權會自動更新

2）從客戶機上192.168.4.100訪問Mysql資料庫

[root@client ~]# mysql -h192.168.4.30 -uuser02 -ppwd123

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.4.30' (113)

出現上面這種情況，可以關閉代理的防火牆，或者添加一條允許訪問3306連接埠的防火牆規則

[root@proxy ～]# iptables -I INPUT -s 192.168.4.0/24 -p tcp --dport 3306 -j ACCEPT

再次登入：

[root@client ~]# mysql -h192.168.4.30 -uuser02 -ppwd123

... ...

mysql>                    //表示登入成功

mysql> CREATE DATABASE student;          //建立庫

Query OK, 1 row affected (0.10 sec)

mysql> USE student;//切換到建立的庫

Database changed

mysql> CREATE TABLE info( id int(4),name varchar(48));    //建立表

Query OK, 0 rows affected (1.23 sec)

mysql> INSERT INTO info VALUES(1,"hanmeimei"),(2,"lilei");  //插入2條表記錄

Query OK, 2 rows affected (0.18 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM info;

+------+-----------+

| id  | name        |

+------+-----------+

|    1 | hanmeimei |

|    2 | lilei            |

+------+-----------+

2 rows in set (0.01 sec)

mysql> quit

Bye

3）在master和slave上確認用戶端建立的表

[root@client ~]# mysql -h192.168.4.10 -uuser02 -ppwd123

mysql> USE student;

mysql> SHOW TABLES;

+-------------------+

| Tables_in_student |

+-------------------+

| info                    |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM info;

+------+-----------+

| id    | name      |

+------+-----------+

|    1  | hanmeimei |

|    2  | lilei            |

+------+-----------+

2 rows in set (0.00 sec)

 

[root@client ~]# mysql -h192.168.4.20 -uuser02 -ppwd123

mysql> SELECT * FROM student.info;

+------+-----------+

| id  | name        |

+------+-----------+

|    1 | hanmeimei |

|    2 | lilei            |

+------+-----------+

2 rows in set (0.00 sec)

4）觀察MySQL代理訪問的網路連接

在Proxy代理商可看到與MySQL讀、寫伺服器的網路連接：

[root@proxy mysql-proxy]# netstat -anpt | grep mysql

tcp        0      0 192.168.4.30:3306          0.0.0.0:*                  LISTEN      16119/mysql-proxy 

tcp        0      0 192.168.4.30:60975          192.168.4.10:3306          ESTABLISHED 16119/mysql-proxy 

tcp        0      0 192.168.4.30:60974          192.168.4.10:3306          ESTABLISHED 16119/mysql-proxy 

tcp        0      0 192.168.4.30:60977          192.168.4.10:3306          ESTABLISHED 16119/mysql-proxy 

tcp        0      0 192.168.4.30:60978          192.168.4.10:3306          ESTABLISHED 16119/mysql-proxy 

tcp        0      0 192.168.4.30:60976          192.168.4.10:3306          ESTABLISHED 16119/mysql-proxy 

在Master上可看到來自Slave和Proxy代理的網路連接：

[root@master mysql]# netstat -anpt | grep mysql

tcp        0      0 :::3306                    :::*                        LISTEN      3788/mysqld       

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60974  ESTABLISHED 3788/mysqld       

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60978  ESTABLISHED 3788/mysqld       

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60975  ESTABLISHED 3788/mysqld       

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60977  ESTABLISHED 3788/mysqld       

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.20:33555  ESTABLISHED 3788/mysqld       

tcp        0      0 ::ffff:192.168.4.10:3306    ::ffff:192.168.4.30:60976  ESTABLISHED 3788/mysqld

並發訪問量沒超過指定的閥值，不區分讀寫操作，讀寫都交給負責讀的伺服器，一旦區分，以後都區分，直到串連數為0。

現在即使訪問不同的伺服器，用戶端發覺不出服務端有什麼變化，想要觀察的話，可以在兩個伺服器的同一個表裡存入不同的資料，然後在用戶端開多個終端登入代理觀察資料的變化，

四、擴充

MySQL代理與讀（或寫）伺服器的整合

在許多公司專屬應用程式中，MySQL資料庫基本上都是讀多寫少，因此可以將mysql-proxy與Master或Slave安裝到一起，從而減少一台伺服器，節省成本。比如：

    Master+Proxy：192.168.4.10/24

    Slave：192.168.4.20/24

這種情況下，要避免mysql-proxy與MySQL服務程式的連接埠衝突，必要時可更改相關程式的監聽地址、連接埠。

若要修改MySQL服務程式的監聽地址、連接埠，可採用以下選項：

    --bind-address=：用來限定監聽地址，比如可設為127.0.0.1

    --port=：用來改連接埠號碼，比如可改為33060

當然，最簡單的方法還是直接將mysql-proxy改用其他連接埠。比如，在Master上部署mysql-proxy，啟用時可監聽192.168.4.10的33060連接埠：

    [root@proxy ~]# cd /usr/local/mysql-proxy/

    [root@proxy mysql-proxy]# bin/mysql-proxy -P 192.168.4.10:3333 \

    -b 192.168.4.10:3306 \

    -r 192.168.4.20:3306 \

    -s rw-splitting.lua &

這樣，用戶端只要訪問192.168.4.10的3333連接埠，就可以讀寫資料庫了。當伺服器採用非標準的MySQL連接埠時，需要為mysql工具添加 -P 選項來指定連接埠號碼：

  [root@client ~]# mysql  -h 192.168.4.10  -uuser02 -P 3333 -ppwd123

    mysql>

MySQL5.6 Replication主從複製（讀寫分離) 配置完整版

搭建MySQLProxy 伺服器實現讀寫分離+主從同步

MySQL-5.6+MySQL-Proxy構建主從複製與讀寫分離

MySQL資料庫讀寫分離

Thinkphp架構支援MySQL的讀寫分離 

本文永久更新連結地址：