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'
- Table 1 lists the required permissions for real-time migration, synchronization, and DR.
Run flush privileges; after executing the preceding reference statements. Make the authorization take effect.
- Account migration:
The user must have the SELECT permission of mysql.user if the source database is a non-Alibaba Cloud database. If the source database is an Alibaba Cloud database, the account must have the SELECT permission of mysql.user and mysql.user_view.
Reference statement:
GRANT SELECT ON mysql.user 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
Actions
- Create a user.
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.
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'@'%';
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.