【MySQL】mysql5.7.36 二进制版本升级到mysql5.7.43

1.软件准备 

现有版本:mysql-5.7.36-linux-glibc2.12-x86_64
新版本:
tar xvf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz
chown -R mysql:mysql mysql-5.7.43-linux-glibc2.12-x86_64
2.停止现有数据库 

/opt/mysql/bin/mysqladmin -u root -p'xxxx' shutdown
ps -ef|grep mysql

检查关闭日志:
2024-03-08T10:48:00.655909+08:00 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2024-03-08T10:48:00.655913+08:00 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2024-03-08T10:48:00.655918+08:00 0 [Note] Shutting down plugin 'INNODB_TRX'
2024-03-08T10:48:00.655922+08:00 0 [Note] Shutting down plugin 'InnoDB'
2024-03-08T10:48:00.656064+08:00 0 [Note] InnoDB: FTS optimize thread exiting.
2024-03-08T10:48:00.656175+08:00 0 [Note] InnoDB: Starting shutdown...
2024-03-08T10:48:00.756409+08:00 0 [Note] InnoDB: Dumping buffer pool(s) to /chac/mysql/mysql3000/data/ib_buffer_pool
2024-03-08T10:48:00.756674+08:00 0 [Note] InnoDB: Buffer pool(s) dump completed at 240308 10:48:00
2024-03-08T10:48:02.130934+08:00 0 [Note] InnoDB: Shutdown completed; log sequence number 2832185
2024-03-08T10:48:02.131284+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2024-03-08T10:48:02.131302+08:00 0 [Note] Shutting down plugin 'CSV'
2024-03-08T10:48:02.131315+08:00 0 [Note] Shutting down plugin 'MRG_MYISAM'
2024-03-08T10:48:02.131324+08:00 0 [Note] Shutting down plugin 'sha256_password'
2024-03-08T10:48:02.131328+08:00 0 [Note] Shutting down plugin 'mysql_native_password'
2024-03-08T10:48:02.131481+08:00 0 [Note] Shutting down plugin 'binlog'
2024-03-08T10:48:02.132401+08:00 0 [Note] /opt/mysql/bin/mysqld: Shutdown complete
正常关闭。

3. 修改软件目录和启动文件

unlink mysql   --删除之前的软链接。
 
drwxr-xr-x.  9 mysql mysql       129 11月 10 2021 mysql-5.7.36-linux-glibc2.12-x86_64
-rw-r--r--.  1 mysql mysql 666559924 11月  9 2021 mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
drwxr-x---   9 mysql mysql       129 3月   8 10:43 mysql-5.7.43-linux-glibc2.12-x86_64
-rw-r--r--   1 root  root  694611572 3月   8 10:22 mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz

添加新的数据库目录链接:
ln -sv mysql-5.7.43-linux-glibc2.12-x86_64 mysql
chown -R mysql:mysql mysql 

--可以看到如下软链接:
lrwxrwxrwx   1 mysql mysql        35 3月   8 10:51 mysql -> mysql-5.7.43-linux-glibc2.12-x86_64

4.启动数据库 

vi /etc/my.cnf 
#myisam_repair_threads=1

mysql5.7.36-->5.7.43 之后,myisam_repair_threads 参数已经被取消了。不需要这个参数了。
--不注释掉就会启动失败,并报错:
[ERROR] unknown variable 'myisam_repair_threads=1'
[ERROR] Aborting

--启动数据库。
 /opt/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

5.日志检查 

2024-03-08T10:57:08.589100+08:00 0 [Warning] unknown variable 'loose_rpl_semi_sync_slave_enabled=1'
2024-03-08T10:57:08.589124+08:00 0 [Warning] unknown variable 'loose_rpl_semi_sync_master_enabled=1'
2024-03-08T10:57:08.593896+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-03-08T10:57:08.593901+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-03-08T10:57:08.599414+08:00 0 [Warning] CA certificate ca.pem is self signed.
2024-03-08T10:57:08.613253+08:00 0 [Warning] Optional native table 'performance_schema'.'processlist' has the wrong structure or is missing.
发现三个问题:
(1)loose_rpl_semi_sync_slave_enabled/loose_rpl_semi_sync_master_enabled 这两个变量失效。
(2)TLS 由原先的v1.1版本变更为V1.2版本。
(3)'performance_schema'.'processlist' 表结构损坏:set @@global.show_compatibility_56=ON; 打开参数修复。

mysql已经升级但是:mysql_uprade还么有运行。

6.修复方法

(1)
vi /etc/my.cnf 
#loose_rpl_semi_sync_slave_enabled
#loose_rpl_semi_sync_master_enabled 

(2)执行升级。

mysql_upgrade  -P3300 -p'xxxx' --socket=/tmp/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
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.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         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
Upgrade process completed successfully.
Checking if update is needed.

(3)检查 
11:24:  [(none)]> select * from performance_schema.processlist; 
+----+------+-----------+------+---------+------+--------------+----------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE        | INFO                                         |
+----+------+-----------+------+---------+------+--------------+----------------------------------------------+
|  8 | root | localhost | NULL | Query   |    0 | Sending data | select * from performance_schema.processlist |
+----+------+-----------+------+---------+------+--------------+----------------------------------------------+
1 row in set (0.00 sec)
(4)重启 
2024-03-08T11:27:08.392827+08:00 0 [Note] Plugin 'FEDERATED' is disabled.
2024-03-08T11:27:08.402159+08:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2024-03-08T11:27:08.402185+08:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2024-03-08T11:27:08.402192+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-03-08T11:27:08.402203+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-03-08T11:27:08.405484+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 240308 11:27:08
2024-03-08T11:27:08.408341+08:00 0 [Warning] CA certificate ca.pem is self signed.
2024-03-08T11:27:08.408416+08:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2024-03-08T11:27:08.409721+08:00 0 [Note] Server hostname (bind-address): '*'; port: 3000
2024-03-08T11:27:08.409777+08:00 0 [Note] IPv6 is available.
2024-03-08T11:27:08.409791+08:00 0 [Note]   - '::' resolves to '::';
2024-03-08T11:27:08.409832+08:00 0 [Note] Server socket created on IP: '::'.
2024-03-08T11:27:08.413585+08:00 0 [Note] Failed to start slave threads for channel ''
2024-03-08T11:27:08.419456+08:00 0 [Note] Event Scheduler: Loaded 0 events
2024-03-08T11:27:08.419655+08:00 0 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: '5.7.43-log'  socket: '/tmp/mysql.sock'  port: 3000  MySQL Community Server (GPL)

loose_rpl_semi_sync_slave_enabled/loose_rpl_semi_sync_master_enabled 变量相关告警消失。
performance_schema.processlist 表结构损坏的告警消失。