Migrating Users from Aurora to MDS

If you decided to move your data from Amazon Aurora to the real MySQL Database Service on OCI you can use the nice MySQL Shell Dump & Load utility to dump the data or the full instance. This is the recommended way.

However, I recently saw many people migrating only the data using the dump but skipping the users and deal with the users on a second time.

This is also true because many Aurora users are still using instances based on the old MySQL 5.6. Shell dump & load doesn’t support those very old way to store MySQL users.

However, as I already wrote on this previous post, MySQL Shell Plugins can help for this task.

I recently updated the code to support old versions such as MySQL 5.6 and MariaDB. And I added support for OCI MDS privileges support for Aurora.

The plugin delivers 2 methods, one to just display the users creation and grants and one to copy users from one instance to another one.

Let’s have a look at one example. The source is Aurora 5.6 (5.6.10) and the user is kenny.

We will start just by using the getUsersGrants() method and the first issue we encounter is that by default MySQL Shell is not able to connect to that old instance:

JS  \c admin@database-2.xxxx.rds.amazonaws.com:3306
Creating a session to 'admin@database-2.xxxx.rds.amazonaws.com:3306'
Please provide the password for 'admin@database-2.xxxx.rds.amazonaws.com:3306': **
MySQL Error 2026 (HY000): SSL connection error: error:141A318A:SSL routines:tls_process_ske_dhe:dh key too small

To be able to connect using MySQL Shell we will need to add the ssl-cipher to use:

JS  \c admin@database-2.xxxx.rds.amazonaws.com:3306?ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA
Creating a session to 'admin@database-2.xxxx.rds.amazonaws.com:3306?ssl-cipher=AES256-SHA%3ADHE-DSS-AES256-SHA%3ADHE-RSA-AES256-SHA'
Please provide the password for 'admin@database-2.xxxx.rds.amazonaws.com:3306': **
Save password for 'admin@database-2.xxxx.rds.amazonaws.com:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion… Press ^C to stop.
Closing old connection…
Your MySQL connection id is 19
Server version: 5.6.10 MySQL Community Server (GPL)
No default schema selected; type \use  to set one.

Don’t forget to install the MySQL Shell plugins:

$ cd ~/.mysqlsh
$ git clone https://github.com/lefred/mysqlshell-plugins.git plugins

and back in MySQL Shell, we can connect again to the Aurora instance and get all users grants:

JS  user.getUsersGrants('kenny')
-- User kenny@%
CREATE USER IF NOT EXISTS 'kenny'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*008C9A840032CF5933C8C6954132EE633AA82D1D';
GRANT SELECT, INSERT, UPDATE, SELECT INTO S3 ON *.* TO 'kenny'@'%';

Of course if we want to cut and paste those statements to create the user kenny on OCI, we will have an issue with one of it’s privilege: SELECT INTO S3.

We also have the possibility to tell we want to be compatible with MDS in OCI and fix the grants is necessary:

JS  user.getUsersGrants('kenny','',true)
-- User kenny@%
CREATE USER IF NOT EXISTS 'kenny'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*008C9A840032CF5933C8C6954132EE633AA82D1D';
GRANT SELECT, INSERT, UPDATE ON *.* TO 'kenny'@'%';

Now we will see how we can use the copy() method to copy the user directly to MDS.

The best way to do so is to use MySQL Shell somewhere you can access both instances. Using a VPN for example. Here I just installed MySQL Shell on a compute instance in OCI as my Aurora instance is not really secure and allows connections publicly.

So I’m still connected to the Aurora instance and I run user.copy():

JS  user.copy(false, true)
You need to specify a destination server (server<:port>): admin@10.0.1.91
Enter the user to search (you can use wildcards '%', leave blank for all): kenny
Info: locked users and users having expired password are not listed.
1 user found!
Do you want to copy [kenny@%] ? (y/N) y
Copying USER kenny@%: mysql://admin@database-2.xxxx.rds.amazonaws.com:3306?ssl-cipher=AES256-SHA%3ADHE-DSS-AES256-SHA%3ADHE-RSA-AES256-SHA - ip-10-20-1-212 (5.6.10) --> mysqlx://admin@10.0.1.91:33060 - n6ltbmfweod5wyuq (8.0.25-u1-cloud) 
Copying GRANTS..
User(s) copied successfully!

user.copy(false, true): the first argument is dryrun and the second one is the compatibility mode for MDS

As you can see this is very easy.

If you try it and you have some problem don’t hesitate to fill an issue on the GitHub repository.