mysql5.7小版本之间升级(单机上操作)

环境:
OS:Centos 7
当前版本:5.7.29
升级后版本:5.7.39

说明:
该升级方式才在当前机器上安装一个新的mysql实例进行升级.

1.查看当前的版本
/home/middle/mysql57/bin/mysql -h localhost -uroot -P13306 -p
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)

2.在同机器上安装新版本的mysql实例
具体安装步骤可以参考如下链接
https://www.cnblogs.com/hxlasky/p/14633613.html
注意同一台机器上安装的新实例端口不能与原实例有冲突,可以等升级完成后停掉旧实例,将新实例的端口修改成旧实例的,这样应用程序的链接就不需要修改.

3.查看下新的版本
/home/middle/mysql57_new/bin/mysql -h localhost -uroot -P23306 -p -S /home/middle/mysql57_new/data/mysql.sock
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)

4.停掉新实例并删除数据目录
该步骤的目的是我们需要将旧实例的data目录拷贝到新实例的目录下
/home/middle/mysql57_new/bin/mysqladmin -h localhost -uroot -P23306 -p shutdown -S /home/middle/mysql57_new/data/mysql.sock

[root@localhost middle]# cd /home/middle/mysql57_new
[root@localhost mysql57_new]# mv data bakdata
[root@localhost mysql57_new]# mkdir data

5.停掉旧实例数据库将数据目录拷贝到新实例的数据目录
[root@localhost mysql57]#/home/middle/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p shutdown
[root@localhost mysql57]# cd /home/middle/mysql57
cp -r data /home/middle/mysql57_new/

6.修改新实例的目录权限
[root@localhost mysql5730]# cd /home/middle/mysql57_new/
[root@localhost mysql5730]# chown -R mysql:mysql ./data

7.启动新版本数据库
/home/middle/mysql57_new/bin/mysqld_safe --defaults-file=/home/middle/mysql57_new/conf/my.cnf --user=mysql &

8.登陆查看当前的版本:
/home/middle/mysql57_new/bin/mysql -h localhost -uroot -P23306 -p -S /home/middle/mysql57_new/data/mysql.sock
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)

8.升级处理
[root@localhost mysql57_new]# /home/middle/mysql57_new/bin/mysql_upgrade -s -h localhost -pyeemiao3040 -P23307 -S /home/middle/mysql57_new/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.2).
Upgrade process completed successfully.
Checking if update is needed.
[root@localhost mysql57_new]#


9.停掉新实例修改端口和数据文件目录
/home/middle/mysql57_new/bin/mysqladmin -h localhost -uroot -P23306 -p -S /home/middle/mysql57_new/data/mysql.sock shutdown

[root@localhost middle]# mv mysql57 del_mysql57
[root@localhost middle]# mv mysql57_new mysql57

9.1修改配置文件

9.2修改binlog.index
[root@localhost binlog]# more binlog.index
/home/middle/mysql57_new/mysqllog/binlog/binlog.000001
/home/middle/mysql57_new/mysqllog/binlog/binlog.000002
/home/middle/mysql57_new/mysqllog/binlog/binlog.000003


10.启动
/home/middle/mysql57/bin/mysqld_safe --defaults-file=/home/middle/mysql57/conf/my.cnf --user=mysql &

登录
/home/middle/mysql57/bin/mysql -h localhost -uroot -P13306 -p

11.有canal的需要重新启动
否则会出现很多的告警

2023-08-19T11:50:14.654008Z 82 [Note] Aborted connection 82 to db: 'unconnected' user: 'canal' host: '192.168.1.65' (Got an error reading communication packets)
2023-08-19T11:50:14.664688Z 81 [Note] Aborted connection 81 to db: 'unconnected' user: 'canal' host: '192.168.1.65' (Got an error reading communication packets)
2023-08-19T11:50:14.666890Z 84 [Note] Aborted connection 84 to db: 'unconnected' user: 'canal' host: '192.168.1.65' (Got an error reading communication packets)
2023-08-19T11:50:25.493968Z 86 [Note] Aborted connection 86 to db: 'unconnected' user: 'canal' host: '192.168.1.65' (Got an error reading communication packets)

11.安装审计(原实例安装了审计,新实例也需要安装)
mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
ERROR 1030 (HY000): Got error 1 from storage engine
mysql> exit

/home/middle/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p shutdown
/home/middle/mysql57/bin/mysqld_safe --defaults-file=/home/middle/mysql57/conf/my.cnf --user=mysql &

重启后发现会自动安装
2023-08-19T11:57:38.897550Z 0 [Note] Plugin 'FEDERATED' is disabled.
230819 19:57:38 server_audit: MariaDB Audit Plugin version 1.4.14 STARTED.

mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'server_audit%';
mysql> show plugins;