Seamlessly migrating users to Azure Database for MySQL

[ambhatna]

Introduction 

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. 

Note

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.

Prerequisites 

To prepare for migrating users, you need: 

• A Linux Virtual Machine (VM) running Ubuntu. For more information, see Create a Linux virtual machine in the Azure portal. 
• An Azure Database for MySQL server to which you want to migrate your users. For more information, see Create an Azure Database for MySQL server by using the Azure portal. 
• Firewall rules allowing the Linux VM to connect to the Azure Database for MySQL master server. For more information, see Create a server-level firewall rule in the Azure portal. 
• A clean local MySQL server running a version consistent with the MySQL version to which you want to migrate.  

 

Process 

Migrating users to Azure Database for MySQL involves three primary tasks: 

1. Fetching users from MySQL running on-premises or in another cloud provider and creating the file with revoke queries for SUPER, SHUTDOWN, FILE, and CREATE TABLESPACE. 
2. Restoring users to the clean local MySQL server and creating a file with user to export to Azure Database for MySQL 
3. Importing users to Azure Database for MySQL 

 

Fetching users 

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: 

1. Log in to the Linux VM running Ubuntu. 
2. Create the get_user_list.sql file in any folder (in this post, we use a named “sql”), and then copy the content below into the file:

   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: 

1. Create the get_revoke_list.sql file in the “sql” folder, and then copy the below content into the file:

   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 <> '');

    
2. To create a revoke.sql file that contains the commands with revoke SUPER, SHUTDOWN, FILE, CREATE TABLESPACE privileges, run the following command.

   mysql -h$Ahost -u$Auser -P$Aport -Nf -p < sql/get_revoke_list.sql > sql/revoke.sql

    
3. To create apply1.sql file with the user privileges, use the percona-toolkit - pt-show-grants program by running the following command:

   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. 

 

Restoring users 

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. 

1. To create users identified in Step 1 in Server B, run the following command:

   USERLIST=$(mysql -h$Bhost -u$Buser -P$Bport -f -p < sql/apply1.sql)

    
2. To revoke the unsupported privileges identified above from the users you just created in Server B, run the following command:

   mysql -h$Bhost -u$Buser -P$Bport -f -p < sql/revoke.sql

    
3. To create the user privileges file, use the percona-toolkit - pt-show-grants program by running the following command:

   pt-show-grants --host=$Bhost --port=$Bport --user=$Buser --only=$USERLIST --ask-pass > sql/apply.sql 

    

Importing users 

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

Conclusion 

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