共计 43556 个字符，预计需要花费 109 分钟才能阅读完成。
MySQL 读写分离与负载均衡 –MHA 与 MaxScale
环境介绍
Part1: 写在最前
看了某大牛的文章，讲述了一下 MaxScale 比 LVS 的好处多多，那您倒是放出来配置文件啊~~ 大牛说：
需要的单独找我吧，太长了配置文件……
看到这我心中久久不能平静啊。。。联系不上您呐 = =，于是各种资料各种找啊~ 各种坑各种血崩啊！~~~
由于不知道大牛的配置文件是什么样子，本文仅以随笔的形式，记录下实施过程。也欢迎您和我探讨您在实施 MaxScale 时遇到的各种问题和心得体会。
Part2: 环境
MySQL5.7 MHA + MaxScale2.0
192.168.1.248 HE1 slave1
192.168.1.249 HE2 slave2
192.168.1.250 HE3 master
192.168.1.251 HE4 MHA-manager
Part3:MHA
MHA 的优点不作赘述，看下原理图吧
从宕机崩溃的 Master 保存二进制日志事件 (binlogevent)
识别含有最新更新的 Slave
应用差异的中继日志 (relaylog) 到其他 Slave
应用从 Master 保存的二进制日志事件
提升一个 Slave 为新的 Master
使其他的 Slave 连接新的 Master 进行复制
构建 MySQL5.7MHA
Part1: 写在最前
MHA 的部署不是本文的叙述重点，网上比比皆是。这里只记录下 MySQL5.7 的 MHA 搭建时的一些坑
Part2: 坑
①mha4mysql-manager-0.57.tar.gz
②mha4mysql-node-0.57.tar.gz
Warning: 警告这两个包首先你要搞到，虽然说 0.56 什么的不代表支持的 mysql 版本，但经过测试，想要在 MySQL5.7 上部署 MHA，少走坑，请用 0.57 的。
Part3: 安装包的位置
请在所有的节点包括 Manager 节点安装好你的 mha4mysql-node-0.57.tar.gz，以保证后期在管理节点执行 perl Makefile.PL 的时候，你能如愿以偿的看到如下信息
[root@HE4 mha4mysql-manager-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies…
[Core Features]
– DBI …loaded. (1.609)
– DBD::mysql …loaded. (4.013)
– Time::HiRes …loaded. (1.9721)
– Config::Tiny …loaded. (2.12)
– Log::Dispatch …loaded. (2.26)
– Parallel::ForkManager …loaded. (0.7.5)
– MHA::NodeConst …loaded. (0.57)
*** Module::AutoInstall configuration finished.
Writing Makefile for mha4mysql::manager
Part4: 一些常见错误记录
如果遇到
①这样
[root@HE2 bin]# masterha_check_repl –conf=/etc/mha/mha.conf
Tue Apr 5 22:09:32 2016 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 5 22:09:32 2016 – [info] Reading application default configuration from /etc/mha/mha.conf..
Tue Apr 5 22:09:32 2016 – [info] Reading server configuration from /etc/mha/mha.conf..
Tue Apr 5 22:09:32 2016 – [info] MHA::MasterMonitor version 0.57.
Tue Apr 5 22:09:32 2016 – [error][/usr/local/lib64/perl5/MHA/ServerManager.pm, ln188] There is no alive server. We can’t do failover
Tue Apr 5 22:09:32 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/lib64/perl5/MHA/MasterMonitor.pm line 326
Tue Apr 5 22:09:32 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Tue Apr 5 22:09:32 2016 – [info] Got exit code 1 (Not master dead).
解决方案
用的不是默认端口 3306，请修改你的配置文件
②这样
[root@HE4 ~]#
masterha_check_repl –conf=/etc/mha/mha.conf
Tue Apr 5 22:36:33 2016 – [warning] Global
configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 5 22:36:33 2016 – [info] Reading application
default configuration from /etc/mha/mha.conf..
Tue Apr 5 22:36:33 2016 – [info] Reading server
configuration from /etc/mha/mha.conf..
Tue Apr 5 22:36:33 2016 – [info] MHA::MasterMonitor
version 0.57.
Tue Apr 5 22:36:34 2016 – [info] GTID failover mode =
0
Tue Apr 5 22:36:34 2016 – [info] Dead Servers:
Tue Apr 5 22:36:34 2016 – [info] Alive Servers:
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.248(192.168.1.248:4008)
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.249(192.168.1.249:4008)
Tue Apr 5 22:36:34 2016 – [info] Alive Slaves:
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.248(192.168.1.248:4008) Version=5.6.16-log (oldest major version
between slaves) log-bin:enabled
Tue Apr 5 22:36:34 2016 – [info] Replicating from
192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] Primary candidate for the new Master
(candidate_master is set)
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.249(192.168.1.249:4008) Version=5.6.16-log (oldest major version
between slaves) log-bin:enabled
Tue Apr 5 22:36:34 2016 – [info] Replicating from
192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] Not candidate for the new Master
(no_master is set)
Tue Apr 5 22:36:34 2016 – [info] Current Alive
Master: 192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] Checking slave
configurations..
Tue Apr 5 22:36:34 2016 – [warning] relay_log_purge=0 is not set on slave
192.168.1.248(192.168.1.248:4008).
Tue Apr 5 22:36:34 2016 – [warning] relay_log_purge=0 is not set on slave
192.168.1.249(192.168.1.249:4008).
Tue Apr 5 22:36:34 2016 – [info] Checking replication
filtering settings..
Tue Apr 5 22:36:34 2016 – [info] binlog_do_db= , binlog_ignore_db=
Tue Apr 5 22:36:34 2016 – [info] Replication filtering check ok.
Tue Apr 5 22:36:34 2016 – [info] GTID (with auto-pos)
is not supported
Tue Apr 5 22:36:34 2016 – [info] Starting SSH
connection tests..
Tue Apr 5 22:36:35 2016 – [info] All SSH connection
tests passed successfully.
Tue Apr 5 22:36:35 2016 – [info] Checking MHA Node
version..
Tue Apr 5 22:36:36 2016 – [info] Version check ok.
Tue Apr 5 22:36:36 2016 – [info] Checking SSH
publickey authentication settings on the current master..
Tue Apr 5 22:36:36 2016 – [info] HealthCheck: SSH to
192.168.1.250 is reachable.
Tue Apr 5 22:36:36 2016 – [info] Master MHA Node
version is 0.57.
Tue Apr 5 22:36:36 2016 – [info] Checking recovery
script configurations on 192.168.1.250(192.168.1.250:4008)..
Tue Apr 5 22:36:36 2016 – [info] Executing command: save_binary_logs
–command=test –start_pos=4 –binlog_dir=/log/mysql
–output_file=/usr/local/mha/save_binary_logs_test –manager_version=0.57
–start_file=mysql-bin.000009
Tue Apr 5 22:36:36 2016 – [info] Connecting to
root@192.168.1.250(192.168.1.250:22)..
Creating /usr/local/mha if not exists..
Creating directory /usr/local/mha.. done.
ok.
Checking output directory is accessible or
not..
ok.
Binlog found at /log/mysql, up to
mysql-bin.000009
Tue Apr 5 22:36:36 2016 – [info] Binlog setting check
done.
Tue Apr 5 22:36:36 2016 – [info] Checking SSH
publickey authentication and checking recovery script configurations on all
alive slave servers..
Tue Apr 5 22:36:36 2016 – [info] Executing command : apply_diff_relay_logs
–command=test –slave_user=’root’ –slave_host=192.168.1.248
–slave_ip=192.168.1.248 –slave_port=4008 –workdir=/usr/local/mha
–target_version=5.6.16-log –manager_version=0.57
–relay_log_info=/data/mysql/relay-log.info
–relay_dir=/data/mysql/
–slave_pass=xxx
Tue Apr 5 22:36:36 2016 – [info] Connecting to
root@192.168.1.248(192.168.1.248:22)..
Can’t exec
“mysqlbinlog”: No such file or directory at
/usr/local/lib64/perl5/MHA/BinlogManager.pm line 106.
mysqlbinlog version
command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client
options
at /usr/local/bin/apply_diff_relay_logs line
493
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings
check failed!
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration
failed.
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424] Error happened on
checking configurations. at
/usr/local/bin/masterha_check_repl line 48
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523] Error happened on
monitoring servers.
Tue Apr 5 22:36:36 2016 – [info] Got exit code 1 (Not
master dead).
MySQL Replication
Health is NOT OK!
解决方案
[root@HE1 MHA]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
③或者这样
12345678910111213 Binlog found at /log/mysql, up to mysql-bin.000009
Tue Apr 5 22:43:55 2016 – [info] Binlog setting check done.
Tue Apr 5 22:43:55 2016 – [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Apr 5 22:43:55 2016 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=’root’ –slave_host=192.168.1.248 –slave_ip=192.168.1.248 –slave_port=4008 –workdir=/usr/local/mha –target_version=5.6.16-log –manager_version=0.57 –relay_log_info=/data/mysql/relay-log.info –relay_dir=/data/mysql/ –slave_pass=xxx
Tue Apr 5 22:43:55 2016 – [info] Connecting to root@192.168.1.248(192.168.1.248:22)..
mysqlbinlog: unknown variable ‘default-character-set=utf8’
mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options
at /usr/local/bin/apply_diff_relay_logs line 493
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings check failed!
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration failed.
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Tue Apr 5 22:43:55 2016 – [info] Got exit code 1 (Not master dead).
解决方案
注释掉 my.cnf 中的
[client]
#default-character-set=utf8
遇到上述错误别慌张，看日志，根据报错来排查问题。
折腾半天，就为了这个 OK
[root@HE4 mha4mysql-manager-0.57]# masterha_check_status –conf=/etc/mha/mha.conf
mha (pid:32726) is running(0:PING_OK), master:192.168.1.250
MaxScale2.0
Part1: 写在最前
maxscale 是 mariadb 公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。另外 maxscale 对于前端应用而言是透明的，我们可以很方便的将应用迁移到 maxscale 中实现读写分离方案，来分担主库的压力。maxscale 也提供了 sql 语句的解析过滤功能。这里我们主要讲解 maxscale 的安装、配置以及注意事项。
Part2: 整体架构
Part3: 安装
Maxscale 配置很简单
[root@HE3 MHA]# yum -y install maxscale-2.0.1-2.CentOS.6.x86_64.rpm (只在 Maxscale 上执行)
[root@HE3 ~]# cat /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
# Global parameters
#
# Number of threads is autodetected, uncomment for manual configuration
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=auto
# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=192.168.1.248
port=3306
protocol=MySQLBackend
myweight=5
[server2]
type=server
address=192.168.1.249
port=3306
protocol=MySQLBackend
myweight=5
[server3]
type=server
address=192.168.1.100
port=3306
protocol=MySQLBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=mysync
passwd=MANAGER
monitor_interval=10000
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
#[Read-Only Service] ### 只读服务
#type=service
#router=readconnroute
#servers=server1,server2,server3
#user=sys_admin
#passwd=MANAGER
#router_options=slave
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
[Read-Write Service] #### 写服务
type=service
router=readwritesplit
enable_root_user=1
servers=server1,server2,server3
user=sys_admin
passwd=MANAGER
weightby=myweight
router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS
max_slave_connections=1
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
[MaxAdmin Service]
type=service
router=cli
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008 ## 读服务启动监听 端口 4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 #### 写服务启动监听 端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603 ### 管理端口
[root@HE3 ~]# /etc/init.d/maxscale start
Starting MaxScale: maxscale (pid 28851) is running… [OK]
[root@HE3 ~]# netstat -lntp |grep maxscale
tcp 0 0 0.0.0.0:6603 0.0.0.0:* LISTEN 29878/maxscale
tcp 0 0 0.0.0.0:4006 0.0.0.0:* LISTEN 29878/maxscale
[root@HE3 ~]# maxadmin -pmariadb list services
Services.
————————–+———————-+——–+—————
Service Name | Router Module | #Users | Total Sessions
————————–+———————-+——–+—————
Read-Only Service | readconnroute | 1 | 5
Read-Write Service | readwritesplit | 1 | 11
MaxAdmin Service | cli | 2 | 3
————————–+———————-+——–+—————
[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
——————-+—————–+——-+————-+——————–
至此，MHA+Maxscale 的环境就完成了。
读写分离与负载均衡校验
Part1: 读写分离
在 Master 构建相应的测试表
1234567 mysql> select * from helei;
+——–+
| a |
+——–+
| HE3 |
+——–+
3 rows in set (0.00 sec)
在 slave1 插入数据 HE1
mysql> select * from helei;
+——+
| a |
+——+
| HE3 |
| HE1 |
+——+
在 slave2 插入数据 HE2
1234567 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://www.linuxidc.com/Linux/2016-11/136970.htm
现象：
程序会不定时的出现连接错误，问题 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 的核心就在于内个配置文件，根据不同的业务和架构，做好相应的配置，本文意在抛砖引玉，如果您有更全的配置文件，欢迎您分享给笔者。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/136969.htm
MySQL 读写分离与负载均衡 –MHA 与 MaxScale
环境介绍
Part1: 写在最前
看了某大牛的文章，讲述了一下 MaxScale 比 LVS 的好处多多，那您倒是放出来配置文件啊~~ 大牛说：
需要的单独找我吧，太长了配置文件……
看到这我心中久久不能平静啊。。。联系不上您呐 = =，于是各种资料各种找啊~ 各种坑各种血崩啊！~~~
由于不知道大牛的配置文件是什么样子，本文仅以随笔的形式，记录下实施过程。也欢迎您和我探讨您在实施 MaxScale 时遇到的各种问题和心得体会。
Part2: 环境
MySQL5.7 MHA + MaxScale2.0
192.168.1.248 HE1 slave1
192.168.1.249 HE2 slave2
192.168.1.250 HE3 master
192.168.1.251 HE4 MHA-manager
Part3:MHA
MHA 的优点不作赘述，看下原理图吧
从宕机崩溃的 Master 保存二进制日志事件 (binlogevent)
识别含有最新更新的 Slave
应用差异的中继日志 (relaylog) 到其他 Slave
应用从 Master 保存的二进制日志事件
提升一个 Slave 为新的 Master
使其他的 Slave 连接新的 Master 进行复制
构建 MySQL5.7MHA
Part1: 写在最前
MHA 的部署不是本文的叙述重点，网上比比皆是。这里只记录下 MySQL5.7 的 MHA 搭建时的一些坑
Part2: 坑
①mha4mysql-manager-0.57.tar.gz
②mha4mysql-node-0.57.tar.gz
Warning: 警告这两个包首先你要搞到，虽然说 0.56 什么的不代表支持的 mysql 版本，但经过测试，想要在 MySQL5.7 上部署 MHA，少走坑，请用 0.57 的。
Part3: 安装包的位置
请在所有的节点包括 Manager 节点安装好你的 mha4mysql-node-0.57.tar.gz，以保证后期在管理节点执行 perl Makefile.PL 的时候，你能如愿以偿的看到如下信息
[root@HE4 mha4mysql-manager-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies…
[Core Features]
– DBI …loaded. (1.609)
– DBD::mysql …loaded. (4.013)
– Time::HiRes …loaded. (1.9721)
– Config::Tiny …loaded. (2.12)
– Log::Dispatch …loaded. (2.26)
– Parallel::ForkManager …loaded. (0.7.5)
– MHA::NodeConst …loaded. (0.57)
*** Module::AutoInstall configuration finished.
Writing Makefile for mha4mysql::manager
Part4: 一些常见错误记录
如果遇到
①这样
[root@HE2 bin]# masterha_check_repl –conf=/etc/mha/mha.conf
Tue Apr 5 22:09:32 2016 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 5 22:09:32 2016 – [info] Reading application default configuration from /etc/mha/mha.conf..
Tue Apr 5 22:09:32 2016 – [info] Reading server configuration from /etc/mha/mha.conf..
Tue Apr 5 22:09:32 2016 – [info] MHA::MasterMonitor version 0.57.
Tue Apr 5 22:09:32 2016 – [error][/usr/local/lib64/perl5/MHA/ServerManager.pm, ln188] There is no alive server. We can’t do failover
Tue Apr 5 22:09:32 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/lib64/perl5/MHA/MasterMonitor.pm line 326
Tue Apr 5 22:09:32 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Tue Apr 5 22:09:32 2016 – [info] Got exit code 1 (Not master dead).
解决方案
用的不是默认端口 3306，请修改你的配置文件
②这样
[root@HE4 ~]#
masterha_check_repl –conf=/etc/mha/mha.conf
Tue Apr 5 22:36:33 2016 – [warning] Global
configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 5 22:36:33 2016 – [info] Reading application
default configuration from /etc/mha/mha.conf..
Tue Apr 5 22:36:33 2016 – [info] Reading server
configuration from /etc/mha/mha.conf..
Tue Apr 5 22:36:33 2016 – [info] MHA::MasterMonitor
version 0.57.
Tue Apr 5 22:36:34 2016 – [info] GTID failover mode =
0
Tue Apr 5 22:36:34 2016 – [info] Dead Servers:
Tue Apr 5 22:36:34 2016 – [info] Alive Servers:
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.248(192.168.1.248:4008)
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.249(192.168.1.249:4008)
Tue Apr 5 22:36:34 2016 – [info] Alive Slaves:
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.248(192.168.1.248:4008) Version=5.6.16-log (oldest major version
between slaves) log-bin:enabled
Tue Apr 5 22:36:34 2016 – [info] Replicating from
192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] Primary candidate for the new Master
(candidate_master is set)
Tue Apr 5 22:36:34 2016 – [info] 192.168.1.249(192.168.1.249:4008) Version=5.6.16-log (oldest major version
between slaves) log-bin:enabled
Tue Apr 5 22:36:34 2016 – [info] Replicating from
192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] Not candidate for the new Master
(no_master is set)
Tue Apr 5 22:36:34 2016 – [info] Current Alive
Master: 192.168.1.250(192.168.1.250:4008)
Tue Apr 5 22:36:34 2016 – [info] Checking slave
configurations..
Tue Apr 5 22:36:34 2016 – [warning] relay_log_purge=0 is not set on slave
192.168.1.248(192.168.1.248:4008).
Tue Apr 5 22:36:34 2016 – [warning] relay_log_purge=0 is not set on slave
192.168.1.249(192.168.1.249:4008).
Tue Apr 5 22:36:34 2016 – [info] Checking replication
filtering settings..
Tue Apr 5 22:36:34 2016 – [info] binlog_do_db= , binlog_ignore_db=
Tue Apr 5 22:36:34 2016 – [info] Replication filtering check ok.
Tue Apr 5 22:36:34 2016 – [info] GTID (with auto-pos)
is not supported
Tue Apr 5 22:36:34 2016 – [info] Starting SSH
connection tests..
Tue Apr 5 22:36:35 2016 – [info] All SSH connection
tests passed successfully.
Tue Apr 5 22:36:35 2016 – [info] Checking MHA Node
version..
Tue Apr 5 22:36:36 2016 – [info] Version check ok.
Tue Apr 5 22:36:36 2016 – [info] Checking SSH
publickey authentication settings on the current master..
Tue Apr 5 22:36:36 2016 – [info] HealthCheck: SSH to
192.168.1.250 is reachable.
Tue Apr 5 22:36:36 2016 – [info] Master MHA Node
version is 0.57.
Tue Apr 5 22:36:36 2016 – [info] Checking recovery
script configurations on 192.168.1.250(192.168.1.250:4008)..
Tue Apr 5 22:36:36 2016 – [info] Executing command: save_binary_logs
–command=test –start_pos=4 –binlog_dir=/log/mysql
–output_file=/usr/local/mha/save_binary_logs_test –manager_version=0.57
–start_file=mysql-bin.000009
Tue Apr 5 22:36:36 2016 – [info] Connecting to
root@192.168.1.250(192.168.1.250:22)..
Creating /usr/local/mha if not exists..
Creating directory /usr/local/mha.. done.
ok.
Checking output directory is accessible or
not..
ok.
Binlog found at /log/mysql, up to
mysql-bin.000009
Tue Apr 5 22:36:36 2016 – [info] Binlog setting check
done.
Tue Apr 5 22:36:36 2016 – [info] Checking SSH
publickey authentication and checking recovery script configurations on all
alive slave servers..
Tue Apr 5 22:36:36 2016 – [info] Executing command : apply_diff_relay_logs
–command=test –slave_user=’root’ –slave_host=192.168.1.248
–slave_ip=192.168.1.248 –slave_port=4008 –workdir=/usr/local/mha
–target_version=5.6.16-log –manager_version=0.57
–relay_log_info=/data/mysql/relay-log.info
–relay_dir=/data/mysql/
–slave_pass=xxx
Tue Apr 5 22:36:36 2016 – [info] Connecting to
root@192.168.1.248(192.168.1.248:22)..
Can’t exec
“mysqlbinlog”: No such file or directory at
/usr/local/lib64/perl5/MHA/BinlogManager.pm line 106.
mysqlbinlog version
command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client
options
at /usr/local/bin/apply_diff_relay_logs line
493
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings
check failed!
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration
failed.
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424] Error happened on
checking configurations. at
/usr/local/bin/masterha_check_repl line 48
Tue Apr 5 22:36:36 2016 –
[error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523] Error happened on
monitoring servers.
Tue Apr 5 22:36:36 2016 – [info] Got exit code 1 (Not
master dead).
MySQL Replication
Health is NOT OK!
解决方案
[root@HE1 MHA]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
③或者这样
12345678910111213 Binlog found at /log/mysql, up to mysql-bin.000009
Tue Apr 5 22:43:55 2016 – [info] Binlog setting check done.
Tue Apr 5 22:43:55 2016 – [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Apr 5 22:43:55 2016 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=’root’ –slave_host=192.168.1.248 –slave_ip=192.168.1.248 –slave_port=4008 –workdir=/usr/local/mha –target_version=5.6.16-log –manager_version=0.57 –relay_log_info=/data/mysql/relay-log.info –relay_dir=/data/mysql/ –slave_pass=xxx
Tue Apr 5 22:43:55 2016 – [info] Connecting to root@192.168.1.248(192.168.1.248:22)..
mysqlbinlog: unknown variable ‘default-character-set=utf8’
mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options
at /usr/local/bin/apply_diff_relay_logs line 493
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings check failed!
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration failed.
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48
Tue Apr 5 22:43:55 2016 – [error][/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Tue Apr 5 22:43:55 2016 – [info] Got exit code 1 (Not master dead).
解决方案
注释掉 my.cnf 中的
[client]
#default-character-set=utf8
遇到上述错误别慌张，看日志，根据报错来排查问题。
折腾半天，就为了这个 OK
[root@HE4 mha4mysql-manager-0.57]# masterha_check_status –conf=/etc/mha/mha.conf
mha (pid:32726) is running(0:PING_OK), master:192.168.1.250
MaxScale2.0
Part1: 写在最前
maxscale 是 mariadb 公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。另外 maxscale 对于前端应用而言是透明的，我们可以很方便的将应用迁移到 maxscale 中实现读写分离方案，来分担主库的压力。maxscale 也提供了 sql 语句的解析过滤功能。这里我们主要讲解 maxscale 的安装、配置以及注意事项。
Part2: 整体架构
Part3: 安装
Maxscale 配置很简单
[root@HE3 MHA]# yum -y install maxscale-2.0.1-2.CentOS.6.x86_64.rpm (只在 Maxscale 上执行)
[root@HE3 ~]# cat /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
# Global parameters
#
# Number of threads is autodetected, uncomment for manual configuration
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=auto
# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=192.168.1.248
port=3306
protocol=MySQLBackend
myweight=5
[server2]
type=server
address=192.168.1.249
port=3306
protocol=MySQLBackend
myweight=5
[server3]
type=server
address=192.168.1.100
port=3306
protocol=MySQLBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=mysync
passwd=MANAGER
monitor_interval=10000
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
#[Read-Only Service] ### 只读服务
#type=service
#router=readconnroute
#servers=server1,server2,server3
#user=sys_admin
#passwd=MANAGER
#router_options=slave
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
[Read-Write Service] #### 写服务
type=service
router=readwritesplit
enable_root_user=1
servers=server1,server2,server3
user=sys_admin
passwd=MANAGER
weightby=myweight
router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS
max_slave_connections=1
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
[MaxAdmin Service]
type=service
router=cli
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008 ## 读服务启动监听 端口 4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 #### 写服务启动监听 端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603 ### 管理端口
[root@HE3 ~]# /etc/init.d/maxscale start
Starting MaxScale: maxscale (pid 28851) is running… [OK]
[root@HE3 ~]# netstat -lntp |grep maxscale
tcp 0 0 0.0.0.0:6603 0.0.0.0:* LISTEN 29878/maxscale
tcp 0 0 0.0.0.0:4006 0.0.0.0:* LISTEN 29878/maxscale
[root@HE3 ~]# maxadmin -pmariadb list services
Services.
————————–+———————-+——–+—————
Service Name | Router Module | #Users | Total Sessions
————————–+———————-+——–+—————
Read-Only Service | readconnroute | 1 | 5
Read-Write Service | readwritesplit | 1 | 11
MaxAdmin Service | cli | 2 | 3
————————–+———————-+——–+—————
[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
——————-+—————–+——-+————-+——————–
至此，MHA+Maxscale 的环境就完成了。
读写分离与负载均衡校验
Part1: 读写分离
在 Master 构建相应的测试表
1234567 mysql> select * from helei;
+——–+
| a |
+——–+
| HE3 |
+——–+
3 rows in set (0.00 sec)
在 slave1 插入数据 HE1
mysql> select * from helei;
+——+
| a |
+——+
| HE3 |
| HE1 |
+——+
在 slave2 插入数据 HE2
1234567 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://www.linuxidc.com/Linux/2016-11/136970.htm
现象：
程序会不定时的出现连接错误，问题 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 的核心就在于内个配置文件，根据不同的业务和架构，做好相应的配置，本文意在抛砖引玉，如果您有更全的配置文件，欢迎您分享给笔者。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/136969.htm
