This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
Migrating users from one database platform to another can be a challenging task. In this article, we will discuss the process of migrating users from AWS RDS MySQL or AWS RDS Aurora MySQL to Azure Database for MySQL - Flexible Server. We will cover the steps involved in exporting user data from AWS RDS MySQL by Percona Toolkit, which is a is a collection of advanced open-source command-line tools supporting MySQL and other open-source databases. Then we could import the users and privileges output statements into Azure Database for MySQL, as well as any considerations and best practices to keep in mind during the migration process. By the end of this article, you will have a clear understanding of how to successfully migrate users from AWS MySQL to Azure Database for MySQL. Let’s get started!
Users and privileges in MySQL
In MySQL, the user is the secure object that can connect to the MySQL server, and user privileges determine the level of access and actions that a user can perform on a database or table. Proper management of user privileges is essential for maintaining security, data integrity, and preventing unauthorized access.
The default authentication plugin for MySQL 5.6/5.7 is mysql_native_password, which relies on the SHA1 algorithm. The sha256_password authentication plugin is supported starting with MySQL 5.6. It uses salted passwords for multi-round SHA-256 hashing to ensure more secure hash value conversion. Starting with MySQL 8, a new authentication plugin, caching_sha2_password, is introduced and it becomes the new default authentication plugin for the MySQL server. caching_sha2_password adopts the same SHA-256 authentication as sha256_password plugin but has a better performance because it uses caching on the server side for faster re-authentication.
Migrating users and privileges from AWS RDS MySQL involves a six stage process:
- Verifying existing users and privileges.
- Installing Percona Toolkit to leverage the pt-show-grants tool to retrieve the users and privileges you want to migrate to Azure.
- Exporting users and privileges from AWS RDS MySQL by pt-show-grants.
- Examine the exported file to ensuring that the users are all ready to migrate.
- Importing users and privileges to Azure Database for MySQL with mysql command tool.
- Validating the users and privileges imported to Azure Database for MySQL.
In this example, I will choose AWS RDS MySQL 8 as the source server and migrate users with three authentication solution (mysql_native_password, sha256_password and caching_sha2_password) which we will be mostly encounter in the real migration case.
Verify existing users and privileges in AWS RDS MySQL
In this example, we have already had some existing users other than the default system users in MySQL database, we could get the user details in table user locating in schema mysql. Here I filter the default system users which we are not going to migrate to Azure Database for MySQL.
mysql> select user,host,plugin,authentication_string,max_questions,password_expired,password_lifetime,account_locked from mysql.user where user in ('developer', 'dba', 'audit') \G
After running the command above, we can see the existing user information displayed in console:
*************************** 1. row ***************************
user: audit
host: %
plugin: sha256_password
authentication_string: $5$#P!*e}]M26%wl!7X
$Ge6M.jXQzUEJwVImOGABMGGWJot2Les8.PXOWtLSJ9C
max_questions: 60
password_expired: N
password_lifetime: NULL
account_locked: Y
*************************** 2. row ***************************
user: dba
host: %
plugin: caching_sha2_password
authentication_string: $A$005? W'I> JLC.jmMVmbORZeZW.mPm3FXDp/xM8.QYYwIdEMdhSX3
max_questions: 0
password_expired: N
password_lifetime: 180
account_locked: N
*************************** 3. row ***************************
user: developer
host: %
plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
max_questions: 0
password_expired: N
password_lifetime: 180
account_locked: N
3 rows in set (0.00 sec)
We are going to migrate three users dba, developer and audit user with different authentication plugin. For user developer who are using mysql_native_password plugin, we could notice that the authentication string is in hashed format, which is the output from function PASSWORD() and the function generates a hashed password from a plain-text password string. In addition, for user dba and audit, they are using sha256_password and caching_sha2_password plugin to encrypt the password string into binary display format, which contains unprintable characters that have adverse effects on terminal displays.
To display the privileges for a specific user, in mysql command-line tool, you can run the following command:
show grants for '<user>'@'<host>';
For example, I’ll verify permissions for various users to any host in the following sections.
Developer
show grants for 'developer'@'%';
+-------------------------------------------------------------------------+
| Grants for developer@% |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'developer'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `sampledb`.* TO 'developer'@'%' |
+-------------------------------------------------------------------------+
DBA
show grants for 'dba'@'%';
+--------------------------------------------------------------+
| Grants for dba@% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba'@'%' |
| GRANT ALL PRIVILEGES ON `%`.* TO 'dba'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
Audit
show grants for 'audit'@'%';
+---------------------------------------------+
| Grants for audit@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'audit'@'%' |
| GRANT SELECT ON `sampledb`.* TO 'audit'@'%' |
+---------------------------------------------+
There we go! We already have the users we want to migrate to Azure Database for MySQL, so let’s start migrating the users in AWS RDS MySQL to Azure Database for MySQL using Percona Toolkit, which has enhanced tool sets to maintain MySQL databases.
Install Percona Toolkit
Percona Toolkit is a collection of advanced command-line tools for MySQL and other database engine that can be used to perform a variety of database administration tasks. It includes tools for tasks such as backup and recovery, server monitoring, query analysis, and more. In this article, we use pt-show-grants tool in Percona Toolkit to export users and privileges from AWS RDS MySQL.
To install Percona Toolkit on Redhat or compatible derivatives, perform the following steps:
- In the terminal or AWS Cloudshell, add the Percona release by running the following command:
$ sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
- To update the package manager, run the following command:
sudo yum -y update
- To install Percona Toolkit, run the following command:
sudo yum -y install percona-toolkit
- To verify that the pt-show-grants command-line tool has already been enabled, run the following command:
pt-show-grants –version
Note: For more information, see Installing Percona Toolkit — Percona Toolkit Documentation
Export users and privileges from AWS RDS MySQL
As I mentioned in the last section, the password, which is encrypted by the sha256_password and caching_sha2_password plugins, contains unprintable characters, it will not be able to replay the user’s creation statements in target database. To solve the problem, MySQL has a system variable, print_identified_with_as_hex, to control whether to display hash values as hexadecimal strings rather than as regular string literals.
By default, it is not enabled and if we omit this system variable configuration, the following error will occur while you are importing the statements to the target database:
ERROR 1827 (HY000) at line 5: The password hash doesn't have the expected format.
Next, export the users and privileges from the AWS RDS MySQL instance. To do this, at the pt-show-grants command prompt or in AWS Cloudshell, run the following command:
- For AWS RDS MySQL version 5.7:
$ pt-show-grants --host {hostname} --user {username} --ask-pass --set-vars "print_identified_with_as_hex=ON" --ignore 'mysql.session'@'localhost','mysql.sys'@'localhost','rdsadmin'@'localhost',admin > users.sql
- For AWS RDS MySQL version 8:
$ pt-show-grants --host {hostname} --user {username} --ask-pass --set-vars "print_identified_with_as_hex=ON" --ignore 'mysql.session'@'localhost','mysql.sys'@'localhost','rdsadmin'@'localhost','mysql.infoschema'@'localhost',admin > users.sql
In the above commands:
- {hostname} is the AWS RDS MySQL hostname.
- {username} is the username of the AWS RDS MySQL instance.
- The --ask-pass parameter requires inputting the password after executing the command.
- The --ignore parameter skips users in MySQL (in this example, we’ll skip the default system users in AWS RDS MySQL).
- The --set-vars command sets the MySQL variables with “variable=value” pairs and if you have more than one variables to set please add a comma between the pairs.
Validate exported users and privileges
The exported file users.sql contains the users and privileges of all the databases in the AWS RDS MySQL instance. Let’s find out what has been generated after we run pt-show-grants and to validate the users and privileges are all ready to migrate to Azure.
The content of the users.sql file is shown below:
-- Grants dumped by pt-show-grants
-- Dumped from server rdsmysql8.cw7ooxr4ocfc.us-east-1.rds.amazonaws.com via TCP/IP, MySQL 8.0.32 at 2023-04-14 07:35:29
-- Grants for 'audit'@'%'
CREATE USER IF NOT EXISTS `audit`@`%`;
ALTER USER `audit`@`%` IDENTIFIED WITH 'sha256_password' AS 0x2435242350212A657D5D4D32362577126C1A1A2137580C244765364D2E6A58517A55454A7756496D4F4741424D4747574A6F74324C6573382E50584F57744C534A3943 REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 60 PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT SELECT ON `sampledb`.* TO `audit`@`%`;
GRANT USAGE ON *.* TO `audit`@`%`;
-- Grants for 'dba'@'%'
CREATE USER IF NOT EXISTS `dba`@`%`;
ALTER USER `dba`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035247F0F3F1B10652057021F150427141A491A3E20184A4C432E6A6D4D566D624F525A655A572E6D506D33465844702F784D382E515959774964454D6468535833 REQUIRE NONE PASSWORD EXPIRE INTERVAL 180 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALL PRIVILEGES ON `%`.* TO `dba`@`%`;
GRANT USAGE ON *.* TO `dba`@`%`;
-- Grants for 'developer'@'%'
CREATE USER IF NOT EXISTS `developer`@`%`;
ALTER USER `developer`@`%` IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' REQUIRE NONE PASSWORD EXPIRE INTERVAL 180 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT DELETE, INSERT, SELECT, UPDATE ON `sampledb`.* TO `developer`@`%`;
GRANT USAGE ON *.* TO `developer`@`%`;
As you can see, the user-defined users have all been recorded and dumped by pt-show-grants . Now, we can replay the CREATE and GRANT statements above and apply them to MySQL – Flexible Server. Note that you can modify this to meet your scenario prior to applying to MySQL – Flexible Server.
Import users and privileges to MySQL – Flexible Server
After confirming the output in the users.sql file and making any additional modifications, import the file to the Azure Database for MySQL flexible server by running the following command:
mysql -h{hostname} -u{username} -p < users.sql
Validate users and privileges imported to Azure Database for MySQL
To validate the users imported from the users.sql file, run the following command:
mysql> select user,host,plugin,authentication_string,max_questions,password_expired,password_lifetime,account_locked from mysql.user where user in ('developer', 'dba', 'audit') \G
The following text is displayed:
*************************** 1. row ***************************
user: audit
host: %
plugin: sha256_password
authentication_string: $5$#P!*e}]M26%wl!7X
$Ge6M.jXQzUEJwVImOGABMGGWJot2Les8.PXOWtLSJ9C
max_questions: 60
password_expired: N
password_lifetime: NULL
account_locked: Y
*************************** 2. row ***************************
user: dba
host: %
plugin: caching_sha2_password
authentication_string: $A$005$?e W'I> JLC.jmMVmbORZeZW.mPm3FXDp/xM8.QYYwIdEMdhSX3
max_questions: 0
password_expired: N
password_lifetime: 180
account_locked: N
*************************** 3. row ***************************
user: developer
host: %
plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
max_questions: 0
password_expired: N
password_lifetime: 180
account_locked: N
3 rows in set (0.00 sec)
Notice that the hexadecimal string has been translated into the original hash value that appeared in the source database. Now, let’s find out whether the “developer” user’s privileges have also been moved successfully by running the following command:
show grants for 'developer'@'%';
+-------------------------------------------------------------------------+
| Grants for developer@% |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'developer'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `sampledb`.* TO 'developer'@'%' |
+-------------------------------------------------------------------------+
Then we could try to login with user dba to see if it could login with same password in AWS with caching_sha2_password authentication plugin.
$ mysql -h corpdb-mysql8-azure.mysql.database.azure.com -u dba -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.32 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
For user audit, since it is locked in AWS RDS MySQL and it is supposed to be locked as well in Azure side, let’s try to login and see if it is in lock mode.
$ mysql -h corpdb-mysql8-azure.mysql.database.azure.com -u audit -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'audit'@'74.235.144.250'. Account is locked.
Both the test cases above are passed without any permission issue, then it proves that the users and privileges migration is successful with pt-show-grants by Percona Toolkit.
Conclusion
Migrating users and privileges from AWS RDS MySQL to Azure Database for MySQL is a simple process that can be completed by Percona Toolkit. By following the above steps, you can ensure a smooth migration of users and privileges from AWS RDS MySQL or AWS RDS Aurora MySQL to Azure Database for MySQL - Flexible Server.
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!
Reference