教程:使用 Azure CLI 设计 Azure Database for MariaDBTutorial: Design an Azure Database for MariaDB using Azure CLI

Azure Database for MariaDB 是云中基于 MariaDB 社区版数据库引擎的一种关系数据库服务。Azure Database for MariaDB is a relational database service in the cloud based on MariaDB Community Edition database engine. 在本教程中,需使用 Azure CLI(命令行接口)以及其他实用工具了解如何完成以下操作:In this tutorial, you use Azure CLI (command-line interface) and other utilities to learn how to:

  • 创建 Azure Database for MariaDBCreate an Azure Database for MariaDB
  • 配置服务器防火墙Configure the server firewall
  • 使用 mysql 命令行工具创建数据库Use mysql command-line tool to create a database
  • 加载示例数据Load sample data
  • 查询数据Query data
  • 更新数据Update data
  • 还原数据Restore data

如果没有 Azure 订阅,请在开始前创建一个试用 Azure 帐户If you don't have an Azure subscription, create a trial Azure account before you begin.

在自己的计算机上安装 Azure CLI,运行本教程中的代码块。You can install Azure CLI on your own computer to run the code blocks in this tutorial.

本文要求运行 Azure CLI 2.0 或更高版本。This article requires that you are running the Azure CLI version 2.0 or later. 运行 az --version 即可查找版本。Run az --version to find the version. 如果需要进行安装或升级,请参阅安装 Azure CLIIf you need to install or upgrade, see Install Azure CLI.

如果有多个订阅,请选择资源所在的相应订阅或对资源进行计费的订阅。If you have multiple subscriptions, choose the appropriate subscription in which the resource exists or is billed for. 使用 az account set 命令选择帐户下的特定订阅 ID。Select a specific subscription ID under your account using az account set command.

az account set --subscription 00000000-0000-0000-0000-000000000000

创建资源组Create a resource group

使用 az group create 命令创建 Azure 资源组Create an Azure resource group with az group create command. 资源组是在其中以组的形式部署和管理 Azure 资源的逻辑容器。A resource group is a logical container into which Azure resources are deployed and managed as a group.

以下示例在 chinaeast2 位置创建名为 myresourcegroup 的资源组。The following example creates a resource group named myresourcegroup in the chinaeast2 location.

az group create --name myresourcegroup --location chinaeast2

创建 Azure Database for MariaDB 服务器Create an Azure Database for MariaDB server

使用 az mariadb server create 命令创建 Azure Database for MariaDB 服务器。Create an Azure Database for MariaDB server with the az mariadb server create command. 一个服务器可以管理多个数据库。A server can manage multiple databases. 通常,每个项目或每个用户使用一个单独的数据库。Typically, a separate database is used for each project or for each user.

以下示例在资源组 myresourcegroup 中的 chinaeast2 处创建名为 mydemoserver 的 Azure Database for MariaDB 服务器。The following example creates an Azure Database for MariaDB server located in chinaeast2 in the resource group myresourcegroup with name mydemoserver. 该服务器的管理员登录名为 myadminThe server has an administrator log in named myadmin. 它是一台常规用途第 5 代服务器,具有 2 个 vCore。It is a General Purpose, Gen 5 server with 2 vCores. 用自己的值替换 <server_admin_password>Substitute the <server_admin_password> with your own value.

az mariadb server create --resource-group myresourcegroup --name mydemoserver --location chinaeast2 --admin-user myadmin --admin-password <server_admin_password> --sku-name GP_Gen5_2 --version 10.2

sku-name 参数值遵循 {定价层}_{计算层代}_{vCore 数} 约定,如以下示例中所示:The sku-name parameter value follows the convention {pricing tier}_{compute generation}_{vCores} as in the examples below:

  • --sku-name B_Gen5_4 映射到基本、第 5 代和 4 个 vCore。--sku-name B_Gen5_4 maps to Basic, Gen 5, and 4 vCores.
  • --sku-name GP_Gen5_32 映射到常规用途、第 5 层和 32 个 vCore。--sku-name GP_Gen5_32 maps to General Purpose, Gen 5, and 32 vCores.
  • --sku-name MO_Gen5_2 映射到内存优化、第 5 层和 2 个 vCore。--sku-name MO_Gen5_2 maps to Memory Optimized, Gen 5, and 2 vCores.

请参阅定价层文档来了解适用于每个区域和每个层的有效值。Please see the pricing tiers documentation to understand the valid values per region and per tier.

Important

此处指定的服务器管理员登录名和密码是以后在本快速入门中登录到服务器及其数据库所必需的。The server admin login and password that you specify here are required to log in to the server and its databases later in this quickstart. 请牢记或记录此信息,以后会使用到它。Remember or record this information for later use.

配置防火墙规则Configure firewall rule

使用 az mariadb server firewall-rule create 命令创建 Azure Database for MariaDB 服务器级防火墙规则。Create an Azure Database for MariaDB server-level firewall rule with the az mariadb server firewall-rule create command. 服务器级防火墙规则允许外部应用程序(如 mysql 命令行工具或 MySQL Workbench)通过 Azure MariaDB 服务防火墙连接到服务器。A server-level firewall rule allows an external application, such as mysql command-line tool or MySQL Workbench to connect to your server through the Azure MariaDB service firewall.

以下示例创建名为 AllowMyIP 的防火墙规则,该规则允许从特定的 IP 地址 (192.168.0.1) 进行连接。The following example creates a firewall rule called AllowMyIP that allows connections from a specific IP address, 192.168.0.1. 替代与要从其进行连接的地址相对应的 IP 地址或 IP 地址范围。Substitute in the IP address or range of IP addresses that correspond to where you'll be connecting from.

az mariadb server firewall-rule create --resource-group myresourcegroup --server mydemoserver --name AllowMyIP --start-ip-address 192.168.0.1 --end-ip-address 192.168.0.1

获取连接信息Get the connection information

若要连接到服务器,需要提供主机信息和访问凭据。To connect to your server, you need to provide host information and access credentials.

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

结果采用 JSON 格式。The result is in JSON format. 记下 fullyQualifiedDomainName 和 administratorLogin。Make a note of the fullyQualifiedDomainName and administratorLogin.

{
  "administratorLogin": "myadmin",
  "administratorLoginPassword": null,
  "fullyQualifiedDomainName": "mydemoserver.mariadb.database.chinacloudapi.cn",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myresourcegroup/providers/Microsoft.DBforMariaDB/servers/mydemoserver",
  "location": "chinaeast2",
  "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.DBforMariaDB/servers",
  "userVisibleState": "Ready",
  "version": "10.2"
}

使用 mysql 连接服务器Connect to the server using mysql

使用 mysql 命令行工具建立与 Azure Database for MariaDB 服务器的连接。Use the mysql command-line tool to establish a connection to your Azure Database for MariaDB server. 在此示例中,该命令是:In this example, the command is:

mysql -h mydemoserver.database.chinacloudapi.cn -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

现已介绍了如何连接 Azure Database for MariaDB 数据库,接下来可以完成一些基本任务。Now that you know how to connect to the Azure Database for MariaDB 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;

将数据库还原到以前的时间点Restore a database to a previous point in time

假设意外删除了此表。Imagine you have accidentally deleted this table. 这是不能轻易还原的内容。This is something you cannot easily recover from. 借助 Azure Database for MariaDB,可返回到最近 35 天内的任意时间点并将此时间点还原到新的服务器。Azure Database for MariaDB allows you to go back to any point in time in the last up to 35 days and restore this point in time to a new server. 可以使用此新服务器恢复已删除的数据。You can use this new server to recover your deleted data. 以下步骤将示例服务器还原到添加此表之前的时间点。The following steps restore the sample server to a point before the table was added.

执行还原需要以下信息:For the restore, you need the following information:

  • 还原点:选择更改服务器前的时间点。Restore point: Select a point-in-time that occurs before the server was changed. 必须大于或等于源数据库的最早备份值。Must be greater than or equal to the source database's Oldest backup value.
  • 目标服务器:提供一个要还原到的新服务器名称Target server: Provide a new server name you want to restore to
  • 源服务器:提供想从中进行还原的服务器的名称Source server: Provide the name of the server you want to restore from
  • 位置:不能选择区域,此区域默认与源服务器相同Location: You cannot select the region, by default it is same as the source server
az mariadb server restore --resource-group myresourcegroup --name mydemoserver-restored --restore-point-in-time "2017-05-4 03:10" --source-server-name mydemoserver

az mariadb server restore 命令需以下参数:The az mariadb server restore command needs the following parameters:

设置Setting 建议的值Suggested value 说明Description  
resource-groupresource-group  myresourcegroupmyresourcegroup  源服务器所在的资源组。The resource group in which the source server exists. 
namename mydemoserver-restoredmydemoserver-restored 通过还原命令创建的新服务器的名称。The name of the new server that is created by the restore command.
restore-point-in-timerestore-point-in-time 2017-04-13T13:59:00Z2017-04-13T13:59:00Z 选择要还原到的时间点。Select a point-in-time to restore to. 此日期和时间必须在源服务器的备份保留期限内。This date and time must be within the source server's backup retention period. 使用 ISO8601 日期和时间格式。Use ISO8601 date and time format. 例如,可使用自己的本地时区(如 2017-04-13T05:59:00-08:00),或使用 UTC Zulu 格式 2017-04-13T13:59:00ZFor example, you may use your own local timezone, such as 2017-04-13T05:59:00-08:00, or use UTC Zulu format 2017-04-13T13:59:00Z.
source-serversource-server mydemoservermydemoserver 要从其还原的源服务器的名称或 ID。The name or ID of the source server to restore from.

将服务器还原到某个时间点会创建一个新服务器,该服务器是通过复制所指定的时间点之前那段时间的原始服务器而生成的。Restoring a server to a point-in-time creates a new server, copied as the original server as of the point in time you specify. 还原的服务器的位置值和定价层值与源服务器相同。The location and pricing tier values for the restored server are the same as the source server.

该命令是同步的,且会在服务器还原后返回。The command is synchronous, and will return after the server is restored. 还原完成后,找到创建的新服务器。Once the restore finishes, locate the new server that was created. 验证数据是否按预期还原。Verify the data was restored as expected.

后续步骤Next steps

本教程介绍了:In this tutorial you learned to:

  • 创建 Azure Database for MariaDB 服务器Create an Azure Database for MariaDB server
  • 配置服务器防火墙Configure the server firewall
  • 使用 mysql 命令行工具创建数据库Use mysql command-line tool to create a database
  • 加载示例数据Load sample data
  • 查询数据Query data
  • 更新数据Update data
  • 还原数据Restore data