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
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 email@example.com:3306 Creating a session to 'firstname.lastname@example.org:3306' Please provide the password for 'email@example.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 firstname.lastname@example.org:3306?ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA Creating a session to 'email@example.com:3306?ssl-cipher=AES256-SHA%3ADHE-DSS-AES256-SHA%3ADHE-RSA-AES256-SHA' Please provide the password for 'firstname.lastname@example.org:3306': ** Save password for 'email@example.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
%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
%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
JS user.copy(false, true) You need to specify a destination server (server<:port>): firstname.lastname@example.org 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 [
%] ? (y/N) y Copying USER
%: mysql://email@example.com:3306?ssl-cipher=AES256-SHA%3ADHE-DSS-AES256-SHA%3ADHE-RSA-AES256-SHA - ip-10-20-1-212 (5.6.10) --> mysqlx://firstname.lastname@example.org:33060 - n6ltbmfweod5wyuq (8.0.25-u1-cloud) Copying GRANTS.. User(s) copied successfully!
user.copy(false, true): the first argument is
dryrunand 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.