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.