MySQL-主主模式集群部署
目录
一、简介
什么是双主复制
在传统的主从复制架构中，从库仅仅是作为主库数据的备份，当主库发生故障时，数据库将停止对外提供服务，并且主库故障后手动进行主从切换的过程也较为繁琐。为了解决这个问题，可以采用 MySQL 双主模式，其中一台主库提供服务，另一台作为热备。结合 keepalived 使用虚拟 IP 对外提供服务，一旦主库发生故障，备库可以在很短的时间内接管服务
二、服务器规划
| 主机名 | ip地址 | 角色 | 版本 |
|---|---|---|---|
| db01 | 10.0.0.51 | master（主库A） | 8.0.26 |
| db02 | 10.0.0.52 | slave（主库B） | 8.0.26 |
| 10.0.0.50 | 虚拟ip | 8.0.26 |
三、安装MySQL
1.下载安装包
下载地址：https://downloads.mysql.com/archives/community/，选择适合自己linux上的版本
解压安装包
tar -xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.26-linux-glibc2.12-x86_64 /usr/local/mysql
1.1 关闭防火墙
1.#闭防火墙、selinux、dnsmasq/NetworkManager
systemctl disable --now firewalld
systemctl disable --now dnsmasq
systemctl disable --now NetworkManager
setenforce 0
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/sysconfig/selinux
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
#检查
grep ^SELINUX= /etc/selinux/config
2.创建相关目录
#创建用户
useradd mysql -s /sbin/nologin -M
id mysql
#创建目录
# /mysql/data/3306/data/ MySQL 数据文件目录
# /mysql/log/3306/binlog MySQL 二进制日志目录
# /mysql/log/3306/relaylog MySQL 中继日志目录
# /mysql/backup/3306/xtrabackup/target_dir MySQL xtrabackup 物理备份目录
# /mysql/backup/3306/mysqldump MySQL mysqldump 逻辑备份目录
# /mysql/script MySQL 常用脚本存放目录
mkdir -p /mysql/data/3306/data/
mkdir -p /mysql/log/3306/binlog
mkdir -p /mysql/log/3306/relaylog
mkdir -p /mysql/backup/3306/xtrabackup/target_dir
mkdir -p /mysql/backup/3306/mysqldump
mkdir -p /mysql/script
#给目录授权
chown -R mysql:mysql /mysql
3.配置环境变量
cat >> ~/.bash_profile <<-EOF
export PATH=$PATH:/usr/local/mysql/bin
EOF
source ~/.bash_profile
4.初始化数据库A
主库 A 重要配置如下：
- 开启 binlog：log_bin=binlog 目录
- 设置 server_id：server_id = 1，主库 A 的 server_id 和主库 B 要不一样。
- 针对 GTIP 的方式同步有两个参数必须设置：
- gtid_mode=on
- enforce_gtid_consistency=on
- 防止主键冲突：
- 设置自增主键步长，通常有几个主库 就写几，避免主键冲突：auto_increment_increment=2
- 设置自增主键起始值，第一个主库为 1，第二个主库为 2，以此类推：auto_increment_offset=1
关于防止主键冲突的两个参数的详解可以参考这篇博客
https://www.cnblogs.com/kerrycode/p/11150782.html
4.1 设置环境变量
#主机名和端口号作为目录名的一部分
HostName=`hostname`
MySql_Port=3306
#IP地址
Ip=10.0.0.51
#master server_id 要和 slave 不一样
Server_Id=1
4.2 初始化配置文件
cat > /etc/my.cnf <<-EOF
[client]
port=$MySql_Port
socket=/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8mb4
[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>" # 登录时显示用户名、服务器地址、默认数据库名、当前时间
auto-rehash # 读取表信息和列信息，可以在连上终端后开启 Tab 补全功能
default-character-set=utf8mb4 # 默认字符集
[mysqld]
bind-address=0.0.0.0 # 监听本地所有地址
port=$MySql_Port # 端口号
user=mysql # 用户
basedir=/usr/local/mysql/ # 安装路径
datadir=/mysql/data/$MySql_Port/data # MySQL 数据目录
socket=/mysql/data/$MySql_Port/mysql.sock # 用于本地连接的 socket 文件目录
pid-file=/mysql/data/$MySql_Port/mysql.pid # 进程 ID 文件目录
character-set-server=utf8mb4 # 默认字符集
collation-server=utf8mb4_general_ci # 默认校对规则
lower_case_table_names=1 # 表名大小写不敏感
innodb_buffer_pool_size=256G #根据内存大小配置，一般为主机内存的百分之七十
innodb_buffer_pool_instances=8 #缓冲池分成多个实例
long_query_time=10 # 慢查询时间，超过 10 秒则认为是慢查询
slow_query_log=ON # 启用慢查询日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log # 慢查询日志目录
log-queries-not-using-indexes=1 # 记录未使用索引的语句
log-slow-admin-statements=1 # 慢查询日志记录优化表、分析表和修改表语句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log # 错误日志目录
server-id=$Server_Id # master 和 slave server_id 需要不同
relay_log=/mysql/log/$MySql_Port/relaylog/${HostName}-relaylog # 中继日志目录
relay-log-index=/mysql/log/$MySql_Port/relaylog/${HostName}-relay.index # 中继日志索引目录
log_slave_updates=1 # 主库B从主库A复制的数据会写入主库B binlog 日志文件
read_only=0 # 主库B读写权限
relay_log_purge=1 # 自动清空不再需要的中继日志
# 二进制日志参数配置
log-bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog # binlog目录
log-bin-index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index # 指定索引文件的位置
binlog_format=row # 行模式复制，默认是 row
binlog_rows_query_log_events=ON # 在 row 模式下开启此参数，将 SQL 语句记录到 binlog 日志中
binlog_cache_size=1M # 事务可使用的最大 binlog 缓存空间
max_binlog_size=2048M # binlog 文件最大空间，达到该大小时分割文件
expire_logs_days=7 # 设置自动删除 binlog 文件的天数
sync_binlog=1 # 每次事务的 binlog 都会 fsync 持久化到磁盘，MySQL 5.7.7 之后默认为 1
innodb_flush_log_at_trx_commit=1 # 每次事务的 redo log 都直接持久化到磁盘，默认值为 1
gtid_mode=ON # 开启 GTID 同步
enforce_gtid_consistency=ON # 强制事务一致，确保 GTID 的安全，在事务中不能创建和删除临时表
binlog_gtid_simple_recovery=ON # 在 MySQL 重启或启动时寻找 GTIDs 过程中控制 binlog 如何遍历的算法
auto_increment_increment=2 # 自增主键步长，通常有几个主库A就设几，避免主键冲突
auto_increment_offset=1 # 设置自增主键起始值，第一个主库A为1，第二个主库A为2，以此类推
EOF
4.3 初始化数据目录
mysqld \
--initialize-insecure --user=mysql \
--basedir=/usr/local/mysql/ \
--datadir=/mysql/data/3306/data
4.4 配置启动脚本
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld #<==拷贝mysql启动脚本到启动路径。
systemctl enable mysqld #<==等价chkconfig mysqld on(c7把服务设置开机自启动命令)
4.5 启动MYSQL
systemctl start mysqld
4.6 设置root密码
[root@db01 ~]# mysqladmin password 123456
4.7 允许root远程登录
CREATE USER 'root'@'10.0.0.%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
5.初始化数据库B
5.1 设置环境变量
#主机名和端口号作为目录名的一部分
HostName=`hostname`
MySql_Port=3306
#IP地址
Ip=10.0.0.52
#master server_id 要和 slave 不一样
Server_Id=2
5.2 初始化配置文件
cat > /etc/my.cnf <<-EOF
# 客户端设置
[client]
port=$MySql_Port
socket=/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8mb4
# mysql 连接工具设置
[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>" # 登录时显示用户名、服务器地址、默认数据库名、当前时间
auto-rehash # 读取表信息和列信息，可以在连上终端后开启 Tab 补全功能
default-character-set=utf8mb4
# 基本设置
[mysqld]
bind-address=0.0.0.0 # 监听本地所有地址
port=$MySql_Port # 端口号
user=mysql
basedir=/usr/local/mysql/
datadir=/mysql/data/$MySql_Port/data
socket=/mysql/data/$MySql_Port/mysql.sock
pid-file=/mysql/data/$MySql_Port/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
lower_case_table_names=1 # 表名大小写不敏感
# 日志设置
long_query_time=10 # 慢查询时间，超过 10 秒则认为是慢查询
slow_query_log=ON # 启用慢查询日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log # 慢查询日志目录
log-queries-not-using-indexes=1 # 记录未使用索引的语句
log-slow-admin-statements=1 # 慢查询日志记录优化表、分析表和修改表语句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log # 错误日志目录
# 主库A复制更改参数
server-id=$Server_Id # master 和 slave server_id 需要不同
# 主库B参数
relay_log=/mysql/log/$MySql_Port/relaylog/${HostName}-relaylog
relay-log-index=/mysql/log/$MySql_Port/relaylog/${HostName}-relay.index
log_slave_updates=1 #
read_only=0 # 主库B读写权限
relay_log_purge=1 # 自动清空不再需要的中继日志
# 二进制日志参数配置
log-bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog # binlog目录
log-bin-index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index # 指定索引文件的位置
binlog_format=row # 行模式复制，默认是 row
binlog_rows_query_log_events=ON # 在 row 模式下开启此参数，将 SQL 语句记录到 binlog 日志中
binlog_cache_size=1M # 事务可使用的最大 binlog 缓存空间
max_binlog_size=2048M # binlog 文件最大空间，达到该大小时分割文件
expire_logs_days=7 # 设置自动删除 binlog 文件的天数
sync_binlog=1 # 每次事务的 binlog 都会 fsync 持久化到磁盘，MySQL 5.7.7 之后默认为 1
innodb_flush_log_at_trx_commit=1 # 每次事务的 redo log 都直接持久化到磁盘，默认值为 1
# GTID 同步复制设置
gtid_mode=ON # 开启 GTID 同步
enforce_gtid_consistency=ON # 强制事务一致，确保 GTID 的安全，在事务中不能创建和删除临时表
binlog_gtid_simple_recovery=ON # 在 MySQL 重启或启动时寻找 GTIDs 过程中控制 binlog 如何遍历的算法
# 避免主键冲突设置
auto_increment_increment=2 # 自增主键步长，通常有几个主库A就设几，避免主键冲突
auto_increment_offset=2 # 设置自增主键起始值，第一个主库A为1，第二个主库A为2，以此类推
EOF
5.3 初始化数据目录
mysqld \
--initialize-insecure --user=mysql \
--basedir=/usr/local/mysql/ \
--datadir=/mysql/data/3306/data
5.4 配置启动脚本
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld #<==拷贝mysql启动脚本到启动路径。
systemctl enable mysqld #<==等价chkconfig mysqld on(c7把服务设置开机自启动命令)
5.5 启动MYSQL
systemctl start mysqld
5.6 设置root密码
5.7 允许root远程登录
6.创建复制用户
6.1 主库A创建复制用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%';
6.2 主库B创建复制用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%';
7.建立主从关系
7.1 主库 A 和主库 B 都先清除下 binlog
reset master;
7.2 主库 A 配置主从，指向主库 B
stop slave;
change master to
master_host='10.0.0.52',
master_user='repl',
master_password='123',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
start slave;
7.3 主库 B 配置主从，指向主库 A
stop slave;
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
start slave;
7.4 检查主从状态
主库A和主库B检查主从状态
IO 线程和 SQL 线程都为 YES 表示同步正常，主库 A 和主库 B 互为主从
show slave status\G
若主从构建失败，则重新构建主从
STOP SLAVE;
#重新配置主从
RESET SLAVE ALL;
7.5 测试主从是否同步数据
7.5.1 测试主库A
主库A创建数据
# 51:
create database test01;
主库B查看数据
# 52:
show databases;
show slave status\G
Retrieved_Gtid_Set: 0d07bf42-1a42-11ef-a36f-000c29720d9c:1
Executed_Gtid_Set: 0d07bf42-1a42-11ef-a36f-000c29720d9c:1，359f4820-1a46-11ef-a7d1-000c29ee2289:
可以看到GTID已经同步
7.5.2 测试主库B
主库B创建数据
# 52:
create database test02;
主库A查看数据
# 51:
show databases;
show slave status\G
Retrieved_Gtid_Set: 359f4820-1a46-11ef-a7d1-000c29ee2289:1
Executed_Gtid_Set: 0d07bf42-1a42-11ef-a36f-000c29720d9c:1-2,359f4820-1a46-11ef-a7d1-000c29ee2289:1
可以看到GTID已经同步
四、安装keepalived
安装keepalived
yum install keepalived -y
主库A配置文件
cat > /etc/keepalived/keepalived.conf <<-EOF
global_defs {
router_id mysql01
}
vrrp_script check_mysqld {
script "/etc/keepalived/mysqlcheck/check_port.sh"
interval 2
}
vrrp_instance Mysql-HA {
state MASTER
interface eth0
virtual_router_id 10
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
track_script {
check_mysqld
}
virtual_ipaddress {
10.0.0.50/24 dev eth0 label eth0:1
}
}
EOF
主库B配置文件
cat > /etc/keepalived/keepalived.conf <<-EOF
global_defs {
router_id mysql02
}
vrrp_script check_mysqld {
script "/etc/keepalived/mysqlcheck/check_port.sh"
interval 2
}
vrrp_instance Mysql-HA {
state BACKUP
interface eth0
virtual_router_id 10
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
track_script {
check_mysqld
}
virtual_ipaddress {
10.192.45.15/24 dev eth0 label eth0:1
}
}
EOF
主库A和主库B配置检测脚本
#!/bin/bash
# 定义日志文件
LOGFILE="/var/log/mysqlcheck.log"
# 定义要测试的端口号
port=3306
# 定义要ping的IP地址
ping_ip="10.0.0.254"
# 检查ping是否通
if ! /bin/ping -c 2 $ping_ip &> /dev/null; then
echo "$(date) - Ping to $ping_ip failed" >> $LOGFILE
exit 1
else
echo "$(date) - Ping to $ping_ip succeeded" >> $LOGFILE
fi
# 检查 MySQL 端口是否开放
if ! /usr/bin/nc -zv 127.0.0.1 $port &> /dev/null; then
echo "$(date) - MySQL port $port is closed" >> $LOGFILE
exit 1
else
echo "$(date) - MySQL port $port is open" >> $LOGFILE
fi
# 一切正常
echo "$(date) - All checks passed" >> $LOGFILE
exit 0
全部增加权限
chmod +x /etc/keepalived/mysqlcheck/check_port.sh
启动keepalived
systemctl enable --now keepalived.service
systemctl status keepalived.service
查看虚拟IP
查看网卡地址，此时虚拟 IP 在主库 A 上。
- 通过虚拟ip连接mysql
mysql -uroot -p123456 -h 10.0.0.50
五、高可用验证
1.vip连接数据库
select @@hostname;
可以看到db01为主库
2.增加测试数据
create database testdb;
create table testdb.data01(
id int not null primary key auto_increment,
name varchar(60),
age int);
insert into testdb.data01 (name,age) values
('tom',18),
('jack',17),
('rock',16),
('james',15),
('cris',20);
此时分别登录主库 A 和主库 B 查看 testdb.data01 表中的数据，可以确定主库 A 和主库 B 目前数据是同步的。并且查看表中的内容可以发现主键是以 2 为间隔递增的，这是为了防止主从切换时插入数据产生主键冲突。主库 A 的主键会以 1，3，5，7，9 的序号递增。假如在序号为 9 时发生主从切换，新的主库（主库 A）的主键会以 10，12，14，16，18 的序号递增。
3.停止主库A，模拟故障切换
[root@db01 ~]# systemctl stop mysqld
3.1 客户端查看
客户端发生了重连，通过 select @@hostname 查看可以看到此时连接的是主库 B。
增加测试数据
insert into testdb.data01 (name,age) values
('peter',28),
('mark',27),
('marry',26),
('hule',25),
('handson',20);
查询数据，可以看到在原主库 B 上插入的数据主键会以 10，12，14，16，18 的序号递增。
root@10.0.0.50 (none) :55: >select * from testdb.data01;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tom | 18 |
| 3 | jack | 17 |
| 5 | rock | 16 |
| 7 | james | 15 |
| 9 | cris | 20 |
| 10 | peter | 28 |
| 12 | mark | 27 |
| 14 | marry | 26 |
| 16 | hule | 25 |
| 18 | handson | 20 |
+----+---------+------+
10 rows in set (0.00 sec)
4.重新启动主库 A，观察数据是否同步
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# mysql -uroot -p123456
root@localhost (none) :55: >select * from testdb.data01;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tom | 18 |
| 3 | jack | 17 |
| 5 | rock | 16 |
| 7 | james | 15 |
| 9 | cris | 20 |
| 10 | peter | 28 |
| 12 | mark | 27 |
| 14 | marry | 26 |
| 16 | hule | 25 |
| 18 | handson | 20 |
+----+---------+------+
10 rows in set (0.00 sec)
由于我们开启了抢占模式，当主库 A 重新启动时，主库A会切换成master模式。
本文来自博客园，作者：&UnstopPable，转载请注明原文链接：https://www.cnblogs.com/Unstoppable9527/p/18362898
