MySQL dual-machine hot standby +keepalived based on primary master replication for high availability

1. Environment
Master1 192.168.1.135
Master2 192.168.1.136
Client 192.168.1.137


2. Master1 Operation authorization
Mysql->grant replication Slave on * * to ' admin ' @ ' 192.168.1.136 ' identified by ' aixocm '; Authorization actions
Mysql->flush privileges; Refresh Permissions
Mysql->show Master Status\g; View the Binlog log file name and POS number at this time


3. Master2 Operation authorization
Mysql->grant replication Slave on * * to ' backup ' @ ' 192.168.1.135 ' identified by ' aixocm '; Authorization actions
Mysql->flush privileges; Ditto
Mysql->show Master Status\g; Ditto


4. Operation on Main 1-135
Mysql->stop slave;
Mysql->change Master to master_host= ' 192.168.1.136 ', master_user= ' backup ', master_password= ' aixocm ', Master_log_ File= ' mysql-bin.000006 ', master_log_pos=411; Here the file name and POS number is the above Master1 view of the specific content
Mysql->start slave;
Mysql->show slave status\g;

After you turn on the copy function, if you can see slave_io_runing: and slave_sql_running: Both show yes, which means that the copy function is turned on normally, which is indispensable


5. Operation on Main 2-136
Mysql->stop slave;
Mysql->change Master to master_host= ' 192.168.1.135 ', master_user= ' admin ', master_password= ' aixocm ', Master_log_ File= ' mysql-bin.000002 ', master_log_pos=242;
Mysql->start slave;
Mysql->show slave status\g;
This is consistent with the above principle, not to be exhausted.


6, main 135 and 136 install keepalived simultaneously
This is Keepalived's official website: www.keepalived.org
# TAR-XVF Keepalived-1.1.20.tar.gz
# CD keepalived-1.1.20
#./configure--prefix=/usr/local/keepalived
#yum Install openssl* If you do not have OpenSSL installed
# Make && make install
# cp/usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/Mobile Startup script
# cp/usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/
# mkdir/etc/keepalived
# cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/Copy configuration file
# cp/usr/local/keepalived/sbin/keepalived/usr/sbin/Modify executable binary file path


7. Configure the keepalived configuration file on Master
! 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 30
ROUTER_ID Master #此处填本机的主机名
}

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 #这里主135和主136都写上BACKUP
Interface Eth0 #vip对外的网络接口
VIRTUAL_ROUTER_ID #标识号, the main unity together
Priority #优先级, the greater the precedence, the higher the main 136 is changed to 90
Advert_int 2
Nopreempt #不抢占模式, set this parameter on the high priority

Authentication {
Auth_type PASS
Auth_pass 1111
}

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

virtual_ipaddress {
192.168.1.200 dev eth0; set VIP, which is the virtual IP address, mine is on the eth0 NIC
}
}
When Master1 down, Master2 took over, Master1 again up, will not become master, that is, not to preempt the VIP again.
Otherwise master resumed the takeover, if there is a problem, then switching two times is not good for the business.
The workaround is to:
The state is set to backup, which sets the parameter nopreempt on the high priority.
Also, to configure the file in Maste2, you need to modify the host name, priority, and so on.


8, monitoring the MySQL replication status of the script, on the Master1, the script location on the keepalived configuration file is recorded.
#!/bin/bash
#function: monitor MySQL master and slave server status
#written By:alec

User=admin
host=192.168.1.135
password=aixocm

Check_mysql () {
Io= '/usr/local/mysql/bin/mysql-u$user-p$password-h$host-e ' show slave status\g ' |grep io_running|tr-s ' |awk ' {print $ ' |grep yes|wc-l '
Sql= '/usr/local/mysql/bin/mysql-u$user-p$password-h$host-e ' show slave status\g ' |grep sql_running|tr-s ' |sed-n ' 1p ' |awk ' {print $} ' |grep yes|wc-l '
If ["$IO"-eq 1]&&["$SQL"-eq 1]
Then
Service keepalived Status|grep PID
If [$?-eq 0]
Then
echo "Service has been started"
Else
Service keepalived Start
If [$?-eq 0]
Then
echo "Service started successfully"
Else
echo "Service failed to start"
Fi
Fi
Else
Service keepalived Status|grep PID
If [$?-eq 0]
Then
Service keepalived Stop
If [$?-eq 0]
Then
echo "Service shutdown succeeded"
Else
echo "Service shutdown failed"
Fi
Else
echo "Service is off"
Fi
Fi
}
Main () {
Check_mysql
}
Main
Similarly, the monitoring script is also required on Master2, which requires a username and password that can be logged on to the local MySQL, and only the user name, hostname and password should be modified on Master2. Note that you need to add executable permissions to the script.


9, on the Master1 and MASTER2 remote authorized VIP login User
>grant all on * * to [e-mail protected] '% ' identified by ' aixocm ';
The host with the VIP address is the real external service host, and the remote user connects to the real server via VIP
/etc/init.d/keepalived start; Both hosts start keepalived this service
The VIP will appear first on the Master1 with high priority, as shown in:



10 Final Test
Log in to the server with the client, so that the VIP server that is Master1 down, we only need to stop the slave copy function.
# Mysql–utest–paixocm–h192.168.1.200–p 3306
# stop Slave;
# quit;
Then at this time in view of the IP address, the VIP should be no, run to Master2 under the VIP address, if you can see the VIP address, indicating that you have succeeded, such as:

MySQL dual-machine hot standby +keepalived based on primary master replication for high availability