MySQL 双主复制 + keepalived
参考：
https://www.jianshu.com/p/38223585e5ba
https://blog.csdn.net/xiaoyi23000/article/details/80525625
https://blog.csdn.net/u011310274/article/details/77775632
https://www.cnblogs.com/benjamin77/p/8682360.html
https://blog.csdn.net/zt15732625878/article/details/86493096
https://blog.51cto.com/853056088/2465855
1.双主规划
架构：双主复制+keepalived
mysql01: 192.168.10.161 3306
mysql02: 192.168.10.162 3306
keepalived: 192.168.10.160
操作系统： CentOS 7 x64
数据库版本：mysql-5.7.28
备注：MySQL 安装过程省略。
2.修改参数
#mysql01
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/3306
pid-file = /data/3306/mysql_3306.pid
socket = /tmp/mysql3306.sock
port = 3306
server_id = 330601
log_error = /data/3306/mysql.log
log_timestamps = system
log_bin = /data/3306/mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates=1
#binlog-do-db=mydb01,mydb02
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
auto-increment-increment= 2
auto-increment-offset = 1
expire_logs_days = 15
slow_query_log = 1
slow_query_log_file = /data/3306/slow.log
long_query_time = 2
log_queries_not_using_indexes
#mysql02
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/3306
pid-file = /data/3306/mysql_3306.pid
socket = /tmp/mysql3306.sock
port = 3306
server_id = 330602
log_error = /data/3306/mysql.log
log_timestamps = system
log_bin = /data/3306/mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates=1
#binlog-do-db=mydb01,mydb02
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
auto-increment-increment= 2
auto-increment-offset = 2
expire_logs_days = 15
slow_query_log = 1
slow_query_log_file = /data/3306/slow.log
long_query_time = 2
log_queries_not_using_indexes
#重启mysql
#mysql01
systemctl stop mysqld3306
systemctl stop mysqld3306
#mysql02
systemctl start mysqld3306
systemctl start mysqld3306
3.主从1：mysql01 -> mysql02
#mysql01
GRANT REPLICATION SLAVE ON *.* TO'repl1'@'%' IDENTIFIED BY '123';
#mysql02
change master to
master_host='192.168.10.161',
master_port=3306,
master_user='repl1',
master_password='123',
master_auto_position=1;
start slave；
4.主从2：mysql02 -> mysql01
#mysql02
GRANT REPLICATION SLAVE ON *.* TO'repl2'@'%' IDENTIFIED BY '123';
#mysql01
change master to
master_host='192.168.10.162',
master_port=3306,
master_user='repl2',
master_password='123',
master_auto_position=1;
start slave;
5.安装keepalived
yum -y install -y openssl-devel
yum -y install gcc gcc-c++
yum -y install libnl libnl-devel
yum -y install libnfnetlink-devel
yum -y install wget
mkdir /etc/keepalived
wget https://www.keepalived.org/software/keepalived-2.0.18.tar.gz
tar -zxvf keepalived-2.0.18.tar.gz
mv keepalived-2.0.18 /usr/local/keepalived
cd /usr/local/keepalived
./configure && make && make install
cp -a /usr/local/etc/keepalived /etc/init.d/
cp -a /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
cp -a /usr/local/sbin/keepalived /usr/sbin/
6.配置keepalived
#mysql01
cat > /etc/keepalived/keepalived.conf << EOF
! Configuration File for keepalived
global_defs {
notification_email {
test1@qq.com
test2@qq.com
}
notification_email_from test1@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
}
vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
script "/opt/chk_mysql.sh" #这里通过脚本监测
interval 2 #脚本执行间隔，每2s检测一次
weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
rise 1 #检测1次成功就算成功。但不修改优先级
}
vrrp_instance VI_1 {
state MASTER
interface ens33 #指定虚拟ip的网卡接口
mcast_src_ip 192.168.10.161
virtual_router_id 51 #路由器标识，MASTER和BACKUP必须是一致的
priority 101 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.160
}
track_script {
chk_mysql_port
}
}
EOF
#mysql02
cat > /etc/keepalived/keepalived.conf << EOF
! Configuration File for keepalived
global_defs {
notification_email {
test1@qq.com
test2@qq.com
}
notification_email_from test1@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
}
vrrp_script chk_mysql_port {
script "/opt/chk_mysql.sh"
interval 2
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
mcast_src_ip 192.168.10.162
virtual_router_id 51
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.160
}
track_script {
chk_mysql_port
}
}
EOF
7.配置mysql状态检查脚本
#mysql01 and mysql02
cat >/opt/chk_mysql.sh <<'EOF'
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
/etc/init.d/keepalived stop
fi
EOF
8.启动keepalived
#mysql01
netstat -lnp|grep 330 #确认mysql服务启动状态
systemctl enable keepalived
systemctl start keepalived
#mysql02
netstat -lnp|grep 330 #确认mysql服务启动状态
systemctl enable keepalived
systemctl start keepalived
9.测试
#mysql01
#1.检查当前192.168.10.160 MySQL 连接。
[root@mysql01 ~]# mysql -h192.168.10.160 -P3306 -uroot -p123456 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 33061 |
+-------------+
#2.检查当前192.168.10.161 keepalived服务状态。
[root@mysql01 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-11-23 19:37:46 CST; 58min ago
Process: 6666 ExecStart=/usr/local/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 6667 (keepalived)
CGroup: /system.slice/keepalived.service
├─6667 /usr/local/sbin/keepalived -D
└─6668 /usr/local/sbin/keepalived -D
Nov 23 20:09:04 mysql01 Keepalived_vrrp[6668]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:09:09 mysql01 Keepalived_vrrp[6668]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.10.160
Nov 23 20:09:09 mysql01 Keepalived_vrrp[6668]: Sending gratuitous ARP on ens33 for 192.168.10.160
#3.停止 192.168.10.161 MySQL服务。
[root@mysql01 ~]# ps -ef|grep 3306
root 12846 12811 0 20:07 pts/1 00:00:00 mysql -h192.168.10.160 -P3306 -uroot -px xxxx
mysql 13205 1 0 20:08 ? 00:00:02 /usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf
root 18582 16424 0 20:38 pts/2 00:00:00 grep --color=auto 330
[root@mysql01 ~]# kill -9 13205
#4.检查当前192.168.10.161 keepalived服务状态。
[root@mysql01 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-11-23 19:37:46 CST; 1h 3min ago
Process: 6666 ExecStart=/usr/local/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 6667 (keepalived)
CGroup: /system.slice/keepalived.service
├─6667 /usr/local/sbin/keepalived -D
└─6668 /usr/local/sbin/keepalived -D
Nov 23 20:09:04 mysql01 Keepalived_vrrp[6668]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:09:09 mysql01 Keepalived_vrrp[6668]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.10.160
Nov 23 20:09:09 mysql01 Keepalived_vrrp[6668]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:40:51 mysql01 Keepalived_vrrp[6668]: Script `chk_mysql_port` now returning 126
Nov 23 20:40:53 mysql01 Keepalived_vrrp[6668]: VRRP_Script(chk_mysql_port) failed (exited with status 126)
Nov 23 20:40:53 mysql01 Keepalived_vrrp[6668]: (VI_1) Changing effective priority from 101 to 96
#5.检查当前192.168.10.160 MySQL 连接。
[root@mysql01 ~]# mysql -h192.168.10.160 -P3306 -uroot -p123456 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 33062 |
+-------------+
#mysql02
#6.检查当前192.168.10.162 keepalived服务状态。
[root@mysql02 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-11-23 19:38:11 CST; 1h 3min ago
Process: 21069 ExecStart=/usr/local/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 21070 (keepalived)
CGroup: /system.slice/keepalived.service
├─21070 /usr/local/sbin/keepalived -D
└─21071 /usr/local/sbin/keepalived -D
Nov 23 20:40:57 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:40:57 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:40:57 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:40:57 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
#mysql01
#7.检查当前192.168.10.161 启动MySQL服务。
[root@mysql01 ~]# systemctl start mysqld3306
#8.检查当前192.168.10.161 keepalived服务状态。
[root@mysql01 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-11-23 19:37:46 CST; 1h 11min ago
Process: 6666 ExecStart=/usr/local/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 6667 (keepalived)
CGroup: /system.slice/keepalived.service
├─6667 /usr/local/sbin/keepalived -D
└─6668 /usr/local/sbin/keepalived -D
Nov 23 20:40:53 mysql01 Keepalived_vrrp[6668]: VRRP_Script(chk_mysql_port) failed (exited with status 126)
Nov 23 20:40:53 mysql01 Keepalived_vrrp[6668]: (VI_1) Changing effective priority from 101 to 96
Nov 23 20:40:56 mysql01 Keepalived_vrrp[6668]: (VI_1) Master received advert from 192.168.10.162 with higher priority 99, ours 96
Nov 23 20:40:56 mysql01 Keepalived_vrrp[6668]: (VI_1) Entering BACKUP STATE
Nov 23 20:40:56 mysql01 Keepalived_vrrp[6668]: (VI_1) removing VIPs.
Nov 23 20:49:33 mysql01 Keepalived_vrrp[6668]: Script `chk_mysql_port` now returning 0
Nov 23 20:49:33 mysql01 Keepalived_vrrp[6668]: VRRP_Script(chk_mysql_port) succeeded
Nov 23 20:49:33 mysql01 Keepalived_vrrp[6668]: (VI_1) Changing effective priority from 96 to 101
Nov 23 20:49:33 mysql01 Keepalived_vrrp[6668]: (VI_1) received lower priority (99) advert from 192.168.10.162 - discarding
Nov 23 20:49:34 mysql01 Keepalived_vrrp[6668]: (VI_1) received lower priority (99) advert from 192.168.10.162 - discarding
#9.再次检查当前192.168.10.160 MySQL 连接。
[root@mysql01 ~]# mysql -h192.168.10.160 -P3306 -uroot -p123456 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 33061 |
+-------------+
备注：可以看出，再次启动 主机mysql01 上MySQL服务，keepalived将 192.168.10.160 切换到 mysql01 主机上。
#mysql02
#10.检查当前192.168.10.162 keepalived服务状态。
[root@mysql02 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-11-23 19:38:11 CST; 1h 16min ago
Process: 21069 ExecStart=/usr/local/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 21070 (keepalived)
CGroup: /system.slice/keepalived.service
├─21070 /usr/local/sbin/keepalived -D
└─21071 /usr/local/sbin/keepalived -D
Nov 23 20:40:57 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:41:02 mysql02 Keepalived_vrrp[21071]: Sending gratuitous ARP on ens33 for 192.168.10.160
Nov 23 20:49:37 mysql02 Keepalived_vrrp[21071]: (VI_1) Master received advert from 192.168.10.161 with higher priority 101, ours 99
Nov 23 20:49:37 mysql02 Keepalived_vrrp[21071]: (VI_1) Entering BACKUP STATE
Nov 23 20:49:37 mysql02 Keepalived_vrrp[21071]: (VI_1) removing VIPs.
