运维文档

# 主主互备单写

操作系统：almalinux 9.2 热备软件：keepalived 2.2.4 数据库：mysql 8.0.35

# 系统架构

1740102016784.png

# 系统调整

systemctl stop firewalld; systemctl disable firewalld

# 安装和配置mysql

# 安装mysql

bash multi-install-mysql.sh depend
dnf install -y iproute perl perl-Data-Dumper libaio util-linux tar gzip numactl libatomic libxcrypt-compat
cd /usr/lib64 && ln -s libncurses.so.6.2 libncurses.so.5 && ln -s libtinfo.so.6.2 libtinfo.so.5
bash multi-install-mysql.sh install

# 安装提示信息

安装信息
安装路径: /apps/mysql
当前版本: 8.0.35
root密码: oHATfvyR5H
远程登录: 已配置 root 账号可以从任意主机上登录
登录认证: 已配置 root 账号认证为 caching_sha2_password
连接命令: /apps/mysql/bin/mysql -h192.168.100.61 -P3306 -uroot -poHATfvyR5H
修改密码: alter user 'root'@'%' identified with caching_sha2_password by 'i4Seeyon'; flush privileges;

安装信息
安装路径: /apps/mysql
当前版本: 8.0.35
root密码: 6fhCVlQh2h
远程登录: 已配置 root 账号可以从任意主机上登录
登录认证: 已配置 root 账号认证为 caching_sha2_password
连接命令: /apps/mysql/bin/mysql -h192.168.100.62 -P3306 -uroot -p6fhCVlQh2h
修改密码: alter user 'root'@'%' identified with caching_sha2_password by 'i4Seeyon'; flush privileges;

# 修改root密码及认证方式

# mysql-1
/apps/mysql/bin/mysql -h192.168.100.61 -P3306 -uroot -poHATfvyR5H
# mysql-2
/apps/mysql/bin/mysql -h192.168.100.62 -P3306 -uroot -p6fhCVlQh2h

-- mysql-1 和 mysql-2
alter user 'root'@'%' identified with caching_sha2_password by 'i4Seeyon'; flush privileges;

# 配置主从同步

# 创建复制用户

在两个 mysql 主从节点上，分别创建可从对方 IP 进行登录的 replica 用户。

# 登录数据库
mysql -h$(ip addr | grep -Po '(?<=inet ).*(?=\/)' | grep -v '127.0.0' | head -1) -P3306 -uroot -pi4Seeyon

-- mysql-1
use mysql;
create user if not exists backup@'192.168.100.62' identified with caching_sha2_password by '110110';
grant replication slave on *.* to 'backup'@'192.168.100.62';
flush privileges;

-- mysql-2
use mysql;
create user if not exists backup@'192.168.100.61' identified with caching_sha2_password by '110110';
grant replication slave on *.* to 'backup'@'192.168.100.61';
flush privileges;

-- 删除用户
-- drop user 'backup'@'192.168.100.61';
-- drop user 'backup'@'192.168.100.62';

如果有必要，则需要修改相关帐号的身份登录认证方法。

-- 修改用户身份认证类型为 caching_sha2_password
alter user 'root'@'%' identified with caching_sha2_password by 'i4Seeyon'; flush privileges;
-- mysql-1
alter user 'backup'@'192.168.100.62' identified with caching_sha2_password by 'i4Seeyon'; flush privileges;
-- mysql-2
alter user 'backup'@'192.168.100.61' identified with caching_sha2_password by 'i4Seeyon'; flush privileges;

-- 查看数据库用户信息
select user, host, plugin from mysql.user;

# 调整配置文件

目标文件：/apps/mysql/my.cnf

# mysql-1
cat > /apps/mysql/my.cnf << 'EOF'
[client]
port=3306
default-character-set=utf8mb4
socket=/apps/mysql/data/mysql.sock

[mysql]
default-character-set=utf8mb4
socket=/apps/mysql/data/mysql.sock

[mysqld]
port=3306
character-set-server=utf8mb4
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
basedir=/apps/mysql
datadir=/apps/mysql/data
pid-file=/apps/mysql/data/mysqld.pid
user=mysql
slow_query_log=OFF
# slow-query-log-file=默认为主机名-slow.log
# long_query_time=10
# log_queries_not_using_indexes=OFF
# log_output=file,table
skip-name-resolve=off
lower_case_table_names=1
# expire_logs_days=60
socket=/apps/mysql/data/mysql.sock
default-storage-engine=InnoDB
# 字典对象缓存实时更新 此参数对性能有一定影响
# information_schema_stats_expiry=0
max_connections=600
# max_connect_errors=100
tmp_table_size=1024M
max_heap_table_size=1024M
join_buffer_size=1024M
thread_cache_size=32
# key_buffer_size 仅对 myisam 有效
key_buffer_size=256M
read_buffer_size=1M
read_rnd_buffer_size=128M
# default_authentication_plugin=mysql_native_password
default_authentication_plugin=caching_sha2_password
# sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
sql_mode="NO_ENGINE_SUBSTITUTION"
max_allowed_packet=1024M
# 从MySQL 8.0.30开始，innodb_redo_log_capacity 系统变量控制 Redo 日志文件占用的磁盘空间。
# 取代了innodb_log_files_in_group 变量和 innodb_log_file_size 变量，并已经被弃用。
# 如下表示 将Redo日志容量设置为8GB
innodb_redo_log_capacity = 8589934592
innodb_log_buffer_size=512M
innodb_buffer_pool_size=25804M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=1800
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
# innodb_lru_scan_depth=256
# innodb_io_capacity=
# innodb_io_capacity_max=
# innodb_buffer_pool_instances=CPU核数
# innodb_read_io_threads=64
# innodb_write_io_threads=64
# innodb_open_files=300
innodb_file_per_table=1
# innodb_file_format=Barracuda
innodb_page_size=16K
# innodb_thread_concurrency=并发线程数(官方推荐CPU核数的2倍)
group_concat_max_len=1024000
transaction-isolation=READ-COMMITTED
default-time-zone='+8:00'

# mysql 主从相关设置 - 主库
server-id=61
log_bin=mysql-1-mysql-bin
# 指定 mysql 的 binlog 日志记录哪个库 缺点: 过滤操作带来的负载都在 master 上 无法做基于时间点的复制(利用 binlog)
# binlog-do-db=oadb
# binlog_format 从 MySQL 8.0.34 开始被废弃
# binlog_format=row
max_binlog_size=512M
# 归档日志过期时间 单位秒 86400秒=1天 604800秒=7天 2592000秒=30天
binlog_expire_logs_seconds=86400
## 指定 slave 要复制哪个库 参数是在 slave 上配置 如果有多个库 需要多行列举
# replicate-do-db=oadb
# replicate_wild_do_table=copv8.%
# replicate_wild_do_table=nacos.%
# binlog-ignore-db=information_schema
# binlog-ignore-db=mysql
# binlog-ignore-db=performance_schema
# binlog-ignore-db=sys
# replicate_ignore_db=information_schema
# replicate_ignore_db=mysql
# replicate_ignore_db=performance_schema
# replicate_ignore_db=sys
auto-increment-offset=1
auto-increment-increment=2
## 8.0.26 之前的版本使用 log_slave_updates 为了兼容类似 8.0.11 的老版本在安装时能启动服务 所以注释了 如果需要请手动开启
# log_replica_updates=1
sync_binlog=1
## 8.0.26 之前的版本使用 slave_parallel_type | 8.0.29 废弃
# replica_parallel_type=LOGICAL_CLOCK
# replica_parallel_workers=4
# slave-skip-errors=all
# read-only=1
# gtid 相关设置
gtid_mode=ON
enforce_gtid_consistency=ON
# 为了兼容类似 8.0.11 的老版本在安装时能启动服务 所以注释了 如果需要请手动开启
# skip_replica_start=ON
relay-log=mysql-1-relay-bin
# max_relay_log_size=
# relay_log_space_limit=
# relay_log_purge=ON
# relay_log_recovery=ON

# 安全加固
local-infile=0
log_timestamps=system
# connect_timeout=60

# 国密支持 需满足前提条件 否则无需开启
# require_secure_transport=ON
# tls_ciphersuites=TLS_SM4_GCM_SM3:TLS_SM4_CCM_SM3
# tls_version=TLSv1.3

[mysqld_safe]
log-error=/apps/mysql/logs/error.log

[mysql.server]
basedir=/apps/mysql
EOF
# mysql-2
cat > /apps/mysql/my.cnf << 'EOF'
[client]
port=3306
default-character-set=utf8mb4
socket=/apps/mysql/data/mysql.sock

[mysql]
default-character-set=utf8mb4
socket=/apps/mysql/data/mysql.sock

[mysqld]
port=3306
character-set-server=utf8mb4
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true
basedir=/apps/mysql
datadir=/apps/mysql/data
pid-file=/apps/mysql/data/mysqld.pid
user=mysql
slow_query_log=OFF
# slow-query-log-file=默认为主机名-slow.log
# long_query_time=10
# log_queries_not_using_indexes=OFF
# log_output=file,table
skip-name-resolve=off
lower_case_table_names=1
# expire_logs_days=60
socket=/apps/mysql/data/mysql.sock
default-storage-engine=InnoDB
# 字典对象缓存实时更新 此参数对性能有一定影响
# information_schema_stats_expiry=0
max_connections=600
# max_connect_errors=100
tmp_table_size=1024M
max_heap_table_size=1024M
join_buffer_size=1024M
thread_cache_size=32
# key_buffer_size 仅对 myisam 有效
key_buffer_size=256M
read_buffer_size=1M
read_rnd_buffer_size=128M
# default_authentication_plugin=mysql_native_password
default_authentication_plugin=caching_sha2_password
# sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
sql_mode="NO_ENGINE_SUBSTITUTION"
max_allowed_packet=1024M
# 从MySQL 8.0.30开始，innodb_redo_log_capacity 系统变量控制 Redo 日志文件占用的磁盘空间。
# 取代了innodb_log_files_in_group 变量和 innodb_log_file_size 变量，并已经被弃用。
# 如下表示 将Redo日志容量设置为8GB
innodb_redo_log_capacity = 8589934592
innodb_log_buffer_size=512M
innodb_buffer_pool_size=25804M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=1800
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
# innodb_lru_scan_depth=256
# innodb_io_capacity=
# innodb_io_capacity_max=
# innodb_buffer_pool_instances=CPU核数
# innodb_read_io_threads=64
# innodb_write_io_threads=64
# innodb_open_files=300
innodb_file_per_table=1
# innodb_file_format=Barracuda
innodb_page_size=16K
# innodb_thread_concurrency=并发线程数(官方推荐CPU核数的2倍)
group_concat_max_len=1024000
transaction-isolation=READ-COMMITTED
default-time-zone='+8:00'

# mysql 主从相关设置 - 主库
server-id=62
log_bin=mysql-2-mysql-bin
# 指定 mysql 的 binlog 日志记录哪个库 缺点: 过滤操作带来的负载都在 master 上 无法做基于时间点的复制(利用 binlog)
# binlog-do-db=oadb
# binlog_format 从 MySQL 8.0.34 开始被废弃
# binlog_format=row
max_binlog_size=512M
# 归档日志过期时间 单位秒 86400秒=1天 604800秒=7天 2592000秒=30天
binlog_expire_logs_seconds=86400
## 指定 slave 要复制哪个库 参数是在 slave 上配置 如果有多个库 需要多行列举
# replicate-do-db=oadb
# replicate_wild_do_table=copv8.%
# replicate_wild_do_table=nacos.%
# binlog-ignore-db=information_schema
# binlog-ignore-db=mysql
# binlog-ignore-db=performance_schema
# binlog-ignore-db=sys
# replicate_ignore_db=information_schema
# replicate_ignore_db=mysql
# replicate_ignore_db=performance_schema
# replicate_ignore_db=sys
auto-increment-offset=2
auto-increment-increment=2
## 8.0.26 之前的版本使用 log_slave_updates 为了兼容类似 8.0.11 的老版本在安装时能启动服务 所以注释了 如果需要请手动开启
# log_replica_updates=1
sync_binlog=1
## 8.0.26 之前的版本使用 slave_parallel_type | 8.0.29 废弃
# replica_parallel_type=LOGICAL_CLOCK
# replica_parallel_workers=4
# slave-skip-errors=all
# read-only=1
# gtid 相关设置
gtid_mode=ON
enforce_gtid_consistency=ON
# 为了兼容类似 8.0.11 的老版本在安装时能启动服务 所以注释了 如果需要请手动开启
# skip_replica_start=ON
relay-log=mysql-2-relay-bin
# max_relay_log_size=
# relay_log_space_limit=
# relay_log_purge=ON
# relay_log_recovery=ON

# 安全加固
local-infile=0
log_timestamps=system
# connect_timeout=60

# 国密支持 需满足前提条件 否则无需开启
# require_secure_transport=ON
# tls_ciphersuites=TLS_SM4_GCM_SM3:TLS_SM4_CCM_SM3
# tls_version=TLSv1.3

[mysqld_safe]
log-error=/apps/mysql/logs/error.log

[mysql.server]
basedir=/apps/mysql
EOF

# 启动主从同步

相关参数的含义如下：

  • get_master_public_key：用于指定从服务器获取主服务器的公共密钥。这个公共密钥用于在主从复制过程中进行身份验证和加密通信。

  • master_host='192.168.100.61'：指定主服务器的IP地址或主机名。

  • master_port=3306：指定主服务器的端口号，MySQL的默认端口是3306。

  • master_user='backup'：指定用于连接主服务器的用户名。

  • master_password='110110'：指定用于连接主服务器的密码。

  • master_auto_position=1：设置从服务器的复制位置为自动定位。这意味着从服务器将自动选择主服务器上的一个位置开始复制。

-- change master to get_master_public_key=1;

-- 设置和启动
-- 在 master-1 上：
# change master to master_host='192.168.100.62',master_port=3306,master_user='backup', master_password='110110', master_auto_position=1;
-- change master to master_host='192.168.100.62',master_user='backup',master_password='110110',get_master_public_key=1;
change master to master_host='192.168.100.62',master_port=3306,master_user='backup',master_password='110110',master_auto_position=1,get_master_public_key=1;
start replica;

-- 在 master-2 上：
# change master to master_host='192.168.100.61',master_port=3306,master_user='backup', master_password='110110', master_auto_position=1;
-- change master to master_host='192.168.100.61',master_user='backup',master_password='110110',get_master_public_key=1;
change master to master_host='192.168.100.61',master_port=3306,master_user='backup',master_password='110110',master_auto_position=1,get_master_public_key=1;
start replica;

# 检查主从状态

# mysql-1 和 mysql-2
mysql -h$(ip addr | grep -Po '(?<=inet ).*(?=\/)' | grep -v '127.0.0' | head -1) -P3306 -uroot -pi4Seeyon

show master status; show replica status\G
-- 当以下两个参数值皆为 yes 说明主从同步成功
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes

# 重置主从关系

当主从同步不成功时，可以考虑重置主从关系，重新配置主从。

-- 关闭复制
stop replica;

-- 从
stop replica;
reset replica all;
show replica status\G

-- 主
reset master;
show master status\G

# 服务管理命令

# 启动服务 - mysql
systemctl start mysqld
# 关闭服务 - mysql
systemctl stop mysqld
# 重启服务 - mysql
systemctl restart mysqld
# 服务状态 - mysql
systemctl status mysqld
# 开机自启 - mysql
systemctl enable mysqld

# 卸载mysql

bash multi-install-mysql.sh remove

# 验证主从同步

# 连接命令 - vip
mysql -h192.168.100.160 -P3306 -uroot -pi4Seeyon
# 连接命令 - 根据自动判断的当前主机 IP 地址进行连接
mysql -h$(ip addr | grep -Po '(?<=inet ).*(?=\/)' | grep -v '127.0.0' | head -1) -P3306 -uroot -pi4Seeyon

# 安装和配置keepalived

# 安装keepalived

# 安装 keepalived 软件 - 本环境采用系统自带版本，亦可自行编译安装最新版本
yum install -y keepalived
# 安装后先不启动服务 仅加入开机自启
systemctl enable keepalived

# 当前方案，两个节点都挂掉之后，必须手动重启 keepalived 服务。
# 可考虑在 /usr/lib/systemd/system/keepalived.service 的 [Service] 字段下添加以下参数 实现自动重启
# Restart=always

# 找到网卡名 这里是 ens33
ip addr show

# 配置主备模式

目标文件：/etc/keepalived/keepalived.conf

为了避免脑裂现象，采用非抢占模式。配置关键点：

  • 两个节点的 state 都必须是 BACKUP

  • 两个节点的都必须加上参数 nopreempt

  • 其中一个节点的优先级必须高于另外一个节点的优先级

# mysql-1

# mysql-1
cat > /etc/keepalived/keepalived.conf << 'EOF'
global_defs {
    router_id mysql
}

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

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    mcast_src_ip 192.168.100.61
    virtual_router_id 50
    priority 100
    nopreempt
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass myKeepalived
    }
    virtual_ipaddress {
        # 虚拟 IP 地址
        192.168.100.60
    }
    track_script {
        check_mysql
    }
}
EOF

# mysql-2

cat > /etc/keepalived/keepalived.conf << 'EOF'
global_defs {
    router_id mysql
}

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

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    mcast_src_ip 192.168.100.62
    virtual_router_id 50
    priority 90
    nopreempt
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass myKeepalived
    }
    virtual_ipaddress {
        192.168.100.60
    }
    track_script {
        check_mysql
    }
}
EOF

# 创建服务检查脚本

目标文件：/etc/keepalived/check_mysql.sh

# mysql-1 和 mysql-2 两个节点都执行
yum install -y nc
cat > /etc/keepalived/check_mysql.sh << 'EOF'
#!/usr/bin/env bash
# This script checks if MySQL is running

# Assuming MySQL is listening on localhost:3306
if nc -z localhost 3306; then
    echo "MySQL is running"
else
    echo "MySQL is not running"
    exit 1
fi
EOF

chmod +x /etc/keepalived/check_mysql.sh

# 卸载keepalived

yum remove -y keepalived

# 服务管理

# 启动服务 - keepalived
systemctl start keepalived
# 关闭服务 - keepalived
systemctl stop keepalived
# 重启服务 - keepalived
systemctl restart keepalived
# 服务状态 - keepalived
systemctl status keepalived
# 开机自启 - mysql
systemctl enable keepalived

# 测试验证

随意关闭任一节点的服务都能正常连接则说明部署成功。

# 连接命令 - vip
mysql -h192.168.100.60 -P3306 -uroot -pi4Seeyonshow global status like 'max_used_connections';
show variables like '%max_connections%';
show status like 'threads_connected';
select * from information_schema.processlist;
show processlist;

mysql不同版本参数对比

https://mysql-params.tmtms.net/mysqld
编撰人：yangfc

快速跳转

← 中电科金仓数据库 →