To provide a managed service experience, all managed offerings of MySQL restrict access to certain advanced privileges, such as SUPER, SHUTDOWN, FILE, and CREATE TABLESPACE. Every instance of MySQL has some users with these advanced privileges, and you can’t migrate these users directly from an instance of MySQL running on-premises or in another cloud provider to Azure Database for MySQL.
This blog post explains how to migrate MySQL users from an instance of MySQL running on-premises or in another cloud provider to Azure Database for MySQL.
If you receive the error "Table "mysql.proc" doesn't exist" when migrating from MySQL 5.6/5.7 to MySQL 8.0 then there are two workarounds:
1. Remove CheckParameters=false and configure ProxySQL server as a pooler . Please note that a ProxySQL will be running in a separate VM that will generate additional cost if customers would like to have it in an Azure VM. The benefits of a ProxySQL are not just bypassing this issue but leveraging the power of a connection pool. Please refer to configuring ProxySQL for more details.
2. Specify CheckParameters=false in connection string and align the order of the parameters passed in when calling commandType.StoredProcedure with the order of the column defined in stored procedure.
You can refer to detailed answer and workaround here.
To prepare for migrating users, you need:
Migrating users to Azure Database for MySQL involves three primary tasks:
The first thing you need to do is to fetch users from the MySQL running on-premises or in another cloud provider (Server A, for the purposes of this post) and then create the file with revoke queries for SUPER, SHUTDOWN, FILE, and CREATE TABLESPACE.
To create a user list with all users except localhost users and proxy accounts:
SELECT group_concat(concat("'",user,"'@'",host,"'"))
FROM mysql.user
WHERE host not in ('localhost','127.0.0.1')
AND user not in (select user from mysql.proxies_priv where proxied_user <> '');
3. Create a user list with all users except localhost and proxy accounts by running the following command:USERLIST=$(mysql -h$Ahost -u$Auser -P$Aport -Nf -p < sql/get_user_list.sql)
To remove the SUPER, SHUTDOWN, FILE, and CREATE TABLESPACE privileges from all users:
SELECT concat("REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM '",user,"'@'",host,"';") as 'query' from mysql.user
WHERE host not in ('localhost', '127.0.0.1')
AND user not in (SELECT user from mysql.proxies_priv where proxied_user <> '');
mysql -h$Ahost -u$Auser -P$Aport -Nf -p < sql/get_revoke_list.sql > sql/revoke.sql
sudo apt install percona-toolkit #This will install the percona toolkit
pt-show-grants --host=$Ahost --port=$Aport --user=$Auser --only=$USERLIST --ask-pass > sql/apply1.sql
The above statement will create the file, which will contain the create statements for all the supported user with appropriate permissions, in the ‘sql’ folder.
Next, we need to restore the users we fetched previously to the clean local MySQL server (Server B, for the purposes of this post), and then create a file with the users to export to Azure Database for MySQL.
USERLIST=$(mysql -h$Bhost -u$Buser -P$Bport -f -p < sql/apply1.sql)
mysql -h$Bhost -u$Buser -P$Bport -f -p < sql/revoke.sql
pt-show-grants --host=$Bhost --port=$Bport --user=$Buser --only=$USERLIST --ask-pass > sql/apply.sql
Finally, we need to import the users from the file created above to Azure Database for MySQL by running the following command:
mysql -hmydemoserver.mysql.database.azure.com -umydemouser@mydemoserver -p <sql/apply.sql
That’s it – you’re all done with the process!
If you have any questions or trouble migrating MySQL users from an instance of MySQL running on-premises or in another cloud provider to Azure Database for MySQL, please contact the Azure Database for MySQL team at AskAzureDBforMySQL@service.microsoft.com.
Thank you!
Amol Bhatnagar
Program Manager - Microsoft
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.