MySQL高可用架構之MaxScale實踐
本次安裝部署基于MHA已部署的情況下,只部署MaxScale,并且采用的是rpm包安裝
MaxScale是mariadb公司開發的一套數據庫中間件。它是一個支持高可用、讀寫分離、
負載均衡，并且具有良好的可擴展性，不但高性能的基于事件驅動，同時具有代理和管理功能。
1、MaxScale軟件下載
下載地址：https://downloads.mariadb.com/files/MaxScale/
https://github.com/mariadb-corporation/MaxScale --源碼下載
參考手冊：https://downloads.mariadb.com/files/MaxScale/docs
https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale
2、解壓安裝包
（二進制安裝包）
[root@node3 MaxScale]# tar -xzvf maxscale-1.4.3-1.rhel.6.x86_64.tar.gz
3、安裝依賴包
配置本地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
依賴包檢查安裝：
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
升級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）源碼安裝
(以下是源碼編譯，其中編譯沒通過，源碼安裝失敗，建議在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 ./
或者手動指定編譯參數，如下
cmake ./ -DCMAKE_INSTALL_PREFIX=/usr/local/maxscale \
-D
MYSQL_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）二進制安裝
[root@node3 MaxScale]# mv maxscale-1.4.3-1.rhel.6.x86_64 /usr/local/maxscale
在~/.bash_profile中添加以下環境變量
export MAXSCALE_HOME=/usr/local/maxscale
export LD_LIBRARY_PATH=/usr/local/maxscale/lib
MaxScale配置：
在 master 中為 MaxScale 創建兩個用戶，用于監控模塊和路由模塊
創建監控賬戶
mysql> create user maxscalemon@'%' identified by "monitor" ;
mysql> grant replication slave, replication client on *.* to maxscalemon@'%';
創建路由用戶
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地址和端口以及復制相應的server2,server3配置，修改監控和路由配置，清除只讀服務配置。
[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
將加密后的密碼填寫到配置文件中
啟動maxscale服務：
[root@node3 MaxScale]# maxscale --config=/etc/maxscale.cnf
或者 maxscale -f /etc/maxscale.cnf
5、MaxScale使用
1） 通過service服務關閉、啟動、重啟、查看maxscale狀態等
[root@node3
MaxScale]# service maxscale
Usage:
/etc/init.d/maxscale {start|stop|status|restart|condrestart|reload}
2） 啟動maxscale
service maxscale start
maxscale -f /etc/maxscale.conf
3） 關閉maxscale
service
maxscale stop
4） 登陸maxscale管理控制臺管理
[root@node3
MaxScale]# maxadmin -uadmin -pmariadb
-P6603
6、MaxScale讀寫分離和負載均衡測試
開啟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安裝錯誤信息以及解決方案
錯誤信息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
解決方案：
上述錯誤是源碼編譯報錯，暫無好的解決方案，如果可以連接外網可以通過源碼編譯安裝或者使用高版本os的
服務器。
錯誤信息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.
解決方案：
創建密碼文件，并將加密后的密碼在配置文件中替換。
maxkeys /var/lib/maxscale
maxpasswd /var/lib/maxscale/.secrets monitor
maxpasswd /var/lib/maxscale/.secrets maxscale
錯誤信息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內容補全，內容如下
[Read-Only Service]
type=service
router=readconnroute
servers=
user=maxscale
passwd=26C20853B625AD18686C0D2AC8A11E60
router_options=slave
錯誤信息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
解決方案：
通過升級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默認安裝包相關路徑
/usr/share/maxscale/ 共享配置安裝路徑
/var/lib/maxscale 數據文件目錄
/usr/bin/maxscale 執行文件目錄
/var/log/maxscale 日志目錄
/usr/lib64/maxscale 庫文件目錄
2）配置文件詳解
[maxscale]
threads=auto #開啟線程個數，默認為1.設置為auto會同cpu核數相同
ms_timestamp=1 #timestamp精度
syslog=1 #將日志寫入到syslog中
maxlog=1 #將日志寫入到maxscale的日志文件中
log_to_shm=0 #不將日志寫入到共享緩存中，開啟debug模式時可打開加快速度
log_warning=1 #記錄告警信息
log_notice=1 #記錄notice
log_info=1 #記錄info
log_debug=0 #不打開debug模式
log_augmentation=1 #日志遞增
#相關目錄設置 ,如果改變rpm的日志和數據文件路徑，需要通過創建相應目錄并改變相應屬主為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/
#相關的監控信息，監控的用戶需要對后端數據庫有訪問replication client的權限：grant replication client
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=root
passwd=7AE087FBF864EBB87D108C3AB1603D0D
monitor_interval=1000 #監控心跳為1秒
detect_replication_lag=true #監控主從復制延遲，可用后續指定router service的max_slave_replication_lag單位是秒，來控制maxscale運行的最大延遲
detect_stale_master=true #當復制slave全部斷掉時，maxscale仍然可用，將所有的訪問指向
#read-only的只讀節點slave分離
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=root
passwd=7AE087FBF864EBB87D108C3AB1603D0D
router_options=slave
enable_root_user=1
#讀寫分離，用戶需要有SELECT ON mysql.db；SELECT ON mysql.tables_priv；SHOW DATABASES ON *.*的權限
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=root
passwd=7AE087FBF864EBB87D108C3AB1603D0D
use_sql_variables_in=master #sql語句中的存在變量只指向master中執行
enable_root_user=1 #允許root用戶登錄執行
# master_accept_reads=true #master節點也可以轉發讀請求
max_slave_replication_lag=5 #復制延遲最大為5秒(必須比monitor的interval大)
3)maxscale缺點
1）創建鏈接的時候，不支持壓縮協議
2）轉發路由不能動態的識別master節點的遷移
3）LONGLOB字段不支持
4）在一下情況會將語句轉到master節點中(保證事務一致)：
明確指定事務；
prepared的語句；
語句中包含存儲過程，自定義函數
包含多條語句信息：INSERT INTO ... ; SELECT LAST_INSERT_ID();
5）一些語句默認會發送到后端的所有server中，但是可以指定use_sql_variables_in=[master|all] (default: all)
6）maxscale不支持主機名匹配的認證模式，只支持IP地址方式的host解析。所以在添加user的時候記得使用合適的范式。
7）跨庫查詢不支持，會顯示的指定到第一個數據庫中
8）通過select方式改變會話變量的行為不支持
