13 Oct 09 Copy/Export MySQL User Priviledges

I’m often asked how to copy or export MySQL Users from one machine to another. The following SQL query will show your users:

SELECT DISTINCT CONCAT (‘show grants for `’, user, ‘`@`’, host, ‘`;’) AS query FROM mysql.user;

In my case on my test server, this shows:

SHOW GRANTS FOR ‘root’@’127.0.0.1′;
SHOW GRANTS FOR ‘debian-sys-maint’@'localhost’;
SHOW GRANTS FOR ‘root’@'localhost’;

Now, I’ll need to execute each one of these as separate statements. The output of SHOW GRANTS FOR ‘root’@'localhost’; is:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@'localhost’ IDENTIFIED BY PASSWORD ‘*XXX…XXX’ WITH GRANT OPTION;

Copy and paste each ‘GRANT’ statement to your new SQL server, with the hashed password intact and you should be ready to go.

Tags: PHP MySQL Developer, sql

Reader's Comments

1. Peter Kingsbury | 07 Feb 2010 2:59 pm

   Hi Adam,

   Excellent article, but your site is rendering HTML with extraneous characters which don’t equate to quotation marks.

   The correct code would be:
   SELECT DISTINCT CONCAT (‘show grants for `’, user, ‘`@`’, host, ‘`;’) AS query FROM mysql.user;

   Cheers!

Leave a Comment

You must be logged in to post a comment.