Create master account for MySQL 5.5/5.6

Last Updated: Dec 12, 2017

ApsaraDB for MySQL allows you to create master account. You can directly run 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 cannot be deleted and it is no longer possible to create any databases and/or other accounts. However, it is 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, see Commonly used SQL commands (MySQL).

For the list of permissions supported by the master account, see the following Permission List of master Accounts.

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

The following changes 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 run the corresponding command directly on the instance to manage it.

    The Account Management and Database Management pages on the console 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 is no longer provided and it is 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 cannot 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 must 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 is restarted once the master account has been created and there is a transient network disconnection for 30 seconds. Make sure that this is done at a convenient time and the application supports database reconnection.

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, enter the ticket information, and click Submit, as shown in the following figure.

    Ticket Application

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

  4. Enter 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 takes place during the process. Make sure that all the affected applications have an automatic reconnection policy. After an account is created, the account name cannot be modified, but the password can be changed later by 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.