當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 主从 keepalived_一次mysql主从加keepalived配置搭建及切换演示

發布時間：2024/10/8 数据库 34 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 主从 keepalived_一次mysql主从加keepalived配置搭建及切换演示小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

【需求】

根據需求需要搭建mysql主從架構數據庫及加keepalived進行自動切換VIP(沒有使用腳本進行檢查對端數據庫及互換主從)

【環境介紹】

系統環境：CentOS release 6.4 (Final) + Server version: 5.7.25-log MySQL Community Server (GPL) + Keepalived v1.2.7

系統

主機名

備注

版本

xx系統

192.168.8.100

mysqldb1

主庫

Server version: 5.7.25-log MySQL Community Server (GPL)

192.168.8.101

mysqldb2

備庫

192.168.8.105

mysqldb1

主庫(VIP)

【搭建步驟：mysql軟件部署】

可參考之前mysql主從搭建步驟

【搭建步驟：keepalived軟件部署】

安裝步驟：

使用系統自帶的keepalived版本或者下載其他版本rpm安裝。yum -y install?keepalived*

節點一(主庫)

[root@mysqldb1 keepalived]# rpm -qa |grep keepalived

keepalived-1.2.7-3.el6.x86_64

[root@mysqldb1 keepalived]#

節點二(從庫)

[root@mysqldb2 ~]# rpm -qa |grep keepalived

keepalived-1.2.7-3.el6.x86_64

[root@mysqldb2 ~]#

keepalived配置文件：

節點一配置:

[root@mysqldb1 keepalived]# cat keepalived.conf

! Configuration File for keepalived

global_defs {

#notification_email {

#chenxxx@gmail.com

#notification_email_from xxx@163.com

#smtp_server smtp.163.com

smtp_connect_timeout 30

router_id LVS_DEVEL ? >>>可以自定義名字，跟從庫一致

}

vrrp_instance VI_1 {

state MASTER ? >>>表明為master節點

interface eth1 ? ? >>>指定主機IP掛載的接口與從庫一致

virtual_router_id 100 ?? >>>指定ID標示，與從庫一致

garp_master_delay 1

priority 100?????????>>>指定等級這里為100從庫為99

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.8.105/24 dev eth1 ? >>>VIP

}

[root@mysqldb1 keepalived]#

監控mysql數據庫狀態，輸出check_mysql.log日志

[root@mysqldb1 keepalived]# cat check_mysql.sh

#!/bin/bash

mysql=/var/lib/mysql/bin/mysql

mysql_host=localhost

mysql_user=root

mysql_password=mysql

check_time=3

mysql_ok=1

function check_mysql_health(){

$mysql -h $mysql_host -u$mysql_user -p$mysql_password -e 'show status;' >/dev/null 2>&1

if [ $? = 0 ] ;then

mysql_ok=1

echo 'mysql_ok'

else

mysql_ok=0

echo 'mysql_failed'

return $mysql_ok

}

while [ $check_time -ne 0 ]

let "check_time -= 1"

check_mysql_health

if [ $mysql_ok = 1 ] ;then

check_time=0

exit 0

if [ $mysql_ok -eq 0 ] && [ $check_time -eq 0 ]

then

pkill keepalived

sleep 1

done

[root@mysqldb1 keepalived]#

定時任務

#check_mysql

*/1 * * * * bash /etc/keepalived/check_mysql.sh >> /etc/keepalived/check_mysql.log

節點二配置：

[root@mysqldb2 keepalived]# cat? keepalived.conf

! Configuration File for keepalived

global_defs {

#notification_email {

#chenxxx@gmail.com

#notification_email_from myxxx@163.com

#smtp_server smtp.163.com

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance VI_1 {

state MASTER

interface eth1

virtual_router_id 100

garp_master_delay 1

priority 99

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.8.105/24 dev eth1

}

[root@mysqldb2 keepalived]# cat check_mysql.sh

#!/bin/bash

mysql=/var/lib/mysql/bin/mysql

mysql_host=localhost

mysql_user=root

mysql_password=mysql

check_time=3

mysql_ok=1

function check_mysql_health(){

$mysql -h $mysql_host -u$mysql_user -p$mysql_password -e 'show status;' >/dev/null 2>&1

if [ $? = 0 ] ;then

mysql_ok=1

echo 'mysql_ok'

else

mysql_ok=0

echo 'mysql_failed'

return $mysql_ok

}

while [ $check_time -ne 0 ]

let "check_time -= 1"

check_mysql_health

if [ $mysql_ok = 1 ] ;then

check_time=0

exit 0

if [ $mysql_ok -eq 0 ] && [ $check_time -eq 0 ]

then

pkill keepalived

sleep 1

done

[root@mysqldb2 keepalived]#

啟動keepalived服務：

主庫啟動keepalived，VIP已經掛載，查看日志為MASTER STATE模式

[root@mysqldb1 keepalived]# service keepalived start

Starting keepalived: [? OK? ]

[root@mysqldb1 keepalived]# ip a

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 08:00:27:62:61:3a brd ff:ff:ff:ff:ff:ff

inet 192.168.8.100/24 brd 192.168.8.255 scope global eth1

inet 192.168.8.105/24 scope global secondary eth1

inet6 fe80::a00:27ff:fe62:613a/64 scope link

valid_lft forever preferred_lft forever

[root@mysqldb1 keepalived]#

查看keepalived日志：

tail -f messages

Mar? 6 02:33:09 mysqldb1 Keepalived[12767]: Stopping Keepalived v1.2.7 (02/21,2013)

Mar? 6 02:33:09 mysqldb1 Keepalived_vrrp[12770]: VRRP_Instance(VI_1) sending 0 priority

Mar? 6 02:33:12 mysqldb1 Keepalived[12814]: Starting Keepalived v1.2.7 (02/21,2013)

Mar? 6 02:33:12 mysqldb1 Keepalived[12815]: Starting Healthcheck child process, pid=12817

Mar? 6 02:33:12 mysqldb1 Keepalived[12815]: Starting VRRP child process, pid=12818

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Interface queue is empty

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Netlink reflector reports IP 192.168.8.100 added

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Netlink reflector reports IP fe80::a00:27ff:fe62:613a added

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Registering Kernel netlink reflector

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Registering Kernel netlink command channel

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Interface queue is empty

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Netlink reflector reports IP 192.168.8.100 added

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Netlink reflector reports IP fe80::a00:27ff:fe62:613a added

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Registering Kernel netlink reflector

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Registering gratuitous ARP shared channel

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Registering Kernel netlink command channel

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Opening file '/etc/keepalived/keepalived.conf'.

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Opening file '/etc/keepalived/keepalived.conf'.

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Configuration is using : 5627 Bytes

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Configuration is using : 61320 Bytes

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: Using LinkWatch kernel netlink reflector...

Mar? 6 02:33:12 mysqldb1 Keepalived_vrrp[12818]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]

Mar? 6 02:33:12 mysqldb1 Keepalived_healthcheckers[12817]: Using LinkWatch kernel netlink reflector...

Mar? 6 02:33:13 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) Transition to MASTER STATE

Mar? 6 02:33:14 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) Entering MASTER STATE

Mar? 6 02:33:14 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) setting protocol VIPs.

Mar? 6 02:33:14 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.8.105

Mar? 6 02:33:14 mysqldb1 Keepalived_healthcheckers[12817]: Netlink reflector reports IP 192.168.8.105 added

Mar? 6 02:33:15 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.8.105

Mar? 6 02:34:04 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) Received lower prio advert, forcing new election

Mar? 6 02:34:04 mysqldb1 Keepalived_vrrp[12818]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.8.105

查看mysql數據庫監控日志

[root@mysqldb1 keepalived]# tail -f check_mysql.log

mysql_ok

從庫啟動keepalived，查看日志為BACKUP STATE模式

[root@mysqldb2 keepalived]# service keepalived start

Starting keepalived: [? OK? ]

[root@mysqldb2 keepalived]#

tail -f messages

Mar? 6 02:34:42 mysqldb2 Keepalived[12288]: Starting Keepalived v1.2.7 (02/21,2013)

Mar? 6 02:34:42 mysqldb2 Keepalived[12289]: Starting Healthcheck child process, pid=12291

Mar? 6 02:34:42 mysqldb2 Keepalived[12289]: Starting VRRP child process, pid=12292

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Interface queue is empty

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Interface queue is empty

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Netlink reflector reports IP 192.168.8.101 added

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Netlink reflector reports IP fe80::a00:27ff:fe53:eeca added

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Registering Kernel netlink reflector

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Registering Kernel netlink command channel

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Netlink reflector reports IP 192.168.8.101 added

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Netlink reflector reports IP fe80::a00:27ff:fe53:eeca added

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Registering Kernel netlink reflector

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Registering Kernel netlink command channel

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Registering gratuitous ARP shared channel

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Opening file '/etc/keepalived/keepalived.conf'.

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Opening file '/etc/keepalived/keepalived.conf'.

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Configuration is using : 5610 Bytes

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Configuration is using : 61303 Bytes

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: Using LinkWatch kernel netlink reflector...

Mar? 6 02:34:42 mysqldb2 Keepalived_vrrp[12292]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]

Mar? 6 02:34:42 mysqldb2 Keepalived_healthcheckers[12291]: Using LinkWatch kernel netlink reflector...

Mar? 6 02:34:43 mysqldb2 Keepalived_vrrp[12292]: VRRP_Instance(VI_1) Transition to MASTER STATE

Mar? 6 02:34:43 mysqldb2 Keepalived_vrrp[12292]: VRRP_Instance(VI_1) Received higher prio advert

Mar? 6 02:34:43 mysqldb2 Keepalived_vrrp[12292]: VRRP_Instance(VI_1) Entering BACKUP STATE

查看數據庫監控日志

[root@mysqldb2 keepalived]# tail -f check_mysql.log

mysql_ok

【進行遠程連接】

連接為VIP，端口為3306，schema為test

查看連接是為節點一主機

至此keepalived搭建完成

【進行keepalived模擬自動切換】

停止節點一mysql主數據庫

[root@mysqldb1 keepalived]# mysqladmin --defaults-file=/etc/my.cnf -P3306 shutdown -uroot -pmysql

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[1]+? Done??????????????????? mysqld --defaults-file=/etc/my.cnf --user=mysql

[root@mysqldb1 keepalived]#

查看節點一mysql進程監控日志

[root@mysqldb1 keepalived]# tail -f check_mysql.log

mysql_ok

mysql_failed

查看節點一keepalived日志信息