本文最后更新于 319 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com
MySQL的MHA启用应用透明vip功能实战案例
一.MHA启用应用透明vip功能说明:
只能同机房使用，无法跨机房跨网络。如果有必要，可以借助类似于keepalive工具实现vip功能。二.MySQL实例服务器的网卡名称统一(生产环境中建议大家将第一块网卡设置为"eth0"，第二块网卡设置为"eth1"，以此类推)
生产环境中建议大家将服务器的网卡名称统一，这属于标准化的范畴，只有将网卡名称标准化后才能有助于自动化运维的推动。
[root@mysql201.oldboyedu.com ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.200.1.201 netmask 255.255.248.0 broadcast 172.200.7.255
inet6 fe80::20c:29ff:fee2:97a4 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:e2:97:a4 txqueuelen 1000 (Ethernet)
RX packets 36156 bytes 11842421 (11.2 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 22149 bytes 4070115 (3.8 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 896 bytes 101776 (99.3 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 896 bytes 101776 (99.3 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens33
TYPE=Ethernet
BOOTPROTO=static
NAME=ens33
UUID=2e8e9fb2-eca7-43ce-bfcd-7663a6cfc0fc
DEVICE=ens33
ONBOOT=yes
IPADDR=172.200.1.201
NETMASK=255.255.248.0
GATEWAY=172.200.7.254
DNS1=172.200.7.254
[root@mysql201.oldboyedu.com ~]#
(1)先编辑网卡的配置文件将里面的"NAME"和"DEVICE"项修改为eth0
[root@mysql201.oldboyedu.com ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens33
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# grep NAME /etc/sysconfig/network-scripts/ifcfg-ens33
NAME=eth0
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# grep DEVICE /etc/sysconfig/network-scripts/ifcfg-ens33
DEVICE=eth0
[root@mysql201.oldboyedu.com ~]#
(2)修改配置文件名称
[root@mysql201.oldboyedu.com ~]# mv /etc/sysconfig/network-scripts/ifcfg-ens33 /etc/sysconfig/network-scripts/ifcfg-eth0
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
TYPE=Ethernet
BOOTPROTO=static
NAME=eth0
UUID=2e8e9fb2-eca7-43ce-bfcd-7663a6cfc0fc
DEVICE=eth0
ONBOOT=yes
IPADDR=172.200.1.201
NETMASK=255.255.248.0
GATEWAY=172.200.7.254
DNS1=172.200.7.254
[root@mysql201.oldboyedu.com ~]#
(3)禁用可预测命名规则，通过编辑"/etc/default/grub"文件并将"net.ifnames=0 biosdevname=0"追加到"GRUB_CMDLINE_LINUX"变量来实现的。当然，你也可以在启动时传递"net.ifnames=0 biosdevname=0"的内核参数。
[root@mysql201.oldboyedu.com ~]# cat /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="crashkernel=auto spectre_v2=retpoline rhgb quiet"
GRUB_DISABLE_RECOVERY="true"
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# vim /etc/default/grub
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# grep GRUB_CMDLINE_LINUX /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto spectre_v2=retpoline rhgb quiet net.ifnames=0 biosdevname=0"
[root@mysql201.oldboyedu.com ~]#
(4)来重新生成GRUB配置并更新内核参数。
[root@mysql201.oldboyedu.com ~]# grub2-mkconfig -o /boot/grub2/grub.cfg
Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.10.0-1160.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-1160.el7.x86_64.img
Found linux image: /boot/vmlinuz-0-rescue-5cfda6b57c41477396d835ef0fb84442
Found initrd image: /boot/initramfs-0-rescue-5cfda6b57c41477396d835ef0fb84442.img
done
[root@mysql201.oldboyedu.com ~]#
(5)重启操作系统，并查看网卡名称
[root@mysql201.oldboyedu.com ~]# reboot # 重启操作系统
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# ifconfig # Duang~发现网卡名称被修改过来啦~
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.200.1.201 netmask 255.255.248.0 broadcast 172.200.7.255
inet6 fe80::20c:29ff:fee2:97a4 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:e2:97:a4 txqueuelen 1000 (Ethernet)
RX packets 59 bytes 6566 (6.4 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 67 bytes 8465 (8.2 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@mysql201.oldboyedu.com ~]#
三.MHA Manager节点准备脚本(用官方提供的samples里面的"master_ip_failover_script"脚本稍作修改即可)，注意要有执行权限哟~
修改官方的启动脚本如下(尤其关注中文注释的行):
[root@mysql203.oldboyedu.com ~]# install -o mysql -g mysql -d /oldboyedu/softwares/mha/script
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# vim /oldboyedu/softwares/mha/script/master_ip_failover
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# cat /oldboyedu/softwares/mha/script/master_ip_failover
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
# 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 Street, Fifth Floor, Boston, MA 02110-1301 USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password
);
# VIP必须是空闲地址，即不能被任何MySQL实例使用且无其它服务器使用该IP，否则可能会出现IP冲突哟~
my $vip = '172.200.1.168/24';
# 指定子虚拟网卡的key,比如"eth0:2021"，那么key就等于2021
my $key = '2021';
# 启动VIP的命令
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
# 停止VIP的命令
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";}
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# ll /oldboyedu/softwares/mha/script/master_ip_failover
-rw-r--r-- 1 root root 2933 2月 18 00:48 /oldboyedu/softwares/mha/script/master_ip_failover
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# chown mysql:mysql /oldboyedu/softwares/mha/script/master_ip_failover
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# chmod +x /oldboyedu/softwares/mha/script/master_ip_failover
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# ll /oldboyedu/softwares/mha/script/master_ip_failover
-rwxr-xr-x 1 mysql mysql 2933 2月 18 00:48 /oldboyedu/softwares/mha/script/master_ip_failover
[root@mysql203.oldboyedu.com ~]#
温馨提示:
如果你是在window上写好的配置文件，在Linux上使用的话，建议使用"dos2unix"工具转换一下:
[root@mysql203.oldboyedu.com ~]# yum -y install dos2unix
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# dos2unix /oldboyedu/softwares/mha/script/master_ip_failover
dos2unix: converting file /oldboyedu/softwares/mha/script/master_ip_failover to Unix format ...
[root@mysql203.oldboyedu.com ~]#
四.MHA Manager节点修改配置文件
修改配置文件，如下所示:
[root@mysql203.oldboyedu.com ~]# vim /oldboyedu/softwares/mha/etc/app1.cnf
[root@mysql203.oldboyedu.com ~]#
[root@mysql203.oldboyedu.com ~]# cat /oldboyedu/softwares/mha/etc/app1.cnf
[server default]
manager_log=/oldboyedu/logs/mha/app1/manager
manager_workdir=/oldboyedu/logs/mha/app1
master_binlog_dir=/oldboyedu/logs/mysql/binlog
user=mha
password=oldboyedu
ping_interval=2
repl_user=copy
repl_password=oldboyedu
ssh_user=root
master_ip_failover_script=/oldboyedu/softwares/mha/script/master_ip_failover
[server1]
hostname=172.200.1.201
port=3306
[server2]
hostname=172.200.1.202
port=3306
[server3]
hostname=172.200.1.203
port=3306
[root@mysql203.oldboyedu.com ~]#
很明显，我添加了"master_ip_failover_script"参数，该参数用于指定故障转移时调用的脚本。
五.在主库上手动生成第一个VIP地址
[root@mysql201.oldboyedu.com ~]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.200.1.201 netmask 255.255.248.0 broadcast 172.200.7.255
inet6 fe80::20c:29ff:fee2:97a4 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:e2:97:a4 txqueuelen 1000 (Ethernet)
RX packets 1027 bytes 78519 (76.6 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 790 bytes 72090 (70.4 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# ifconfig eth0:2021 172.200.1.168/24
[root@mysql201.oldboyedu.com ~]#
[root@mysql201.oldboyedu.com ~]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.200.1.201 netmask 255.255.248.0 broadcast 172.200.7.255
inet6 fe80::20c:29ff:fee2:97a4 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:e2:97:a4 txqueuelen 1000 (Ethernet)
RX packets 1106 bytes 84799 (82.8 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 841 bytes 80484 (78.5 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
eth0:2021: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.200.1.168 netmask 255.255.255.0 broadcast 172.200.1.255
ether 00:0c:29:e2:97:a4 txqueuelen 1000 (Ethernet)
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@mysql201.oldboyedu.com ~]#
六.重启MHA
停止MHA:
[root@mysql203.oldboyedu.com ~]# masterha_stop --conf=/oldboyedu/softwares/mha/etc/app1.cnf
启动MHA:
[root@mysql203.oldboyedu.com ~]# nohup masterha_manager --conf=/oldboyedu/softwares/mha/etc/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /oldboyedu/logs/mha/app1/manager.log 2>&1 &
检查MHA状态:
[root@mysql203.oldboyedu.com ~]# masterha_check_status --conf=/oldboyedu/softwares/mha/etc/app1.cnf
温馨提示:
如果报错了就查看"/oldboyedu/logs/mha/app1/manager"日志信息。当然，该日志信息由"app1.cnf"配置文件"manager_log"指定的哟~
七.如果使用keepalived替换MHA的VIP功能需要注意的事项
第一:
可以不使用MHA官方提供的准备脚本(即"master_ip_failover")来创建子虚拟网卡，而是可以使用keepalived的VIP功能来替代;
第二:
由于keepalived组件并不知道MHA的工作原理，因此需要使用"candidate_master=1"和"check_repl_delay=0"参数进行配合，防止VIP和主库选择不在一个节点上。
其它:
实验模拟环节待补充。