Help Center/ Data Replication Service/ FAQs/ Permissions Management/ Which MySQL Permissions Are Required for DRS?
Updated on 2024-11-30 GMT+08:00
View PDF
Share

Which MySQL Permissions Are Required for DRS?

DRS has certain permission requirements on accounts during migration, synchronization, and DR. This section describes the permission requirements on the MySQL engine.

Permission

  • You must have the login permission of the source and destination database connection accounts. If you do not have the account, perform the following operations to create one. user1 is used as an example.

    Reference statement: CREATE USER 'user1'@'host' IDENTIFIED BY 'password'

  • The following table uses user1 as an example and lists the permissions required in DRS online migration, data synchronization, and disaster recovery.
    Table 1 Permission requirements and reference statements

    Function Modules

    Source/Service Database

    Destination/DR Database

    Real-time migration

    Full migration:

    SELECT, SHOW VIEW, and EVENT

    Reference statement: GRANT SELECT, SHOW VIEW, EVENT ON *.* TO 'user1';

    Full+incremental migration:

    SELECT, SHOW VIEW, EVENT, LOCK TABLES, REPLICATION SLAVE, and REPLICATION CLIENT

    • REPLICATION SLAVE and REPLICATION CLIENT are global permissions and must be enabled separately. The reference statement is as follows:

      GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user1';

    • SELECT, SHOW VIEW, EVENT, and LOCK TABLES are non-global permissions. The reference statement is as follows:

      GRANT SELECT, SHOW VIEW, EVENT, LOCK TABLES, ON [Database to be migrated].* TO 'user1';

    • If the service database version is later than 8.0.2, the XA_RECOVER_ADMIN permission is also required.

    Full migration:

    SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER, REFERENCES, and WITH GRANT OPTION. If the destination database version is in the range 8.0.14 to 8.0.18, the SESSION_VARIABLES_ADMIN permission is required.

    Reference statement: GRANT SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER ON *.* TO 'user1' WITH GRANT OPTION;

    Full+incremental migration:

    SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER, REFERENCES, and WITH GRANT OPTION. If the destination database version is in the range 8.0.14 to 8.0.18, the SESSION_VARIABLES_ADMIN permission is required.

    Reference statement: GRANTSELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER, REFERENCES ON [Databases to be migrated].* TO 'user1' WITH GRANT OPTION;

    Real-time synchronization

    SELECT, SHOW VIEW, EVENT, LOCK TABLES, REPLICATION SLAVE, and REPLICATION CLIENT For a full+incremental task, if the source database version is 8.0.2 or later, the XA_RECOVER_ADMIN permission is required to prevent data loss caused by uncommitted XA transactions during startup or task editing.

    • REPLICATION SLAVE and REPLICATION CLIENT are global permissions and must be enabled separately. The reference statement is as follows:

      GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user1';

    • SELECT, SHOW VIEW, EVENT, and LOCK TABLES are non-global permissions. The reference statement is as follows:

      GRANT SELECT, SHOW VIEW, EVENT, LOCK TABLES, ON [Database to be synchronized].* TO 'user1';

    • If the service database version is later than 8.0.2, the XA_RECOVER_ADMIN permission is also required.

    SELECT, CREATE, DROP, DELETE, INSERT, UPDATE, ALTER, CREATE VIEW, CREATE ROUTINE, and REFERENCES

    Reference statement: GRANT SELECT, CREATE, DROP, DELETE, INSERT, UPDATE, ALTER, REFERENCES ON [Databases to be migrated].* TO 'user1';

    Real-time disaster recovery

    SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, TRIGGER, REFERENCES, SHOW VIEW, EVENT, INDEX, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, and WITH GRANT OPTION. The user root of the RDS for MySQL instance has the preceding permissions by default. If the service database version is 8.0.14 to 8.0.18, the SESSION_VARIABLES_ADMIN permission is required. If the service database version is 8.0.2 or later, the XA_RECOVER_ADMIN permission is required to prevent data loss caused by uncommitted XA transactions during startup. The root account of the RDS for MySQL DB instance has the preceding permissions by default.

    If the service database version is later than 8.0.2, the XA_RECOVER_ADMIN permission is also required.

    Reference statements: GRANT SELECT,CREATE,ALTER,DROP,DELETE,INSERT,UPDATE,TRIGGER,SHOW VIEW,EVENT,INDEX,LOCK TABLES,CREATE VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,RELOAD,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'user1';

    SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, TRIGGER, REFERENCES, SHOW VIEW, EVENT, INDEX, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, and WITH GRANT OPTION. The user root of the RDS for MySQL instance has the preceding permissions by default. If the DR database version is 8.0.14 to 8.0.18, the SESSION_VARIABLES_ADMIN permission is required.

    Reference statements: GRANT SELECT,CREATE,ALTER,DROP,DELETE,INSERT,UPDATE,TRIGGER,REFERENCES,SHOW VIEW,EVENT,INDEX,LOCK TABLES,CREATE VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,RELOAD,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'user1'@'%' WITH GRANT OPTION;

    Run flush privileges; after executing the preceding reference statements. Make the authorization take effect.

  • Account migration:

    If the source database version is 8.0, the user must have the SELECT permission for the mysql.user table. If the source database version is 5.7 or earlier, the user must have the SELECT permission for the MySQL system database. If the source database is a Alibaba Cloud database, the user must have the SELECT permission for both mysql.user and mysql.user_view.

    Reference statement:

    GRANT SELECT ON mysql.user TO 'user1'@'host' ;

    GRANT SELECT ON mysql.* TO 'user1'@'host' ;

    GRANT SELECT ON mysql.user_view TO 'user1';

    The destination database users must have the SELECT, INSERT, UPDATE, DELETE, and WITH GRANT OPTION permissions on all databases.

    Reference statement: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user1' WITH GRANT OPTION

  • Function and stored procedure migration

    If the source database is MySQL 5.6 or 5.7 and you need to migrate functions and stored procedures, you must have the SELECT permission on the proc table in the MySQL system database.

    Reference statement:

    GRANT SELECT ON `mysql`.`proc` TO 'user1'@'host';

    If the source database is MySQL 8.0 or later and you need to migrate functions and stored procedures, you must have the SELECT permission on the entire DB instance. For MySQL 8.0.20 or later, you can also use the SHOW_ROUTINE permission to migrate functions and stored procedures. Reference statement:

    GRANT SELECT ON *.* TO 'user1'@'host';

    Or:

    GRANT SHOW_ROUTINE ON *.* TO 'user1'@'host';

    Reference: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-routine

Actions

  • Create a user.

    Operation:

    CREATE USER 'username'@'host' IDENTIFIED BY 'password';

    · username: indicates the account to be created.

    · host: indicates the host that allows the account to log in. If the account is allowed to log in to the database from any host, use %.

    · password: indicates the password of the account.

    For example, run the following command to grant the drsmigration account with all permissions on all databases and tables and allow the drsmigration account to log in to the database from any host:

    CREATE USER 'drsmigration'@'%' IDENTIFIED BY 'Drs123456';

  • Grant corresponding permissions.

    Operation:

    GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

    flush privileges;

    · privileges: indicates the operation permissions granted to the account, such as SELECT, INSERT, and UPDATE. To grant all permissions to the account, use ALL.

    · databasename: indicates the database name. To grant the account with all database operation permissions, use *.

    · tablename: indicates table name. To grant the account with all table operation permissions, use *.

    · username: indicates the account to be authorized.

    · host: indicates the host that allows the account to log in. If the account is allowed to log in from any host, use %.

    · WITH GRANT OPTION: indicates that the permission to use the GRANT command is granted to the account. This parameter is optional.

    For example, run the following command to create an account drsmigration with the password Drs123456 and allow the account to log in to the database from any host:

    GRANT ALL ON *.* TO 'drsmigration'@'%';

Parent topic: Permissions Management