Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the “SHOW GRANTS FOR” command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:
mygrants()
{
mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
) AS query FROM mysql.user" | \
mysql $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}
The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.
The $@ in the command will allow you to call it as:
mygrants –host=prod-db1 –user=admin –password=secret
You can use your full unix tool kit on this like so:
mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret
That is THE right way to move users. Your MySQL ACL is modified with pure SQL.