MySQL 升级

由 MySQL 8.0 离线升级到 8.4，身份验证插件发生了变化，用户密码需要重置。

1. 备份及恢复

mkdir /var/bakup -p

# 使用mysqldump命令备份全局数据：
mysqldump -u root -p --all-databases --single-transaction --triggers --routines --events > /var/bakup/bak20240510.sql

# 恢复全局数据备份：
mysql < /var/bakup/bak20240507.sql

2. 文件准备

• MySQL 升级到 8.4 官方文档
• MySQL 官方下载中心
• MySQL 安装升级命令 官方文档
• MySQL 身份验证插件说明 caching_sha2_password
• MySQL :: MySQL Community Downloads

yumdownloader --resolve --destdir /data/pkg lsof
yumdownloader --resolve --destdir /data/pkg vim

3. 服务启停

systemctl stop mysqld
systemctl start mysqld
systemctl status mysqld

# 检查服务是否被锁定
lsof -i -P | grep mysqld
systemctl kill --signal=SIGKILL mysqld
kill -9 `ps -ef | grep 'mysqld' | grep -v grep|awk '{print $2}'`

# 如果所有方法都无法停止服务，关闭开机启动，修改配置，然后重启服务器
vim /etc/my.cnf
# 添加配置：skip-grant-tables

systemctl disable mysqld
reboot
systemctl enable mysqld

systemctl stop mysqlrouter
systemctl start mysqlrouter
systemctl status mysqlrouter

4. 升级软件

rpm -Uvh mysql-community-libs-compat-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-libs-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-common-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-icu-data-files-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-client-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-client-plugins-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-embedded-compat-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-server-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-community-devel-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-shell-8.4.0-1.el7.x86_64.rpm --nodeps
rpm -Uvh mysql-router-community-8.4.0-1.el7.x86_64.rpm --nodeps

# 权限相关
setenforce 0
getenforce
sed -i 's/^SELINUX=enforcing$/SELINUX=disabled/' /etc/selinux/config

grep 'datadir' /etc/my.cnf | cut -d'=' -f 2 | xargs chown -R mysql:mysql
grep 'mysqld.pid' /etc/my.cnf
chown -R mysql:mysql /var/run/mysqld/
sed -ri 's/^(binlog_transaction_dependency_tracking.*)/#\1/g' /etc/my.cnf
sed -ri 's/^(profiling.*)/#\1/g' /etc/my.cnf

# 日志
cat /var/log/mysqld.log
grep password /var/log/mysqld.log

mysql -u root -p

5. 数据库操作命令

use mysql;
SET GLOBAL read_only = OFF;
FLUSH PRIVILEGES;
select user,plugin from user \G;
alter user 'root'@'%' identified with 'caching_sha2_password' by 'password123XXX!';
FLUSH PRIVILEGES;

select host,user,plugin,authentication_string from user where plugin='mysql_native_password' \G;
SHOW STATUS LIKE 'group_replication_primary_member';
show variables like 'server_uuid';
SELECT * FROM performance_schema.replication_group_members;

set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

# 更新配置文件
# 主节点上运行
mysqlrouter --bootstrap=192.120.10.65:3306 --user=mysqlrouter --account mysql_router3 --account-create always
mysqlrouter --bootstrap=192.120.10.64:3306 --user=mysqlrouter --account mysql_router2 --account-create always
mysqlrouter --bootstrap=192.120.10.63:3306 --user=mysqlrouter --account mysql_router1 --account-create always
# 各自节点上运行
mysqlrouter --bootstrap=192.120.10.64:3306 --user=mysqlrouter --account mysql_router2
mysqlrouter --bootstrap=192.120.10.63:3306 --user=mysqlrouter --account mysql_router1

6. mysqlsh 命令

mysqlsh -u root -p
\js
var cluster = dba.getCluster()
cluster.status()
cluster.rescan()
# 查询帮助信息
\? Promt

7. 注意
   服务恢复后，临时配置需要修改回去，比如注销 /etc/my.cnf 中的 skip-grant-tables；