編碼的世界 / 優質文選 / 歷史

mysql[漏洞打補丁]小版本升級5.7.25到5.7.28

2022年7月28日
-

mysql[漏洞]小版本升級

事件背景

近日，公司進行安全掃描，發現mysql存在漏洞，高危漏洞編號為(CVE-2019-3822)，此外還有多個中危，低危漏洞，目前mysql版本為5.7.25

解決方案

對mysql進行小版本升級到5.7.28，生產環境無法聯網，用rpm 包離線安裝，選擇就地升級。
Mysql的兩種升級方式

就地升級（In-place Upgrade）關閉舊版本mysql，用新的替換舊的二進制文件或軟件包，在現有數據目錄上重啟數據庫，執行mysql_upgrade

邏輯升級（Logical Upgrade）使用備份或導出實用程序（如mysqldump，Xtrabackup）從舊mysql實例導出SQL ，安裝新的mysql數據庫版本，再將SQL應用於新的mysql實例。

官方支持的升級路徑

同一個大版本中的小版本升級，比如5.7.25到5.7.28。
跨版本升級，但只支持跨一個版本升級，比如5.5到5.6，5.6到5.7。
不支持跨版本的直接升級，比如直接從5.5到5.7，可以先從5.5升級到5.6，再從5.6升級到5.7。

升級步驟

查看目前版本

[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper

下載mysql5.7.28 rpm包
官方下載地址：https://dev.mysql.com/downloads/mysql/
備份數據文件

[root@localhost ~]# cat /etc/my.cnf |grep datadir
#datadir=/var/lib/mysql
datadir=/file/mysql
[root@localhost ~]# cp -pr /file/mysql/ /file/mysql.bak

備份配置文件

[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf.bak

備份sql數據

[root@localhost ~]# mysqldump -uroot -p opt socket=/file/mysql/mysql.sock all-databases > /root/backup/mysqlbackup.20191226.sql //備份很重要，防止升級失敗

配置MySQL以通過設置innodb_fast_shutdown為執行慢速關閉 0

[root@localhost ~]# mysql -u root -p execute="SET GLOBAL innodb_fast_shutdown=0" //在關閉過程中，InnoDB執行完全清除並在關閉之前更改緩沖區合並，這可確保在發布版本之間存在文件格式差異時完全准備好數據文件

關閉當前mysql服務

 [root@localhost ~]# ps -ef |grep mysql |grep -v color
mysql 1061 1 0 09:24 ? 00:00:09 /usr/sbin/mysqld daemonize pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]# mysqladmin -u root -p socket=/file/mysql/mysql.sock shutdown
[root@localhost ~]# ps -ef |grep mysql |grep -v color

卸載mysql5.7.25並解壓安裝mysql.5.7.28

[root@localhost mysql_rpm]# rpm -qa |grep mysql
mysql-community-client-5.7.25-1.el7.x86_64
mysql-community-libs-5.7.25-1.el7.x86_64
mysql-community-common-5.7.25-1.el7.x86_64
mysql-community-server-5.7.25-1.el7.x86_64
[root@localhost mysql_rpm]# rpm -qa |grep mysql |xargs rpm -ev nodeps
[root@localhost mysql_rpm]# rpm -qa |grep mysql
[root@localhost mysql_rpm]# rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
[root@localhost mysql_rpm]# rpm -ivh mysql-community-libs-5.7.28-1.el6.x86_64.rpm
[root@localhost mysql_rpm]# rpm -ivh mysql-community-client-5.7.28-1.el6.x86_64.rpm
[root@localhost mysql_rpm]# rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm
[root@localhost mysql_rpm]# rpm -ivh mysql-community-server-5.7.28-1.el6.x86_64.rpm nodeps
 [root@localhost mysql_rpm]# rpm -qa |grep mysql
mysql-community-libs-5.7.28-1.el6.x86_64
mysql-community-common-5.7.28-1.el7.x86_64
mysql-community-client-5.7.28-1.el6.x86_64
mysql-community-server-5.7.28-1.el6.x86_64

注意：rpm包有嚴格的依賴關系，必須按照順序執行安裝： mysql-community-common-5.7.24-1.el6.x86_64.rpm mysql-community-libs-5.7.24-1.el6.x86_64.rp mysql-community-client-5.7.24-1.el6.x86_64.rpm libaio-0.3.107-10.el6.x86_64.rpm（若在有網情況下可執行yum install libaio） mysql-community-server-5.7.24-1.el6.x86_64.rpm
安裝mysql-community-server前需要安裝libaio 下載地址http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm
初始化數據庫

[root@localhost ~]# cp /etc/my.cnf.bak /etc/my.cnf
[root@localhost ~]# mysqld initialize user=mysql explicit_defaults_for_timestamp //執行完成後查看 /var/log/mysqld.log日志中可看到root用戶的初始密碼

啟動服務

root@localhost ~]# systemctl start mysqld
[root@localhost ~]# ps -ef |grep mysql |grep -v color
root 2168 1 0 10:49 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe datadir=/file/mysql socket=/file/mysql/mysql.sock pid-file=/var/run/mysqld/mysqld.pid basedir=/usr user=mysql
mysql 2398 2168 13 10:49 ? 00:00:01 /usr/sbin/mysqld basedir=/usr datadir=/file/mysql plugin-dir=/usr/lib64/mysql/plugin user=mysql log-error=/file/logs/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid socket=/file/mysql/mysql.sock

登陸mysql

[root@localhost ~]# mysql -uroot –p //輸入自動生成的初始密碼
//或者在配置文件添加skip-grant-tables，重啟服務跳過密碼驗證，配置新密碼後再刪除這條配置
(root@localhost) [mysql] 11:33:53> set password = password("yournewpassword");
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [mysql] 11:34:22> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [mysql] 11:34:33> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uroot –p //登陸驗證密碼是否生效

Mysql數據結構升級

[root@localhost ~]# mysql_upgrade -uroot -p socket=/file/mysql/mysql.sock
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.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.

再次查看版本，到此升級成功

[root@localhost ~]# mysql –V
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper
(root@localhost) [(none)] 11:55:45> select@@version; //登陸數據庫也可查看版本
+-+
| @@version |
+-+
| 5.7.28 |
+-+
1 row in set (0.00 sec)

遇到的問題

初始化報錯data

[root@localhost ~]# mysqld initialize user=mysql explicit_defaults_for_timestamp
2019-12-26T02:46:41.298053Z 0 [ERROR] initialize specified but the data directory has files in it. Aborting.
2019-12-26T02:46:41.298144Z 0 [ERROR] Aborting

解決辦法

[root@localhost ~]# rm -rf /file/mysql //刪除你的數據文件
[root@localhost ~]# mysqld initialize user=mysql explicit_defaults_for_timestamp

數據結構升級報錯error2002

[root@localhost ~]# mysql_upgrade -uroot -p
Enter password:
mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) while connecting to the MySQL server
Upgrade process encountered error and will not continue.