教學課程:使用 Azure Resource Manager 範本佈建「適用於 MySQL 的 Azure 資料庫」伺服器Tutorial: Provision an Azure Database for MySQL server using Azure Resource Manager template

適用於 MySQL 的 Azure 資料庫 REST API 可讓 DevOps 工程師在 Azure 中自動化及整合受控 MySQL 伺服器和資料庫的佈建、設定和作業。The Azure Database for MySQL REST API enables DevOps engineers to automate and integrate provisioning, configuration, and operations of managed MySQL servers and databases in Azure. 此 API 可用來建立、列舉、管理和刪除「適用於 MySQL 的 Azure 資料庫」服務的 MySQL 伺服器和資料庫。The API allows the creation, enumeration, management, and deletion of MySQL servers and databases on the Azure Database for MySQL service.

Azure Resource Manager 會利用基礎的 REST API 來宣告和程式化大規模部署所需的 Azure 資源,使其與「基礎結構即程式碼」的概念相一致。Azure Resource Manager leverages the underlying REST API to declare and program the Azure resources required for deployments at scale, aligning with infrastructure as a code concept. 此範本會將 Azure 資源名稱、SKU、網路、防火牆組態和設定參數化,使其在建立之後可以使用多次。The template parameterizes the Azure resource name, SKU, network, firewall configuration, and settings, allowing it to be created one time and used multiple times. Azure Resource Manager 範本可使用 Azure 入口網站Visual Studio Code 輕易建立。Azure Resource Manager templates can be easily created using Azure portal or Visual Studio Code. 這些範本可用於應用程式的封裝、標準化和部署自動化,而隨後應用程式可整合到 DevOps CI/CD 管線中。They enable application packaging, standardization, and deployment automation, which can be integrated in the DevOps CI/CD pipeline. 例如,如果您想要在「適用於 MySQL 的 Azure 資料庫」後端快速部署 Web 應用程式,您可以從 GitHub 資源庫使用此快速入門範本執行端對端部署。For instance, if you are looking to quickly deploy a Web App with Azure Database for MySQL backend, you can perform the end-to-end deployment using this QuickStart template from the GitHub gallery.

在本教學課程中,您將使用 Azure Resource Manager 範本和其他公用程式來了解如何:In this tutorial, you use Azure Resource Manager template and other utilities to learn how to:

  • 使用 Azure Resource Manager 範本在 VNet 服務端點建立「適用於 MySQL 的 Azure 資料庫」伺服器Create an Azure Database for MySQL server with VNet Service Endpoint using Azure Resource Manager template
  • 使用 mysql 命令列工具建立資料庫Use mysql command-line tool to create a database
  • 載入範例資料Load sample data
  • 查詢資料Query data
  • 更新資料Update data

必要條件Prerequisites

如果您沒有 Azure 訂用帳戶,請在開始前建立免費 Azure 帳戶If you don't have an Azure subscription, create a free Azure account before you begin.

使用 Azure Resource Manager 範本在 VNet 服務端點建立「適用於 MySQL 的 Azure 資料庫」伺服器Create an Azure Database for MySQL server with VNet Service Endpoint using Azure Resource Manager template

若要取得「適用於 MySQL 的 Azure 資料庫」伺服器的 JSON 範本參考,請移至 Microsoft.DBforMySQL 伺服器範本參考。To get the JSON template reference for an Azure Database for MySQL server, go to Microsoft.DBforMySQL servers template reference. 下列範例 JSON 範本可用來建立在 VNet 服務端點上執行「適用於 MySQL 的 Azure 資料庫」的新伺服器。Below is the sample JSON template that can be used to create a new server running Azure Database for MySQL with VNet Service Endpoint.

{
  "apiVersion": "2017-12-01",
  "type": "Microsoft.DBforMySQL/servers",
  "name": "string",
  "location": "string",
  "tags": "string",
  "properties": {
    "version": "string",
    "sslEnforcement": "string",
    "administratorLogin": "string",
    "administratorLoginPassword": "string",
    "storageProfile": {
      "storageMB": "string",
      "backupRetentionDays": "string",
      "geoRedundantBackup": "string"
    }
  },
  "sku": {
    "name": "string",
    "tier": "string",
    "capacity": "string",
    "family": "string"
  },
  "resources": [
    {
      "name": "AllowSubnet",
      "type": "virtualNetworkRules",
      "apiVersion": "2017-12-01",
      "properties": {
        "virtualNetworkSubnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets', parameters('virtualNetworkName'), parameters('subnetName'))]",
        "ignoreMissingVnetServiceEndpoint": true
      },
      "dependsOn": [
        "[concat('Microsoft.DBforMySQL/servers/', parameters('serverName'))]"
      ]
    }
  ]
}

在此要求中,需要自訂的值包括:In this request, the values that need to be customized are:

  • name - 指定 MySQL 伺服器的名稱 (不含網域名稱)。name - Specify the name of your MySQL Server (without domain name).
  • location - 為您的 MySQL 伺服器指定有效的 Azure 資料中心區域。location - Specify a valid Azure data center region for your MySQL Server. 例如 westus2。For example, westus2.
  • properties/version - 指定要部署的 MySQL 伺服器版本。properties/version - Specify the MySQL server version to deploy. 例如 5.6 或 5.7。For example, 5.6 or 5.7.
  • properties/administratorLogin - 指定伺服器的 MySQL 管理員登入資料。properties/administratorLogin - Specify the MySQL admin login for the server. 管理員登入名稱不可以是 azure_superuser、admin、administrator、root、guest 或 public。The admin sign-in name cannot be azure_superuser, admin, administrator, root, guest, or public.
  • properties/administratorLoginPassword - 為上述指定的 MySQL 管理員使用者指定密碼。properties/administratorLoginPassword - Specify the password for the MySQL admin user specified above.
  • properties/sslEnforcement - 指定 [啟用/停用] 以啟用/停用 sslEnforcement。properties/sslEnforcement - Specify Enabled/Disabled to enable/disable sslEnforcement.
  • storageProfile/storageMB - 指定伺服器所需的最大佈建儲存體大小 (以 MB 為單位)。storageProfile/storageMB - Specify the max provisioned storage size required for the server in megabytes. 例如 5120。For example, 5120.
  • storageProfile/backupRetentionDays - 指定所需的備份保留期限天數。storageProfile/backupRetentionDays - Specify the desired backup retention period in days. 例如 7。For example, 7.
  • storageProfile/geoRedundantBackup - 根據異地災害復原需求指定 [啟用/停用]。storageProfile/geoRedundantBackup - Specify Enabled/Disabled depending on Geo-DR requirements.
  • sku/tier - 指定 Basic、GeneralPurpose 或 MemoryOptimized 層的部署。sku/tier - Specify Basic, GeneralPurpose, or MemoryOptimized tier for deployment.
  • sku/capacity - 指定虛擬核心容量。sku/capacity - Specify the vCore capacity. 可能的值包括 2、4、8、16、32 或 64。Possible values include 2, 4, 8, 16, 32 or 64.
  • sku/family -指定 Gen5 以選擇伺服器部署的硬體世代。sku/family - Specify Gen5 to choose hardware generation for server deployment.
  • sku/name - 指定 TierPrefix_family_capacity。sku/name - Specify TierPrefix_family_capacity. 例如 B_Gen5_1、GP_Gen5_16、MO_Gen5_32。For example B_Gen5_1, GP_Gen5_16, MO_Gen5_32. 請參閱定價層文件,以了解每個區域和每一層的有效值。See the pricing tiers documentation to understand the valid values per region and per tier.
  • resources/properties/virtualNetworkSubnetId - 指定 VNet 中要用來放置 Azure MySQL 伺服器的子網路所具備的 Azure 識別碼。resources/properties/virtualNetworkSubnetId - Specify the Azure identifier of the subnet in VNet where Azure MySQL server should be placed.
  • tags(optional) - 指定選擇性標記,以標示您用來分類計費用資源的索引鍵值組。tags(optional) - Specify optional tags are key value pairs that you would use to categorize the resources for billing etc.

如果您想要建置可為組織自動部署「適用於 MySQL 的 Azure 資料庫」的 Azure Resource Manager 範本,建議您先使用 Azure 快速入門 GitHub 資源庫中的 Azure Resource Manager 範本,並以此為基礎進一步建置。If you are looking to build an Azure Resource Manager template to automate Azure Database for MySQL deployments for your organization, the recommendation would be to start from the sample Azure Resource Manager template in Azure Quickstart GitHub Gallery first and build on top of it.

如果您未曾 Azure Resource Manager 範本,而想要試用,您可以依照下列步驟著手使用:If you are new to Azure Resource Manager templates and would like to try it, you can start by following these steps:

  • 從 Azure 快速入門資源庫中複製或下載範例 Azure Resource Manager 範本Clone or download the Sample Azure Resource Manager template from Azure Quickstart gallery.
  • 根據您的偏好修改 azuredeploy.parameters.json 以更新參數值,並儲存檔案。Modify the azuredeploy.parameters.json to update the parameter values based on your preference and save the file.
  • 在 Azure CLI 中使用下列命令建立 Azure MySQL 伺服器Use Azure CLI to create the Azure MySQL server using the following commands

您可以在瀏覽器中使用 Azure Cloud Shell 或在自己的電腦上安裝 Azure CLI,以執行本教學課程中的程式碼區塊。You may use the Azure Cloud Shell in the browser, or Install Azure CLI on your own computer to run the code blocks in this tutorial.

使用 Azure Cloud ShellUse Azure Cloud Shell

Azure Cloud Shell 是裝載於 Azure 中的互動式殼層環境,可在瀏覽器中使用。Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. 您可以使用 Bash 或 PowerShell 搭配 Cloud Shell,與 Azure 服務共同使用。You can use either Bash or PowerShell with Cloud Shell to work with Azure services. Azure Cloud Shell 已預先安裝一些命令,可讓您執行本文提到的程式碼,而不必在本機環境上安裝任何工具。You can use the Cloud Shell preinstalled commands to run the code in this article without having to install anything on your local environment.

要啟動 Azure Cloud Shell:To start Azure Cloud Shell:

選項Option 範例/連結Example/Link
選取程式碼區塊右上角的 [試試看]。Select Try It in the upper-right corner of a code block. 選取 [試用] 並不會自動將程式碼複製到 Cloud Shell 中。Selecting Try It doesn't automatically copy the code to Cloud Shell. Azure Cloud Shell 的試試看範例
請前往 https://shell.azure.com 或選取 [啟動 Cloud Shell] 按鈕,在瀏覽器中開啟 Cloud Shell。Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. 在新視窗中啟動 Cloud ShellLaunch Cloud Shell in a new window
選取 Azure 入口網站右上方功能表列上的 [Cloud Shell] 按鈕。Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. Azure 入口網站中的 [Cloud Shell] 按鈕

若要在 Azure Cloud Shell 中執行本文中的程式碼:To run the code in this article in Azure Cloud Shell:

  1. 啟動 Cloud Shell。Start Cloud Shell.

  2. 選取程式碼區塊上的 [複製] 按鈕,複製程式碼。Select the Copy button on a code block to copy the code.

  3. 在 Windows 和 Linux 上選取 Ctrl+Shift+V;或在 macOS 上選取 Cmd+Shift+V,將程式碼貼到 Cloud Shell 工作階段中。Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux or by selecting Cmd+Shift+V on macOS.

  4. 選取 Enter 鍵執行程式碼。Select Enter to run the code.

az login
az group create -n ExampleResourceGroup  -l "West US2"
az deployment group create -g $ ExampleResourceGroup   --template-file $ {templateloc} --parameters $ {parametersloc}

取得連線資訊Get the connection information

若要連線到您的伺服器,您必須提供主機資訊和存取認證。To connect to your server, you need to provide host information and access credentials.

az mysql server show --resource-group myresourcegroup --name mydemoserver

結果會採用 JSON 格式。The result is in JSON format. 請記下 fullyQualifiedDomainNameadministratorLoginMake a note of the fullyQualifiedDomainName and administratorLogin.

{
  "administratorLogin": "myadmin",
  "administratorLoginPassword": null,
  "fullyQualifiedDomainName": "mydemoserver.mysql.database.azure.com",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myresourcegroup/providers/Microsoft.DBforMySQL/servers/mydemoserver",
  "location": "westus2",
  "name": "mydemoserver",
  "resourceGroup": "myresourcegroup",
 "sku": {
    "capacity": 2,
    "family": "Gen5",
    "name": "GP_Gen5_2",
    "size": null,
    "tier": "GeneralPurpose"
  },
  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageMb": 5120
  },
  "tags": null,
  "type": "Microsoft.DBforMySQL/servers",
  "userVisibleState": "Ready",
  "version": "5.7"
}

使用 mysql 來連線到伺服器Connect to the server using mysql

使用 mysql 命令列工具建立對「適用於 MySQL 的 Azure 資料庫」伺服器的連線。Use the mysql command-line tool to establish a connection to your Azure Database for MySQL server. 在此範例中,命令是:In this example, the command is:

mysql -h mydemoserver.database.windows.net -u myadmin@mydemoserver -p

建立空白資料庫Create a blank database

連線到伺服器之後,請建立一個空白資料庫。Once you’re connected to the server, create a blank database.

mysql> CREATE DATABASE mysampledb;

在提示字元,執行下列命令以將連線切換到這個新建立的資料庫:At the prompt, run the following command to switch the connection to this newly created database:

mysql> USE mysampledb;

在資料庫中建立資料表Create tables in the database

既然您已經知道如何連線到適用於 MySQL 資料庫的 Azure 資料庫,請完成一些基本工作。Now that you know how to connect to the Azure Database for MySQL database, complete some basic tasks.

首先,建立資料表並在其中載入一些資料。First, create a table and load it with some data. 我們將建立一個儲存清查資訊的資料表。Let's create a table that stores inventory information.

CREATE TABLE inventory (
    id serial PRIMARY KEY, 
    name VARCHAR(50), 
    quantity INTEGER
);

將資料載入到資料表Load data into the tables

既然您已經有資料表,請在其中插入一些資料。Now that you have a table, insert some data into it. 在開啟的命令提示字元視窗,執行下列查詢以插入幾列資料。At the open command prompt window, run the following query to insert some rows of data.

INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);

您現在已將兩列範例資料插入到先前建立的資料表。Now you have two rows of sample data into the table you created earlier.

查詢並更新資料表中的資料Query and update the data in the tables

執行下列查詢,以從資料庫資料表中擷取資訊。Execute the following query to retrieve information from the database table.

SELECT * FROM inventory;

您也可以更新資料表中的資料。You can also update the data in the tables.

UPDATE inventory SET quantity = 200 WHERE name = 'banana';

當您擷取資料時,資料列會相應地更新。The row gets updated accordingly when you retrieve data.

SELECT * FROM inventory;

清除資源Clean up resources

如果不再需要,請刪除資源群組,這會刪除資源群組中的資源。When it's no longer needed, delete the resource group, which deletes the resources in the resource group.

  1. Azure 入口網站中,搜尋並選取 [資源群組]。In the Azure portal, search for and select Resource groups.

  2. 在 [資源群組] 清單中,選擇資源群組的名稱。In the resource group list, choose the name of your resource group.

  3. 在資源群組的 [概觀] 頁面中,選取 [刪除資源群組]。In the Overview page of your resource group, select Delete resource group.

  4. 在確認對話方塊凹輸入您的資源群組名稱,然後選取 [刪除]。In the confirmation dialog box, type the name of your resource group, and then select Delete.

後續步驟Next steps

在本教學課程中,您已了解:In this tutorial you learned to:

  • 使用 Azure Resource Manager 範本在 VNet 服務端點建立「適用於 MySQL 的 Azure 資料庫」伺服器Create an Azure Database for MySQL server with VNet Service Endpoint using Azure Resource Manager template
  • 使用 mysql 命令列工具建立資料庫Use the mysql command-line tool to create a database
  • 載入範例資料Load sample data
  • 查詢資料Query data
  • 更新資料Update data