mysql 主從複製之mysql-proxy讀寫分離之負載平衡完整詳解

繼先前實做出 mysql replication 主從網路拓樸架構後，我們繼續在建置一個 mysql-proxy server 將他設定為 192.168.1.214，並加入到整個 mysql replication 主從架構來實現讀寫分離機制。
實驗環境如下
centos6.6 192.168.1.215 master db
centos6.6 192.168.1.212 slave db
centos6.6 192.168.1.214 mysql-proxy server
mysql replication master-slave 這邊不在多加贅述，請參考前篇
在主從資料庫中加入mysql-proxy帳號權限
grant all on *.* to 'root'@'%' identified by 'password';
grant all on *.* to 'root'@'192.168.1.214' identified by 'password';

#Install EPEL
## Step 1
wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm

## Step 2
ls -1 /etc/yum.repos.d/epel* /etc/yum.repos.d/remi.repo
/etc/yum.repos.d/epel.repo
/etc/yum.repos.d/epel-testing.repo
/etc/yum.repos.d/remi.repo

## Step 3
nano /etc/yum.repos.d/remi.repo

## Step 4
[remi]
name=Les RPM de remi pour Enterprise Linux $releasever – $basearch
#baseurl=http://rpms.famillecollet.com/enterprise/$releasever/remi/$basearch/
mirrorlist=http://rpms.famillecollet.com/enterprise/$releasever/remi/mirror
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi
failovermethod=priority

## Step 5
yum update -y

## Step 6
yum install mysql-proxy

我們查看epel源的mysql-proxy包提供的lun script：
   
[root@node5 ~]# ls /usr/share/doc/mysql-proxy-0.8.5/examples/
tutorial-basic.lua      tutorial-prep-stmts.lua  tutorial-states.lua
tutorial-constants.lua  tutorial-query-time.lua  tutorial-tokenize.lua
tutorial-inject.lua     tutorial-resultset.lua   tutorial-union.lua
tutorial-keepalive.lua  tutorial-rewrite.lua     tutorial-warnings.lua
tutorial-monitor.lua    tutorial-routing.lua
tutorial-packets.lua    tutorial-scramble.lua

可以發現沒有rw-splitting.lua 這個讀寫分離script，我們去下載一個同版本的源碼包，將其中的讀寫分離script拷貝到指定目錄：
   
[root@node5 ~]# tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@node5 ~]# ls mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/
active-queries.lua       ro-balance.lua           tutorial-resultset.lua
active-transactions.lua  ro-pooling.lua           tutorial-rewrite.lua
admin-sql.lua            rw-splitting.lua         tutorial-routing.lua
analyze-query.lua        tutorial-basic.lua       tutorial-scramble.lua
auditing.lua             tutorial-constants.lua   tutorial-states.lua
commit-obfuscator.lua    tutorial-inject.lua      tutorial-tokenize.lua
commit-obfuscator.msc    tutorial-keepalive.lua   tutorial-union.lua
COPYING                  tutorial-monitor.lua     tutorial-warnings.lua
histogram.lua            tutorial-packets.lua     xtab.lua
load-multi.lua           tutorial-prep-stmts.lua
README                   tutorial-query-time.lua
[root@node5 ~]# ls /usr/lib64/mysql-proxy/lua/proxy/
auto-config.lua  balance.lua  commands.lua  parser.lua  test.lua  tokenizer.lua
[root@node5 ~]# cp mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua /usr/lib64/mysql-proxy/lua/
[root@node5 ~]# ls /usr/lib64/mysql-proxy/lua/
admin.lua   glib2.so  lpeg.so   posix.so  rw-splitting.lua
chassis.so  lfs.so    mysql.so  proxy

## Step 7
vi /etc/default/mysql-proxy

ENABLED=”true”
OPTIONS=”–defaults-file=/etc/mysql-proxy.cnf”


## Step 8
vi /etc/mysql-proxy.cnf

[mysql-proxy]
daemon = true
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
max-open-files = 1024
plugins = admin,proxy
user = mysql-proxy
keepalive = true
event-threads = 50
#
#Proxy Configuration
proxy-address = 0.0.0.0:4040
proxy-backend-addresses = 192.168.1.215:3306
proxy-read-only-backend-addresses = 192.168.1.212:3306
proxy-lua-script =/usr/lib64/mysql-proxy/lua/rw-splitting.lua
proxy-skip-profiling = true
#
# Admin Configuration
admin-address = 0.0.0.0:4041
admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua
admin-username = root
admin-password = password


## Step 9
chmod 06660 /etc/mysql-proxy.cnf

## Step 10
vi /etc/rc.local

mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

## Step 11
記得將防火牆關閉或者是開啟3306 port

## Step 12
service mysql-proxy start

## Step 13
ps aux | grep mysql-proxy

mysql -uroot -p -h192.168.1.214 -P4040(admin port)
mysql -uroot -p -h192.168.1.214 -P4041(proxy port)

檢查日誌
vi /etc/log/mysql-proxy.log
2015-08-24 09:56:07: (message) chassis-unix-daemon.c:176: [angel] PID=13911 exited normally with exit-code = 0 (it used 1 kBytes max)
2015-08-24 09:56:07: (message) Initiating shutdown, requested from mysql-proxy-cli.c:608
2015-08-24 09:56:07: (message) shutting down normally, exit code is: 0
2015-08-24 09:56:08: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=13969 alive
2015-08-24 09:56:08: (debug) chassis-unix-daemon.c:157: waiting for 13969
2015-08-24 09:56:08: (debug) chassis-unix-daemon.c:121: we are the child: 13969
2015-08-24 09:56:08: (critical) plugin admin 0.8.5 started
2015-08-24 09:56:08: (critical) plugin proxy 0.8.5 started
2015-08-24 09:56:08: (debug) max open file-descriptors = 1024
2015-08-24 09:56:08: (message) admin-server listening on port 0.0.0.0:4041
2015-08-24 09:56:08: (message) proxy listening on port 0.0.0.0:4040
2015-08-24 09:56:08: (message) added read/write backend: 192.168.1.215:3306
2015-08-24 09:56:08: (message) added read-only backend: 192.168.1.212:3306
2015-08-24 09:56:08: (debug) now running as user: mysql-proxy (498/498)
2015-08-24 09:56:08: (message) chassis-event-thread.c:491: starting 49 threads

從最後幾行中可以發現到 mysql-proxy server 已經加入 read/write backend 及 read-only backend 並且成功啟動。

檢查 mysql-proxy 程序
[root@localhost log]# ps aux |grep mysql-proxy
root     13968  0.0  0.0  41548   852 ?        S    09:56   0:00 mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
498      13969  0.0  0.2 1072376 2580 ?        Sl   09:56   0:00 mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root     14044  0.0  0.0 103304   880 pts/1    S+   10:23   0:00 grep mysql-proxy
 
檢查開啟的 port
[root@localhost log]# netstat -tunpel
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       User       Inode      PID/Program name
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      0          28805      2358/sshd
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      0          10689      1057/master
tcp        0      0 0.0.0.0:4040                0.0.0.0:*                   LISTEN      0          48123      13969/mysql-proxy
tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      0          48122      13969/mysql-proxy
tcp        0      0 :::22                       :::*                        LISTEN      0          28808      2358/sshd
tcp        0      0 ::1:25                      :::*                        LISTEN      0          10691      1057/master


mysql -uroot -P 4040 -p -h192.168.1.214 -e "select user,此處mysql-proxy有兩個監聽端口分表用於不同功能

4041端口用於監控mysql-proxy讀寫分離狀態，此時只能使用 select * from backends 查看後端服務器狀態；

3306端口用於正常連接後端服務器讀寫請求；

# mysql -uadmin -padmin -h192.168.1.214 --port=4041;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from backends;
+-------------+----------------------+---------+------+------+-------------------+
| backend_ndx | address              | state   | type | uuid | connected_clients |
+-------------+----------------------+---------+------+------+-------------------+
|           1 | 192.168.1.215:3306 | unknown | rw   | NULL |                 0 |
|           2 | 192.168.1.212:3306 | unknown | ro   | NULL |                 0 |
+-------------+----------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

由於此時沒有通過mysql-proxy這台服務器操作後端mysql服務器，所以state都為"unknown";

嘗試對後端主機進行讀或寫操作：

注意，還需要在主服務器上創建可遠程操用的用戶名和主機，這裡我創建了用戶admin,而從服務器會自動同步主服務器的修改操作，所以不需要對從服務器添加admin用戶；

# mysql -uadmin -padmin -h192.168.1.214
mysql> creat databases mydb;

# mysql -uadmin -padmin -h192.168.1.214 --port=4041
mysql> select * from backends;
+-------------+----------------------+---------+------+------+-------------------+
| backend_ndx | address              | state   | type | uuid | connected_clients |
+-------------+----------------------+---------+------+------+-------------------+
|           1 | 192.168.1.215:3306 | up      | rw   | NULL |                 0 |
|           2 | 192.168.1.212:3306 | unknown | ro   | NULL |                 0 |
+-------------+----------------------+---------+------+------+-------------------+

由於多次單條語句都無法使查詢到從服務器，下面我們使用mysql自帶的一個壓力測試工具mysqlslap來測試：

# mysqlslap -uroot -p -h192.168.1.214  -P4040 --concurrency=100 iterations=1 --create-schema='mydb' --query='select * from tb1' --number-of-queries=1 --debug-info
Benchmark
 Average number of seconds to run all queries: 0.222 seconds
 Minimum number of seconds to run all queries: 0.222 seconds
 Maximum number of seconds to run all queries: 0.222 seconds
 Number of clients running queries: 100
 Average number of queries per client: 0

User time 0.00, System time 0.04
Maximum resident set size 6828, Integral resident set size 0
Non-physical pagefaults 1328, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 404, Involuntary context switches 107

# mysql -uroot -p -h192.168.1.214 --port=4041
mysql> select * from backends;
+-------------+----------------------+-------+------+------+-------------------+
| backend_ndx | address              | state | type | uuid | connected_clients |
+-------------+----------------------+-------+------+------+-------------------+
|           1 | 192.168.1.215:3306 | up    | rw   | NULL |                 0 |
|           2 | 192.168.1.212:3306 | up    | ro   | NULL |                 0 |
+-------------+----------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)

mysqlslap常用參數詳解：

    -u username         指定用戶名；

    -p password          指定用戶密碼；

    -h host                  指定測的主機；

    -P port                   服務端口；

    --concurrency        並發數量，多個可以用逗號隔開，concurrency=10,50,100, 並發連接線程數分別是10、50、100個並發；

    --engines               要測試的引擎，可以有多個，用分隔符隔開；
    --iterations            要運行這些測試多少次；
    --auto-generate-sql       用系統自己生成的SQL腳本來測試；
    --auto-generate-sql-load-type  要測試的是讀還是寫還是兩者混合的（read,write,update,mixed）；
    --number-of-queries      總共要運行多少次查詢。每個客戶運行的查詢數量可以用查詢總數/並發數來計算；
    --debug-info                  要額外輸出CPU以及內存的相關信息。
    --number-int-cols          創建測試表的 int 型字段數量；
    --auto-generate-sql-add-autoincrement :         對生成的表自動添加auto_increment列，從5.1.18版本開始；
    --number-char-cols        創建測試表的 char 型字段數量。
    --create-schema             測試的schema，MySQL中schema也就是database；
    --query                            使用自定義腳本執行測試，例如可以調用自定義的一個存儲過程或者sql語句來執行測試；
    --only-print                     如果只想打印看看SQL語句是什麼，可以用這個選項；host from mysql.user"

mysql -uroot -P 4040 -p -h192.168.1.214 -e "select user,host from mysql.user"

+------------+---------------+
| user       | host          |
+------------+---------------+
| repl       | %             |
| root       | %             |
| proxy_user | 192.168.1.214 |
| root       | 192.168.1.214 |
| root       | localhost     |
+------------+---------------+
多查詢幾次
我們可以修改定義連線數來啟用讀寫分離
#vi /usr/lib64/mysql-proxy/lua/rw-splitting.lua

配置並使用rw-splitting.lua讀寫分離script，script目錄是 /usr/share/mysql-proxy，修改讀寫分離script rw-splitting.lua，修改預設連接數，進行快速測試，如果不修改連接數的話要達到連接數為4時才會啟用讀寫分離。

-- connection pool

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1, //預設為4

max_idle_connections = 1, //預設為8

is_debug = false //將 is_debug = false 改成 is_debug = true，debug的log 會在應用程式執行時顯示出來

}

end

這是因為mysql-proxy會檢測客戶端連接，當連接沒有超過min_idle_connections預設值時, 不會進行讀寫分離, 即查詢操作會發生到Master上。


簡單的PHP程式，跑個小迴圈，在debug log中就會顯示出 SELECT的資料是從那一台機器提供

<?php

$link = mysql_connect('192.168.1.214:4040','root', 'password');

mysql_select_db('mysql');

for($i=0;$i<50;$i++){

$sql='select user,host from user';

$result = mysql_query( $sql);

}
至mysql-proxy server查看 debug 訊息  

# 略....

# 由於php程式迴圈共跑50次，我們僅查看最後是由主從庫哪個所提供的

[read_query] 192.168.1.213:59745

  current backend   = 0

  client default db = mysql

  client username   = root

  query             = select user,host from user

  sending to backend : 192.168.1.212:3306

    is_slave         : true # 驗證的結果讀取資料是從 slave 資料庫所提供的

    server default db: mysql

    server username  : root

    in_trans        : false

    in_calc_found   : false

    COM_QUERY       : true

[read_query] 192.168.1.213:59745

  current backend   = 0

  client default db = mysql

  client username   = root

  (QUIT) current backend   = 0

[disconnect_client] 192.168.1.213:59745