mysql router 自动failover测试

时间:2022-05-24 11:08:31
mysql router 启动服务文件内容:

[root@monitor mysqlrouter]# cat /etc/init.d/mysqlrouter
#! /bin/bash
#
# mysqlrouter This shell script takes care of starting and stopping
# the MySQL Router
#
# chkconfig: 2345 66 34
# description: MySQL Router
# processname: mysqlrouter
# config: /etc/mysqlrouter/mysqlrouter.ini
# pidfile: /var/run/mysqlrouter/mysqlrouter.pid
#
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

#
# Maintainer: MySQL Release Engineering <mysql-build@oss.oracle.com>
#

# Source function library
. /etc/rc.d/init.d/functions

# Source networking configuration
. /etc/sysconfig/network

# add general install path
base_dir=/usr/local/mysql-router
# fix exec path
exec=${base_dir}/bin/mysqlrouter
prog=mysqlrouter
piddir=${base_dir}/run/mysqlrouter
pidfile=${piddir}/mysqlrouter.pid
logdir=/var/log/mysqlrouter
logfile=$logdir/mysqlrouter.log
lockfile=/var/lock/subsys/$prog

# add conf path
conf=/etc/mysqlrouter/mysqlrouter.ini

start () {
[ -d $piddir ] || mkdir -p $piddir
chown mysql:mysql $piddir
[ -d $logdir ] || mkdir -p $logdir
chown mysql:mysql $logdir
[ -e $logfile ] || touch $logfile
chown mysql:mysql $logfile
export ROUTER_PID=$pidfile
# add opt -c to resolv mysqlrouter.ini
daemon --user mysql $exec -c $conf >/dev/null 2>&1 &              #
ret=$?
if [ $ret -eq "0" ]; then
action $"Starting $prog: " /bin/true
touch /var/lock/subsys/$prog
else
action $"Starting $prog: " /bin/false
fi
return $ret
}

stop () {
[ -f /var/lock/subsys/$prog ] || return 0
killproc mysqlrouter >/dev/null 2>&1
ret=$?
if [ $ret -eq "0" ]; then
rm -f $pidfile
rm -f /var/lock/subsys/$prog
action $"Stopping $prog: " /bin/true
else
ation $"Stopping $prog: " /bin/false
fi
}

restart () {
stop
start
}

condrestart () {
[ -e /var/lock/subsys/$prog ] && restart || return 0
}

case "$1" in
start)
start
;;
stop)
stop
;;
status)
status -p "$pidfile" $prog
;;
restart)
restart
;;
condrestart|try-restart)
condrestart
;;
reload)
exit 3
;;
force-reload)
restart
;;
*)
echo $"Usage: $0 {start|stop|status|condrestart|try-restart|reload|force-reload}"
exit 2
esac

exit $?

当程序意外被KILL后,有相关程序运行标识,需要先STOP

rm -f $pidfile
rm -f /var/lock/subsys/$prog

再启动,否则程序会提示有一个实例运行而不能运行该服务

/var/lock/subsys目录的作用
很多程序需要判断是否当前已经有一个实例在运行,这个目录就是让程序判断是否有实例运行的标志,比如说xinetd,如果存在这个文件,表示已经有 xinetd在运行了,否则就是没有,当然程序里面还要有相应的判断措施来真正确定是否有实例在运行。通常与该目录配套的还有/var/run目录,用来 存放对应实例的PID,如果你写脚本的话,会发现这2个目录结合起来可以很方便的判断出许多服务是否在运行,运行的相关信息等等。

实际上,判断是否上锁就是判断这个文件,所以文件存在与否也就隐含了是否上锁。而这个目录的内容并不能表示一定上锁了,因为很多服务在启动脚本里用 touch来创建这个加锁文件,在系统结束时该脚本负责清除锁,这本身就不可靠(比如意外失败导致锁文件仍然存在),我在脚本里一般是结合PID文件(如 果有PID文件的话),从PID文件里得到该实例的PID,然后用ps测试是否存在该PID,从而判断是否真正有这个实例在运行,更加稳妥的方法是用进程 通讯了,不过这样的话单单靠脚本就做不到了。

[root@monitor bin]# cat /etc/mysqlrouter/mysqlrouter.ini
[DEFAULT]
#mkdir -p /var/log/mysql-router
logging_folder = /var/log/mysql-router
plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
runtime_folder = /usr/local/mysql-router [logger]
# 定义日志等级
level = INFO # 一个高可用的标签
[routing:failover]
bind_address = 0.0.0.0
bind_port =
max_connections =
# 目前就支持两种 : read-write 和 read-only
# read-write:用于高可用,用于可读可写
# read-only:用于负载均衡,只读
mode = read-write
# 实际转发的地址
# 第一个socket如果可用,就一直会使用第一个
# 如果第一个socket无法连接了,才会连接到第二个socket
destinations = 10.24.220.70:, 10.169.214.33:
起动:

[root@monitor bin]# ./mysqlrouter -c /etc/mysqlrouter/mysqlrouter.ini &
[]
[root@monitor bin]# Logging to /var/log/mysql-router/mysqlrouter.log
^C
[root@monitor bin]# cat Logging to /var/log/mysql-router/mysqlrouter.log
cat: Logging: No such file or directory
cat: to: No such file or directory
-- :: INFO [7f2cb44a4700] [routing:failover] listening on 0.0.0.0:; read-write
-- :: INFO [7f13125a2700] [routing:failover] listening on 0.0.0.0:; read-write
-- :: INFO [7f2816b4a700] [routing:failover] listening on 0.0.0.0:; read-write
-- :: INFO [7fec2b5f4700] [routing:failover] listening on 0.0.0.0:; read-write
-- :: INFO [7ff971019700] [routing:failover] listening on 0.0.0.0:; read-write
[root@monitor bin]# mysql -u root -p123 -h 172.18.14.68^CP -e "show variables like 'server_id'";
[root@monitor bin]# mysql -u root -p -h 10.169.216.172 -P  -e "show variables like 'server_id'";
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | |
+---------------+-------+
[root@monitor bin]# mysql -u root -p -h 10.169.216.172 -P -e "show variables like 'server_id'";
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | |
+---------------+-------+ server1:
service mysq.server stop [root@monitor bin]# mysql -u root -p -h 10.169.216.172 -P -e "show variables like 'server_id'";
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | |
+---------------+-------+
[root@monitor bin]# mysql -u root -p -h 10.169.216.172 -P -e "show variables like 'server_id'";
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | |
+---------------+-------+