---
# System prepended metadata

title: DB32昇級5743
tags: ['#MySQL']

---

# 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正常。參考訊息[#原機檢查](#原機檢查)
	
	<!--
	- 資料昇級檢查mysql_upgrade的受損TABLE：`slot-game`.`session_log`
		1.Kill有問題的程序[#狀況情境TABLE.session_log](#狀況情境TABLE.session_log) 
		2.調整資料表 [#狀況調整TABLE.session_log](狀況調整TABLE.session_log)
	```script
	C: 
	cd \Program Files\MySQL\MySQL Server 5.7\bin 
	echo   [upgrade.star]%date%-%time% >> D:\_StoreData\ztmp_e5729\v43_upgrade_5743_2.log
	mysql_upgrade -u root -p  --force  >> D:\_StoreData\ztmp_e5729\v43_upgrade_5743_2.log
	echo   [upgrade.end]%date%-%time%  >> D:\_StoreData\tmp_e5729\zv43_upgrade_5743_2.log
	檢查(記錄.log)不要有Eroor
	```
	-->
	
	- 資料庫檢查

	```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，解決問題。

<!--

* EventData-Can't create test file C:\_StoreData\data5743\db-ufabet.lower-test 
	資料夾 右鍵內容(R)[Properties] > 進階(D)[Advanced...]
	>	Archive and Index attributes
	>	[ ](A)Folder is ready for archiving
	>	[ ](I)Allow files in this folder to have contents indexed in addition to file properties
	>  以上兩項都不選取  確定[OK]  -> 套用[Apply] -> 確定[OK]
-->


## 相關資訊


### 備份傾倒

* 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 | 監控用  |

