Migrating mysql databases is easy using mysqldump*. But moving users from one DB server to another seems a little tricky. Based on Vivek Gite’s post, here’s a neat little piped set of commands that you can run on the source DB server. The command will generate all the GRANT statements that you can simply run on the destination DB server.

mysql -u <ROOTUSERNAME> -B -N -p<ROOTPASSWORD> -e "SELECT user, host FROM user" mysql | awk '{print "mysql -u <ROOTUSERNAME> -p<ROOTPASSWORD> -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\""}'

The beauty of this is that you need not know the passwords for any of the mysql user accounts.

*Migrating all databases except mysql and information_schema:

mysql -u <MYSQLROOTTUSERNAME> -p<MYSQLROOTPWD> -B -N -e"SHOW DATABASES;" | grep -v mysql | grep -v information_schema | xargs mysqldump -u <MYSQLROOTTUSERNAME> -p<MYSQLROOTPWD> --databases > mysqldbdump.sql