Export and import MySQL users and privileges to Azure Database for MySQL

In Azure Database for MySQL, “mysql.user” table is a read-only table, to migrate your mysql.user table  you can use the following command to script out the users table content before migration:

 

• Generate create user statements:

Before going to generate the scripts , we need to check the variable secure_file_priv on your database which limit the directories where you can load or writing files to :

mysql> SHOW VARIABLES LIKE "secure_file_priv";

+------------------+-----------------------+

| Variable_name    | Value                 |

+------------------+-----------------------+

| secure_file_priv | /var/lib/mysql-files/ |

+------------------+-----------------------+

After checking the path, now you can generate the create users from your database:

 

Option 1:

mysql> SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','YourPassword''',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile  '/var/lib/mysql-files/create_users.sql';

The generated file contents will look like:

create user 'myuser1'@'%' identified by 'YourPassword';

create user 'myuser2'@'%' identified by 'YourPassword';

create user 'myuser3'@'%' identified by 'YourPassword';

create user 'myuser4'@'%' identified by 'YourPassword';

create user 'replication_user'@'%' identified by 'YourPassword';

modify the password above based on your requirements and run the above generated create user statements on Azure database for MySQL.

 

option 2:

if you are not aware of the passwords in your database and you want to let the password change on the client side , you can generate a user creation script with temporary password and expired option:

SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','Temp@123''', ' password expire',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile  '/var/lib/mysql-files/create_users.sql';

The generated file contents will look like:

create user 'myuser1'@'%' identified by 'Temp@123' password expire;

create user 'myuser2'@'%' identified by 'Temp@123' password expire;

create user 'myuser3'@'%' identified by 'Temp@123' password expire;

create user 'myuser4'@'%' identified by 'Temp@123' password expire;

create user 'replication_user'@'%' identified by 'Temp@123' password expire;

 

run the above generated create user statements on Azure Database for MySQL.

after the user logged in to the database with that temp password , and tried to run any query the user will be notified that he must change the password before running any query , and the user can use “set password = ‘newpassword’ ” statement to reset the password:

 

mysql> select 1;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql>

mysql> set password = 'Mysql@1234';

Query OK, 0 rows affected (0.01 sec)

mysql> select 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

 

 

2. Export users’ privileges:

To export the user privileges, you can run the below query:

SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM  mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile  '/var/lib/mysql-files/user_grants.sql';

The generated file will look like:

SHOW GRANTS FOR 'myuser1'@'%';

SHOW GRANTS FOR 'myuser2'@'%';

SHOW GRANTS FOR 'myuser3'@'%';

SHOW GRANTS FOR 'myuser4'@'%';

SHOW GRANTS FOR 'myuser5'@'%';

SHOW GRANTS FOR 'replication_user'@'%';

 

You can source the generated file to get the required commands , before sourcing the file to generate a ready contents of command lines , connect to your local database with options -Ns to remove the headers from the output:

 

mysql -Ns -h root -p

mysql> source /var/lib/mysql-files/user_grants.sql

GRANT USAGE ON *.* TO 'myuser1'@'%'

GRANT USAGE ON *.* TO 'myuser2'@'%'

GRANT USAGE ON *.* TO 'myuser3'@'%'

GRANT USAGE ON *.* TO 'myuser4'@'%'

GRANT USAGE ON *.* TO 'myuser5'@'%'

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%'

 

Connect to your Azure database for MySQL and run the above generated commands:

 

mysql -h ServerName.mysql.database.azure.com -u username@servername -p

mysql> GRANT USAGE ON *.* TO 'myuser1'@'%';

GRANT USAGE ON *.* TO 'myuser3'@'%';

GRANT USAGE ON *.* TO 'myuser4'@'%';

GRANT USAGE ON *.* TO 'myuser5'@'%';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%';Query OK, 0 rows affected (0.03 sec)

 

mysql> GRANT USAGE ON *.* TO 'myuser2'@'%';

Query OK, 0 rows affected (0.02 sec)

 

mysql> GRANT USAGE ON *.* TO 'myuser3'@'%';

Query OK, 0 rows affected (0.02 sec)

 

mysql> GRANT USAGE ON *.* TO 'myuser4'@'%';

Query OK, 0 rows affected (0.04 sec)

 

mysql> GRANT USAGE ON *.* TO 'myuser5'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%';

Query OK, 0 rows affected (0.02 sec)

 

Please note that select into outfile is not supported in Azure Database for MySQL. Instead, use the Create users and Create privilege queries below and run them from workbench and copy the outputs:

 

Example:

Create users:

SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','YourPassword''',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session');

Create Privileges:

SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM  mysql.user where user not in ('root','mysql.sys','mysql.session');

And run the generated commands:

SHOW GRANTS FOR 'myuser1'@'%';

SHOW GRANTS FOR 'myuser2'@'%';

SHOW GRANTS FOR 'myuser3'@'%';

SHOW GRANTS FOR 'myuser4'@'%';

SHOW GRANTS FOR 'myuser5'@'%';

SHOW GRANTS FOR 'replication_user'@'%';

 

Please visit the Performance considerations guide for best practices while migrating into Azure Database for MySQL.

 

Thank You !