How to migrate mysql users from one server to another?

Step 1: Get a List of MySQL Users from the source server

[root@db01 tmp]# mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > /tmp/mysql_all_users.txt
Enter password: redhat
[root@db01 tmp]# cat /tmp/mysql_all_users.txt
'CitLivehappy'@'%'
'brightonlands'@'%'
'brochure'@'%'
'carver'@'%'
'CitLivehappy'@'localhost'
'brightonlands'@'localhost'
'brochure'@'localhost'
'carver'@'localhost'


Step 2:- Obtain a List of User Privileges

Fire the next command in the terminal to get the privileges:
[root@db01 tmp]# cd /tmp/
[root@db01 tmp]# while read line; do mysql -B -N -uroot -predhat -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

Basically, the above command reads every individual line in mysql_all_users.txt and tries to get privileges for each user and paste it on mysql_all_users_sql.sql file.
This file will contain the SQL queries to obtain users along with their privileges.

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA67561F7C46D591CC6F15FD7BF1E'
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%'
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%'
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD7FED678FA'
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%'
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD230662388030CA895BEB'
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%'
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1ADBE3'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%'

We can see from mysql_all_users_sql.sql that all the users information along with the privileges and the script is almost ready.
Only thing the above script missing is a ";" at the end of every sql command.

We will add ";" at the end of each sql command
[root@db01 tmp]# sed -i 's/$/;/' mysql_all_users_sql.sql

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA6756C9BE6591CC6F15FD7BF1E';
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%';
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%';
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD6E99678FA';
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%';
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD23CA750C895BEB';
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%';
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1BBE3';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%';

Now add "flush privileges" to reload the grant tables.
[root@server1 tmp]# echo "flush privileges;" >> mysql_all_users_sql.sql

Step 3: Now lets import the sql file into new server.

Transfer this sql file into destination(new) server and fire up the below command.

[root@db02 tmp]# mysql -u root -ppassword < mysql_all_users_sql.sql