DB32昇級5743

# DB32昇版5743 author: eason@iplaystar.net HOST:db-32.clone() v2023/09/27 :::info :mega: DB系統昇版，請先 1.MySQL的檔案 [#原機準備](#原機準備)，2.後在作業原機 [#操作昇版](#操作昇版) ::: ## 原機準備 ### 原機檢查 * cmd.exe檢查 ```shell mysql/bin > # v5.x 先檢查 echo [mysqlcheck.star]%date%-%time% >> C:\upgradev57_20230927_chk1.txt mysqlcheck -u root -p --all-databases --check-upgrade >> C:\upgradev57_20230927_chk1.txt echo [mysqlcheck.end.]%date%-%time% >> C:\upgradev57_20230927_chk1.txt ``` * chkN.txt 文字內容 ``` slot-game.session_log Warning : Trigger slot-game.session_log.session_log_parse_user_agent does not have CREATED attribute. status : OK warning : The partition engine, used by table 'slot-game.session_log', is deprecated and will be removed in a future release. Please use native partitioning instead. ``` ***[說明]*** `does not have CREATED attribute.`，這類文字可省略。 `warning : The partition engine,`，各機器`table`數量不一定，遇到需各別處理，參考程序[#原機作業](#原機作業)。 ### 原機作業 [#原機檢查](#原機檢查)沒有`warning : The partition engine,`可省略以下作業。如果遇到有問題的TABLE都要處理。 * SQL調整作業時間:限在***維護時*** HOST:db-32 時間: 使用檔(4 min 55.16 sec)，整個程序約20分鐘內 TABLE：`session_log` (2.2 GB/120.8 GB) 以單一資料表為例如下： ```sql ## 1.只保留近30日資料 use `slot-game`; CREATE TABLE `slot-game`.session_log_ztmp202309 LIKE `slot-game`.session_log ; ALTER TABLE `slot-game`.session_log_ztmp202309 ENGINE = INNODB; INSERT INTO `slot-game`.session_log_ztmp202309 (SELECT * FROM `slot-game`.session_log WHERE create_time>=concat(DATE_SUB(CURDATE(),INTERVAL 30 DAY)," 00:00:00") ); ## 2.調整檔案&TRIGGER DROP TABLE IF EXISTS `slot-game`.session_log ; DROP TRIGGER IF EXISTS `slot-game`.`session_log_parse_user_agent`; ALTER TABLE `slot-game`.session_log_ztmp202309 RENAME `slot-game`.session_log ; CREATE TRIGGER `slot-game`.`session_log_parse_user_agent` BEFORE INSERT ON `session_log` FOR EACH ROW SET NEW.`device_type` = CASE WHEN NEW.user_agent REGEXP 'iPad|iPhone|Android|Mobile|Tablet' THEN 2 WHEN NEW.user_agent REGEXP 'Mac OS X|X11|Windows' THEN 1 ELSE 0 END ; ## 3.TABLE.session_log完成。 SELECT substr(create_time,1,10)as _day,count(1) FROM `slot-game`.session_log GROUP BY substr(create_time,1,10) order by substr(create_time,1,10); ``` ## 操作昇版 :::warning 昇級前請先檢查作業[#原機檢查](#原機檢查)(上半部的程序)。 DB系統相關 [#資料路徑](#資料路徑)有各版本使用的位置。 ::: * HOST:db-32.clone() 原版本: 5.7.29-enterprise-commercial-advanced-log 昇版本: 5.7.43-log MySQL Community Server (GPL) Date: 2023/09/27 本程序二次完整備份包含：備份A-昇級前備份＆備份B-昇級完成的初始資料。照以下順序歩驟作業如下。 --------------------------------------------------------------------------------------- ### 原機備份 1. MySQL關閉：停下MySQL服務。 - 原機"MySQL" 開動(S-Star)/停止(O-Stop)需接常。(原機v5.7.29) - Services/Name:"MySQL"/內容(R):Properties/Startup type:"Manual"手動 ->[Apply]確定。 - Services/Name:"MySQL"/停止(O):STOP 。確定。 - 修改"C:\mysql-advanced-5.7.29\bin\my.ini"內容:[mysqld] port第6行改"port= 5729" 2. 備份A `datadir` 位置"D:/mysql_data" (HOST:db-32)整個資料夾備份。 ### 安裝設定 3. 下載/安裝/設定 MySQL 5.7.43 - 下載[MySQL5.7.43](https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-5.7.43.0.msi) https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-5.7.43.0.msi - 安裝MySQL (...5.7.43.0.msi) ```shell MySQL Installer| Upgrading Community / Welcome Back! [Cancel]->[Yes] / MySQL Installer [Add ...] / Select Products > [-]MySQL Server [-]MySQL Server 5.7 --"MySQL Server -X64" => Products To Be Installed:"MySQL Server -X64" [v]Enable the Select Features page to... [Next] > Check Requirements "For Product":MySQL Server 5.7.43 [Execute] > [v]I agree to the license terms and conditions. [Install] =>Setup Successful[Close] > Check Requirements [Next] Select Features To Install [Next >] Installation > Product:"MySQL Server"/Arch:"X64" [Execute] & [Next >] Product Configuration > [Next] Installation Complete > Type and Networking [Next] > Accounts and Roles / MySQL Root Password:"{xxx密碼xxx}"輸入密碼 & 重複一次密碼 [Next > ] Windows Server > 預設名稱Windows Service Name:"MySQL57" 移除自動開啟[ ]Start the MySQL Server at System Startup [Next >] Server File Permissions > (v)No,I will manage the permissions after the server configuration.[Next >] Apply Configuration > [Execte] & [Finish] Product Configuration [Next] > Installation Complete [Finish] 完成安裝。 ``` - MySQL關閉：停下MySQL服務。 ```shell Service(Local) 右鍵 [Refresh] Services/Name:"MySQL57"/內容(R):Properties/Startup type:"Manual"手動 ->[Apply]確定。 Services/Name:"MySQL57"/內容(R):Properties/-Log On-(v)Local System account ->[Apply]確定。 Services/Name:"MySQL57"/停止(O):STOP 。確定。 ``` 4. 檢視安裝檔案位置 ```shell 程式位置 "C:\Program Files\MySQL\MySQL Server 5.7\bin\" 初始資料 "C:\ProgramData\MySQL\MySQL Server 5.7\Data"，資料夾更名"OriginalFile5743_Data" 設定更名 "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"，更名"OriginalFile5743.my.ini" 建新設定 "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"，來源"C:\mysql\bin\my.ini" ``` - 資料庫夾`datadir`移檔:"D:\_StoreData\data5743" 將舊版`datadir`移入。 (原機v5729-`datadir`:"D:/mysql_data") - 移除檔案 `ib_%`,`mysql_error.log`,`ps-service-slow.log` & 備份檔(file) 。參考[#備份雜檔v5729](#備份雜檔v5729) - 記錄檔案大小 ```script D: cd \_StoreData echo [datadir.star]%date%-%time% >> D:\_StoreData\ztmp_e5729\v43_size_datadir2.log ; dir /s D:\_StoreData >> D:\_StoreData\ztmp_e5729\v43_size_datadir2.log ; echo [datadir.star]%date%-%time% >> D:\_StoreData\ztmp_e5729\v43_size_datadir2.log ; ``` - 攺設定my.ini：產生bin-log檔，調整參數。 - 攺設定my.ini：停用排程。[mysqld] event_scheduler = 0 ##ON - 開啟"MySQL57"：Services/Name:"MySQL57"/啟動(S):Start 。確定。 - 確定服務開啟，如果未能啟動請：特別留意> [#啟不來](#啟不來)，修正到Status:Running 服務中。 ### 昇級檢查 5. 資料版本昇級：mysql_upgrade & 檢查 - 資料昇級mysql_upgrade檢查 ```script C: cd \Program Files\MySQL\MySQL Server 5.7\bin echo [upgrade.star]%date%-%time% >> D:\_StoreData\ztmp_e5729\upgrade57_5743.log mysql_upgrade -u root -p --force >> D:\_StoreData\ztmp_e5729\upgrade57_5743.log echo [upgrade.end]%date%-%time% >> D:\_StoreData\ztmp_e5729\upgrade57_5743.log 檢查不要有Eroor ``` - 資料昇級mysql_upgrade.log正常。參考訊息[#原機檢查](#原機檢查)  - 資料庫檢查 ```javascript mysql -uroot -p ; mysql > SELECT version(),now(),sleep(5); ##留意slow.log需有這筆記錄 USE `slot-game`; CREATE TABLE z_session_recovery_202309 LIKE session_recovery; INSERT INTO z_session_recovery_202309 (SELECT * FROM session_recovery); DROP TABLE z_session_recovery_202309 ; CREATE USER 'user520'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT,INSERT ON `slot-game`.* TO 'user520'@'localhost'; DROP USER 'user520'@'localhost'; call calc_acc_member_daily('2023-09-20'); SELECT 'call_fun2',get_game_type('PSS-ON-00116',0) ,get_game_type('PSF-ON-00006',2),count(1) FROM acc_member_daily_new WHERE game_day>='2023-09-08 00:00:00' AND game_day<='2023-09-08 23:59:59' ; #以上程序都要正常的回應`Query OK,` ``` ### 營運檢查 6. 營運前準備&備份資料庫 - 重點my.ini (v5.7.43) ```text [mysql] port= 3306 datadir=D:/_StoreData/data5743 ##資料區 event_scheduler = ON ##排程:主DB啟用正試開始 server-id = 20230927 ##UUID:同機群的唯一代號(int),主從架構使用 ``` - 服務停用:Services/Name:"MySQL57"/停止(O):STOP 。確定。 - 攺設定my.ini：啟用排程。[mysqld] event_scheduler = ON - 刪除雜檔 ```text D:\_StoreData\data5743 ##移除檔案限包含如下 ib_logfile% mysql-bin.0% mysql-bin.index PS-SERVICE.err PS-SERVICE-slow.log ``` - 備份B`datadir`，開啟前的初始備份。位置:"D:\_StoreData\data5743" 整個資料夾備份。 - 服務啟動:Services/Name:"MySQL57"/啟動(S):Star 。確定。 7. 昇級完成 - 檢查MySQL產生的log,不要有`Error` 等訊息產生 - 掛載-監視應用程序。 - 完成。 --------------------------------------------------------------------------------------- ## 特別留意 ### 啟不來 * Services > Name:MySQL57 The MySQL57 service on Local Computer stated and then stopped. Some services stop automatically if theay are not in use by other services or programs. ***[解決步驟]*** - Step1, 快捷鍵 Windows + R 開啟(Open)。 - Step2. 輸入 eventvwr.msc ，[Enter]，開啟 Event Viewer。 - Step3. 點入EventViewer()->Windows Log -> Application 找到對應的Service的Error log查看詳情。 - Step4. 根據 Details，解決問題。  ## 相關資訊 ### 備份傾倒 * HOST:db-32.clone() 統計Dump所需時間 Date:2023-09-21 | DB | Dump | Size(BG) | Size(倍數) | Time | Note | |-|-|-:|-:|-:|-| | slot-game | accounting | 345.7 | 157.1 | 超過40分 | 中斷傾倒 | | slot-game | accounting_jackpot | 2.2 | 1.0 | 0:05:23 | 基準 | | jackpot | {ALL-DATA} | 18.6 | 8.5 | 0:45:31 | 估算值up | | slot-game | {ALL-DATA} | 1660.0 | 754.5 | 19:41:58 | 估算值up | | 檔案:ibdata1 | {File-DATA} | 413.0 | x | x | InnoDB產生 | 檔案dump方式，時間過久。 ### 備份雜檔v5729 * v5729原檔案`datadir` DB停服務時，昇級至v5743時,事先移除舊版的檔案。 ```text Directory of D:\_StoreData\ztmp_e5729_data 09/27/2023 03:11 AM <DIR> . 09/27/2023 03:11 AM <DIR> .. 05/06/2023 10:01 AM 17,396,480 20230506.txt 09/01/2023 11:04 AM 59 db-ufabet-slow.7z 09/27/2023 02:26 AM 74,648,707 db-ufabet-slow.log 04/15/2020 05:44 AM 194,493,836 db-ufabet-slow.log.1 05/12/2023 03:49 PM 467,267 e.txt 09/27/2023 02:37 AM 3,813,458 ib_buffer_pool 09/27/2023 02:37 AM 8,589,934,592 ib_logfile0 09/20/2023 02:21 AM 8,589,934,592 ib_logfile1 09/26/2023 08:06 AM 8,589,934,592 ib_logfile2 09/26/2023 08:39 AM 8,589,934,592 ib_logfile3 09/27/2023 02:37 AM 3,715,381 mysql_error.log 04/15/2020 07:30 AM 35,169,922 mysql_error.log.1 06/14/2023 10:33 AM 12,016,494,941 mysql_error_2023.log 13 File(s) 46,705,938,419 bytes ``` ### 備份雜檔v5743 * v5.7.x原檔案`datadir` DB停服務時，初始化能移除備份的檔案。 ```text Directory of D:\_StoreData\ztmp_20230927_v57 09/27/2023 04:51 AM <DIR> . 09/27/2023 04:51 AM <DIR> .. 09/27/2023 04:49 AM 50,331,648 ib_logfile0 09/27/2023 03:51 AM 50,331,648 ib_logfile1 09/27/2023 04:49 AM 3,823,802 mysql-bin.000001 ~int(N) 09/27/2023 03:52 AM 19 mysql-bin.index 09/27/2023 04:49 AM 1,225 PS-SERVICE-slow.log 09/27/2023 04:49 AM 10,883 PS-SERVICE.err 6 File(s) 104,499,225 bytes ``` ### 狀況.session_log.1 不是每台機器都遇到這問題，但有可能發生在其他資料表(table)。 * TABLE:slot-game.session_log (1/2) mysql_upgrade的訊息 ```txt slot-game.session_log Warning : Trigger slot-game.session_log.session_log_parse_user_agent does not have CREATED attribute. error : Partitioning upgrade required. Please dump/reload to fix it or do: ALTER TABLE `slot-game`.`session_log` UPGRADE PARTITIONING warning : The partition engine, used by table 'slot-game.session_log', is deprecated and will be removed in a future release. Please use native partitioning instead. ``` * 修復資料表 (cmd:mysql_upgrade) MySQL > show processlist;（不是每台機器都遇到這問題） | Id | User | Host | db | Command | Time | State | Info | | |-|-|-|-|-|-|-|-|-| | 16 | root | 127.0.0.1:1140 | sys | Query | 8524 | System lock | REPAIR NO\_WRITE\_TO\_BINLOG TABLE \`slot-game\`.\`session\_log\` | | 217 | root | 114.33.71.4:42618 | jackpot | Query | 0 | starting | show processlist | 執行太久,結束檢查 (MySQL> Kill {編號ID}) ### 狀況.session_log.2 * TABLE:slot-game.session_log (2/2) 發生昇級狀況(mysql_upgrade)時的調整。結束後在重執行一次(mysql_upgrade)。 ```sql use `slot-game`; ## 1.只保留近日資料 ALTER TABLE session_log RENAME session_log_tmp20230921; CREATE TABLE session_log LIKE session_log_tmp20230921; INSERT INTO session_log (SELECT * FROM session_log_tmp20230921 WHERE create_time>='2023-09-20 00:00:00' ); ## 2.調整TRIGGER:'session_log_parse_user_agent' DROP TRIGGER IF EXISTS `session_log_parse_user_agent`; CREATE DEFINER=`sa`@`%` TRIGGER `session_log_parse_user_agent` BEFORE INSERT ON `session_log` FOR EACH ROW SET NEW.`device_type` = CASE WHEN NEW.user_agent REGEXP 'iPad|iPhone|Android|Mobile|Tablet' THEN 2 WHEN NEW.user_agent REGEXP 'Mac OS X|X11|Windows' THEN 1 ELSE 0 END ; ## 3.移除大檔。session_log完成。 DROP TABLE session_log_tmp20230921; ``` ## 資料配置 ### 資料路徑 * 系統配置的路徑 | 資料的路徑 | 作用 | 說明 | |-|-|-| | C:\\mysql\\bin\ | v5626.bin | Dev(10) | | C:\\mysql\\bin\\my.ini | v5626.ini | Dev(10) | | C:\\mysql\\data\ | v5626.data | Dev(10) | | | | | | C:\\mysql-advanced-5.7.29\\bin\ | v5729.bin | | | C:\\mysql-advanced-5.7.29\\bin\\my.ini | v5729.ini | | | D:/mysql_data | v5729.data | | | | | | | C:\\Program Files\\MySQL\\MySQL Server 5.7\\bin\ | v5743.bin | v5.7.x終版 | | C:\\ProgramData\\MySQL\\MySQL Server 5.7\\my.ini | v5743.ini | | | D:/_StoreData/data5743 | v5743.data | | | D:/_StoreData/tmp5743 | v5743.tmpdir | | | | | | | C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\ | v8033.bin | | | D:\\_StoreData\\datadir\\my.ini | v8033.ini | | | D:\\_StoreData\\datadir\\Data | v8033.data | | | D:\\_StoreData\\tmpdir | v8033.tmpdir | | | | | | | D:\\\_StoreData\\ztmp\_{TYPE} | 暫存資料夾 | 備份用/待移除 | ### 服務-應用程式 * Services(Local) | Name-名稱 | 作用 | Description-描述 | |-|-|-| | mysql | v5.6.x | |Dev(10) | MySQL | v5729 | | | MySQL57 | v5743 | Status:Running | | MySQL80 | v8033 | | | mysqld_exporter | Prometheus | 監控用 |