mysql[漏洞打补丁]小版本升级5.7.25到5.7.28

旧城等待， 2023-06-25 06:05 7阅读 0赞

# mysql\[漏洞\]小版本升级 #

## 事件背景 ##

近日，公司进行安全扫描，发现mysql存在漏洞，高危漏洞编号为(CVE-2019-3822)，此外还有多个中危，低危漏洞，目前mysql版本为5.7.25  
![CVE-2019-3822][]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxNTE4Mw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][20191226143240510.png]

## 解决方案 ##

对mysql进行小版本升级到5.7.28，生产环境无法联网，用rpm 包离线安装，选择就地升级。

**Mysql的两种升级方式**

1.  就地升级（In-place Upgrade）  
    关闭旧版本mysql，用新的替换旧的二进制文件或软件包，在现有数据目录上重启数据库，执行mysql\_upgrade
2.  逻辑升级（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.

解决办法

[root@localhost ~]# mysql_upgrade -uroot -p --socket=/file/mysql/mysql.sock   //指定sock文件位置

参考文章：https://blog.csdn.net/ximenjianxue/article/details/97274198  
https://blog.csdn.net/memory6364/article/details/87169889

[CVE-2019-3822]: https://img-blog.csdnimg.cn/20191226143204516.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxNTE4Mw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxNTE4Mw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191226143226571.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxNTE4Mw==,size_16,color_FFFFFF,t_70
[20191226143240510.png]: https://img-blog.csdnimg.cn/20191226143240510.png