MySQL Primary master replication +keepalived

1 "Environment

Master1 10.0.0.201

Master2 10.0.0.202

2 "Master1 Operation authorization

Mysql>grant replication Slave on * * to ' admin ' @ ' 10.0.0.202 ' identified by ' 123456 '; authorization

Mysql>show Master Status\g;

3 "Master2 Operation authorization

Mysql->grant replication Slave on * * to ' admin ' @ ' 10.0.0.201 ' identified by ' 123456 '; authorization
Mysql->show Master Status\g;

4 "Operation on master 1-201

Change Master to
Master_host= ' 10.0.0.202 ',
Master_user= ' admin ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000006 ',
master_log_pos=242;
Mysql->start slave;
Mysql->show slave status\g;

5 " operation on Master 2-202

Change Master to
Master_host= ' 10.0.0.201 ',
Master_user= ' admin ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=242;
Mysql->start slave;
Mysql->show slave status\g;

6 " Master 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/keepalived/etc/sysconfig/
# mkdir/etc/keepalived
# cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/
# cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

7 "keepalived configuration file

! Configuration File for keepalived

Global_defs {
Notification_email {
[email protected]
[email protected]
[email protected]
}
notification_email_from [email protected]
Smtp_server 192.168.200.1
Smtp_connect_timeout
router_id Mysqlha_devel
}

vrrp_script check_mysqld {
script "/etc/keepalived/mysqlcheck/check_mysql.sh" #检查MYSQL Replication status Scripts
Interval 2
Weight 2
}

vrrp_instance vi_1 {
State BACKUP #这里主201和主202都写上BACKUP
interface eth1 #vip对外的网络接口
virtual_router_id #标识号. The Lord is mainly unified.
Priority #优先级. The main 202 is changed to
Advert_int 2
nopreempt #不抢占模式. This parameter is set at the higher priority level, which is usually set in the master, and the priority of the main is high


Authentication {
Auth_type PASS
Auth_pass 1111
}

Track_script {
check_mysqld #调用MYSQL脚本的函数
}

virtual_ipaddress {
10.0.2.204/16
}
}

when Master is down, backup takes over, master is up again and will no longer be master. Otherwise master resumes the re-takeover. If there is a problem, it is not good to switch two times to the health station business.

The workaround is to:
The state is set to backup, which sets the parameter nopreempt on the high priority.

8 " script to monitor 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
#每个机器改成自己的IP地址和远程授权的用户
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 IO or SQL error" | Mail-s "Mysql IO error" [Email protected]
Exit 1
Fi

}
Main;

9 "in 201 and 202 remote authorized VIP login User

>grant all on * * to [e-mail protected] '% ' identified by ' 123456 ';

10 " final Test

Usually the VIP mode is above 201, we test it in 202. With VIP landing whether see Yes 201,
# 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 this master switch. Is it OK? We're on the 201. Give the slave IO to stop.
Mysql->slave stop;
And then quit. See if the 201 VIP is still there? If it does not exist. So has the VIP been taken over at 202?

MySQL Primary master replication +keepalived