My approach
In this article I will describe on how to transfer the privileges (grants) from one MySQL server to another.
While you could do it dumping and importing the mysql database from one to another, I prefer it to do using 'plain' SQL.
The base of this article is taken from the Serverfault discussion
How can I export the privileges from MySQL and then import to a new server?.
Do not mess with the database mysql.
There is a lot more going on there than just the users table.
Your best bet is the ''SHOW GRANTS FOR'' command.
Bash function mygrants()
You could use this Bash alias function as a helper in this maintenance.
Just add it in your .bashrc file or include it globally on your server.
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 query 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 with the same options as you would call mysql, e.g.:
mygrants --host=prod-db1 --user=admin --password=secret
Warning
You might not want to disclose your MySQL password in the process list this way.
To have the script read the password from stdin (ask you for the password) it has to be adjusted, which I will do some time later.
Migrate from one server to another
You can use your full Unix toolkit on this like so:
mygrants --host=server1 --user=root --password=secret | grep phpbb_user | mysql --host=server2 --user=root --password=secret
That is the right way to move users; your MySQL ACL is modified with pure SQL.