Mysql5.7.22+keepalived High-availability cluster for dual-master interconnection

db1:192.168.254.128
db2:192.168.254.129
School-time operation prior to configuration
#安装ntpdate工具
Yum Install Ntpdate-y
#使用ntpdate校时 (followed by NTP server)
Ntpdate pool.ntp.org

Configure MySQL dual master standby

Install the database link (you can see it in the home database)
http://blog.51cto.com/10158955/1926574

DB1 Modify configuration file (restart required)
Vi/etc/my.cnf
#在 [mysqld] Add
server-id=166
#开启mysql日志功能
Log-bin=mysql-bin
#定义日志命名格式
Relay-log=mysql-relay-bin
#以下table复制过滤
replicate-wild-ignore-table=test.%
Replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%

DB2 Modify configuration file (restart required)
Vi/etc/my.cnf
#在 [mysqld] Add
server-id=168
#开启mysql日志功能
Log-bin=mysql-bin
#定义日志命名格式
Relay-log=mysql-relay-bin

DB1,DB2 Create duplicate accounts separately
Mysql-u root-p
#创建用户slave_up允许从192.168.254 Network Segment Login
Create user ' slave_cp ' @ ' 192.168.254.% ' identified by ' pass ';
Grant replication Slave on . to ' slave_cp ' @ ' 192.168.254.% ';
Exit
DB1,DB2 to obtain binary log information separately
Mysql-u root-p
#对数据库进行只读锁定 (prevents viewing of binary logs while someone modifies the database)
Flush tables with read lock;
#查询主机二进制文件信息
Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
#解除只读锁定
Unlock tables;

The data inside the database must be the same!!! If different, do synchronize data first!

On DB1 and DB2 respectively set up the other side as the main server!

Change Master to
Master_host= ' 192.168.254.128 ',
Master_user= ' SLAVE_CP ',
Master_password= ' Pass ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=154;

Change Master to
Master_host= ' 192.168.254.129 ',
Master_user= ' SLAVE_CP ',
Master_password= ' Pass ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=154;

#启动slave
Start slave;
#分别查看DB1, DB2 is working correctly
db1:192.168.254.128 Server
Show slave status\g
1. Row
Slave_io_state: Waiting for Master to send event
master_host:192.168.254.129
MASTER_USER:SLAVE_CP
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:154
Relay_log_file: mysql-relay-bin.000002
relay_log_pos:320
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes
Slave_sql_running:yes

db2:192.168.254.129 Server
Mysql> Show Slave Status\g
1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.254.128
Master_user:slave_cp
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000005
read_master_log_pos:154
relay_log_file:mysql-relay-bin.000011
relay_log_pos:367
relay_master_log_file:mysql-bin.000005
Slave_io_running:yes
Slave_sql_running:yes

Verify the dual-master mutual preparation
① uses show slave status\g on both servers to query the main library information as well as the IO process, SQL process working status. If the query results for both servers are Slave_io_running:yes,slave_sql_running:yes, then the current dual-master interoperability status is normal.
② build a table on the Mysql248 database, check if the Mysql249 is in sync, and build a table on the Mysql249 to check if the Mysql248 is in sync.
Quit MySQL after success
Exit

Configuring keepalived for MySQL dual master high Availability
128 Configuration on the server
! Configuration File for Keepalived
Global_defs {
#设置报警通知邮件地址, you can set multiple
notification_email {br/>[email protected]

#设置邮件的发送地址
Notification_email_from [email protected]
#设置smtp the address of the server, which must be present
Smtp_server 127.0.0.1
Time-out period for #设置连接smtp server
Smtp_connect_timeout 30
#运行Keepalived服务器的标识, the information that appears in the message header when the message is sent
router_id Mysql_msun
}

Detecting Haproxy Scripts

Vrrp_script chk_mysql {
Script "/etc/keepalived/check_slave.sh"
Interval 2
Weight 2
}
#定义VRRP实例, Instance Name Custom
Vrrp_instance Mysql_msun {
#指定Keepalived的角色, master host backup back up
State backup #此处两个都设置为BACKUP
# Specifies the interface for HA monitoring
interface ens32
#虚拟路由标识, which is a number (1-255), and the primary and standby server ID must be the same as
virtual_router_id-D
#优先级 in a VRRP instance. The higher the number priority, the greater the priority of the primary server in one instance than the standby server
First #从服务器99
#设置主备之间同步检查的时间间隔单位秒
Advert_int 1
# Set no preemption mode (DB1 settings)
Nopreempt
#设置验证类型和密码
Authentication {
#验证类型有两种 {pass|ha}
Auth_type PASS
# Set the authentication password, in one instance the master password remains the same
Auth_pass 1689
}
Track_script {
Chk_mysql # perform monitored services
}
#定义虚拟IP地址, can have multiple, one per line
Virtual_ipaddress {
192.168.254.160
}
}

129 configuration on the server
! Configuration File for Keepalived
Global_defs {
#设置报警通知邮件地址, you can set multiple
Notification_email {br/>[email protected]

#设置邮件的发送地址
Notification_email_from [email protected]
#设置smtp the address of the server, which must be present
Smtp_server 127.0.0.1
Time-out period for #设置连接smtp server
Smtp_connect_timeout 30
#运行Keepalived服务器的标识, the information that appears in the message header when the message is sent
router_id Mysql_msun
}

Detecting Haproxy Scripts

Vrrp_script Chk_mysql {
Script "/etc/keepalived/check_slave.sh"
Interval 2
Weight 2
}
#定义VRRP实例, Instance name customization
Vrrp_instance Mysql_msun {
#指定Keepalived的角色, Master host backup
State BACKUP #此处两个都设置为BACKUP
#指定HA监测的接口
Interface Ens32
#虚拟路由标识, this identifier is a number (1-255), and the primary and standby server ID must be the same in a VRRP instance
VIRTUAL_ROUTER_ID 68
#优先级, the higher the number priority, the greater the priority of the primary server in one instance than the standby server
Priority #从服务器99
#设置主备之间同步检查的时间间隔单位秒
Advert_int 1
#设置不抢占模式 (DB1 settings)
#nopreempt
#设置验证类型和密码
Authentication {
#验证类型有两种 {Pass|ha}
Auth_type PASS
#设置验证密码, the master password remains the same in one instance
Auth_pass 1689
}
Track_script {
Chk_mysql # Services to perform monitoring
}
#定义虚拟IP地址, there can be multiple, one per line
virtual_ipaddress {
192.168.254.160
}
}

Creating a monitoring script
Create scripts on two servers, respectively,
chmod +x/etc/keepalived/check_slave.sh
To modify according to your own situation

/etc/keepalived/check_slave.sh
#!/bin/bash
#This scripts is check for Mysql Slave status

Mysqlbin=/usr/bin/mysql
User=root
pw= ' a123456 '
port=3306
host=127.0.0.1
#最大延时
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
Fi

Mysql5.7.22+keepalived High-availability cluster for dual-master interconnection