How to migrate users and privileges in MySQL

User migration is a major section in database server migration tasks. If the number of users are very less, we can recreate it in destination server one by one. If the number of users are huge, the task will be very difficult and time consuming.

We are introducing one shell script to export the MySQL users and privileges in a single step.

#!/bin/bash
#www.techies-world.com

#MySQL server credentials
HOST=
USER=
PASSWORD=

#Collecting the users list and privileges
for i in `mysql -Ns -h $HOST -u $USER -p$PASSWORD -e "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user not in ('mysql.session','mysql.sys','debian-sys-maint','root');"`
do
mysql -Ns -h $HOST -u $USER -p$PASSWORD -e 'SHOW CREATE USER '"$i"';' >> users.sql
mysql -Ns -h $HOST -u $USER -p$PASSWORD -e 'SHOW GRANTS FOR '"$i"';' >> users.sql
done

#Appending ; to the end of the quries
sed -i 's/$/\;/' users.sql

Please note that the source MySQL host, user and password need to be configured within the script before execution.

The exported data will be available in a file with name "users.sql" within the folder from which the script executed.

Since we already excluded the MySQL system users, this sql file can be imported to the destination MySQL server.

#mysql -h HOST -u USER -P < users.sql

Here HOST and USER need to be replaced with the details of destination MySQL server and password through the standard input.

That's all…