Create master account for MySQL 5.5/5.6

Last Updated: Oct 16, 2017

ApsaraDB for MySQL allows you to create master account. You can directly execute the create, drop, grant, and other commands on the instance to perform management operations more conveniently.

Usage instructions

Currently, only ApsaraDB for MySQL allows you to create master accounts. Moreover, only the MySQL 5.5 and MySQL 5.6 versions are supported.

Only one master account can be created for each instance. This account can not be deleted and it will no longer be possible to create any databases and/or other accounts. However, it will be possible to run SQL commands that create databases and accounts. Therefore, proceed with caution when carrying out these operations.

For the list of SQL commands used for creating databases and accounts, refer to Commonly used SQL commands (MySQL).

For the list of permissions supported by the master account, please refer to Permission List of master Accounts provided below.

Once a master account has been created on the primary instance, it will be synchronized to the read-only instance and disaster recovery instance.

The following changes will occur after the system switches to the master account mode:

  • Databases and accounts cannot be managed through the RDS console or the API. You can execute the corresponding command directly on the instance to manage it.

    The Account Management and Database Management pages on the console will become invisible. Keep in mind that the application must be updated accordingly when changes to database and/or accounts have been made.

  • MySQL single database backup function will no longer be provided and it will be necessary to backup the database manually.

  • The accounts created can be seen by executing the command show grants for xxx.

  • With MySQL5.5/5.6, the mysql.user and mysql.db tables cannot be accessed directly, but the existing account and permission can be viewed through mysql.user_view and mysql.db_view. MySQL5.7 without this restriction.

  • Global variables can not be changed.

  • When creating another account, you can assign permissions using a method similar to grant select on test.* to user01@’%’ identified by ‘user01password’;. To change the password, grant permissions again after Drop user user01;.When creating another account, permissions can be assigned using statements like GRANT SELECT ON test to user01@'%' IDENTIFIED BY 'user01password';. If you need to change the permissions or password later use the DROP USER user01; statement to clear the existing setup and then run the granting statements.

  • The permission and password for a master account can be reset through the console or the API. Other accounts already created in the instance are not affected.

  • The instance will be restarted once the master account has been created and there will be a transient network disconnection for 30 seconds. Make sure that this is done at a convenient time and ensure that the application supports database reconnection.

Operation procedure

Note: The master account is currently available upon submitting a ticket and getting it approved.

  1. Log on to RDS Console, and select Technical Support.

  2. Select Open a new ticket in Support Center, fill in the ticket information, and click Submit, as shown in the figure below.

    Ticket Application

  3. After the ticket is processed, the Create a master Account button appears on the console. Click this button.

  4. Fill in the master account information, and click Confirm Creation.

    Note: It takes about 3 to 5 minutes to create an account. A transient disconnection of the instance will take place during the process. Make sure that all the affected applications have an automatic reconnection mechanism. After an account is created, the account name cannot be modified, but the password can be changed later via the console.

Permission list

Permission Y/N
alter Y
Alter_routine Y
create Y
Create_routine Y
Create_tem_table Y
Create_user Y
Create view Y
delete Y
drop Y
ececute Y
event Y
grant Partially supported
index Y
insert Y
Lock_tables Y
process Y
reload Y
Repl_client Y
Repl_slave Y
Select Y
trigger Y
update Y
Thank you! We've received your feedback.