Mysql master replication + keepalived, mysqlkeepalived

Mysql master replication + keepalived, mysqlkeepalived

1. Environment

Master110.0.0.201

Master210.0.0.202

2 Master1 operation authorization

Mysql> grant replication slave on *. * to 'admin' @ '10. 0.0.202 'identified by '123'; Authorize

Mysql> show master status \ G;

3 Master2 operation authorization

Mysql-> grant replication slave on *. * to 'admin' @ '10. 0.0.201 'identified by '123'; Authorize
Mysql-> show master status \ G;

4. Operations on Master 1-201

Change master
Master_host = '10. 0.0.202 ',
Master_user = 'admin ',
Master_password = '000000 ',
Master_log_file = 'mysql-bin.000006 ',
Master_log_pos = 242;
Mysql-> start slave;
Mysql-> show slave status \ G;

5Operations on Master 2-202

Change master
Master_host = '10. 0.0.201 ',
Master_user = 'admin ',
Master_password = '000000 ',
Master_log_file = 'mysql-bin.000002 ',
Master_log_pos = 242;
Mysql-> start slave;
Mysql-> show slave status \ G;

6Master 201 and 202 install keepalived simultaneously

# Tar-xvf keepalived-1.1.20.tar.gz
# Cd keepalived-1.1.20
#./Configure -- prefix =/usr/local/keepalived
# Make & make install
# Cp/usr/local/keepalived/etc/rc. d/init. d/keepalived/etc/rc. d/init. d/
# Cp/usr/local/keepalived/etc/sysconfig/
# Mkdir/etc/keepalived
# Cp/usr/local/keepalived/etc/keepalived. conf/etc/keepalived/
# Cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

7Keepalived configuration file

! Configuration File for keepalived

Global_defs {
Notification_email {
Acassen@firewall.loc
Failover@firewall.loc
Sysadmin@firewall.loc
}
Notification_email_from Alexandre.Cassen@firewall.loc
Smtp_server 192.168.200.1
Smtp_connect_timeout 30
Router_id MYSQLHA_DEVEL
}

Vrrp_script check_mysqld {
Script "/etc/keepalived/mysqlcheck/check_mysql.sh" # Check the MYSQL replication status script
Interval 2
Weight 2
}

Vrrp_instance VI_1 {
State BACKUP # Here both master 201 and master 202 are written as BACKUP
Interface eth1 # vip external network interface
Virtual_router_id 80 # ID .. Master is unified
Priority 100 # priority. Change master 202 to 90
Advert_int 2
Nopreempt # Do not preemptible mode. Set this parameter on the server with a higher priority. This parameter is generally set on the master server. The master server has a higher priority.


Authentication {
Auth_type PASS
Auth_pass 1111
}

Track_script {
Check_mysqld # Call the MYSQL Script Function
}

Virtual_ipaddress {
10.0.2.204/16
}
}

When the master is down, the backup takes over, and the master gets up again, it will no longer become the master.Otherwise, the master will be recovered and then take over. If a problem occurs, switching two times is not good for your site business.

Solution:
State is set to Backup, and the parameter nopreempt is set in the instance with the highest priority.

8Scripts that monitor the MYSQL replication status

#! /Bin/bash
# Totle: check_mysql
# Description: Check mysql status
# System: Use Linux
# Author: Alvin
# Version: 1.0
# DateTime: 2014-09-09
#=====================================================
# Set mysql host
# Change each machine to its own IP address and remotely authorized user
Host_S = 10.0.0.201
User_S = admintest
Pwss_S = 123456
Port_S = 3306
#=====================================================
# Function --> Check_mysql_IO
#==================================================
Check_mysql_IO ()
{

Check_IO = 'mysql-u $ User_S-p $ Pwss_S-h $ Host_S-P $ Port_S-e "show slave status \ G" | grep "Runnin" | sed's // /G' | grep "IO" | awk-F: '{print $ NF}' | grep "Yes" | wc-l'

Check_ SQL = 'mysql-u $ User_S-p $ Pwss_S-h $ Host_S-P $ Port_S-e "show slave status \ G" | grep "Runnin" | sed's // /G' | grep "SQL" | awk-F: '{print $ NF}' | grep "Yes" | wc-l'


If [$ Check_IO-ne 1-o $ Check_ SQL-ne 1]
Then
/Etc/init. d/keepalived stop
Return 1
Else
Check_PID = '/etc/init. d/keepalived status | grep "pid" | wc-l'
If [$ Check_PID-eq 1]
Then
Echo "OK"
Exit 0
Else
/Etc/init. d/keepalived start
If [$? -Eq 0]
Then
Echo "keepalived start OK">/tmp/checkmysql. log
Exit 0
Else
Echo "keepalived start fail ..">/tmp/checkmysql. log
Return 1
Fi
Fi
Fi
}

#=====================================================
# Function --> Main
#==================================================
Main ()
{

Check_mysql_IO
If [$? -Eq 1]
Then
Echo "Mysql $ Host_S I/O or SQL error" | mail-s "Mysql IO error"
Exit 1
Fi

}
Main;

9Remotely authorize VIP login users in 201 and 202

> Grant all on *. * to admintest @ '%' identified by '20140901 ';

10Final Test

Usually the VIP mode is above 201. Let's test it in 202 ,. Use VIP to log on to whether 201 is displayed,
# Mysql-u admintest-p 123456-h 10.0.2.204-P 3306
Mysql à show variables like '% hostname % ';
Mysql à show variables like '% server_id % ';

Next. We test the master-master switchover. OK? We are in the 201. Stop the slave io.
Mysql-> slave stop;
Then exit. Check whether the VIP address of 201 exists? If not. So has the VIP taken over on 202?