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

Azure Database for MariaDB 是 Microsoft 云中基于 MariaDB 社区版数据库引擎的一种关系数据库服务。Azure Database for MariaDB is a relational database service in the Microsoft cloud based on MariaDB Community Edition database engine. 本教程介绍如何使用 PowerShell 和其他实用程序来执行以下操作:In this tutorial, you use PowerShell 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

先决条件Prerequisites

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

如果选择在本地使用 PowerShell,则本文要求安装 Az PowerShell 模块,并使用 Connect-AzAccount cmdlet 连接到 Azure 帐户。If you choose to use PowerShell locally, this article requires that you install the Az PowerShell module and connect to your Azure account using the Connect-AzAccount cmdlet. 有关安装 Az PowerShell 模块的详细信息,请参阅安装 Azure PowerShellFor more information about installing the Az PowerShell module, see Install Azure PowerShell.

重要

尽管 Az.MariaDb PowerShell 模块为预览版,但必须使用以下命令从 Az PowerShell 模块单独安装它:Install-Module -Name Az.MariaDb -AllowPrereleaseWhile the Az.MariaDb PowerShell module is in preview, you must install it separately from the Az PowerShell module using the following command: Install-Module -Name Az.MariaDb -AllowPrerelease.

如果这是你第一次使用 Azure Database for MariaDB 服务,必须注册 Microsoft.DBforMariaDB 资源提供程序。If this is your first time using the Azure Database for MariaDB service, you must register the Microsoft.DBforMariaDB resource provider.

Register-AzResourceProvider -ProviderNamespace Microsoft.DBforMariaDB

如果有多个 Azure 订阅,请选择应当计费的资源所在的相应订阅。If you have multiple Azure subscriptions, choose the appropriate subscription in which the resources should be billed. 使用 Set-AzContext cmdlet 选择特定的订阅 ID。Select a specific subscription ID using the Set-AzContext cmdlet.

Set-AzContext -SubscriptionId 00000000-0000-0000-0000-000000000000

创建资源组Create a resource group

使用 New-AzResourceGroup cmdlet 创建 Azure 资源组Create an Azure resource group using the New-AzResourceGroup cmdlet. 资源组是在其中以组的形式部署和管理 Azure 资源的逻辑容器。A resource group is a logical container in which Azure resources are deployed and managed as a group.

以下示例在“中国东部 2”区域创建名为“myresourcegroup”的资源组 。The following example creates a resource group named myresourcegroup in the China East 2 region.

New-AzResourceGroup -Name myresourcegroup -Location chinaeast2

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

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

以下示例使用服务器管理员登录名 myadmin 在“中国东部 2”区域中的“myresourcegroup”资源组内创建名为“mydemoserver”的 MariaDB 服务器 。The following example creates a MariaDB server in the China East 2 region named mydemoserver in the myresourcegroup resource group with a server admin login of myadmin. 此服务器是常规用途定价层中的第 5 代服务器,其中启用了 2 个 vCore 和异地冗余备份。It is a Gen 5 server in the general-purpose pricing tier with 2 vCores and geo-redundant backups enabled. 记下示例的第一行中使用的密码,因为这是 MariaDB 服务器管理员帐户的密码。Document the password used in the first line of the example as this is the password for the MariaDB server admin account.

提示

服务器名称映射到 DNS 名称,必须在 Azure 中全局唯一。A server name maps to a DNS name and must be globally unique in Azure.

$Password = Read-Host -Prompt 'Please enter your password' -AsSecureString
New-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup -Sku GP_Gen5_2 -GeoRedundantBackup Enabled -Location chinaeast2 -AdministratorUsername myadmin -AdministratorLoginPassword $Password

Sku 参数值遵循 pricing-tier_compute-generation_vCores 约定,如以下示例所示 。The Sku parameter value follows the convention pricing-tier_compute-generation_vCores as shown in the following examples.

  • -Sku B_Gen5_1 映射到基本、第 5 代和 1 个 vCore。-Sku B_Gen5_1 maps to Basic, Gen 5, and 1 vCore. 此选项是可用的最小 SKU。This option is the smallest SKU available.
  • -Sku GP_Gen5_32 映射到常规用途、第 5 层和 32 个 vCore。-Sku GP_Gen5_32 maps to General Purpose, Gen 5, and 32 vCores.
  • -Sku MO_Gen5_2 映射到内存优化、第 5 层和 2 个 vCore。-Sku MO_Gen5_2 maps to Memory Optimized, Gen 5, and 2 vCores.

有关各区域和层级的有效 Sku 值的信息,请参阅 Azure Database for MariaDB 定价层For information about valid Sku values by region and for tiers, see Azure Database for MariaDB pricing tiers.

如果轻量级计算和 I/O 足以满足工作负载要求,请考虑使用基本定价层。Consider using the basic pricing tier if light compute and I/O are adequate for your workload.

重要

在基本定价层中创建的服务器以后无法扩展到常规用途或内存优化层级,并且无法异地复制。Servers created in the basic pricing tier cannot be later scaled to general-purpose or memory- optimized and cannot be geo-replicated.

配置防火墙规则Configure a firewall rule

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

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

New-AzMariaDbFirewallRule -Name AllowMyIP -ResourceGroupName myresourcegroup -ServerName mydemoserver -StartIPAddress 192.168.0.1 -EndIPAddress 192.168.0.1

备注

连接到 Azure Database for MariaDB 时,经端口 3306 进行通信。Connections to Azure Database for MariaDB communicate over port 3306. 如果尝试从企业网络内部进行连接,则可能不允许经端口 3306 的出站流量。If you try to connect from within a corporate network, outbound traffic over port 3306 might not be allowed. 这种情况下,只有在 IT 部门打开了端口 3306 时,才能连接到服务器。In this scenario, you can only connect to the server if your IT department opens port 3306.

获取连接信息Get the connection information

若要连接到服务器,需要提供主机信息和访问凭据。To connect to your server, you need to provide host information and access credentials. 使用以下示例来确定连接信息。Use the following example to determine the connection information. 记下 FullyQualifiedDomainName 和 AdministratorLogin 的值。Make a note of the values for FullyQualifiedDomainName and AdministratorLogin.

Get-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup |
  Select-Object -Property FullyQualifiedDomainName, AdministratorLogin
FullyQualifiedDomainName                    AdministratorLogin
------------------------                    ------------------
mydemoserver.mariadb.database.chinacloudapi.cn       myadmin

使用 mysql 命令行工具连接到服务器Connect to the server using the mysql command-line tool

使用 mysql 命令行工具连接到服务器。Connect to your server using the mysql command-line tool. 若要下载并安装该命令行工具,请参阅 MariaDB 社区下载To download and install the command-line tool, see MariaDB Community Downloads.

mysql -h mydemoserver.mariadb.database.chinacloudapi.cn -u myadmin@mydemoserver -p

创建数据库Create a 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

可以将服务器还原到以前的某个时间点。You can restore the server to a previous point-in-time. 将还原的数据复制到新服务器,现有服务器保持不变。The restored data is copied to a new server, and the existing server is left unchanged. 例如,如果意外删除了某个表,可以还原到删除时的时间点。For example, if a table is accidentally dropped, you can restore to the time just the drop occurred. 然后可以从服务器的已还原副本中检索缺少的表和数据。Then, you can retrieve the missing table and data from the restored copy of the server.

若要还原服务器,请使用 Restore-AzMariaDbServer PowerShell cmdlet。To restore the server, use the Restore-AzMariaDbServer PowerShell cmdlet.

运行还原命令Run the restore command

若要还原服务器,请在 PowerShell 中运行以下示例。To restore the server, run the following example from PowerShell.

$restorePointInTime = (Get-Date).AddMinutes(-10)
Get-AzMariaDbServer -Name mydemoserver -ResourceGroupName myresourcegroup |
  Restore-AzMariaDbServer -Name mydemoserver-restored -ResourceGroupName myresourcegroup -RestorePointInTime $restorePointInTime -UsePointInTimeRestore

将服务器还原到以前的某个时间点时,会创建新服务器。When you restore a server to an earlier point-in-time, a new server is created. 原始服务器及其从指定时间点开始创建的数据库会复制到新服务器。The original server and its databases from the specified point-in-time are copied to the new server.

还原的服务器的位置值和定价层值与原始服务器保持相同。The location and pricing tier values for the restored server remain the same as the original server.

还原过程完成后,找到新服务器,验证数据是否已按预期还原。After the restore process finishes, locate the new server and verify that the data is restored as expected. 新服务器具有相同的服务器管理员登录名和密码,该登录名和密码在开始还原时对现有服务器有效。The new server has the same server admin login name and password that was valid for the existing server at the time the restore was started. 可以从新服务器的“概述”页更改密码。The password can be changed from the new server's Overview page.

还原期间创建的新服务器没有原始服务器上存在的 VNet 服务终结点。The new server created during a restore does not have the VNet service endpoints that existed on the original server. 必须单独为新服务器设置这些规则。These rules must be set up separately for the new server. 将还原原始服务器中的防火墙规则。Firewall rules from the original server are restored.

后续步骤Next steps