[Mysql] 建立叢集式資料庫4/4--觀察及測試 @新精讚

建立Mysql的叢集式資料庫可以讓Mysql的可用性更大，提昇服務的能量和質量。此文分幾個部分：

[Mysql] 建立叢集式資料庫1/4 -- 安裝及設定DB1

[Mysql] 建立叢集式資料庫2/4 -- DB2、DB3設定及測試

[Mysql] 建立叢集式資料庫3/4 -- DB1設置及DB PROXY

[Mysql] 建立叢集式資料庫4/4--觀察及測試

十六、觀察HAPROXY的運作

HAPROXY提供網頁介面的監看畫面，在十三的設定中埠號是8088，這是自帶的服務，不必啟動httpd，請開瀏覽器

十七，測試HAPROXY的運作

連線

在haproxy上不必再安裝mysql，啟動後自動會監聽設定好的埠，在前面的設定是3306

連線的方式就和原本一樣，只是實際連線要交由HAPROXY處理。

但在建立使用者時，使用者的來源不能只限定本機，就算你在haproxy本機連線。例如

man有127.0.0.1的權限

[HAPROXY 192.168.1.243]

$ mysql -u man -p -h 127.0.0.1
Enter password:
ERROR 1045 (28000): Access denied for user 'man'@'192.168.1.243' (using password: YES)

對資料庫來說，透過HAPROXY來存取的來源就是HAPROXY，因此在設權限時要注意。

壓力測試MYSQL CLUSTER

面對 cluster, 採用自帶的mysqlslap來作壓力測試[4]

$ mysqlslap -a --concurrency=50,100 --number-of-queries 600 --iterations=5 --debug-info -uaxer -p -h 127.0.0.1
Enter password:
Benchmark
        Average number of seconds to run all queries: 3.125 seconds
        Minimum number of seconds to run all queries: 2.444 seconds
        Maximum number of seconds to run all queries: 3.651 seconds
        Number of clients running queries: 50
        Average number of queries per client: 12

Benchmark
        Average number of seconds to run all queries: 2.894 seconds
        Minimum number of seconds to run all queries: 2.324 seconds
        Maximum number of seconds to run all queries: 3.244 seconds
        Number of clients running queries: 100
        Average number of queries per client: 6

User time 0.26, System time 1.18
Maximum resident set size 16176, Integral resident set size 0
Non-physical pagefaults 5164, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 12954, Involuntary context switches 560

上面是測試有叢集的情況，如果單對一台進行測試，得到的數據為：

Benchmark
        Average number of seconds to run all queries: 10.242 seconds
        Minimum number of seconds to run all queries: 9.943 seconds
        Maximum number of seconds to run all queries: 10.623 seconds
        Number of clients running queries: 50
        Average number of queries per client: 12

Benchmark
        Average number of seconds to run all queries: 9.432 seconds
        Minimum number of seconds to run all queries: 8.974 seconds
        Maximum number of seconds to run all queries: 9.595 seconds
        Number of clients running queries: 100
        Average number of queries per client: 6

結論

比較兩組數據發現，未使用cluster 的情況下，模擬同時50人和100人，進行600次的查詢，平均耗用的時間分別是 10.242s, 9.432s；而使用cluster 的情況下，平均耗用的時間分別是 3.125s, 2.894s。由於這次實作的cluster是由三台mysql所組成，所以時間上理論應該會減少到原來的1/3，即

50人 10.242/3= 3.414
100人 9.432/3= 3.144

但事實上，藉由叢集後的時間是 3.125和2.894，這表示叢集運作的比我想像的更的效率(把通回收的時間都算進去都還比較快)，實在令人高興。

此外，50人和100人的情況中，同樣查詢數100人表現比50人好這顯然是必然的，但這應該不是鋒值，可能也不是必然如此，不知道最好的表現會是在多少人的時間？我針對叢集又多做了一些測試，發現3台mysql的叢集最好的訪問人數表現落在250、500人左右，為什麼？我也不知道，數據及圖表參考附錄四。

附錄

附錄是精華，做為除錯的參考

附一、haproxy mysql-check的設置

這個設置能讓haproxy清楚每個node是否正常，好讓演算法最適切運作

在HAPROXY建立帳號 checker，請不要設定密碼，也不用給權限，只要能登入即可

CREATE USER 'checker'@'192.168.1.%';GRANT USAGE ON *.* TO 'checker'@'192.168.1.%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

修改 vi /etc/haproxy/haproxy.cfg

option mysql-check user checker

重啟 haproxy

附二、galera使用者權限問題

mysql的使用者沒辦法用匯入的方式來處理，在前面的設置中，我並沒有設定root的帳密，因此我很天真的把原本的資料庫帳密檔匯入DB1，運作正確高興之餘，發現DB2, DB3原本是可以免密碼登入的突然無法登入了。

心想可能DB2,DB3得要用安全模式還原密碼，後來發現[3]有人提供說明，利用insert是沒有辦法同步資料的，但如果是用create user的方式就可以。

因此我在還能登入的DB1 建立使用者(真的好險，我可不想在嘗試在CLUSTER進行安全模式，而且這樣以後建帳號就沒有同步的問題。)

建立帳號並給權限的方法可以參考這篇 [Mysql/MariaDB] 新增使用者@精讚

結論是：

1. mysql 這個本身是 myisam 的engine，galera不支持，所以用 create/drop user的方式來做同步

2. 建立一個全域的帳號即可，其他使用者等mysql連到 HAPROXY 再來加刪管理即可。

附三、錯誤排除

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.x' (113)

網路方面的問題，請試著ping看看是否有回應，如果有回應，請檢查防火牆設定是否有開啟，或是關閉目的的SELINUX。

ERROR 1129 (HY000): Host '192.168.1.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' [1]

# mysqladmin -u galera -p flush-hosts

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

haproxy設定問題，像我發現是 mysql-check 帳號連不上。

log 檔在 /var/log/message中

附四、壓力測試耗用時間和同時連線數關係

附五、當全部重啟進入bootstrap程式發生錯誤

若叢集因為某些原因，例如電源失敗，造成全部同時重啟的情況時，就得進入bootstrap程序，方法已在 [Mysql] 建立叢集式資料庫3/4 -- DB1設置及DB PROXY 中的第十二、測試第三項有說明，

啟動bootstrap程序

# sudo -u mysql mysqld --wsrep-new-cluster &

若發現這樣的錯誤：

[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to

leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the

grastate.dat file manually and set safe_to_bootstrap to 1 .

這時請試著再每一台執行上面的「啟動bootstrap」指令，假設每一台節點啟動都失敗，這時得使用強制bootstrap：

任選一台節點

# vi /var/lib/mysql/grastate.dat

將其中的值改為1

safe_to_bootstrap: 1

再進行同樣的bootstrap程式即可

附六、啟動失敗，出現timeout或timewait錯誤

原本運作良好的資料庫在關掉後各別重啟後失敗，secondary啟動mysql會出現這樣的錯誤(大概180秒)：

Starting mysql (via systemctl): Job for mariadb.service failed because a timeout was exceeded. See "systemctl status mariadb.service" and "journalctl -xe" for details.

而primary 出現這樣的錯誤：

[Warning] WSREP: discarding established (time wait) 3324f771 (tcp://192.168.1.252:4567)

一開始沒有頭絮，以為是什麼地方有錯，不斷的嘗試，後來想到，會不會是因為資料庫太大的關係，造成無法在時間內啟動成功？

因為SECONDARY在啟動的時候會去PRIMARY搬資料庫的檔案，很費時間，從這個方向去想，沒期待的竟然成功了。

啟動不成功的SECONDARY DB上，加上這個檔案：

# vi /etc/systemd/system/mariadb.service.d/timeoutsec.conf

[Service]

TimeoutStartSec=0
TimeoutStopSec=0

重啟服務

# systemctl daemon-reload
# service mysql start