keepalived mysql_Keepalived+MySQL实现高可用

最新推荐文章于 2023-08-11 21:38:10 发布
最新推荐文章于 2023-08-11 21:38:10 发布
阅读量461 收藏 1
点赞数
文章标签： keepalived mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31016735/article/details/113134798
版权

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

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

简单环境如下：

b1fa97f097b947696f310a95dacf3679.png

master 192.168.0.100slave192.168.0.101VIP192.168.0.88

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

1.keepalived软件安装(主从操作一样)

[root@mysql-server-01 ~]# wget -q http://www.keepalived.org/software/keepalived-1.2.13.tar.gz

[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]# catkeepalived.conf

global_defs {

router_id MySQL-HA

}

vrrp_script check_run {

script"/data/sh/mysql_check.sh"interval300}

vrrp_sync_group VG1 {

group {

VI_1

}

}

vrrp_instance VI_1 {

state BACKUP

interface eth1

virtual_router_id51priority100advert_int1nopreempt

authentication {

auth_type PASS

auth_pass1111}

track_script {

check_run

}

notify_master/data/sh/master.shnotify_backup/data/sh/backup.shnotify_stop/data/sh/stop.shvirtual_ipaddress {192.168.0.88}

}

[root@mysql-server-01 keepalived]#

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

[root@mysql-server-02 keepalived]# catkeepalived.conf

global_defs {

router_id MySQL-HA

}

vrrp_script check_run {

script"/data/sh/mysql_check.sh"interval300}

vrrp_sync_group VG1 {

group {

VI_1

}

}

vrrp_instance VI_1 {

state BACKUP

interface eth1

virtual_router_id51priority90advert_int1authentication {

auth_type PASS

auth_pass1111}

track_script {

check_run

}

notify_master/data/sh/master.shnotify_backup/data/sh/backup.shnotify_stop/data/sh/stop.shvirtual_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提升为主库时，发送邮件通知。

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

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

./root/.bash_profile

count=1

while true

domysql-uroot -pmsandbox -S /tmp/mysql_sandbox14520.sock -e "show status;" > /dev/null 2>&1i=$?

ps aux | grep mysqld | grep -v grep > /dev/null 2>&1j=$?

if [ $i = 0 ] && [ $j = 0]thenexit0

else

if [ $i = 1 ] && [ $j = 0]thenexit0

else

if [ $count -gt 5]thenbreakfilet count++continuefi

fi

done

/etc/init.d/keepalived stop

[root@mysql-server-01 sh]#

View Code

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

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[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"breakelse

sleep 1

if [ $i -gt 60]thenbreakficontinue

let i++

fi

donemysql-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]#

View Code

slave上的master.sh

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[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"breakelse

sleep 1

if [ $i -gt 60]thenbreakficontinue

let i++

fi

donemysql-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/statusecho "salve已经提升为主库，请进行检查！" >> /tmp/statusifconfig | 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/statusecho "#####################################" >> /tmp/status

master=`cat /tmp/status`echo "$master" | mutt -s "slave to primary!!!" 13143753516@139.com

View Code

脚本中检查复制是否延时的思想如下：

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以后执行的脚本。

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[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;"

View Code

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

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[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 1M_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"breakelse

sleep 1

if [ $i -gt 60]thenbreakficontinue

let i++

fi

done[root@mysql-server-02 sh]#

View Code

到这里基本就介绍完了。最后我们先看主从复制是否正常，如果正常，然后分别启动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'PAGERset 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.000001Read_Master_Log_Pos:409Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Exec_Master_Log_Pos:409Slave_SQL_Running_State: Slave hasread all relay log; waiting for the slave I/O thread to updateit1 row in set (0.00sec)

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.00sec)

node1[localhost] {msandbox} ((none)) >

根据我前面给的判断条件，可以看出我的复制没有任何延时。

下面分别在master上和slave上启动keepalived进程。以及查看日志(上面的查看只是给大家说明如何判断复制是否延迟)

master

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[root@mysql-server-01 sh]# /etc/init.d/keepalived start

Starting keepalived: [ OK ]

[root@mysql-server-01 sh]# tail -f /var/log/messages

Jul20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Netlink reflector reports IP 192.168.87.134added

Jul20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Netlink reflector reports IP 192.168.0.100added

Jul20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering Kernel netlink reflector

Jul20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering Kernel netlink command channel

Jul20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering gratuitous ARP shared channel

Jul20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.100added

Jul20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.87.134added

Jul20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.100added

Jul20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Registering Kernel netlink reflector

Jul20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Registering Kernel netlink command channel

Jul20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Opening file '/etc/keepalived/keepalived.conf'.

Jul20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Configuration is using : 6489Bytes

Jul20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Opening file '/etc/keepalived/keepalived.conf'.

Jul20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Configuration is using : 66476Bytes

Jul20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Using LinkWatch kernel netlink reflector...

Jul20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Using LinkWatch kernel netlink reflector...

Jul20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Entering BACKUP STATE

Jul20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]

Jul20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Script(check_run) succeeded

Jul20 20:48:27 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Transition to MASTER STATE

Jul20 20:48:27 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Group(VG1) Syncing instances to MASTER state

Jul20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Entering MASTER STATE

Jul20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) setting protocol VIPs.

Jul20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88Jul20 20:48:28 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.88added

Jul20 20:48:33 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88

View Code

slave

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[root@mysql-server-02 tmp]# /etc/init.d/keepalived start

Starting keepalived: [ OK ]

[root@mysql-server-02 tmp]# tail -f /var/log/messages

Jul20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc91 added

Jul20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP 192.168.0.101added

Jul20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc91 added

Jul20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc9b added

Jul20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering Kernel netlink reflector

Jul20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc9b added

Jul20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Registering Kernel netlink reflector

Jul20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering Kernel netlink command channel

Jul20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Registering Kernel netlink command channel

Jul20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering gratuitous ARP shared channel

Jul20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Opening file '/etc/keepalived/keepalived.conf'.

Jul20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Configuration is using : 6467Bytes

Jul20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Opening file '/etc/keepalived/keepalived.conf'.

Jul20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Configuration is using : 66454Bytes

Jul20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Using LinkWatch kernel netlink reflector...

Jul20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Using LinkWatch kernel netlink reflector...

Jul20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: VRRP_Instance(VI_1) Entering BACKUP STATE

Jul20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]

Jul20 20:48:35 mysql-server-02 Keepalived_vrrp[10680]: VRRP_Script(check_run) succeeded

View Code

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

[root@mysql-server-01 ~]# ip addr | grepeth13: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000inet192.168.0.100/24 brd 192.168.0.255scope global eth1

inet192.168.0.88/32scope global eth1

[root@mysql-server-01 ~]#

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

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

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[root@mysql-server-03 ~]# mysql -uadmin -p123456 -h 192.168.0.88 -P 14520Warning: Using a passwordonthe command line interface can be insecure.

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 47Server version:5.6.19-logMySQL Community Server (GPL)

Copyright (c)2000, 2014, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> create databasedengyayun;

Query OK,1 row affected (0.01sec)

mysql> usedengyayunDatabasechanged

mysql> create table t1 ( id int);

Query OK,0 rows affected (0.38sec)

mysql> insert into t1 select 999;

Query OK,1 row affected (0.03sec)

Records:1 Duplicates: 0 Warnings: 0mysql>

View Code

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

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

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

31eb563cb23cee4829f6a127844efdeb.png

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

[root@mysql-server-02 ~]# tail -n 20 /var/log/messages

Jul20 22:00:20 mysql-server-02 Keepalived_healthcheckers[13327]: Registering Kernel netlink command channel

Jul20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Opening file '/etc/keepalived/keepalived.conf'.

Jul20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Configuration is using : 66454Bytes

Jul20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Using LinkWatch kernel netlink reflector...

Jul20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Opening file '/etc/keepalived/keepalived.conf'.

Jul20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Configuration is using : 6467Bytes

Jul20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Using LinkWatch kernel netlink reflector...

Jul20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Entering BACKUP STATE

Jul20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]

Jul20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Script(check_run) succeeded

Jul20 22:07:47 mysql-server-02 dhclient[7343]: DHCPREQUEST on eth0 to 192.168.87.254 port 67 (xid=0x4ada08db)

Jul20 22:07:47 mysql-server-02 dhclient[7343]: DHCPACK from 192.168.87.254 (xid=0x4ada08db)

Jul20 22:07:49 mysql-server-02 dhclient[7343]: bound to 192.168.87.135 -- renewal in 885seconds.

Jul20 22:10:38 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Transition to MASTER STATE

Jul20 22:10:38 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Group(VG1) Syncing instances to MASTER state

Jul20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Entering MASTER STATE

Jul20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) setting protocol VIPs.

Jul20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88Jul20 22:10:39 mysql-server-02 Keepalived_healthcheckers[13327]: Netlink reflector reports IP 192.168.0.88added

Jul20 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 passwordonthe command line interface can be insecure.

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 301Server version:5.6.19-logMySQL Community Server (GPL)

Copyright (c)2000, 2014, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> select * fromdengyayun.t1;+------+

| id |

+------+

| 999 |

+------+

1 row in set (0.00sec)

mysql> pager cat | egrep 'IO_Running|SQL_Running'PAGERset 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.00sec)

mysql>

麓鸯
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL——Keepalived+Mysql高可用架构实战
庄小焱
01-09 4556
在生产环境中mysql的高可用的对于整个项目而言有着至关重要的作用。因此需要将mysql设计成为高可用的架构。本博文将详细介绍的mysql+keepalived构建的mysql互为主从的架构实战。
LVS+keepalived搭建负载均衡学习手札（二）
朱智文的专栏
03-01 590
上次做了LVS+keepalived的负载均衡，效果还不错，但是没有做主备，这次补上： 安装环境：环境 centos4.4 一共准备四台机器： 负载均衡机器： 主：192.168.2.158              #安装lvs+keepalived 备：192.168.2.159              #安装lvs+keepalived VIP:19
MySQL+KeepAlived实现双主高可用方案实践
长沙老码农
02-18 3996
目录 1、什么叫双主高可用（双机热备） 2、搭建主主同步 3、配置MySQL+Keepalived故障转移的高可用环境 4、测试 5、MyCAT双主高可用 5.1 架构图 5.2 准备环境 5.3 高可用配置 1、什么叫双主高可用（双机热备） 我们通常说的双机热备是指两台机器都在运行，但并不是两台机器都同时在提供服务。当提供服务的一台出现故障的时候，另外一台会马上自动接管并且提供服务，而且切换的时间非常短。MySQL双主复制，即互为Master-Slave(只有一个Master提供.
mysql keepalived_mysql高可用+keepalived
weixin_39737757的博客
01-18 1669
生产环境中一台mysql主机存在单点故障，所以我们要确保mysql的高可用性，即俩台mysql服务器如果其中有一台mysql服务器挂掉后，另外一台就能立刻接替进行工作。MYSQL的高可用方案一般有Keepalived+双主，MHA,PXC,MMM,Heartbeat+DRBD等 比较常用的是keepalived+双主MHA和PXC这次主要介绍利用keepalived实现MYSQL数据库的高可用。基...
mysql双主之keepalived
m0_46648661的博客
12-08 4212
生产环境中一台mysql主机存在单点故障，所以我们要确保mysql的高可用性，即两台MySQL服务器如果其中有 一台MySQL服务器挂掉后，另外一台能立马接替其进行工作。 MySQL的高可用方案一般有如下几种： keepalived+双主，MHA，PXC，MMM，Heartbeat+DRBD等，比较常用的是keepalived+双主，MHA和PXC。 本节主要介绍了利用 keepalived 实现 MySQL 数据库的高可用。 Keepalived+mysql双主来实现MySQL-HA，我 们必须保证两..
Mysql集群之mysql双主双活+keepalived实现高可用_mysql+keepalived搭建教程
热门推荐
a360284634的博客
11-24 1万+
史上最全mysql+keepalived教程。 本文介绍在linux服务下安装mysql并实现mysql主从配置，双主双活+keepalive实现mysql高可用的详细部署流程
keepalived实现mysql高可用_keepalived+MySQL实现高可用
weixin_35936869的博客
01-19 2967
(一)keepalived概述Keepalived通过VRRP(虚拟路由冗余协议)协议实现虚拟IP的漂移。当master故障后，VIP会自动漂移到backup，这时通知下端主机刷新ARP表，如果业务是通过VIP连接到服务器的，则此时依然能够连接到正常运行的主机，RedHat给出的VRRP工作原理如下图：本来对VIP漂移有一定了解的我，看了上面的图后，越来越懵了。因此只能根据我的个人理解，来对kee...
keepalived+mysql高可用
xiegongmiao的博客
02-10 1023
keepalived+mysql高可用
Mysql+Keepalived实现mysql数据库高可用
07-17
随着mysql在市场上的占有率不断提高，mysql数据库的安全也不容忽视。本文描述了keepalived与mysql配合是实现高可用，实现故障转移，负载均衡等。
MySQL主从复制+lvs与keepalived实现负载高可用
02-24
然而，单纯的 MySQL 主从复制无法满足高并发和高可用的需求，这时就需要引入 LVS 和 Keepalived 来实现负载均衡和高可用。 LVS（Linux Virtual Server）是一种开源的负载均衡解决方案，它可以将来自客户端的请求...
Keepalived+HAProxy实现MySQL高可用负载均衡的配置
09-10
总的来说，Keepalived和HAProxy的组合为MySQL提供了高可用性和负载均衡能力。当主MySQL服务器出现问题时，Keepalived会自动将VIP切换到备份服务器，同时HAProxy会根据新的配置继续进行负载均衡。这种方案不仅提高了...
mysql 双主单活+keepalived 实现高可用
11-20
本指导文档聚焦于“MySQL双主单活+Keepalived”架构，旨在实现数据库的高可用部署，以便在主节点故障时能自动切换到备用节点，保持业务连续运行。以下是关于这一主题的详细知识解释： 一、MySQL双主复制 MySQL双主...
Mysql8高可用之双主+keepalived
m0_61388193的博客
06-28 5600
利用keepalived实现Mysql数据库的高可用，Keepalived+Mysql双主来实现MYSQL-HA，我们必须保证两台Mysql数据库的数据完全一致，实现方法是两台Mysql互为主从关系，通过keepalived配置VIP，实现当其中的一台Mysql数据库宕机后，应用能够自动切换到另外一台Mysql数据库上去，保证系统的高可用。1、 采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，避免因为意外情况下相互抢占导致两个节点内写入相同的数据而引发冲突；
Openwrt 下实现主备路由自动切换 -- keepalived
bingyu的博客
04-07 1万+
Openwrt 下实现主备路由自动切换 – keepalived目前，一个项目中用到了H3C的设备，组网中使用了VRRP功能，实现主备路由的自动切换。经过查阅资料，发现openwrt中也支持类似的功能 — keepalived。参考链接：http://www.keepalived.org/最新的版本为V1.3.5，而我现有openwrt中的版本是V1.2.15。
Keepalived实现Mysql高可用
super_chenzhou
09-02 863
Keepalived实现Mysql高可用
Mysql主从之keepalived保证高可用搭建详细教程
qq_33549942的博客
03-13 1807
在很早之前搭建过Mysql的主从，但是当时年幼无知，没有想到过高可用怎么保证，这两天在复习以前的笔记的时候，无意中看到了Mysql之keepalived的文章，自己就想着实践一下，于是就有了这篇文章。文章大体分为两部分：1.主从搭建，2.keepalived集成。
keepalived与mysql搭建VIP漂移
qq_57052445的博客
05-11 1226
Keepalived 是一款用于实现高可用性和负载均衡的开源软件，它运行在 Linux 系统上，可以将多个服务器节点组合成一个虚拟服务器，通过 IP 地址漂移技术实现故障转移和负载均衡。Keepalived 支持 VRRP（虚拟路由冗余协议）和 LVS（Linux 虚拟服务器）两种模式，可以根据实际需求选择使用。VRRP 模式可以实现在多个节点之间自动切换 VIP（虚拟 IP）地址，确保在某个节点故障时，其他节点可以接管服务并保证服务的连续性。而 LVS 模式则可以在多个节点之间分配流量，实现负载均衡。
Mysql - 配置Mysql主从复制-keepalived高可用-读写分离集群
最新发布
lpfstudy的博客
08-11 2915
VRRP（Virtual Router Redundancy Protocol，虚拟路由器冗余协议）是一种网络协议用于提高路由器系统的可用性和可靠性。它通过将多个路由器组合成一个虚拟路由器，只有一个路由器处于活动状态，其他路由器都是备份状态，从而达到提供冗余和故障转移的目的。vip：virtual ip 虚拟IP地址，这个地址是对外提供服务的VRRP原理及配置 - 榕霖 - 博客园 (cnblogs.com)提供冗余。
Mysql+Keepalived实现双主热备
郑某某的博客
12-31 4358
Mysql双主热备
MySQL LVS+Keepalived+MHA高可用集群部署指南
本文档详细介绍了如何在IT环境中部署MySQL LVS (Linux Virtual Server)、Keepalived和MHA (Master High Availability)的高可用集群应用。LVS作为负载均衡器，负责分发流量到后端MySQL服务器，提高系统的可用性和性能...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值