Azure mysql内网_创建数据库和用户 - Azure Database for MySQL | Azure Docs

在 Azure Database for MySQL 中创建数据库和用户Create databases and users in Azure Database for MySQL

10/29/2020

本文内容

备注

将要查看的是 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 服务器中创建用户。This article describes how you can create users in an Azure Database for MySQL server.

首次创建 Azure Database for MySQL 服务器时，需要提供服务器管理员用户名和密码。When you first created your Azure Database for MySQL server, you provided a server admin user name and password. 有关详细信息，请参阅此快速入门。For more information, see this Quickstart. 你可以在 Azure 门户中确定你的服务器管理员用户名。You can determine your server admin user name in the Azure portal.

服务器管理员用户具有以下权限：The server admin user has these privileges:

选择、插入、更新、删除、创建、放置、重载、处理、引用、索引、更改、显示数据库、创建临时表、锁定表、执行、复制从属、复制客户端、创建视图、显示视图、创建例程、更改例程、创建用户、事件、触发器SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER

创建 Azure Database for MySQL 服务器后，你可以使用第一个服务器管理员帐户来创建其他用户，并授予这些用户管理员访问权限。After you create an Azure Database for MySQL server, you can use the first server admin account to create additional users and grant admin access to them. 你还可以使用服务器管理员帐户来创建只能访问各个数据库架构的权限较低的用户。You can also use the server admin account to create less privileged users that have access to individual database schemas.

备注

不支持 SUPER 权限和 DBA 角色。The SUPER privilege and DBA role aren't supported. 请在“限制”一文中查看权限，以了解服务中不支持的权限。Review the privileges in the limitations article to understand what's not supported in the service.

服务器不支持 validate_password 和 caching_sha2_password 等密码插件。Password plugins like validate_password and caching_sha2_password aren't supported by the service.

若要在 Azure Database for MySQL 中使用非管理员用户创建数据库To create a database with a non-admin user in Azure Database for MySQL

获取连接信息和管理员用户名。Get the connection information and admin user name.

若要连接到数据库服务器，需提供完整的服务器名称和管理员登录凭据。To connect to your database server, you need the full server name and admin sign-in credentials. 可以在 Azure 门户的服务器“概览”页或“属性”页中轻松地找到服务器名称和登录信息 。You can easily find the server name and sign-in information on the server Overview page or on the Properties page in the Azure portal.

使用管理员帐户和密码连接到你的数据库服务器。Use the admin account and password to connect to your database server. 使用你的首选客户端工具，如 MySQL Workbench、mysql.exe 或 HeidiSQL。Use your preferred client tool, such as MySQL Workbench, mysql.exe, or HeidiSQL.

如果你不确定如何连接，请参阅连接和查询单一服务器的数据。

编辑并运行下面的 SQL 代码。Edit and run the following SQL code. 将占位符 db_user 替换为所需的新用户名。Replace the placeholder value db_user with your intended new user name. 将占位符 testdb 替换为数据库名称。Replace the placeholder value testdb with your database name.

此 SQL 代码创建名为 testdb 的新数据库。This SQL code creates a new database named testdb. 然后，它在 MySQL 服务中创建新用户，并将所有权限授予该用户的新数据库架构 (testdb.*)。It then creates a new user in the MySQL service and grants all privileges for the new database schema (testdb.*) to that user.

CREATE DATABASE testdb;

CREATE USER 'db_user'@'%' IDENTIFIED BY 'StrongPassword!';

GRANT ALL PRIVILEGES ON testdb . * TO 'db_user'@'%';

FLUSH PRIVILEGES;

验证数据库中的授予：Verify the grants in the database:

USE testdb;

SHOW GRANTS FOR 'db_user'@'%';

登录到服务器，指定选定的数据库并使用新用户名和密码。Sign in to the server, specifying the designated database and using the new user name and password. 此示例显示了 mysql 命令行。This example shows the mysql command line. 使用此命令时，系统会提示你输入用户的密码。When you use this command, you'll be prompted for the user's password. 使用你自己的服务器名称、数据库名称和用户名。Use your own server name, database name, and user name.

mysql --host mydemoserver.mysql.database.chinacloudapi.cn --database testdb --user db_user@mydemoserver -p

若要在 Azure Database for MySQL 中创建其他管理员用户To create additional admin users in Azure Database for MySQL

获取连接信息和管理员用户名。Get the connection information and admin user name.

若要连接到数据库服务器，需提供完整的服务器名称和管理员登录凭据。To connect to your database server, you need the full server name and admin sign-in credentials. 可以在 Azure 门户的服务器“概览”页或“属性”页中轻松地找到服务器名称和登录信息 。You can easily find the server name and sign-in information on the server Overview page or on the Properties page in the Azure portal.

使用管理员帐户和密码连接到你的数据库服务器。Use the admin account and password to connect to your database server. 使用你的首选客户端工具，如 MySQL Workbench、mysql.exe 或 HeidiSQL。Use your preferred client tool, such as MySQL Workbench, mysql.exe, or HeidiSQL.

编辑并运行下面的 SQL 代码。Edit and run the following SQL code. 将占位符 new_master_user 替换为新用户名。Replace the placeholder value new_master_user with your new user name. 此语法会将所有数据库架构 ( . ) 上列出的权限授予该用户名(本示例中的 new_master_user)。This syntax grants the listed privileges on all the database schemas ( . ) to the user (new_master_user in this example).

CREATE USER 'new_master_user'@'%' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'new_master_user'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

验证授予：Verify the grants:

USE sys;

SHOW GRANTS FOR 'new_master_user'@'%';

azure_superuserazure_superuser

所有 Azure Database for MySQL 服务器都是使用名为“azure_superuser”的用户创建的。All Azure Database for MySQL servers are created with a user called "azure_superuser". 这是 Azure 创建的系统帐户，用于管理服务器以执行监视、备份和其他定期维护。This is a system account created by Azure to manage the server to conduct monitoring, backups, and other regular maintenance. 待命工程师还可以使用此帐户在发生证书身份验证事件期间访问服务器，并且必须使用实时 (JIT) 进程请求访问。On-call engineers may also use this account to access the server during an incident with certificate authentication and must request access using just-in-time (JIT) processes.

后续步骤Next steps

针对新用户计算机的 IP 地址打开防火墙，使其能够连接：Open the firewall for the IP addresses of the new users' machines to enable them to connect:

有关用户帐户管理的详细信息，请参阅 MySQL 产品文档，了解用户帐户管理、GRANT 语法和权限。For more information about user account management, see the MySQL product documentation for User account management, GRANT syntax, and Privileges.