Mysql高可用故障转移（双主热备）

基础环境规划：

-	主机名	IP地址	备注
主库	oraclelinux-mysql-node1	192.168.192.171	keepalive主节点
从库	oraclelinux-mysql-node2	192.168.192.172	keepalive备节点
-	-	192.168.192.170	虚拟IP，会在keepalive启动后分配到上面2台机器的主节点上

数据库安装

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.37-linux-glibc2.28-x86_64.tar.xz
tar -xvJf mysql-8.0.37-linux-glibc2.28-x86_64.tar.xz
cp -r mysql-8.0.37-linux-glibc2.28-x86_64/* /usr/local/mysql/
#mv mysql-8.0.37-linux-glibc2.28-x86_64 /usr/local/mysql

groupadd mysql
useradd -r -g mysql mysql

mkdir /usr/local/mysql/{binlog,logs,tmp,data} -p

cd /data
chgrp -R mysql .

cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .

修改环境变量

vim /etc/profile

最后一行加上

export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib

source /etc/profile

初始化mysql数据库

rm -rf /etc/my.cnf
vim /etc/my.cnf

主库my.cnf

[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
default-character-set = utf8mb4

[mysqld]
server-id = 1 # 任意自然数n，若部署主从同步时需要修改该id避免重复
port = 3306
datadir = /data
basedir = /usr/local/mysql
socket = /usr/local/mysql/data/mysql.sock
tmpdir = /usr/local/mysql/tmp
# MySQL启动用户
user = mysql
# 建议禁用符号链接以防止各种安全风险
symbolic-links = 0
explicit_defaults_for_timestamp = true
# 服务器的默认排序规则。
collation_server=utf8mb4_general_ci
# 默认的身份验证插件。(mysql_native_password,sha256_password,caching_sha2_password)
default-authentication-plugin=mysql_native_password
#mysql_native_password=ON
max_allowed_packet = 16M
# 允许的最大同时客户端连接数。（1-100000）
max_connections = 1000
max_user_connections = 1000 
# 在max_connect_errors之后，来自主机的连续连接请求在没有成功连接的情况下被中断，服务器将阻止该主机进行进一步的连接。
max_connect_errors = 1000000
transaction_isolation = READ-COMMITTED
thread-cache-size = 50
open-files-limit = 65535
# 表名不区分大小写（修改后需要重新初始化mysql）
lower_case_table_names=1
# 设置sql_mode（没有NO_AUTO_CREATE_USER了）
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
group_concat_max_len=102400
character-set-server = utf8mb4
wait_timeout=1800
tmp_table_size=1G
sort_buffer_size=1048576
innodb_buffer_pool_size = 22G 
innodb_buffer_pool_instances = 8            
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_print_all_deadlocks = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /usr/local/mysql/logs
gtid-mode=on
log-slave-updates=true
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
#rpl_semi_sync_master_enabled=1 
#rpl_semi_sync_master_timeout=1000 
#rpl_semi_sync_master_trace_level=32 
#rpl_semi_sync_master_wait_no_slave=on
#auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n
#auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL
#binlog-ignore=mysql #忽略mysql库
#binlog-ignore=information_schema #忽略information_schema库
#replicate-do-db=test #要同步的数据库，默认所有库
log-bin = /usr/local/mysql/binlog/mysql-bin.log 
expire_logs_days = 7
binlog_cache_size = 2097152
max-binlog-size=1G
log_bin_trust_function_creators = 1
sync-binlog = 1
binlog_format=row
binlog_row_image = FULL
log_slave_updates=1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
relay_log = /usr/local/mysql/binlog/mysql-relay.log
log-error = /usr/local/mysql/logs/mysqld.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /usr/local/mysql/logs/mysql-slow.log   
long_query_time = 3

[mysqld_safe]
pid-file = /usr/local/mysql/data/mysqld.pid

从库my.cnf

[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
default-character-set = utf8mb4
[mysqld]
port = 3306
datadir = /data
basedir = /usr/local/mysql
socket = /usr/local/mysql/data/mysql.sock
tmpdir = /usr/local/mysql/tmp
user = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0
explicit_defaults_for_timestamp = true
collation_server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_allowed_packet = 16M
max_connections = 1000
max_user_connections = 1000 
max_connect_errors = 1000000
transaction_isolation = READ-COMMITTED
thread-cache-size = 50
open-files-limit = 65535
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
group_concat_max_len=102400
character-set-server = utf8mb4
wait_timeout=1800
tmp_table_size=1G
sort_buffer_size=1048576
innodb_buffer_pool_size = 22G 
innodb_buffer_pool_instances = 8            
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_print_all_deadlocks = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /usr/local/mysql/logs/
gtid-mode=on
log-slave-updates=true
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
#rpl_semi_sync_slave_enabled=1
#auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n
#auto_increment_offset=2 #起始值。一般填第n台主MySQL。此时为第一台主MySQL
#binlog-ignore=mysql #忽略mysql库
#binlog-ignore=information_schema #忽略information_schema库
#replicate-do-db=test #要同步的数据库，默认所有库
log-bin = /usr/local/mysql/binlog/mysql-bin.log 
expire_logs_days = 7
binlog_cache_size = 2097152
max-binlog-size=1G
log_bin_trust_function_creators = 1
sync-binlog = 1
binlog_format=row
binlog_row_image = FULL
server-id = 2
log_slave_updates=1
# 从节点
#read_only=on #只读
#super_read_only=on
master_info_repository = TABLE
relay_log_info_repository = TABLE

relay_log_recovery = 1
relay_log = /usr/local/mysql/binlog/mysql-relay.log
log-error = /usr/local/mysql/logs/mysqld.log                 
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /usr/local/mysql/logs/mysql-slow.log   
long_query_time = 3

[mysqld_safe]
pid-file = /usr/local/mysql/data/mysqld.pid

初始化配置

chmod 644 /etc/my.cnf
chown -R mysql:mysql /etc/my.cnf

chmod 755 /data
chown -R mysql:mysql /data

# 如果要重新初始化则需要删掉datadir下的所有文件 
rm -rf /data/*
rm -rf /usr/local/mysql/data/*
rm -rf /usr/local/mysql/logs/*

touch /usr/local/mysql/logs/mysqld.log
touch /usr/local/mysql/logs/mysqld_safe.log
chown -R mysql:mysql  /usr/local/mysql/logs/

# 初始化配置
mysqld  --defaults-file=/etc/my.cnf --initialize

# 映射mysql服务
ln -s /usr/local/mysql/bin/mysql /usr/bin

开启mysql服务

# mysqld_safe方式启动
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql  > /usr/local/mysql/logs/mysqld_safe.log 2>&1 &

查看默认账号和密码：

cat /usr/local/mysql/logs/mysqld.log | grep "A temporary password"

通过本地localhost登录

mysql -hlocalhost -uroot -p

修改密码

-- 先改密码之后才能查询mysql.user
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql@123456';
flush privileges;

select host , user , plugin ,authentication_string from mysql.user;

quit;

放行防火墙

#centos/oracle
firewall-cmd --add-port=3306/tcp --permanent --zone=public
#重启防火墙(修改配置后要重启防火墙)
firewall-cmd --reload

# ubuntu/debian
ufw allow 3306/tcp
ufw allow 22/tcp
ufw reload

设置自动启动

vim /etc/systemd/system/mysql.service

修改为我们自定义的配置

[Unit]
Description=MySQL Server
After=network.service

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --log-error=/usr/local/mysql/logs/mysqld_safe.log
LimitNOFILE=5000
Restart=on-failure
RestartSec=10
RestartPreventExitStatus=1
PrivateTmp=false

# 重载系统服务
systemctl daemon-reload
# 实现开机自启 
systemctl enable mysql

关闭SELINUX：

vim /etc/selinux/config
#SELINUX=enforcing 修改为  SELINUX=disabled

重启服务器验证

reboot

#重新连接服务器，查看mysql 服务的状态：
systemctl status mysql
#查看端口 
netstat -tnap
#查看mysql启动情况
ps aux|grep mysql

创建主从同步进程（需要修改对应IP地址）

通过本地localhost登录

mysql -hlocalhost -uroot -p

主库、从库操作：

CREATE USER 'rep_user'@'192.168.192.172' IDENTIFIED WITH mysql_native_password BY 'repluser@123456';
CREATE USER 'rep_user'@'192.168.192.171' IDENTIFIED WITH mysql_native_password BY 'repluser@123456';

GRANT REPLICATION SLAVE,replication client  ON *.* TO 'rep_user'@'192.168.192.172' ;
GRANT REPLICATION SLAVE,replication client  ON *.* TO 'rep_user'@'192.168.192.171' ;

install plugin  rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1;

查看生成的binlog日志，记录下来日志名字和起始位置

查询主库、从库

show master status\G

主库输出：

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 1319
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 6ac2190e-a271-11ef-8ec6-bc2411799364:1-6
1 row in set (0.00 sec)

从库输出：

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 1319
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 6c6540e1-a271-11ef-985e-bc241191a340:1-6
1 row in set (0.00 sec)

主库使用从库的日志起点

CHANGE MASTER TO MASTER_HOST='192.168.192.172', MASTER_USER='rep_user',MASTER_PASSWORD='repluser@123456',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1319;

从库使用主库的日志起点

CHANGE MASTER TO MASTER_HOST='192.168.192.171',MASTER_USER='rep_user',MASTER_PASSWORD='repluser@123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1319;

启动并查询同步状态

start slave;    #启动
show slave status\G     #查看

主库操作-开启远程访问

select host , user , plugin ,authentication_string from mysql.user;

update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
flush privileges;

quit;

部署keepalived做高可用配置

Keepalived是Linux下一个轻量级的高可用解决方案。 官网地址：https://www.keepalived.org

在线安装

#centos/oracle
yum install keepalived -y

# ubuntu/debian
apt-get install keepalived -y
# apt-get remove keepalived -y

源码编译

1、官网下载源码包，解压

wget https://www.keepalived.org/software/keepalived-2.3.1.tar.gz
tar xvf keepalived-2.3.1.tar.gz

#centos/oracle
yum install -y gcc gcc-c++ wget popt-devel openssl openssl-devel
yum install -y libnl3 libnl3-devel

# ubuntu/debian
sudo apt-get install make -y
sudo apt-get install wget -y
sudo apt-get install gcc -y
sudo apt-get install g++ -y
sudo apt-get install libssl-dev -y
sudo apt-get install openssl -y
sudo apt-get install libpopt-dev -y
sudo apt-get install libnl-3-dev -y
sudo apt-get install libnl-genl-3-dev -y

2、编译安装，这里需要指定一下安装路径

cd keepalived-2.3.1/
sudo ./configure --prefix=/usr/local/keepalived
sudo make && make install

配置文件位置：/etc/keepalived 启动位置：/usr/local/keepalived/sbin/keepalived keepalived 日志: tail -22f /var/log/messages | grep Keepalived

3、拷贝配置文件和启动脚本到相应的目录(debian/ubuntu系列忽略)，并设置开机启动

centos/oracle

mkdir /etc/keepalived
#cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
chkconfig keepalived on

ubuntu/debian

cat <<EOF | sudo tee /etc/systemd/system/keepalived.service
[Unit]
Description=Keepalived
After=network.target

[Service]
ExecStart=/usr/local/keepalived/sbin/keepalived -D
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
Type=forking

[Install]
WantedBy=multi-user.target
EOF

# 重载系统服务
systemctl daemon-reload
systemctl enable keepalived

MySQL状态监测脚本

mkdir /etc/keepalived

#check_mysql.sh:
cat>/etc/keepalived/check_mysql.sh<<EOF
#!/bin/bash
service_name="mysqld"
get_pid=`pidof $service_name`

if [ "${get_pid}" == "" ]; then
    echo "mysql_status=1"
    exit 0
else
    systemctl stop keepalived.service
fi
EOF
chmod +x /etc/keepalived/check_mysql.sh

#shutdown.sh:
cat>/etc/keepalived/shutdown.sh<<EOF
systemctl stop keepalived.service
EOF
chmod +x /etc/keepalived/shutdown.sh

配置文件

修改主库的keepalived配置文件

rm -rf /etc/keepalived/keepalived.conf
vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
    router_id HA_MySQL #标识，双主相同
    script_user root
    enable_script_security 
}
# 定义自动核对mysql的脚本
vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql.sh" 
    interval 22
    weight 2   
} 
vrrp_instance VI_1 {
    state BACKUP #注意，主从两端都配置成了backup，因为使用了nopreempt，即非抢占模式
    interface ens18 #指定HA监测网络的接口
    virtual_router_id 55 #虚拟路由标识，这个标识是一个数字(取值在0-255之间,用来区分多个instance的VRRP组播)，同一个vrrp
    priority 100 #优先级，用来选举master的，要成为master，该项取值范围是1-255（在此范围之外会被识别成默认值100）,此处master2上设置为50
    advert_int 1 #发VRRP包的时间间隔，即多久进行一次master选举（可以认为是健康查检时间间隔）
    nopreempt #不主动抢占资源，设置非抢占模式，即允许一个priority比较低的节点作为master，即使有priority更高的节点启动
    authentication {
        #认证区域，认证类型有PASS和HA（IPSEC），推荐使用PASS（密码只识别前8位）
        auth_type PASS
        auth_pass 721000
    }
    virtual_ipaddress {
        # 指定vip地址
        192.168.192.170
    }
    track_script {
        # 指定核对的脚本，check_mysql是上述自定义的
        check_mysql
    }
}
virtual_server 192.168.192.170 3306 {
    delay_loop 2 #设置运行情况检查时间，单位是秒
    lb_algo wrr #设置后端调度算法，带有权重的轮询
    lb_kind DR #设置LVS实现负载均衡的机制，有NAT、TUN、DR三个模式可选
    persistence_timeout 60 #会话保持时间，单位是秒。这个选项对动态网页是非常有用的，为集群系统中的session共享提供了一个很好的解决方案。有了这个会话保持功能，用户的请求会被一直分发到某个服务节点，直到超过这个会话的保持时间。
    protocol TCP # 指定转发协议类型，有TCP和UDP两种

    real_server 192.168.192.171 3306 {
        weight 3 #配置服务节点的权值，权值大小用数字表示，数字越大，权值越高，设置权值大小为了区分不同性能的服务器
        notify_down /etc/keepalived/shutdown.sh #当mysq服务down时，执行此脚本，杀死keepalived实现切换, 自杀脚本.
        TCP_CHECK {
            connect_timeout 10 #连接超时时间
            nb_get_retry 3 #重连次数
            delay_before_retry 3 #重连间隔时间
            connect_port 3306 #健康检查端口
        }
    }
}

启动keepalived服务

systemctl start keepalived

修改从库的keepalived配置文件

rm -rf /etc/keepalived/keepalived.conf
vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
    router_id HA_MySQL #表示运行keepalived服务器的一个标识
    script_user root
    enable_script_security
}
# 定义自动核对mysql的脚本
vrrp_script check_mysql {
    script "/etc/keepalived/check_mysql.sh" 
    interval 22
    weight 2   
} 
vrrp_instance VI_1 {
    state BACKUP #指定keepalived的角色, 两台配置此处均是BACKUP,设为BACKUP将根据优先级决定主或从
    interface ens18 #指定HA监测网络的接口
    virtual_router_id 55 #虚拟路由标识，这个标识是一个数字(取值在0-255之间,用来区分多个instance的VRRP组播)，同一个vrrp实例使用唯一的标识,确保和master2相同，同网内不同集群此项必须不同,否则发生冲突。
    priority 90 #用来选举master的，要成为master，该项取值范围是1-255（在此范围之外会被识别成默认值100）,此处master2上设置为50
    advert_int 1 #发VRRP包的时间间隔，即多久进行一次master选举（可以认为是健康查检时间间隔）
    nopreempt #不抢占，即允许一个priority比较低的节点作为master，即使有priority更高的节点启动
    authentication {
        #认证区域，认证类型有PASS和HA（IPSEC），推荐使用PASS（密码只识别前8位）
        auth_type PASS
        auth_pass 721000
    }
    virtual_ipaddress { #虚拟IP (VIP)
        # 指定vip地址
        192.168.192.170
    }
    track_script {
        # 指定核对的脚本，check_mysql是上述自定义的
        check_mysql
    }
}
virtual_server 192.168.192.170 3306 {
     delay_loop 2 #设置运行情况检查时间，单位是秒
     lb_algo wrr #设置后端调度算法，带有权重的轮询
     lb_kind DR #设置LVS实现负载均衡的机制，有NAT、TUN、DR三个模式可选
     persistence_timeout 60 #会话保持时间，单位是秒。这个选项对动态网页是非常有用的，为集群系统中的session共享提供了一个很好的解决方案。有了这个会话保持功能，用户的请求会被一直分发到某个服务节点，直到超过这个会话的保持时间。
     protocol TCP #指定转发协议类型，有TCP和UDP两种
     real_server 192.168.192.172 3306 {
         weight 3 #配置服务节点的权值，权值大小用数字表示，数字越大，权值越高，设置权值大小为了区分不同性能的服务器
         notify_down /etc/keepalived/shutdown.sh #检测到mysql服务down后执行的脚本，在一些版本中叫notify_down
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             nb_get_retry 3 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口，配置自己mysql服务端口
         }
     }
}

启动keepalived服务

systemctl start keepalived

keepalived服务控制

systemctl enable keepalived.service #设置开机自动启动

systemctl disable keepalived.service     #取消开机自动启动

systemctl start keepalived.service       #启动服务

systemctl restart keepalived.service     #重启服务

systemctl stop keepalived.service         #停止服务

systemctl status keepalived.service      #查看服务状态

通过 ip a 命令查看虚拟IP绑定在哪台服务器（以VIP 192.168.11.200为例，keepalived运行在192.168.11.13）

在从库上将数据库设置为只读模式,在没有故障的情况下暂时只开放只读（可用脚本实现故障转移）

将MySQL设置为只读状态的命令：

show global variables like "%read_only%";
flush tables with read lock;
set global read_only = 1;
set global super_read_only = 1;
show global variables like "%read_only%";

将MySQL从只读设置为读写状态的命令：

unlock tables;
set global read_only = 0;
set global super_read_only = 0;

或者修改配置，在[mysqld]下添加后重启服务：

read_only=on #只读
super_read_only=on

异常问题解决

通过查看 systemctl status keepalived.service 状态，如果是这个异常则需要重新启动ip_vs

9月 24 15:53:20 oraclelinux-mysql-node2 Keepalived_healthcheckers[36569]: IPVS: Can't initialize ipvs: No such file or directory

手动加载一下：

modprobe ip_vs
modprobe ip_vs_wrr
systemctl restart keepalived.service     #重启服务

x86_64下libaio.so.1t64文件缺失

#1、安装依赖包
apt install libaio1t64 libnuma1 -y

#2、创建软连接
ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1

libprotobuf-lite.so

数据库备份方案

为了确保 MySQL 数据库的数据安全，建议实施定期备份策略。您可以参考专门的 MySQL 定时备份方案（集成 MinIO） 文档，该方案详细介绍了如何将 MySQL 数据库的定时备份集成到 MinIO 对象存储中，实现数据的远程备份和高可用性保护。