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

mysql[漏洞]小版本升级

事件背景

近日，公司进行安全扫描，发现mysql存在漏洞，高危漏洞编号为(CVE-2019-3822)，此外还有多个中危，低危漏洞，目前mysql版本为5.7.25

解决方案

对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