CENTOS6.3 using Keepalived to build dual-master MySQL + dual-host Hot Backup

CENTOS6.3 using Keepalived to build dual-master MySQL + dual-host Hot Backup

Previous blog posts introduced how to configure dual-master and dual-slave MYSQL.

See http://www.centoscn.com/mysql/2015/1030/6351.html

This section describes how to implement Keepalived dual-machine hot backup with the former.

System Environment: CentOS 6.3x64

MySQL version: mysql-5.6.10

Keepalived version: keepalived-1.2.7

MySQL-VIP: 192.168.7.253

MySQL-master1: 192.168.7.201

MySQL-master2: 192.168.7.249

First, disable iptables and SELINUX.

# Service iptables stop

# Setenforce 0

# Vi/etc/sysconfig/selinux

---------------

SELINUX = disabled

---------------

Note: If you need to enable iptables online, you must add a rule to enable vrrp access for keepalived.

# Iptables-a input-p vrrp-j ACCEPT

1. install and configure keepalived on the MySQL-master1: 192.168.7.201 Server

Compile and install it. The local kernel version is used as the standard.

# Wgethttp: // www.keepalived.org/software/keepalived-1.2.7.tar.gz

# Tar zxvf keepalived-1.2.7.tar.gz

# Cd keepalived-1.2.7

#./Configure -- prefix =/usr/local/keepalived -- with-kernel-dir =/usr/src/kernels/2.6.32-279. el6.x86 _ 64

# Make & make install

Set keepalived boot script

# Cp/usr/local/keepalived/etc/rc. d/init. d/keepalived/etc/rc. d/init. d/

# Cp/usr/local/keepalived/etc/sysconfig/

# Cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

# Chkconfig keepalived on

Create a configuration file. The default keepalived startup will go to the/etc/keepalived directory to find the configuration file.

# Mkdir/etc/keepalived

# Vi/etc/keepalived. conf

------------------

Global_defs {

Notification_email {

Lzyangel@126.com

}

# Send an email notification when the master and backup devices change

Notification_email_fromlzyangel@126.com

Smtp_server stmp.126.com

Smtp_connect_timeout 30

Router_id MySQL-ha

}

Vrrp_instance VI_1 {

# Define as the master device in the initialization status

State BACKUP

# Nic Interface

Interface eth0

Virtual_router_id 51

# Priority. Change the value of another instance to 90.

Priority100

Advert_int 1

# Do not take the initiative to seize resources

Nopreempt

Authentication {

# Authentication method, which can be PASS or AH

Auth_type PASS

# Authentication Password

Auth_pass 1111

}

Virtual_ipaddress {

# Virtual IP address, which will be deleted as the state Changes

192.168.7.253

}

}

Virtual_server 192.168.7.253 3306 {

# Check the real_server status every 2 seconds

Delay_loop 2

# LVS Algorithm

Lb_algo wrr

# LVS Mode

Lb_kind DR

# Session persistence time

Persistence_timeout 60

Protocol TCP

Real_server 192.168.7.201 3306 {

# Weight

Weight 3

# Scripts executed after the service is down

Notify_down/etc/rc. d/keepalived. sh

TCP_CHECK {

# Connection timeout

Connect_timeout 10

# Number of reconnections

Nb_get_retry 3

# Reconnection Interval

Delay_before_retry 3

# Health Check Port

Connect_port 3306

}

}

}

----------------------

Compile the script to be executed after the detection service is down

# Vi/etc/rc. d/keepalived. sh

-------------

#! /Bin/sh

/Etc/init. d/keepalived stop

-------------

# Chmod + x/etc/rc. d/keepalived. sh

Note: This script is used by the notify_down option in the configuration file above. keepalived uses the notify_down option to check the service status of real_server.

This script is triggered when real_server service failure is found.

We can see that the script is a command:

You can use pkill keepalived to forcibly kill the keepalived process, so as to realize automatic MySQL failover.

In addition, we do not have to worry that two MySQL databases will provide data update operations at the same time, because the keepalived configuration on each MySQL Server only contains the IP address + VIP address of the local MySQL server, instead of the IP address and VIP address of two MySQL instances.

The above script is used to implement VIP switching in the test environment. The following script is recommended in the production environment:

---------------------------------

#! /Bin/bash

# Environment Variables

PATH =/bin:/usr/bin:/sbin:/usr/sbin; export PATH

# Pause execution in 5 seconds to prevent manual restart of the database

Sleep 5

# Mysql_id (Survival = 1 dead = 0)

Mysql_id = 'ps-C mysqld -- noheader | wc-l'

# Determine if mysql_id is dead, restart mysql once. If mysql still cannot be started, kill the keepaliaved process to implement VIP switching.

If [$ mysql_id-eq 0]; then

/Etc/init. d/mysqld restart

Sleep 5

If [$ mysql_id-eq 0]; then

/Etc/init. d/keepalived stop

Fi

Fi

---------------------------------

Start keepalived

#/Etc/init. d/keepalived start

View connection status

# Ps-aux | grep keepalived

Return Value:

---------------

Root 1387 1 0 21:13? 00:00:00 keepalived-D

Root 1390 1387 0? 00:00:00 keepalived-D

Root 1391 1387 0? 00:00:00 keepalived-D

Root 1976 1911 0 00:00:00 pts/0 grep keepalived

--------------------

Test

View VIP Information

# Ip addr

--------------------------

............

Inet 192.168.7.201/24 brd 192.168.7.255 scope global eth0

Inet 192.168.7.253/32 scope global eth0

Inet6 fe80 ::20c: 29ff: feb2: 9199/64 scope link

Valid_lft forever preferred_lft forever

--------------------------

Note: If the master-slave VIP address is not automatically disabled when the master-slave VIP address is switched over, And the slave VIP address is enabled normally, the master VIP address is obtained, causing mutual conflict, you can manually enter the following command to delete the VIP address of one party:

# Ip addr del "virtual ip" dev eth0

However, this method is only a temporary solution. After you manually delete the VIP, restarting the keepalived service will not enable the vip normally. You need to restart the server to recover the service.

View vrrp Communication

# Tcpdump vrrp

-------------------------

.....

18:10:56. 365730 IP 192.168.7.201> vrrp.mcast.net: VRRPv2, Advertisement, vrid 52, prio 40, authtype simple, intvl 1 s, length 20

18:10:57. 366825 IP 192.168.7.201> vrrp.mcast.net: VRRPv2, Advertisement, vrid 52, prio 40, authtype simple, intvl 1 s, length 20

18:10:58. 367914 IP 192.168.7.201> vrrp.mcast.net: VRRPv2, Advertisement, vrid 52, prio 40, authtype simple, intvl 1 s, length 20

-------------------------

Note: If the master keepalived fails, the log here will switch to the backup keeplived information.

Find a lan pc and ping the VIP address of MySQL. At this time, the VIP address of MySQL can be pinged.

Stop the MySQL service and check whether the keepalived health check program will trigger the script we wrote to kill the keepalived process.

# Service mysqld stop

# Ps-aux | grep keepalived

No returned results

2. keepalived installation and configuration on MySQL-master2: 192.168.7.249 Server

Compile and install it. The local kernel version is used as the standard.

# Wgethttp: // www.keepalived.org/software/keepalived-1.2.7.tar.gz

# Tar zxvf keepalived-1.2.7.tar.gz

# Cd keepalived-1.2.7

#./Configure -- prefix =/usr/local/keepalived -- with-kernel-dir =/usr/src/kernels/2.6.32-279. el6.x86 _ 64

# Make & make install

Set keepalived boot script

# Cp/usr/local/keepalived/etc/rc. d/init. d/keepalived/etc/rc. d/init. d/

# Cp/usr/local/keepalived/etc/sysconfig/

# Cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

# Chkconfig keepalived on

Create a configuration file. The default keepalived startup will go to the/etc/keepalived directory to find the configuration file.

# Mkdir/etc/keepalived

# Vi/etc/keepalived. conf

------------------

Global_defs {

Notification_email {

Lzyangel@126.com

}

Notification_email_fromlzyangel@126.com

Smtp_server stmp.126.com

Smtp_connect_timeout 30

Router_id MySQL-ha

}

Vrrp_instance VI_1 {

# Both configurations are BACKUP

State BACKUP

# Nic Interface

Interface eth0

Virtual_router_id 51

# Priority. Change the priority of the other instance to 100.

Priority 90

Advert_int 1

# Do not take the initiative to seize resources

Nopreempt

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.7.253

}

}

Virtual_server 192.168.7.253 3306 {

# Check the real_server status every 2 seconds

Delay_loop 2

# LVS Algorithm

Lb_algo wrr

# LVS Mode

Lb_kind DR

# Session persistence time

Persistence_timeout 60

Protocol TCP

Real_server 192.168.7.249 3306 {

Weight 3

# Scripts executed after the service is down

Notify_down/etc/rc. d/keepalived. sh

TCP_CHECK {

# Connection timeout

Connect_timeout 10

# Number of reconnections

Nb_get_retry 3

# Reconnection Interval

Delay_before_retry 3

# Health Check Port

Connect_port 3306

}

}

}

----------------------

Compile the script to be executed after the detection service is down

# Vi/etc/rc. d/keepalived. sh

-------------

#! /Bin/sh

/Etc/init. d/keepalived stop

-------------

# Chmod + x/etc/rc. d/keepalived. sh

Start keepalived

#/Etc/init. d/keepalived start

View connection status

# Ps-aux | grep keepalived

Return Value:

---------------

Root 1387 1 0 21:13? 00:00:00 keepalived-D

Root 1390 1387 0? 00:00:00 keepalived-D

Root 1391 1387 0? 00:00:00 keepalived-D

Root 1976 1911 0 00:00:00 pts/0 grep keepalived

--------------------

After the configuration is complete, you only need to configure the MySQL-VIP: 192.168.7.253 virtual address in the website background.

The actual master database to be connected here is subject to which master should first enable the mysql service.

When the master1: 192.168.7.201 mysql port is blocked or the server is shut down abnormally, keepalived automatically jumps to master2: 192.168.7.249.

Because the data of the two databases is synchronized, the user accesses the MySQL-VIP: 192.168.7.253 virtual address, so the website data connection is seamlessly and transparently transferred to the master2 service.

To achieve dual-machine Hot Standby + data synchronization. Ensure the real-time availability of the website database.

Note: After a master server crashes and recovers, you must enable the MYSQL service and keepalived service at the same time to ensure seamless transfer if another server fails.

Therefore, we recommend that you set the Service Startup for mysql and keeplived.

# Chkconfig mysqld on

# Chkconfig keepalived on

---------- Success ------------