教程:使用 Azure 门户设计 Azure Database for MySQL 数据库Tutorial: Design an Azure Database for MySQL database using the Azure portal

Note

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

Azure Database for MySQL 是一种托管服务,可用于在云中运行、管理和缩放高可用性的 MySQL 数据库。Azure Database for MySQL is a managed service that enables you to run, manage, and scale highly available MySQL databases in the cloud. 使用 Azure 门户可以轻松管理服务器和设计数据库。Using the Azure portal, you can easily manage your server and design a database.

本教程介绍如何使用 Azure 门户完成以下操作:In this tutorial, you use the Azure portal to learn how to:

  • 创建 Azure Database for MySQLCreate an Azure Database for MySQL
  • 配置服务器防火墙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 门户Sign in to the Azure portal

打开最喜爱的 Web 浏览器,然后访问 Azure 门户Open your favorite web browser, and visit the Azure portal. 输入登录到门户所需的凭据。Enter your credentials to sign in to the portal. 默认视图是服务仪表板。The default view is your service dashboard.

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

创建 Azure Database for MySQL 服务器时,会使用定义好的一组计算和存储资源An Azure Database for MySQL server is created with a defined set of compute and storage resources. 将在 Azure 资源组中创建服务器。The server is created within an Azure resource group.

  1. 导航到数据库 > MySQL 的 Azure 数据库Navigate to Databases > Azure Database for MySQL. 如果在“数据库” 类别下找不到 MySQL 服务器,请单击“查看全部” 显示所有可用的数据库服务。If you cannot find MySQL Server under Databases category, click See all to show all available database services. 还可以在搜索框中键入“Azure Database for MySQL” 快速查找该服务。You can also type Azure Database for MySQL in the search box to quickly find the service.

    导航到 MySQL

  2. 单击“Azure Database for MySQL” 磁贴,然后单击“创建” 。Click Azure Database for MySQL tile, and then click Create. 填写 Azure Database for MySQL 窗体。Fill out the Azure Database for MySQL form.

    创建窗体

    设置Setting 建议的值Suggested value 字段说明Field description
    服务器名称Server name 唯一的服务器名称Unique server name 选择用于标识 Azure Database for MySQL 服务器的唯一名称。Choose a unique name that identifies your Azure Database for MySQL server. 例如,mydemoserver。For example, mydemoserver. 域名 .mysql.database.chinacloudapi.cn 追加到所提供的服务器名称后面。The domain name .mysql.database.chinacloudapi.cn is appended to the server name you provide. 服务器名称只能包含小写字母、数字和连字符 (-) 字符。The server name can contain only lowercase letters, numbers, and the hyphen (-) character. 必须包含 3 到 63 个字符。It must contain from 3 to 63 characters.
    订阅Subscription 你的订阅Your subscription 选择要用于服务器的 Azure 订阅。Select the Azure subscription that you want to use for your server. 如果有多个订阅,请选择要计费的资源所在的订阅。If you have multiple subscriptions, choose the subscription in which you get billed for the resource.
    资源组Resource group myresourcegroupmyresourcegroup 提供新的或现有的资源组名称。Provide a new or existing resource group name.
    选择源Select source 空白Blank 选择“空白” 可从头开始创建新服务器。Select Blank to create a new server from scratch. (如果要从现有 Azure Database for MySQL 服务器的异地备份创建服务器,请选择“备份” )。(You select Backup if you are creating a server from a geo-backup of an existing Azure Database for MySQL server).
    服务器管理员登录名Server admin login myadminmyadmin 连接到服务器时需使用的登录帐户。A sign-in account to use when you're connecting to the server. 管理员登录名不能是“azure_superuser”、“admin”、“administrator”、“root”、“guest”或“public”。 The admin sign-in name cannot be azure_superuser, admin, administrator, root, guest, or public.
    密码Password 由用户决定Your choice 为服务器管理员帐户提供新密码。Provide a new password for the server admin account. 必须包含 8 到 128 个字符。It must contain from 8 to 128 characters. 密码必须包含以下三个类别的字符:英文大写字母、英文小写字母、数字 (0-9)和非字母数字字符(!, $, #, % 等)。Your password must contain characters from three of the following categories: English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on).
    确认密码Confirm password 由用户决定Your choice 确认管理员帐户密码。Confirm the admin account password.
    位置Location 离用户最近的区域The region closest to your users 选择最靠近用户或其他 Azure 应用程序的位置。Choose the location that is closest to your users or your other Azure applications.
    版本Version 最新版本The latest version 最新版本,有特定要求(即需要其他版本)的除外。The latest version (unless you have specific requirements that require another version).
    定价层Pricing tier 常规用途第 5 代2 vCore5 GB7 天异地冗余General Purpose, Gen 5, 2 vCores, 5 GB, 7 days, Geographically Redundant 新服务器的计算、存储和备份配置。The compute, storage, and backup configurations for your new server. 选择“定价层”。 Select Pricing tier. 接下来,选择“常规用途” 选项卡。“第 5 代”、“2 vCore”、“5 GB”和“7 天”分别是“计算代”、“vCore”、“存储”和“备份保留期”的默认值。 Next, select the General Purpose tab. Gen 5, 2 vCores, 5 GB, and 7 days are the default values for Compute Generation, vCore, Storage, and Backup Retention Period. 可以将这些滑块保留原样。You can leave those sliders as is. 若要在异地冗余存储中启用服务器备份,请从备份冗余选项中选择“异地冗余” 。To enable your server backups in geo-redundant storage, select Geographically Redundant from the Backup Redundancy Options. 若要保存此定价层选择,请选择“确定” 。To save this pricing tier selection, select OK. 下一个屏幕截图捕获了这些选择。The next screenshot captures these selections.

    定价层

    Tip

    启用“自动增长” 后,当接近分配的限制时,服务器会增加存储空间,而不会影响工作负荷。With auto-growth enabled your server increases storage when you are approaching the allocated limit, without impacting your workload.

  3. 单击创建Click Create. 一两分钟后,新 Azure Database for MySQL 服务器将在云中运行。In a minute or two, a new Azure Database for MySQL server is running in the cloud. 可单击工具栏上的“通知” 按钮来监视部署过程。You can click Notifications button on the toolbar to monitor the deployment process.

配置防火墙Configure firewall

Azure Databases for MySQL 受防火墙保护。Azure Databases for MySQL are protected by a firewall. 默认情况下,会拒绝与服务器和服务器内数据库的所有连接。By default, all connections to the server and the databases inside the server are rejected. 首次连接到 Azure Database for MySQL 之前,请配置防火墙以添加客户端计算机的公共网络 IP 地址(或 IP 地址范围)。Before connecting to Azure Database for MySQL for the first time, configure the firewall to add the client machine's public network IP address (or IP address range).

  1. 单击新创建的服务器,并单击“连接安全性” 。Click your newly created server, and then click Connection security.

    连接安全性

  2. 可以“添加我的 IP” 或在此处配置防火墙规则。You can Add My IP, or configure firewall rules here. 创建规则后请记得单击“保存” 。Remember to click Save after you have created the rules. 现在可以使用 mysql 命令行工具或 MySQL Workbench GUI 工具连接服务器。You can now connect to the server using mysql command-line tool or MySQL Workbench GUI tool.

Tip

Azure Database for MySQL 通过端口 3306 进行通信。Azure Database for MySQL server communicates over port 3306. 如果尝试从企业网络内部进行连接,则该网络的防火墙可能不允许经端口 3306 的出站流量。If you are trying to connect from within a corporate network, outbound traffic over port 3306 may not be allowed by your network's firewall. 如果是这样,则无法连接到 Azure MySQL 服务器,除非 IT 部门打开了端口 3306。If so, you cannot connect to Azure MySQL server unless your IT department opens port 3306.

获取连接信息Get connection information

从 Azure 门户获取 Azure Database for MySQL 服务器的完全限定服务器名称服务器管理员登录名Get the fully qualified Server name and Server admin login name for your Azure Database for MySQL server from the Azure portal. 使用 mysql 命令行工具通过完全限定的服务器名称连接到服务器。You use the fully qualified server name to connect to your server using mysql command-line tool.

  1. Azure 门户中,单击左侧菜单中的“所有资源” ,键入名称,然后搜索“Azure Database for MySQL 服务器”。In Azure portal, click All resources from the left-hand menu, type the name, and search for your Azure Database for MySQL server. 选择服务器名称以查看详细信息。Select the server name to view the details.

  2. 在“概述” 页上,记下服务器名称服务器管理员登录名From the Overview page, note down Server Name and Server admin login name. 可以单击每个字段旁边的“复制”按钮,将其复制到剪贴板。You may click the copy button next to each field to copy to the clipboard. 4-2 服务器属性4-2 server properties

在此示例中,服务器名称是 mydemoserver.mysql.database.chinacloudapi.cn,服务器管理员登录名是 myadmin@mydemoserverIn this example, the server name is mydemoserver.mysql.database.chinacloudapi.cn, and the server admin login is myadmin@mydemoserver.

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

使用 mysql 命令行工具建立与 Azure Database for MySQL 服务器的连接。Use mysql command-line tool to establish a connection to your Azure Database for MySQL server.

键入命令以建立连接:Type the command to connect:

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

创建空数据库Create a blank database

连接到服务器后,立即创建一个要使用的空数据库。Once you're connected to the server, create a blank database to work with.

CREATE DATABASE mysampledb;

出现提示时,请运行以下命令,切换为连接此新建的数据库:At the prompt, run the following command to switch connection to this newly created database:

USE mysampledb;

在数据库中创建表Create tables in the database

现已介绍了如何连接 Azure Database for MySQL 数据库,接下来你可以完成一些基本任务:Now that you know how to connect to the Azure Database for MySQL database, you can 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 an important database table, and cannot recover the data easily. 使用 Azure Database for MySQL 可以将服务器还原到某个时间点,并在新服务器中创建数据库的副本。Azure Database for MySQL allows you to restore the server to a point in time, creating a copy of the databases into 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.

  1. 在 Azure 门户中,找到 Azure Database for MySQL。In the Azure portal, locate your Azure Database for MySQL. 在“概述” 页上,单击工具栏上的“还原” 。On the Overview page, click Restore on the toolbar. 此时将打开“还原”页。The Restore page opens.

    10-1 还原数据库

  2. 使用必需信息填充“还原” 窗体。Fill out the Restore form with the required information.

    10-2 还原窗体

    • 还原点:在列出的时间范围内选择要还原到的时间点。Restore point: Select a point-in-time that you want to restore to, within the timeframe listed. 请确保将本地时区转换为 UTC。Make sure to convert your local timezone to UTC.
    • 还原到新服务器:提供一个要还原到的新服务器名称。Restore to new server: Provide a new server name you want to restore to.
    • 位置:该区域与源服务器相同,不能更改。Location: The region is same as the source server, and cannot be changed.
    • 定价层:定价层与源服务器相同,不能更改。Pricing tier: The pricing tier is the same as the source server, and cannot be changed.
  3. 单击“确定” ,将服务器还原到删除该表之前的时间点Click OK to restore the server to restore to a point in time before the table was deleted. 还原服务器时将创建服务器的新副本(从指定的时间点开始)。Restoring a server creates a new copy of the server, as of the point in time you specify.

后续步骤Next steps

本教程介绍如何使用 Azure 门户完成以下操作:In this tutorial, you use the Azure portal to learned how to:

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