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.

    '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


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.

Share on: TwitterHacker NewsFacebookLinkedInRedditEmail


comments powered by Disqus

Related Posts


Last Updated


System administration


Connect with me on...