如何为运行 MySQL 的 Amazon RDS 数据库实例创建另一个主用户?

上次更新时间:2020 年 4 月 6 日

我希望为我的 Amazon Relational Database Service (Amazon RDS) 或 Amazon Aurora MySQL 数据库实例创建另一个与主用户拥有相同权限的用户。如何复制或克隆主用户?

解决方法

运行 MySQL 的 RDS 数据库实例只能有一个主用户,但可以创建一个与主用户拥有相同权限的新用户。要创建拥有主用户权限的新用户,请按照以下步骤操作:

1.    运行 SHOW GRANTS 命令以获取主用户当前可用的权限列表,然后复制此权限列表,以供后续使用:

mysql> SHOW GRANTS for master_username;

此命令会提供与以下类似的输出:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for master_user@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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, LOAD FROM S3, SELECT INTO S3, INVOKE LAMBDA, INVOKE SAGEMAKER, INVOKE COMPREHEND ON *.* TO 'master_user'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

注意:主用户的用户名为 master_user

2.    通过运行 CREATE USER 命令创建新用户:

mysql> CREATE USER 'new_master_user'@'%' IDENTIFIED BY 'password';

注意:请将 new_master_userpassword 替换为您的用户名和密码。

3.    运行 GRANT 命令以向该新用户授予您在第 1 步中获得的权限列表:

mysql> 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;

该新用户现在已拥有与主用户相同的权限。