MySQL5.7MHA+MaxScale2.0构建高可用环境
读写分离与负载均衡校验
Part1:读写分离
在Master构建相应的测试表
mysql> select * from helei; +--------+ | a | +--------+ | HE3 | +--------+ 3 rows in set (0.00 sec)
在slave1插入数据HE1
mysql> select * from helei; +------+ | a | +------+ | HE3 | | HE1 | +------+
在slave2插入数据HE2
mysql> select * from helei; +------+ | a | +------+ | HE3 | | HE2 | +------+
现在链接4006读写分离端口，进行数据写入
[root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e"insert into helei values('写入');"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@HE3 ~]# mysql -h192.168.1.250 -P 4008 -usys_admin -pMANAGER maxscale -e"select * from helei;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| a |
+--------+
| HE3 |
| HE1 |
| 写入 |
+--------+
可以看到主库插入完毕后从库已经同步完成，这条查询完成在了HE1（slave1）上
Part2:负载均衡
我们配置的read比例为1:1
[root@HE3 ~]# maxadmin -pmariadb show service "Read-Write Service"
Service 0xef5570
Service: Read-Write Service
Router: readwritesplit (0x7ff5e8fa6ec0)
State: Started
Number of router sessions: 15
Current no. of router sessions: 0
Number of queries forwarded: 41
Number of queries forwarded to master:2 (4.88%)
Number of queries forwarded to slave: 39 (95.12%)
Number of queries forwarded to all: 0 (0.00%)
Connection distribution based on myweight server parameter.
Server Target % Connections Operations
Global Router
server1 50.0% 0 0 0
server2 50.0% 0 0 0
server3 100.0% 0 0 0
Started: Thu Nov 3 23:46:27 2016
Root user access: Enabled
Backend databases:
192.168.1.248:3306 Protocol: MySQLBackend
192.168.1.249:3306 Protocol: MySQLBackend
192.168.1.100:3306 Protocol: MySQLBackend
Routing weight parameter: myweight
Users data: 0xf09370
Total connections: 16
Currently connected: 1
[root@HE3 ~]# for i in `seq 1 10`; do mysql -h 192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e "select @@hostname; select sleep(10)" 2>/dev/null & done [root@HE3 ~]# +------------+ | @@hostname | +------------+ | HE1 | +------------+ +------------+ | @@hostname | +------------+ | HE1 | +------------+ +------------+ | @@hostname | +------------+ | HE1 | +------------+ +------------+ | @@hostname | +------------+ | HE1 | +------------+ +------------+ | @@hostname | +------------+ | HE1 | +------------+ +------------+ | @@hostname | +------------+ | HE2 | +------------+ +------------+ | @@hostname | +------------+ | HE2 | +------------+ +------------+ | @@hostname | +------------+ | HE2 | +------------+ +------------+ | @@hostname | +------------+ | HE2 | +------------+ +------------+ | @@hostname | +------------+ | HE2 | +------------+
OK!That's how it works!~
故障测试
Part1:stop slave故障
停止HE1的复制
[root@HE1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 74 Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> stop slave; Query OK, 0 rows affected (0.05 sec) [root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Running server2 | 192.168.1.249 | 3306 | 0 | Slave, Running server3 | 192.168.1.250 | 3306 | 0 | Master, Running -------------------+-----------------+-------+-------------+-------------------- [root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e"select * from helei;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | a | +--------+ | HE3 | | HE2 | | 写入 | +--------+ [root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e"select * from helei;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | a | +--------+ | HE3 | | HE2 | | 写入 | +--------+
可以看出，在slave1故障后，所有的读操作都进入了HE2(slave2)；
恢复HE1
mysql> start slave; Query OK, 0 rows affected (0.00 sec) [root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Slave, Running server2 | 192.168.1.249 | 3306 | 0 | Slave, Running server3 | 192.168.1.250 | 3306 | 0 | Master, Running -------------------+-----------------+-------+-------------+-------------------- 验证 [root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e"select * from helei;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | a | +--------+ | HE3 | | HE2 | | 写入 | +--------+ [root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e"select * from helei;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | a | +--------+ | HE3 | | HE1 | | 写入 | +--------+
在HE1（slave1）恢复完成后，重新有了负载均衡。
Part2:mysql down故障
[root@HE1 ~]# /etc/init.d/mysqld stop Shutting down MySQL..... SUCCESS! 停止HE1(slave)可以看到转发到了HE2上 [root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Down server2 | 192.168.1.249 | 3306 | 1 | Slave, Running server3 | 192.168.1.250 | 3306 | 1 | Master, Running -------------------+-----------------+-------+-------------+-------------------- [root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28948 Server version: 5.5.5-10.0.0 2.0.1-maxscale MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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 @@hostname; +------------+ | @@hostname | +------------+ | HE2 | +------------+ 1 row in set (0.00 sec) 停掉2台slave，观察maxscale的状态 [root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Running server2 | 192.168.1.249 | 3306 | 0 | Running server3 | 192.168.1.250 | 3306 | 0 | Master, Stale Status, Running -------------------+-----------------+-------+-------------+--------------------
Warning:警告这里我并没有在
[MySQL Monitor]中配置detect_stale_master=true
可以看出，在maxscale2.0中，已经默认从库都停掉，也不影响
Part3:master故障
[root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Slave, Running server2 | 192.168.1.249 | 3306 | 0 | Slave, Running server3 | 192.168.1.100 | 3306 | 0 | Master, Running -------------------+-----------------+-------+-------------+-------------------- [root@HE3 ~]# ps -ef|grep mysql root 27709 1 0 Nov03 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/HE3.pid mysql 28415 27709 0 Nov03 ? 00:00:19 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/error.log --open-files-limit=8192 --pid-file=/data/mysql/HE3.pid --socket=/tmp/mysql.sock --port=3306 root 30794 28966 0 02:34 pts/1 00:00:00 grep mysql [root@HE3 ~]# kill -9 28415 27709 [root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Slave, Running server2 | 192.168.1.249 | 3306 | 0 | Slave, Running server3 | 192.168.1.100 | 3306 | 0 | Master, Running -------------------+-----------------+-------+-------------+-------------------- [root@HE3 ~]# maxadmin -pmariadb list servers Servers. -------------------+-----------------+-------+-------------+-------------------- Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+-------------------- server1 | 192.168.1.248 | 3306 | 0 | Master, Running server2 | 192.168.1.249 | 3306 | 0 | Slave, Running server3 | 192.168.1.100 | 3306 | 0 | Running -------------------+-----------------+-------+-------------+--------------------
可以看出，kill master的mysql进程后，MHA将slave1拉成新的master,Maxsale也识别了这一状态。
Java Druid注意事项
------------以下文章取材于贺春旸技术博客-------------------
http://hcymysql.blog.51cto.com/5223301/1869414
现象：
程序会不定时的出现连接错误，问题bug的异常信息如下：
The last packet successfully received from the server was 116 milliseconds ago. The last packet sent successfully to the server was 115 milliseconds ago.
java.sql.SQLException: No database selected
但通过客户端sqlyog/navicat连接均为正常。
----------------------------------------------------------------------
另：maxscale1.4.3有时还会出现挂起现象，4006端口直接关闭。后我们用了watch命令后台跑监控。
watch -d /bin/bash /root/sh/restart_maxsacle.sh
#!/bin/bash netstat -ntlp | grep maxscale | grep 4006 > /dev/null 2>&1 if [ $? -eq 1 ];then /etc/init.d/maxscale start fi
解决：5.6以下可以直接在url上配置：autoReconnect=true
对于5.6以上的只能在jdbc的链接池里设置：
第一个设置成ture,超时自动链接，对于mysql要把第二个设置成false，不启用缓存。
以上是开发需要注意的都是一些基本的配置。
其目的为：每次归还连接时执行select 'x'检测连接是否有效。
——总结——
maxscale的核心就在于内个配置文件，根据不同的业务和架构，做好相应的配置，本文意在抛砖引玉，如果您有更全的配置文件，欢迎您分享给笔者。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
1 2
THE END
