我们很好的通过MHA完成了MySQL主从复制过程，当MySQL宕机时，我们DBA可以收到邮件，MHA也能监测到，但是在线的应用程序呢？数据库宕机了，应用程序却不知道，它还是一如既往的向db01（10.0.0.20）传入SQL并且报错。难道我们看到应用程序报错了，才去在应用程序中切换数据源吗，手动将原先的10.0.0.20切换到10.0.0.22？假如你还在地铁上或者路上堵车，需要一个小时才能去处理。这样的话，1个亿的成交额就没了，新客户也受阻，损失惨重。不用担心，MHA中可以通过VIP漂移到主库中，当主库宕机，丝毫不会影响到任何业务，更不用手动切换数据源。为了更好的说明VIP漂移，我来给你们画个图吧。
MHA原理图
##应用程序始终连接VIP（10.0.0.25），当db01宕机后，MHA自动将VIP漂移到db02上，应用程序无法感知主从切换，大家可以把这个vip看作是一个软连接，就像我们之前讲到的给mysql安装路径创建一个软链接，如果升级mysql，只需要更改软连接的指向就行，比如说从mysql5.6升级到mysql5.7，只需要将该软连接从mysql5.6指向mysql5.7即可，开发人员始终从mysql软连接为入口，无需关注后面发生的事情。
配置步骤：(在MHA节点上，也就是db03上，注意：当前环境的主节点为db02，因为上节中MHA已经做过一次主从切换)
- 上传准备好的/usr/local/bin/master_ip_failover ，master_ip_filover（点击下载） 给大家提供，只需要修改其中的几行即可。
需要修改的行：13~16行my $vip = '10.0.0.25/24'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";# $vip 设置为当前网络不存在的ip，不可重复
# $key 是网卡的标识 1即可
# $ssh_start_vip 启动VIP ，如果你的监听网卡是内网就用eth1，我监听的是外网eth0，如果你跟我保持一致默认不动即可
# $ssh_stop_vip 停止VIP ，如果你的监听网卡是内网就用eth1，我监听的是外网eth0，如果你跟我保持一致默认不动即可 -
将master_ip_failover 文件转码并赋权
dos2unix /usr/local/bin/master_ip_failover ## windows与linux之间文件内容格式不一样，防止乱码
（你可能需要安装dos2unix软件 yum install dos2unix -y）[root@db03 app1]# yum install dos2unix -y [root@db03 bin]# dos2unix master_ip_failover dos2unix: converting file master_ip_failover to UNIX format ... [root@db03 bin]# ll total 1164 -rwxr-xr-x 1 root root 1187215 Aug 2 11:35 extundelete -rw-r--r-- 1 root root 2165 Aug 28 15:56 master_ip_failover [root@db03 bin]# chmod +x master_ip_failover [root@db03 bin]# ll total 1164 -rwxr-xr-x 1 root root 1187215 Aug 2 11:35 extundelete -rwxr-xr-x 1 root root 2165 Aug 28 15:56 master_ip_failover -
在/etc/mha/app1.cnf 的[server default]模块中添加配置
master_ip_failover_script=/usr/local/bin/master_ip_failover[root@db03 bin]# vim /etc/mha/app1.cnf [server default] manager_log=/var/log/mha/app1/manager manager_workdir=/var/log/mha/app1 master_binlog_dir=/application/mysql/binlog password=mha ping_interval=2 repl_password=123456 repl_user=repl ssh_user=root user=mha master_ip_failover_script=/usr/local/bin/master_ip_failover [server1] hostname=10.0.0.20 port=3306 [server2] hostname=10.0.0.22 port=3306 [server3] hostname=10.0.0.23 port=3306 -
重启mha
masterha_stop --conf=/etc/mha/app1.cnf ##关闭MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & ## 启动MHA[root@db03 bin]# masterha_stop --conf=/etc/mha/app1.cnf Stopped app1 successfully. [root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 23095 [root@db03 bin]# -
手工在主库上绑定vip，注意一定要和配置文件中的ethN一致，我的是eth0:1(1是key指定的值)
添加子网卡IP：ifconfig eth0:1 10.0.0.25/24 ／／eth0是网卡配置名
删除子网卡IP：ifconfig eth0:1 down[root@db02 tools]# ifconfig eth0:1 10.0.0.25/24 [root@db02 tools]# ifconfig eth0 Link encap:Ethernet HWaddr 00:0C:29:E6:94:91 inet addr:10.0.0.22 Bcast:10.0.0.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fee6:9491/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:40832 errors:0 dropped:0 overruns:0 frame:0 TX packets:22807 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:8317028 (7.9 MiB) TX bytes:2720068 (2.5 MiB) eth0:1 Link encap:Ethernet HWaddr 00:0C:29:E6:94:91 inet addr:10.0.0.25 Bcast:10.0.0.255 Mask:255.255.255.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 eth1 Link encap:Ethernet HWaddr 00:0C:29:E6:94:9B inet addr:172.16.1.22 Bcast:172.16.1.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fee6:949b/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:6 errors:0 dropped:0 overruns:0 frame:0 TX packets:12 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:360 (360.0 b) TX bytes:828 (828.0 b) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:81 errors:0 dropped:0 overruns:0 frame:0 TX packets:81 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:15800 (15.4 KiB) TX bytes:15800 (15.4 KiB)## 这时会发现多出了一个子网卡eth0:1 ，ip为10.0.0.25
-
测试VIP，停主库db02，查看vip是否漂移。
db02：service mysqld stop，发现vip消失了，已经没有eth0:1了[root@db02 tools]# service mysqld stop Shutting down MySQL..... SUCCESS! [root@db02 tools]# ifconfig eth0 Link encap:Ethernet HWaddr 00:0C:29:E6:94:91 inet addr:10.0.0.22 Bcast:10.0.0.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fee6:9491/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:41083 errors:0 dropped:0 overruns:0 frame:0 TX packets:22972 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:8342232 (7.9 MiB) TX bytes:2744308 (2.6 MiB) eth1 Link encap:Ethernet HWaddr 00:0C:29:E6:94:9B inet addr:172.16.1.22 Bcast:172.16.1.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fee6:949b/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:6 errors:0 dropped:0 overruns:0 frame:0 TX packets:12 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:360 (360.0 b) TX bytes:828 (828.0 b) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:81 errors:0 dropped:0 overruns:0 frame:0 TX packets:81 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:15800 (15.4 KiB) TX bytes:15800 (15.4 KiB)再去查看db01和db03，发现vip出现在了db01上。
[root@db01 tools]# ifconfig eth0 Link encap:Ethernet HWaddr 00:0C:29:DB:61:2A inet addr:10.0.0.20 Bcast:10.0.0.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fedb:612a/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:32090 errors:0 dropped:0 overruns:0 frame:0 TX packets:18609 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:13923740 (13.2 MiB) TX bytes:1999467 (1.9 MiB) eth0:1 Link encap:Ethernet HWaddr 00:0C:29:DB:61:2A inet addr:10.0.0.25 Bcast:10.0.0.255 Mask:255.255.255.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 eth1 Link encap:Ethernet HWaddr 00:0C:29:DB:61:34 inet addr:172.16.1.20 Bcast:172.16.1.255 Mask:255.255.255.0 inet6 addr: fe80::20c:29ff:fedb:6134/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:12 errors:0 dropped:0 overruns:0 frame:0 TX packets:12 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:720 (720.0 b) TX bytes:828 (828.0 b) lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:88 errors:0 dropped:0 overruns:0 frame:0 TX packets:88 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:16536 (16.1 KiB) TX bytes:16536 (16.1 KiB)同时进入db01，db02,db03分别查看主从状态：
db01（主库，已经没有状态）mysql> show slave status\G Empty set (0.00 sec)db02
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.20 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 191 Relay_Log_File: db02-relay-bin.000002 Relay_Log_Pos: 361 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes . . .db03
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.20 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 191 Relay_Log_File: db03-relay-bin.000002 Relay_Log_Pos: 361 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes . . .##Master_Host: 10.0.0.20， 说明一切OK！！！！！！！！！
再也不用手动切换应用的数据源了。
