Transferring grants from one mysql DB to another

Question

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> 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?

Answer 1

An easier way is to use pt-show-grants.

On source server:

$ pt-show-grants -uroot -p > grants.sql

Destination server:

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

Answer 2

I had to flush the privileges before Mysql could identify them. I did it via 'FLUSH PRIVILEGES; and it solved the issue.