Chinaunix首页 | 论坛 | 博客

黑马之家

首页　| 　博文目录　| 　关于我

黑马王子

  • 博客访问： 3283050
  • 博文数量： 515
  • 博客积分： 5116
  • 博客等级： 大校
  • 技术积分： 4137
  • 用 户 组： 普通用户
  • 注册时间： 2006-09-30 11:24
个人简介

黑马王子就是我！ 技术认证：系统分析师，网络规划设计师，网络工程师，信息系统监理师，系统集成项目管理师，初级程序员，MCSE，MCDBA，CCNA 目前主攻虚拟化技术，VPN，系统架构，集群和高可用性等。

文章分类

全部博文（515）

文章存档

2023年（11）

2022年（17）

2021年（19）

2020年（28）

2019年（42）

2018年（53）

2017年（41）

2016年（33）

2015年（43）

2014年（25）

2013年（13）

2011年（6）

2010年（28）

2009年（47）

2008年（55）

2007年（26）

2006年（28）

我的朋友
最近访客
推荐博文
相关博文
Keepalived+MySQL实现高可用

分类： Mysql/postgreSQL

2020-07-22 12:27:17

MySQL的高可用方案有很多，比如Cluster，MMM，MHA，DRBD等，这些都比较复杂，我前面的文章也有介绍。最近Oracle官方也推出了Fabric。有时我们不需要这么复杂的环境，这些方案各有优劣。有时简单的且我们能够hold住的方案才是适合我们的。比如MySQL Replication，然后加上各种高可用软件，比如等，就能实现我们需要的高可用环境。

MySQL架构为master/slave，当master故障时，vip漂移到slave上。提供服务。当然也可以设置为双master，但是不是每个方案都是完美的。这里设置为双master有个问题需要注意，比如，当用户发表文章时，由于此时主机的压力很大时，假设落后2000秒，那么这台主机宕机了，另一台主机接管（vip漂移到从机上）时，因为同步延时大，用户刚才发表的文章还没复制过来，于是用户又发表了一遍文章，当原来的master修复好后，由于I/O和SQL线程还处于开启状态，因此还会继续同步刚才没有同步复制完的数据，这时有可能把用户新发表的文章更改掉。这里所以采用master/slave架构。在这种架构中，故障切换以后，采取手动操作的方式与新的master进行复制。

简单环境如下： 

master 192.168.0.100 slave 192.168.0.101 VIP 192.168.0.88

主从复制环境的搭建我这里就不演示了。有需要的同学自己看看官方手册。下面直接介绍keepalived的安装及配置使用。

1.keepalived软件安装（主从操作一样） 

[root@mysql-server-01 ~]# wget -q http:// [root@mysql-server-01 ~]# tar xf keepalived-1.2.13.tar.gz
[root@mysql-server-01 ~]# cd keepalived-1.2.13 [root@mysql-server-01 keepalived-1.2.13]# ./configure && make && make install
复制代码 
[root@mysql-server-01 keepalived]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ [root@mysql-server-01 keepalived]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ [root@mysql-server-01 keepalived]# mkdir /etc/keepalived
[root@mysql-server-01 keepalived]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/ [root@mysql-server-01 keepalived]# cp /usr/local/sbin/keepalived /usr/sbin/ [root@mysql-server-01 keepalived]# chkconfig --add keepalived
[root@mysql-server-01 keepalived]# chkconfig --level 345 keepalived on
复制代码

2.主从的配置文件修改（主的keepalived配置文件修改后如下，其实不相同的就优先级而已）
master的keepalived配置文件如下

复制代码 
[root@mysql-server-01 keepalived]# cat keepalived.conf
global_defs {
   router_id MySQL-HA
} 

vrrp_script check_run {
script "/data/sh/mysql_check.sh" interval 300 }

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1  
    virtual_router_id 51 priority 100 advert_int 1 nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111 }
    track_script {
    check_run
    }
 
    notify_master /data/sh/master.sh notify_backup /data/sh/backup.sh notify_stop /data/sh/stop.sh virtual_ipaddress { 192.168.0.88 }
}

[root@mysql-server-01 keepalived]#
复制代码

slave的keepalived配置文件修改以后如下：

复制代码 
[root@mysql-server-02 keepalived]# cat keepalived.conf
global_defs {
   router_id MySQL-HA
} 

vrrp_script check_run {
script "/data/sh/mysql_check.sh" interval 300 }

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51 priority 90 advert_int 1 authentication {
        auth_type PASS
        auth_pass 1111 }
    track_script {
    check_run
    }
 
    notify_master /data/sh/master.sh notify_backup /data/sh/backup.sh notify_stop /data/sh/stop.sh virtual_ipaddress { 192.168.0.88 }
}
[root@mysql-server-02 keepalived]#
复制代码

其中有几个关键参数的地方：
notify_master：状态改变为master以后执行的脚本。

notify_backup: 状态改变为backup以后执行的脚本。

notify_fault: 状态改变为fault后执行的脚本。

notify_stop: VRRP停止以后执行的脚本。

state backup：我们都设置为了backup，就是为了发生故障以后不会自动切换。

nopreempt： 不进行抢占操作

其中用到了这4个脚本：backup.sh  master.sh  mysql_check.sh  stop.sh

mysql_check.sh是为了检查mysqld进程是否存活的脚本，当发现连接不上mysql，自动把keepalived进程干掉，让VIP进行漂移。

下面的脚本主从服务器上面都有，只是从服务器上面的master.sh有些不一样。添加了当slave提升为主库时，发送邮件通知。

复制代码 
[root@mysql-server-01 sh]# cat mysql_check.sh #!/bin/bash

. /root/.bash_profile

count=1 while true do mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14520.sock -e "show status;" > /dev/null 2>&1 i=$? ps aux | grep mysqld | grep -v grep > /dev/null 2>&1 j=$? if [ $i = 0 ] && [ $j = 0 ] then exit 0 else if [ $i = 1 ] && [ $j = 0 ] then exit 0 else if [ $count -gt 5 ] then break fi let count++ continue fi fi done /etc/init.d/keepalived stop
[root@mysql-server-01 sh]#
复制代码

master.sh的作用是状态改为master以后执行的脚本。首先判断复制是否有延迟，如果有延迟，等1分钟后，不论是否有延迟。都跳过，并停止复制。并且授权账号，记录binlog和pos点。

复制代码 
[root@mysql-server-02 sh]# cat master.sh #!/bin/bash

. /root/.bash_profile

Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
Relay_Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
Read_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
Exec_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')

i=1 while true do if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ] then echo "ok" break else sleep 1 if [ $i -gt 60 ] then break fi continue
   let i++ fi done mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "stop slave;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt


[root@mysql-server-02 sh]#
复制代码

slave上的master.sh

复制代码 
[root@mysql-server-02 sh]# cat master.sh #!/bin/bash

. /root/.bash_profile

Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
Relay_Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
Read_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
Exec_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')

i=1 while true do if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ] then echo "ok" break else sleep 1 if [ $i -gt 60 ] then break fi continue
   let i++ fi done mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "stop slave;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt


#当slave提升为主以后，发送邮件 echo "#####################################" > /tmp/status echo "salve已经提升为主库，请进行检查！" >> /tmp/status ifconfig | sed -n '/inet /{s/.*addr://;s/ .*//;p}' | grep -v 127.0.0.1 >> /tmp/status
mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -Nse "show variables like 'port'" >> /tmp/status echo "#####################################" >> /tmp/status
master=`cat /tmp/status` echo "$master" | mutt -s "slave to primary!!!" 13143753516@139.com
复制代码

脚本中检查复制是否延时的思想如下：
1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异
2、如果Relay_Master_Log_File 和 Master_Log_File 有差异的话，那说明延迟很大了
3、如果Relay_Master_Log_File 和 Master_Log_File 没有差异，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异

而不是通过Seconds_Behind_Master去判断，该值表示slave上SQL线程和IO线程之间的延迟，实际上还要考虑到 Master_Log_File 和 Relay_Master_Log_File 是否有差距，更严谨的则是要同时在master上执行show master status进行对比。这也是MHA在切换过程中可以做到的。MMM的切换也只是在从库上执行了show slave status。所以数据一致性要求还是MHA给力。扯远了。^_^

backup.sh脚本的作用是状态改变为backup以后执行的脚本。

复制代码 
[root@mysql-server-02 sh]# cat backup.sh #!/bin/bash

. /root/.bash_profile

mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global event_scheduler=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=0;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=0;"
复制代码

stop.sh 表示keepalived停止以后需要执行的脚本。更改密码，设置参数，检查是否还有写入操作，最后无论是否执行完毕，都退出。

复制代码 
[root@mysql-server-02 sh]# cat stop.sh #!/bin/bash

. /root/.bash_profile

mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '1q2w3e4r';flush privileges;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=1;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=1;" mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=1;" M_File1=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position1=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status\G" | awk -F': ' '/Position/{print $2}') sleep 1 M_File2=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position2=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status\G" | awk -F': ' '/Position/{print $2}')

i=1 while true do if [ $M_File1 = $M_File1 ] && [ $M_Position1 -eq $M_Position2 ] then echo "ok" break else sleep 1 if [ $i -gt 60 ] then break fi continue
   let i++ fi done [root@mysql-server-02 sh]#
复制代码

到这里基本就介绍完了。最后我们先看主从复制是否正常，如果正常，然后分别启动keepalived，然后进行故障切换测试。

slave状态：

复制代码 
node2 [localhost] {msandbox} ((none)) > pager cat | egrep 'Master_Log_File|Relay_Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Running' PAGER set to 'cat | egrep 'Master_Log_File|Relay_Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Running'' node2 [localhost] {msandbox} ((none)) > show slave status\G
              Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 409 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 409 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec)

node2 [localhost] {msandbox} ((none)) >
复制代码

master 状态：

复制代码 
node1 [localhost] {msandbox} ((none)) > show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 409 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

node1 [localhost] {msandbox} ((none)) >
复制代码

根据我前面给的判断条件，可以看出我的复制没有任何延时。
下面分别在master上和slave上启动keepalived进程。以及查看日志（上面的查看只是给大家说明如何判断复制是否延迟）

master

复制代码 
[root@mysql-server-01 sh]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]
[root@mysql-server-01 sh]# tail -f /var/log/messages
Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Netlink reflector reports IP 192.168.87.134 added
Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Netlink reflector reports IP 192.168.0.100 added
Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering Kernel netlink reflector
Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering Kernel netlink command channel
Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering gratuitous ARP shared channel
Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.100 added
Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.87.134 added
Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.100 added
Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Registering Kernel netlink reflector
Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Registering Kernel netlink command channel
Jul 20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Opening file '/etc/keepalived/keepalived.conf'.
Jul 20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Configuration is using : 6489 Bytes
Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Opening file '/etc/keepalived/keepalived.conf'.
Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Configuration is using : 66476 Bytes
Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Using LinkWatch kernel netlink reflector...
Jul 20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Using LinkWatch kernel netlink reflector...
Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Entering BACKUP STATE
Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Script(check_run) succeeded
Jul 20 20:48:27 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jul 20 20:48:27 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Group(VG1) Syncing instances to MASTER state
Jul 20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Entering MASTER STATE
Jul 20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) setting protocol VIPs.
Jul 20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88 Jul 20 20:48:28 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.88 added
Jul 20 20:48:33 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
复制代码

slave

复制代码 
[root@mysql-server-02 tmp]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]
[root@mysql-server-02 tmp]# tail -f /var/log/messages
Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc91 added
Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP 192.168.0.101 added
Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc91 added
Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc9b added
Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering Kernel netlink reflector
Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc9b added
Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Registering Kernel netlink reflector
Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering Kernel netlink command channel
Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Registering Kernel netlink command channel
Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering gratuitous ARP shared channel
Jul 20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Opening file '/etc/keepalived/keepalived.conf'.
Jul 20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Configuration is using : 6467 Bytes
Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Opening file '/etc/keepalived/keepalived.conf'.
Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Configuration is using : 66454 Bytes
Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Using LinkWatch kernel netlink reflector...
Jul 20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Using LinkWatch kernel netlink reflector...
Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: VRRP_Instance(VI_1) Entering BACKUP STATE
Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
Jul 20 20:48:35 mysql-server-02 Keepalived_vrrp[10680]: VRRP_Script(check_run) succeeded
复制代码

可以看见VIP已经绑定在了master上，执行ip addr看看是否有这个VIP 

[root@mysql-server-01 ~]# ip addr | grep eth1 3: eth1:  mtu 1500 qdisc pfifo_fast state UP qlen 1000 inet 192.168.0.100/24 brd 192.168.0.255 scope global eth1
    inet 192.168.0.88/32 scope global eth1
[root@mysql-server-01 ~]#

可以看见vip也已经绑定成功。

现在我们从远程机器登陆看看，使用vip，创建测试库，插入数据，最后模拟mysqld crash

复制代码 
[root@mysql-server-03 ~]# mysql -uadmin -p123456 -h 192.168.0.88 -P 14520 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 47 Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> create database dengyayun;
Query OK, 1 row affected (0.01 sec)

mysql> use dengyayun Database changed
mysql> create table t1 ( id int);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into t1 select 999;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0 mysql>
复制代码

发现使用vip登陆没有问题，创建库以及插入数据都木有问题。现在杀掉mysqld进程，看vip是否进行了漂移，以及查看数据是否存在。 

[root@mysql-server-01 ~]# pkill -9 mysqld

过了一会儿，报警邮件就到了，以及vip也已经切换了。如下：

查看slave上面的message信息，如下输出：

复制代码 
[root@mysql-server-02 ~]# tail -n 20 /var/log/messages 
Jul 20 22:00:20 mysql-server-02 Keepalived_healthcheckers[13327]: Registering Kernel netlink command channel
Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Opening file '/etc/keepalived/keepalived.conf'.
Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Configuration is using : 66454 Bytes
Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Using LinkWatch kernel netlink reflector...
Jul 20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Opening file '/etc/keepalived/keepalived.conf'.
Jul 20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Configuration is using : 6467 Bytes
Jul 20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Using LinkWatch kernel netlink reflector...
Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Entering BACKUP STATE
Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Script(check_run) succeeded
Jul 20 22:07:47 mysql-server-02 dhclient[7343]: DHCPREQUEST on eth0 to 192.168.87.254 port 67 (xid=0x4ada08db)
Jul 20 22:07:47 mysql-server-02 dhclient[7343]: DHCPACK from 192.168.87.254 (xid=0x4ada08db)
Jul 20 22:07:49 mysql-server-02 dhclient[7343]: bound to 192.168.87.135 -- renewal in 885 seconds.
Jul 20 22:10:38 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jul 20 22:10:38 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Group(VG1) Syncing instances to MASTER state
Jul 20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Entering MASTER STATE
Jul 20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) setting protocol VIPs.
Jul 20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88 Jul 20 22:10:39 mysql-server-02 Keepalived_healthcheckers[13327]: Netlink reflector reports IP 192.168.0.88 added
Jul 20 22:10:44 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88 [root@mysql-server-02 ~]#
复制代码

最后我们再次使用vip登陆；发现数据没有异常。复制也停止了，因为已经切换为主库。

复制代码 
[root@mysql-server-03 ~]# mysql -uadmin -p123456 -h 192.168.0.88 -P14521
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 301 Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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 * from dengyayun.t1; +------+ | id | +------+ | 999 | +------+ 1 row in set (0.00 sec)

mysql> pager cat | egrep 'IO_Running|SQL_Running' PAGER set to 'cat | egrep 'IO_Running|SQL_Running'' mysql> show slave status\G
             Slave_IO_Running: No
            Slave_SQL_Running: No
      Slave_SQL_Running_State: 1 row in set (0.00 sec)

mysql>
复制代码

作者：Atlas

出处：Atlas的博客 http://www.cnblogs.com/gomysql

您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。如果您需要技术支持，本人亦提供有偿服务。


阅读(1225) | 评论(0) | 转发(0) |
0

上一篇：Nginx+Tomcat搭建集群环境

下一篇：实战 SQL Server 2008 数据库误删除数据的恢复

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6