Azure Database for MySQL フレキシブルサーバーのメジャーアップグレードを Azure CLI で試してみた

Azure Database for MySQL フレキシブルサーバーで、インプレースアップグレードが可能になっていました。そこで、まずはデータが何も入っていない MySQL 5.7 を用意して 8.0 へのアップグレードの動作を確認してみました。

## 検証用 MySQL を作成

```bash:bash
region=eastus
prefix=mnrmsql

az group create \
  --name ${prefix}-rg \
  --location $region

az mysql flexible-server create \
  --location $region \
  --resource-group ${prefix}-rg \
  --name ${prefix}-db \
  --admin-user username \
  --admin-password password \
  --sku-name Standard_B1ms \
  --version 5.7
```

## B シリーズのまま試しにアップグレードを試す

`Major version update is not supported for the Burstable pricing tier.` となりました。

```bash:bash
az mysql flexible-server upgrade \
  --resource-group ${prefix}-rg \
  --name ${prefix}-db \
  --version 8

Updating major version in server mnrmsql-db is irreversible. The action you're about to take can't be undone. Going further will initiate major version upgrade to the selected version on this server. (y/n): y
Major version update is not supported for the Burstable pricing tier.
```

## B シリーズから GP シリーズにスケールアップ

```bash:bash
az mysql flexible-server update \
  --resource-group ${prefix}-rg \
  --name ${prefix}-db \
  --tier GeneralPurpose \
  --sku-name Standard_D2ds_v4
```

## GP シリーズでアップグレードを試す

`Issueed values: NO_AUTO_CREATE_USER` となりました。

```bash:bash
az mysql flexible-server upgrade \
  --resource-group ${prefix}-rg \
  --name ${prefix}-db \
  --version 8

Updating major version in server mnrmsql-db is irreversible. The action you're about to take can't be undone. Going further will initiate major version upgrade to the selected version on this server. (y/n): y
(ServerVersionUpgradeConditionCheckFailed) Upgrade server version failed, check the error message for details: Find invalid server parameters, please correct before upgrade to new server version
Name: sql_mode, 
Current value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER, 
Allowed values: ,ALLOW_INVALID_DATES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE,IGNORE_SPACE,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,PAD_CHAR_TO_FULL_LENGTH,PIPES_AS_CONCAT,REAL_AS_FLOAT,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,TIME_TRUNCATE_FRACTIONAL, 
Issueed values: NO_AUTO_CREATE_USER
```

## パラメータを修正

```bash:bash
az mysql flexible-server parameter show \
  --resource-group ${prefix}-rg \
  --server-name ${prefix}-db \
  --name sql_mode \
  --query value

"ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

az mysql flexible-server parameter set \
  --resource-group ${prefix}-rg \
  --server-name ${prefix}-db \
  --name sql_mode \
  --value "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"
```

## もう一度アップグレードを試す

```bash:bash
az mysql flexible-server upgrade \
  --resource-group ${prefix}-rg \
  --name ${prefix}-db \
  --version 8

Updating major version in server mnrmsql-db is irreversible. The action you're about to take can't be undone. Going further will initiate major version upgrade to the selected version on this server. (y/n): y

az mysql flexible-server show \
  --resource-group ${prefix}-rg \
  --name ${prefix}-db \
  --query version

"8.0.21"
```

## 後片付け

```bash:bash
az group delete \
  --name ${prefix}-rg \
  --yes
```

## 参考

https://azure.microsoft.com/ja-jp/updates/general-availability-azure-database-for-mysql-flexible-server-major-version-upgrader/

https://techcommunity.microsoft.com/t5/azure-database-for-mysql-blog/introducing-major-version-upgrade-for-azure-database-for-mysql/ba-p/3821617

https://learn.microsoft.com/ja-jp/azure/mysql/flexible-server/how-to-upgrade

https://learn.microsoft.com/ja-jp/cli/azure/mysql/flexible-server?view=azure-cli-latest#az-mysql-flexible-server-upgrade