一、升级流程
1、下载最新版MySQL 5.7.43
2、上传到指定目录
3、备份全库数据与配置文件
4、解压缩数据库软件到指定路径
5、关闭备库
6、使用新版软件启动备库
7、登录备库，发现版本已升级，使用mysql_upgrade命令升级系统数据库
8、无报错的话，重启备库。
9、主库按照备库流程走一遍，需设置只读模式
10、最后确认主备库是否一致
二、操作
1. 从库升级
# 1. 下载MySQL 5.7.43 rpm 包
看升级流程中连接自行下载
# 2. 将下载好的包上传至可执行目录如：/root/mysql5743-rpm
总用量 229828
-rw-r--r-- 1 root root 193030448 9月 1 15:03 mysql-community-server-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 320780 9月 1 15:04 mysql-community-common-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1266220 9月 1 15:04 mysql-community-libs-compat-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 3091092 9月 1 15:04 mysql-community-libs-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 32652444 9月 1 15:04 mysql-community-client-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 4969828 9月 1 15:10 mysql-community-devel-5.7.43-1.el7.x86_64.rpm
[root@test5 mysql-rpm]# pwd
/root/mysql-rpm
# 3. 备份
## 3.1 查看状态
登入从库，查看主从同步是否有延迟：
主要查看：
Slave_IO_Running: 这个字段显示从库的IO线程是否正在运行。如果值为"YES"，表示IO线程正常运行。
Slave_SQL_Running: 这个字段显示从库的SQL线程是否正在运行。如果值为"YES"，表示SQL线程正常运行。
Seconds_Behind_Master: 这个字段显示从库相对于主库的延迟时间，单位为秒。如果值为0或接近0，表示从库已经追上了主库，复制是同步的。
mysql -uroot -p
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.102.13
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 891
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1057
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..
Seconds_Behind_Master: 0
...
## 3.2 停止从库复制进程
停从库流量及io_thread：
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
待slave sql_thread完全应用后全部落盘
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
停从库同步进程
STOP SLAVE;
show slave status\G;
## 3.3 创建备份
使用MySQL的物理备份工具（如mysqldump、Percona XtraBackup等）来创建从库的备份。
mysqldump -uroot -p --single-transaction --master-data=2 --all-databases > slave_backup.sql
## 3.4停止从库服务
systemctl stop mysqld
## 3.5 保险起见也同步一份数据目录和配置
tar -zcvf slave_data_bakup.tar.gz /data01/mysql/mysql/data/*
cp /etc/my.cnf /root/my.cnf_slavebak
# 4 升级MySQL
cd /root/mysql5743-rpm
ls -lrt
总用量 431308
-rw-r--r-- 1 root root 193030448 9月 1 15:03 mysql-community-server-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 320780 9月 1 15:04 mysql-community-common-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1266220 9月 1 15:04 mysql-community-libs-compat-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 3091092 9月 1 15:04 mysql-community-libs-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 32652444 9月 1 15:04 mysql-community-client-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 4969828 9月 1 15:10 mysql-community-devel-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 48586584 9月 4 15:38 mysql-community-embedded-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 23314208 9月 4 15:38 mysql-community-embedded-compat-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 134413980 9月 4 15:40 mysql-community-embedded-devel-5.7.43-1.el7.x86_64.rpm
yum update ./mysql-community-*
# 5.启动从库服务
查看配置和数据目录是否正确
cat /etc/my.cnf
ls -lrt /data01/mysql/mysql/data/
systemctl status mysqld
systemctl start mysqld
# 6. 运行 mysql_upgrade 升级数据字典
1.出现Upgrade process completed successfully字样表示成功
2.如果中间出现错误打断，需要再次运行
[root@test5 mysql5743-rpm]# mysql_upgrade -uroot -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
...
...
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config OK
test.test OK
Upgrade process completed successfully.
Checking if update is needed.
# 7. 检查从库同步状态
mysql -uroot -p
查看版本信息
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.43-log |
+------------+
1 row in set (0.00 sec)
同步状态
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.102.13
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 73275
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 29208
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 73275
Relay_Log_Space: 29415
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
...
1 row in set (0.00 sec)
ERROR:
No query specified
## 6.1 查询某个表看是否正常
select * from test.test;
2. 主库升级
# 1. 停止写入操作
在开始升级主库之前，需要停止主库的写入操作，以确保在升级过程中不会有数据更新。可以使用以下命令将主库设置为只读模式
SET GLOBAL READ_ONLY = ON;
# 2.等待数据同步：
确保在停止写入操作后，从库已经与主库完成数据同步，数据一致性得到保证。
# 3.备份主库数据
在升级主库之前，务必备份主库的数据，以防止升级过程中数据的丢失或损坏。
mysqldump -uroot -p --single-transaction --all-databases > master_backup.sql
## 3.1 保险起见也同步一份数据目录和配置
tar -zcvf master_data_bakup.tar.gz /data01/mysql/mysql/data/*
cp /etc/my.cnf /root/my.cnf_masterbak
# 4.停止主库服务
systemctl stop mysqld
# 5.升级主库
cd /root/mysql5743
[root@test4 mysql5743]# ls -lrt
总用量 431308
-rw-r--r-- 1 root root 134413980 9月 4 16:19 mysql-community-embedded-devel-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 48586584 9月 4 16:19 mysql-community-embedded-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 23314208 9月 4 16:19 mysql-community-embedded-compat-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 4969828 9月 4 16:19 mysql-community-devel-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 32652444 9月 4 16:19 mysql-community-client-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 3091092 9月 4 16:19 mysql-community-libs-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1266220 9月 4 16:19 mysql-community-libs-compat-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 320780 9月 4 16:19 mysql-community-common-5.7.43-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 193030448 9月 4 16:19 mysql-community-server-5.7.43-1.el7.x86_64.rpm
[root@test4 mysql5743]# yum update ./mysql-community-*
查看配置和数据目录是否正确
cat /etc/my.cnf
ls -lrt /data01/mysql/mysql/data/
systemctl status mysqld
systemctl start mysqld
# 6. 运行 mysql_upgrade 升级数据字典
1.出现Upgrade process completed successfully字样表示成功
2.如果中间出现错误打断，需要再次运行
[root@test5 mysql5743-rpm]# mysql_upgrade -uroot -p
[root@test4 mysql5743]# mysql_upgrade -uroot -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
...
...
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config OK
test.test OK
Upgrade process completed successfully.
Checking if update is needed.
# 登入MySQL
mysql -uroot -p
查看版本信息
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.43-log |
+------------+
1 row in set (0.00 sec)
# 开启主库可写模式：
SET GLOBAL READ_ONLY = OFF;
# 去备库查看同步状态是否正常同步
show slave status\G;
