Transferring grants from one mysql DB to another I would like to migrate a mysql DB from one server (Server A) to another (Server B). I took a Mysqldump and imported into the B.

Now, I would also like to export all the grants from A to B, to do so I did the following:


Ran the following on server A and got all the grants:

mysql -u root -B -N -p -e "SELECT user, host FROM user" mysql






I specifically wanted the grants for user galaxy, readgalaxy and writegalaxy so I ran the following command on server A to get them:

mysql -u root -p -B -N -e"SHOW GRANTS FOR 'galaxy'@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'readgalaxy '@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'writegalaxy '@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'writegalaxy '@localhost";

I got the grants and then ran those grants against the server B like this:

GRANT USAGE ON *.* TO 'readgalaxy'@'localhost' IDENTIFIED BY 
  PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';



I get the following output:

Query OK, 0 rows affected (0.00 sec)



Ran the following on server B:

mysql&gt; SELECT user,host,password FROM mysql.user;






But now when I try to see the grant that I allocated to user readgalaxy, I get the following error:

ERROR 1141 (42000): There is no such grant defined for user 'readgalaxy' on host 'localhost'



What's happening here?
An easier way is to use pt-show-grants.

On source server: 

$ pt-show-grants -uroot -p &gt; grants.sql


Destination server: 

$ mysql -uroot -p &lt; grants.sql
$ mysql -uroot -p -e "FLUSH PRIVILEGES;"