MySQL高可用架構(gòu)之MaxScale實(shí)踐

    本次安裝部署基于MHA已部署的情況下,只部署MaxScale,并且采用的是rpm包安裝
    MaxScale是mariadb公司開(kāi)發(fā)的一套數(shù)據(jù)庫(kù)中間件。它是一個(gè)支持高可用、讀寫(xiě)分離、負(fù)載均衡，并且具有良好的可擴(kuò)展性，不但高性能的基于事件驅(qū)動(dòng)，同時(shí)具有代理和管理功能。

1、MaxScale軟件下載
    下載地址：https://downloads.mariadb.com/files/MaxScale/
         https://github.com/mariadb-corporation/MaxScale                            --源碼下載
    參考手冊(cè)：https://downloads.mariadb.com/files/MaxScale/docs
                 https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale

2、解壓安裝包
   （二進(jìn)制安裝包）
   [root@node3 MaxScale]# tar -xzvf maxscale-1.4.3-1.rhel.6.x86_64.tar.gz
3、安裝依賴(lài)包
    配置本地yum源：
   [root@node3 MaxScale]# mkdir /media/cdrom
   [root@node3 MaxScale]# mount CentOS-6.4-x86_64-bin-DVD1.iso /media/cdrom/ -o loop
   [root@node3 MaxScale]# rm -rf /etc/yum.repos.d/*.repo
   [root@node3 MaxScale]# vi /etc/yum.repos.d/CentOS6.repo
       [Base]
       name=CentOS6 ISO Base
       baseurl=file:///media/cdrom
       enabled=1
       gpgcheck=0
    依賴(lài)包檢查安裝：
   yum install git gcc gcc-c++ ncurses-devel bison flex glibc-devel cmake libgcc perl make libtool openssl-devel libaio libaio-devel librabbitmq-devel libcurl-devel pcre-devel tcl tcl-devel systemtap-sdt-devel libuuid libuuid-devel
        rpm -q libaio libaio-devel novacom-server libedit gcc gcc-c++ ncurses-devel bison glibc-devel cmake libgcc perl make libtool openssl-devel libaio libaio-devel librabbitmq-devel libcurl-devel pcre-devel
    升級(jí)openssl
   [root@node3 MaxScale]# rpm -Uvh openssl-1.0.1e-42.el6_7.1.x86_64.rpm --nodeps
   [root@node3 MaxScale]# rpm -Uvh openssl-devel-1.0.1e-42.el6_7.1.x86_64.rpm
4、MaxScale安裝和部署
    MaxScale安裝：
   1）rpm包安裝
   [root@node3 MaxScale]# rpm -ivh maxscale-beta-2.0.0-1.centos.6.x86_64.rpm --nodeps
    2）源碼安裝
   (以下是源碼編譯，其中編譯沒(méi)通過(guò)，源碼安裝失敗，建議在centos/rhel 7以上版本源碼安裝)
        源碼安裝包要求：
        CMake version 2.8 or later (Packaging requires version 2.8.12 or later)
        GCC version 4.4.7 or later
        libaio
        OpenSSL
        Bison 2.7 or later
        Flex 2.5.35 or later
        libuuid
        rhel 5,6 :libedit-devel MariaDB-devel MariaDB-server
        rhel 7   :mariadb-devel mariadb-embedded-devel libedit-devel

   [root@node3 MaxScale]# cd MaxScale-2.0
   [root@node3 MaxScale-2.0]# cmake ./
   或者手動(dòng)指定編譯參數(shù)，如下
   cmake ./ -DCMAKE_INSTALL_PREFIX=/usr/local/maxscale \
       -DMySQL_DIR=/usr/local/mysql/include/ \
       -DEMBEDDED_LIB=/usr/local/mysql/lib/libmysqld.a \
       -DMYSQL_EMBEDDED_LIBRARIES=/usr/local/mysql/lib/ \
       -DERRMSG=/usr/local/mysql/share/english/errmsg.sys
   [root@node3 MaxScale-2.0]# make -j 8
   [root@node3 MaxScale-2.0]# make install
    3）二進(jìn)制安裝
   [root@node3 MaxScale]# mv maxscale-1.4.3-1.rhel.6.x86_64 /usr/local/maxscale
   在~/.bash_profile中添加以下環(huán)境變量
   export MAXSCALE_HOME=/usr/local/maxscale
   export LD_LIBRARY_PATH=/usr/local/maxscale/lib

    MaxScale配置：
   在 master 中為 MaxScale 創(chuàng)建兩個(gè)用戶(hù)，用于監(jiān)控模塊和路由模塊
   創(chuàng)建監(jiān)控賬戶(hù)
   mysql> create user maxscalemon@'%' identified by "monitor" ;
   mysql> grant replication slave, replication client on *.* to maxscalemon@'%';
   創(chuàng)建路由用戶(hù)
   mysql> create user maxscale@'%' identified by "maxscale";
   mysql> grant select on mysql.* to maxscale@'%';
   mysql> grant show databases on *.* to 'maxscale'@'%';
   mysql> flush privileges;

   編輯配置文件：
   主要修改的文件有server1的IP地址和端口以及復(fù)制相應(yīng)的server2,server3配置，修改監(jiān)控和路由配置，清除只讀服務(wù)配置。
   [root@node3 MaxScale]# cp /etc/maxscale.cnf /etc/maxscale.cnf_20160823
   [root@node3 MaxScale]# vi /etc/maxscale.cnf
[maxscale]
threads=1

[server1]
type=server
address=IP1
port=3306
protocol=MySQLBackend

[server2]
type=server
address=IP2
port=3306
protocol=MySQLBackend

[server3]
type=server
address=IP3
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscalemon
passwd=monitor                              ##--改為加密的密碼
monitor_interval=10000

[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale
passwd=maxscale                            ##--改為加密的密碼
router_options=slave

[Read-Write Service]
type=service
router=readwritesplit
servers=server1
user=maxscale
passwd=maxscale                            ##--改為加密的密碼
max_slave_connections=100%

[MaxAdmin Service]
type=service
router=cli

[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
#socket=default
port=6603
    加密密碼
       [root@node3 ~]# maxkeys /var/lib/maxscale
    加密配置文件密碼
       [root@node3 ~]# maxpasswd /var/lib/maxscale/.secrets monitor
   7429FE1AABA353442178F74131697531
       [root@node3 ~]# maxpasswd /var/lib/maxscale/.secrets maxscale
   26C20853B625AD18686C0D2AC8A11E60

    將加密后的密碼填寫(xiě)到配置文件中

    啟動(dòng)maxscale服務(wù)：
   [root@node3 MaxScale]# maxscale --config=/etc/maxscale.cnf
   或者 maxscale -f /etc/maxscale.cnf

5、MaxScale使用

1）通過(guò)service服務(wù)關(guān)閉、啟動(dòng)、重啟、查看maxscale狀態(tài)等

10余年的皇姑網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開(kāi)發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。成都營(yíng)銷(xiāo)網(wǎng)站建設(shè)的優(yōu)勢(shì)是能夠根據(jù)用戶(hù)設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整皇姑建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。創(chuàng)新互聯(lián)公司從事“皇姑網(wǎng)站設(shè)計(jì)”,“皇姑網(wǎng)站推廣”以來(lái)，每個(gè)客戶(hù)項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

[root@node3 MaxScale]# service maxscale

Usage: /etc/init.d/maxscale {start|stop|status|restart|condrestart|reload}

2）啟動(dòng)maxscale

service maxscale start

maxscale -f /etc/maxscale.conf

3）關(guān)閉maxscale

service maxscale stop

4）登陸maxscale管理控制臺(tái)管理

[root@node3 MaxScale]# maxadmin -uadmin -pmariadb -P6603

6、MaxScale讀寫(xiě)分離和負(fù)載均衡測(cè)試
開(kāi)啟general log

mysql> show variables like 'general_log';

mysql> set global general_log=1;

[root@node3 MaxScale]# mysql -udbadmin -pdbadmin -hip3 -P4008 -e "select * from dbtest.t2"

[root@node2 ~]# tailf /usr/local/mysql/data/node2.log

166 Connect dbadmin@node3 on

166 Query select @@version_comment limit 1

166 Query select * from dbtest.t1

[root@node3 MaxScale]# mysql -udbadmin -pdbadmin -hip3 -P4008 -e "select * from dbtest.t2"

[root@node3 ~]# tailf /usr/local/mysql/data/node3.log

160825 14:25:29 208 Connect dbadmin@node3 on

208 Query select @@version_comment limit 1

208 Query select * from dbtest.t2

208 Quit

179 Query SELECT @@server_id

179 Query SHOW SLAVE STATUS

160825 14:25:30 209 Connect dbadmin@node3 on

209 Query select @@version_comment limit 1

209 Query select * from dbtest.t2

209 Quit

160825 14:25:31 204 Query SELECT @@server_id

204 Query SHOW SLAVE STATUS

[root@node3 MaxScale]# mysql -udbadmin -pdbadmin -hip3 -P4006 -e "insert into dbtest.t2 values(1111)"

[root@node1 ~]# tailf /usr/local/mysql/data/node1.log

160825 14:26:53 203 Connect dbadmin@node3 on

203 Query select @@version_comment limit 1

203 Query insert into dbtest.t2 values(1111)

203 Quit

7、MaxScale安裝錯(cuò)誤信息以及解決方案
    錯(cuò)誤信息01：
Initialized empty Git repository in /tools/MaxScale/MaxScale-2.0/build/connector-c-prefix/src/connector-c/.git/
error: Couldn't resolve host 'github.com' while accessing https://github.com/MariaDB/mariadb-connector-c.git/info/refs

fatal: HTTP request failed
Initialized empty Git repository in /tools/MaxScale/MaxScale-2.0/build/connector-c-prefix/src/connector-c/.git/
error: Couldn't resolve host 'github.com' while accessing https://github.com/MariaDB/mariadb-connector-c.git/info/refs

fatal: HTTP request failed
Initialized empty Git repository in /tools/MaxScale/MaxScale-2.0/build/connector-c-prefix/src/connector-c/.git/
error: Couldn't resolve host 'github.com' while accessing https://github.com/MariaDB/mariadb-connector-c.git/info/refs

fatal: HTTP request failed
-- Had to git clone more than once:
          3 times.
CMake Error at /tools/MaxScale/MaxScale-2.0/build/connector-c-prefix/tmp/connector-c-gitclone.cmake:40 (message):
Failed to clone repository:
'https://github.com/MariaDB/mariadb-connector-c.git'

make[2]: *** [connector-c-prefix/src/connector-c-stamp/connector-c-download] Error 1
make[1]: *** [CMakeFiles/connector-c.dir/all] Error 2
make: *** [all] Error 2
     解決方案：
                    上述錯(cuò)誤是源碼編譯報(bào)錯(cuò)，暫無(wú)好的解決方案，如果可以連接外網(wǎng)可以通過(guò)源碼編譯安裝或者使用高版本os的服務(wù)器。

    錯(cuò)誤信息02：

2016-08-23 18:26:54   notice : Loaded module mysqlmon: V1.4.0 from /usr/lib64/maxscale/libmysqlmon.so
2016-08-23 18:26:54   notice : Encrypted password file /var/lib/maxscale/.secrets can't be accessed (No such file or directory). Password encryption is not used.
2016-08-23 18:26:54   error : 1 errors were encountered while processing the configuration file '/etc/maxscale.cnf'.
2016-08-23 18:26:54   error : Failed to open, read or process the MaxScale configuration file /etc/maxscale.cnf. Exiting.
2016-08-23 18:26:54   MaxScale is shut down.
    解決方案：
   創(chuàng)建密碼文件，并將加密后的密碼在配置文件中替換。
   maxkeys /var/lib/maxscale
   maxpasswd /var/lib/maxscale/.secrets monitor
   maxpasswd /var/lib/maxscale/.secrets maxscale

    錯(cuò)誤信息03：
2016-08-24 11:48:43   notice : Loaded module readconnroute: V1.1.0 from /usr/lib64/maxscale/libreadconnroute.so
2016-08-24 11:48:43   error : The service 'Read-Only Service' is missing a definition of the servers that provide the service.
    解決方案：
   將配置文件中的readonly內(nèi)容補(bǔ)全，內(nèi)容如下
   [Read-Only Service]
   type=service
   router=readconnroute
   servers=
   user=maxscale
   passwd=26C20853B625AD18686C0D2AC8A11E60
   router_options=slave
    錯(cuò)誤信息04：
    rpm -ivh maxscale-1.4.3-1.rhel.6.x86_64.rpm
   warning: maxscale-1.4.3-1.rhel.6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY
   error: Failed dependencies:
        libcrypto.so.10(libcrypto.so.10)(64bit) is needed by maxscale-1.4.3-1.x86_64
        libssl.so.10(libssl.so.10)(64bit) is needed by maxscale-1.4.3-1.x86_64
    解決方案：
   通過(guò)升級(jí)openssl解決，rpm -Uvh openssl-1.0.1e-42.el6_7.1.x86_64.rpm --nodeps和rpm -Uvh openssl-devel-1.0.1e-42.el6_7.1.x86_64.rpm 。

8、MaxScale附錄
    1）rpm默認(rèn)安裝包相關(guān)路徑
   /usr/share/maxscale/   共享配置安裝路徑
   /var/lib/maxscale   數(shù)據(jù)文件目錄
   /usr/bin/maxscale   執(zhí)行文件目錄
   /var/log/maxscale   日志目錄
   /usr/lib64/maxscale   庫(kù)文件目錄
    2）配置文件詳解
   [maxscale]
   threads=auto               #開(kāi)啟線程個(gè)數(shù)，默認(rèn)為1.設(shè)置為auto會(huì)同cpu核數(shù)相同
   ms_timestamp=1             #timestamp精度
   syslog=1                   #將日志寫(xiě)入到syslog中
   maxlog=1                   #將日志寫(xiě)入到maxscale的日志文件中
   log_to_shm=0               #不將日志寫(xiě)入到共享緩存中，開(kāi)啟debug模式時(shí)可打開(kāi)加快速度
   log_warning=1              #記錄告警信息
   log_notice=1               #記錄notice
   log_info=1                 #記錄info
   log_debug=0                #不打開(kāi)debug模式
   log_augmentation=1         #日志遞增
   #相關(guān)目錄設(shè)置 ,如果改變r(jià)pm的日志和數(shù)據(jù)文件路徑，需要通過(guò)創(chuàng)建相應(yīng)目錄并改變相應(yīng)屬主為maxscale。
   logdir=/usr/local/maxscale/log/
   datadir=/usr/local/maxscale/data/
   libdir=/usr/lib64/maxscale/
   cachedir=/usr/local/maxscale/cache/
   piddir=/usr/local/maxscale/
   execdir=/usr/bin/

   #相關(guān)的監(jiān)控信息，監(jiān)控的用戶(hù)需要對(duì)后端數(shù)據(jù)庫(kù)有訪問(wèn)replication client的權(quán)限：grant replication client
   [MySQL Monitor]
   type=monitor
   module=mysqlmon
   servers=server1,server2,server3
   user=root
   passwd=7AE087FBF864EBB87D108C3AB1603D0D
   monitor_interval=1000              #監(jiān)控心跳為1秒
   detect_replication_lag=true        #監(jiān)控主從復(fù)制延遲，可用后續(xù)指定router service的max_slave_replication_lag單位是秒，來(lái)控制maxscale運(yùn)行的最大延遲
   detect_stale_master=true           #當(dāng)復(fù)制slave全部斷掉時(shí)，maxscale仍然可用，將所有的訪問(wèn)指向

   #read-only的只讀節(jié)點(diǎn)slave分離
   [Read-Only Service]
   type=service
   router=readconnroute
   servers=server1,server2,server3
   user=root
   passwd=7AE087FBF864EBB87D108C3AB1603D0D
   router_options=slave
   enable_root_user=1

   #讀寫(xiě)分離，用戶(hù)需要有SELECT ON mysql.db；SELECT ON mysql.tables_priv；SHOW DATABASES ON *.*的權(quán)限
   [Read-Write Service]
   type=service
   router=readwritesplit
   servers=server1,server2,server3
   user=root
   passwd=7AE087FBF864EBB87D108C3AB1603D0D
   use_sql_variables_in=master                  #sql語(yǔ)句中的存在變量只指向master中執(zhí)行
   enable_root_user=1                           #允許root用戶(hù)登錄執(zhí)行
   # master_accept_reads=true                     #master節(jié)點(diǎn)也可以轉(zhuǎn)發(fā)讀請(qǐng)求
   max_slave_replication_lag=5                  #復(fù)制延遲最大為5秒(必須比monitor的interval大)
    3)maxscale缺點(diǎn)
   1）創(chuàng)建鏈接的時(shí)候，不支持壓縮協(xié)議
   2）轉(zhuǎn)發(fā)路由不能動(dòng)態(tài)的識(shí)別master節(jié)點(diǎn)的遷移
   3）LONGLOB字段不支持
   4）在一下情況會(huì)將語(yǔ)句轉(zhuǎn)到master節(jié)點(diǎn)中(保證事務(wù)一致)：
          明確指定事務(wù)；
         prepared的語(yǔ)句；
         語(yǔ)句中包含存儲(chǔ)過(guò)程，自定義函數(shù)
        包含多條語(yǔ)句信息：INSERT INTO ... ; SELECT LAST_INSERT_ID();
   5）一些語(yǔ)句默認(rèn)會(huì)發(fā)送到后端的所有server中，但是可以指定use_sql_variables_in=[master|all] (default: all)
   6）maxscale不支持主機(jī)名匹配的認(rèn)證模式，只支持IP地址方式的host解析。所以在添加user的時(shí)候記得使用合適的范式。
   7）跨庫(kù)查詢(xún)不支持，會(huì)顯示的指定到第一個(gè)數(shù)據(jù)庫(kù)中
   8）通過(guò)select方式改變會(huì)話變量的行為不支持

網(wǎng)頁(yè)名稱(chēng)：MySQL高可用架構(gòu)之MaxScale實(shí)踐
地址分享：http://muchs.cn/article12/ipppgc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、電子商務(wù)、網(wǎng)站設(shè)計(jì)、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、網(wǎng)站制作、定制網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容