Mysql+Keepalived Dual Main Mutual Architecture Framework
Timing before configuration
#Install ntpdate tool yum install ntpdate -y #Use ntpdate to proofread time (followed by ntp server) ntpdate pool.ntp.org
I. Configuration of mysql dual backup
0. Test mysql installation
yum install mariadb mariadb-server -y systemctl restart mariadb systemctl enable mariadb mysql_secure_installation
1.DB1 modifies configuration files (need to restart)
vim /etc/my.cnf #Add in [mysqld] server-id=166 #Turn on mysql logging log-bin=mysql-bin #Define log naming format relay-log=mysql-relay-bin #The following table replication filtering #replicate-wild-ignore-table=test.% #replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table=performance_schema.%
2.DB2 modifies configuration files (need to restart)
vim /etc/my.cnf #Add in [mysqld] server-id=168 #Turn on mysql logging log-bin=mysql-bin #Define log naming format relay-log=mysql-relay-bin
3. DB1 and DB2 create duplicate accounts respectively
mysql -u root -p #Creating user slave_up allows login from 192.168.1 segment create user 'slave_cp'@'192.168.1.%' identified by 'pass'; grant replication slave on *.* to 'slave_cp'@'192.168.1.%'; exit
4.DB1 Gets Binary Log Information
mysql -u root -p #Read-only locking of the database (to prevent viewing binary logs while someone modifies the database) flush tables with read lock; #Query host binary file information show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 494 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) #Unlock read-only unlock tables; exit
5. Synchronized database
#DB1 #Back up the master database and upload it to the slave database mysqldump -uroot -p123456 --all-databases --lock-all-tables >/tmp/dbdump.sql scp /tmp/dbdump.sql 192.168.1.168:/tmp/ #DB2 #Import master server database from server mysql -uroot -p </tmp/dbdump.sql
6. Setting DB1 as the primary server in DB2
#Open mysql firewall port firewall-cmd --add-port=3306/tcp --permanent firewall-cmd --reload change master to master_host='192.168.1.168' , master_user='slave_cp', master_password='pass', master_log_file='mysql-bin.000001', master_log_pos=494; #start slave start slave; #Check to see if it works show slave status\G exit
7. Getting binary log information in DB2
mysql -u root -p #Read-only locking of the database (to prevent viewing binary logs while someone modifies the database) flush tables with read lock; #Query host binary file information show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 513721| | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) #Unlock read-only unlock tables; exit
8. Set DB2 as the primary server in DB1
change master to master_host='192.168.1.168' , master_user='slave_cp', master_password='pass', master_log_file='mysql-bin.000001', master_log_pos=513721; #start slave start slave; #Check to see if it works show slave status\G exit
2. Keepalived configuration (/etc/keepalived/keepalived.conf) (yum install keepalived-y) (note: close selinux policy setenforce 0)
1. Keeping alived configuration file configuration
! Configuration File for keepalived
global_defs {
#Set alarm notification email address, you can set more than one
notification_email {
msun1996@163.com
}
#Setting the mailing address
notification_email_from keepalived@msun.com
#Set the address of smtp server, which must exist
smtp_server 127.0.0.1
#Setting timeout for connection to smtp server
smtp_connect_timeout 30
#Run the Keepalived server's logo to display information in the mail title when sending an email
router_id mysql_msun
}
#Detecting haproxy scripts
vrrp_script chk_mysql {
script "/etc/keepalived/mysqlcheck/check_slave.sh"
interval 2
weight 2
}
#Define VRRP instances with custom instance names
vrrp_instance mysql_msun {
#Specify the Keepalived role, MASTER host BACKUP backup
state BACKUP #Here both are set to BACKUP
#Specify HA monitoring interface
interface eno16777736
#Virtual Routing ID, which is a number (1-255), must be the same as the primary and standby server ID in a VRRP instance
virtual_router_id 68
#Priority, the higher the number, the higher the priority, in an instance, the priority of the primary server is higher than that of the standby server
priority 100 #Slave Server 99
#Set the time interval per second for synchronous checking between master and backup
advert_int 1
#Set no preemption mode (DB1 settings will do)
nopreempt
#Setting authentication type and password
authentication {
#There are two types of validation {PASS|HA}
auth_type PASS
#Set the validation password to keep the primary and standby passwords the same in one instance
auth_pass 1689
}
track_script {
chk_mysql # Implementation of monitoring services
}
#Define virtual IP addresses, which can be multiple, one per line
virtual_ipaddress {
192.168.1.160
}
}2. Create a monitoring script (/etc/keepalived/mysqlcheck/check_slave.sh)
#!/bin/bash
#This scripts is check for Mysql Slave status
Mysqlbin=/usr/bin/mysql
user=root
pw='123456'
port=3306
host=127.0.0.1
#Max Delay
sbm=120
#Check for $Mysqlbin
if [ ! -f $Mysqlbin ];then
echo 'Mysqlbin not found,check the variable Mysqlbin'
exit 99
fi
#Get Mysql Slave Status
IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
#Check if the mysql run
if [[ -z "$IOThread" ]];then
exit 1
fi
#Check if the thread run
if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
exit 1
elif [[ $SBM -ge $sbm ]];then
exit 1
else
exit 0
fi3. Firewall Policy
#Running VPPR multicast firewall-cmd --permanent --add-rich='rule family="ipv4" destination address="224.0.0.18" protocol value="vrrp" accept' firewall-cmd --reload
4. test
1. Start DB1 and DB2 information normally
[root@DB1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet 192.168.1.160/32 scope global eno16777736 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe43:3013/64 scope link valid_lft forever preferred_lft forever
[root@DB2 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:feda:7d9d/64 scope link valid_lft forever preferred_lft forever
2. Close DB1 database and observe IP
[root@DB1 ~]# systemctl stop mariadb [root@DB1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic valid_lft 2591817sec preferred_lft 604617sec inet6 fe80::20c:29ff:fe43:3013/64 scope link valid_lft forever preferred_lft forever
[root@DB2 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet 192.168.1.160/32 scope global eno16777736 valid_lft forever preferred_lft forever inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic valid_lft 2591669sec preferred_lft 604469sec inet6 fe80::20c:29ff:feda:7d9d/64 scope link valid_lft forever preferred_lft forever
3. Restart mariadb and watch IP
[root@DB1 ~]# ip a #DB1 does not switch to host (because it is set to non-preemptive mode) 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic valid_lft 2591605sec preferred_lft 604405sec inet6 fe80::20c:29ff:fe43:3013/64 scope link valid_lft forever preferred_lft forever
[root@DB2 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet 192.168.1.160/32 scope global eno16777736 valid_lft forever preferred_lft forever inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic valid_lft 2591533sec preferred_lft 604333sec inet6 fe80::20c:29ff:feda:7d9d/64 scope link valid_lft forever preferred_lft forever
4. Close DB2's maraiadb again, and the problem arises. It is expected that when DB2 goes down, IP will not drift normally!!!
[root@DB2 ~]# systemctl stop mariadb [root@DB2 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet 192.168.1.160/32 scope global eno16777736 valid_lft forever preferred_lft forever inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic valid_lft 2591326sec preferred_lft 604126sec inet6 fe80::20c:29ff:feda:7d9d/64 scope link valid_lft forever preferred_lft forever
[root@DB1 ~]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 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 valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736 valid_lft forever preferred_lft forever inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic valid_lft 2591241sec preferred_lft 604041sec inet6 fe80::20c:29ff:fe43:3013/64 scope link valid_lft forever preferred_lft forever
Reflections on this issue:
Let's start with the combination of Keepalived+mysql in this case. Keepalived uses dual standby mode. It uses script to check whether the application is normal to determine the weight of keepalived, so that the normal database can get VIP and work properly. Considering that if DB1 fails (only mysql service) (the weight of DB1 will be lower than that of DB2), DB2 Take over the service, and when DB1 is normal again (the weight will be higher than DB2), in order to avoid database switching back and forth, a non-preemptive mode is set in the keepalived configuration of DB1 to avoid this problem. However, a new problem arises, even if DB2's database does not work properly (with a lower weight than DB2), because DB1 does not preempt mode, DB2 will still occupy VIP (unless the Keepalived service is down).
Consideration and solutions to this problem:
1. Keeping alived cancels the use of non-preemptive mode, so that the host can switch normally, but this will also make the DB 1 database service down and start normally, will preempt the VIP of DB2, making the database switch unnecessarily.
2. Modify the detection script, keep alived returns 0 when the detection script is normal, and let keep alived service itself go down when it is abnormal. In this case, the DB1 service mysql service will be normal again (and start keeping alived), and will not preempt the VIP of DB2; when the DB2 mysql service is not working properly, (will automatically stop keeping alived), so that DB1 will get the VIP normally.
#!/bin/bash
#This scripts is check for Mysql Slave status
Mysqlbin=/usr/bin/mysql
user=root
pw='123456'
port=3306
host=127.0.0.1
#Max Delay
sbm=120
#Check for $Mysqlbin
if [ ! -f $Mysqlbin ];then
echo 'Mysqlbin not found,check the variable Mysqlbin'
pkill keepalived
fi
#Get Mysql Slave Status
IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
#Check if the mysql run
if [[ -z "$IOThread" ]];then
pkill keepalived
fi
#Check if the thread run
if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
pkill keepalived
elif [[ $SBM -ge $sbm ]];then
pkill keepalived
else
exit 0
fi