概述

mysql主主复制

所谓主主复制,即双主备份,或者叫互作主从复制,每台master既是master,又是slave。这种方案,既做到了访问量的压力分流,同时也解决了单点故障问题。

Keepalived

VRRP协议

VRRP可以将两台或者多台物理路由器设备虚拟成一个虚拟路由,这个虚拟路由器通过虚拟IP(一个或者多个)对外提供服务,而在虚拟路由器内部十多个物理路由器协同工作,同一时间只有一台物理路由器对外提供服务,这台物理路由设备被成为:主路由器(Master角色),一般情况下Master是由选举算法产生,它拥有对外服务的虚拟IP,提供各种网络功能,如:ARP请求,ICMP 数据转发等,而且其它的物理路由器不拥有对外的虚拟IP,也不提供对外网络功能,仅仅接收MASTER的VRRP状态通告信息,这些路由器被统称为“BACKUP的角色”,当主路由器失败时,处于BACKUP角色的备份路由器将重新进行选举,产生一个新的主路由器进入MASTER角色,继续提供对外服务,整个切换对用户来说是完全透明的。

Keepalived工作原理

Keepalived的作用是检测服务器的状态,如果有一台web服务器宕机,或工作出现故障,Keepalived将检测到,并将有故障的服务器从系统中剔除,同时使用其他服务器代替该服务器的工作,当服务器工作正常后Keepalived自动将服务器加入到服务器群中,这些工作全部自动完成,不需要人工干涉,需要人工做的只是修复故障的服务器。

服务器信息

IP 主机名
192.168.10.21 master-1
192.168.10.22 master-2

架构图:

安装mysql

配置镜像源

配置镜像源创建文件*/etc/yum.repos.d/mysql57.repo*:

1
2
3
4
5
6
7
# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

更新缓存:

1
yum makecache

安装

master-1和master-2都按照以下步骤安装mysql。

1
2
3
yum -y install mysql mysql-server mysql-devel
systemctl start mysqld
systemctl enable mysqld

修改密码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@master-1 ~]#grep "temporary" /var/log/mysqld.log
2021-06-18T08:55:10.565308Z 1 [Note] A temporary password is generated for root@localhost: pFa)ajuqQ1J5
2021-06-18T08:55:12.785391Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
[root@master-1 ~]#mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set GLOBAL validate_password_policy=LOW;
mysql> set password for 'root'@'localhost'=password('XXX');
Query OK, 0 rows affected, 1 warning (0.00 sec)

创建相关目录:

1
2
3
4
5
mkdir /var/log/mysql
chown -R mysql.mysql /var/log/mysql
mkdir -p /data/mysql
cp -fr /var/lib/mysql/* /data/mysql/
chown -R mysql.mysql /data/mysql/

修改配置文件*/etc/my.cnf*:

master-1的server-id为1,master-2的server-id为2,其余配置不变。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[client] port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
log-bin = mysql-bin
binlog_format = ROW
log_slave_updates = 1
expire_logs_days = 7
max_binlog_size = 100M

server-id = 1

log_timestamps = SYSTEM
log_error=/var/log/mysql/mysql.err
character-set-server=utf8
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/
default-storage-engine=INNODB
max_connections=200
validate_password_policy=LOW
pid-file=/var/run/mysqld/mysqld.pid

重启:

1
systemctl restart mysqld

配置主主

创建主从复制账号

1
2
3
4
5
6
7
# master-1
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.10.22' identified by 'Rep@rep123';


# master-2
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.10.21' identified by 'Rep@rep123';

启动主主

master-1查询master状态:

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 599 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master-2启动主从复制:

1
2
3
4
5
6
# 启动主从复制
change master to master_host='192.168.10.21',master_user='rep',master_password='Rep@rep123',master_log_file='mysql-bin.000002',master_log_pos=599;
start slave;

# 查看slave是否正常启动
show slave status\G;

master-2查看状态:

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 599 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master-1启动主从复制:

1
2
3
4
5
6
# 启动主从复制
change master to master_host='192.168.10.22',master_user='rep',master_password='Rep@rep123',master_log_file='mysql-bin.000002',master_log_pos=599;
start slave;

# 查看slave是否正常启动
show slave status\G;

配置keepalived

安装keepalived

master-1和master-2安装keepalived:

1
yum install keepalived -y

配置

master-1的*/etc/keepalived/keepalived.conf*:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
! Configuration File for keepalived

global_defs {
router_id DB-HA
}

vrrp_script chk_mysql_port {
script "/scripts/check_mysql.sh"
interval 2
weight -5
fall 2
rise 1
}

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 110
mcast_src_ip 192.168.10.21
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.20
}
track_script {
chk_mysql_port
}
}

master-2的/etc/keepalived/keepalived.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
! Configuration File for keepalived

global_defs {
router_id DB-HA
}

vrrp_script chk_mysql_port {
script "/scripts/check_mysql.sh"
interval 2
weight -5
fall 2
rise 1
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 110
mcast_src_ip 192.168.10.22
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.20
}
track_script {
chk_mysql_port
}
}

检查mysql状态的脚本*/scripts/check_mysql.sh*:

1
2
3
4
5
#!/bin/bash
counter=$(netstat -lntp|grep 3306|wc -l)
if [ "${counter}" -eq 0 ]; then
systemctl stop keepalived
fi

授权:

1
chmod +x /scripts/check_mysql.sh

两台主机都启动keepalived:

1
systemctl start keepalived

此时,可以通过ip a查看vip在192.168.10.21的网卡上:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@master-1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether bc:95:e1:s9:2G:3g brd ff:ff:ff:ff:ff:ff
inet 192.168.10.12/21 brd 192.168.10.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.10.20/32 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::9714:an6b:43g3:c3i5/64 scope link
valid_lft forever preferred_lft forever