Take the 2-minute tour ×

I have a MySQL installation with many databases and users that I need to migrate to a new MySQL installation. I can use phpMyAdmin to export then import the databases/tables, but I don't know of anything to move the users and permissions. How can I do this easily?

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

A script like this will use the mysql cli client to print out a series of grant statements you would need to use to recreate the user accounts. This command will work best if you have your database credentials stored in you .my.cnf

# adapted from (http://www.pyrosoft.co.uk/blog/2006/10/18/show-grants-for-all-users-on-mysql/)
 mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql 
) | while read user host
  echo "# $user @ $host"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"

If you are jumping from a one version of mysql to another you may want to use this instead of a simply dump of the mysql database. The schema of the mysql database does occasionally get updated.

This will also allow you to pick and choose accounts you want to recreate, if there is some cruft you would like to eliminate.

I was recently using this on a user which included spaces in names, which confused read, since IFS by default includes the space character as a separator. My new and improved command, that seemed to be work better on systems weird usernames.

IFS=$'\t'; while read user host; do
  echo "user:$user host:$host"
  echo ""
done < <(mysql --batch --skip-column-names -e "SELECT user, host FROM mysql.user")
share|improve this answer
add comment

I believe you should be able to migrate this data by backing up and restoring the mysql database.

share|improve this answer
In particular the mysql.user table –  Coops Jan 23 '10 at 9:00
You need to ensure both MySQL instances are running the same main version, otherwise you'll need to run upgrade scripts bundled with the target MySQL server. –  Maxwell Jan 23 '10 at 10:44
add comment

This will create a SHOW GRANTS; file for every user.

Then, echo each user's SHOW GRANTS; and append a semicolon to every line.

MYSQL_CONN="-uroot -prootpassword"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ShowGrants.sql
mysql ${MYSQL_CONN} -ANe < /tmp/ShowGrants.sql | sed 's/$/;/g' > MySQLUserGrants.sql
rm -f /tmp/ShowGrants.sql

You can also download and use these tools to do the same thing:

Give it a Try !!!

share|improve this answer
add comment

Your Answer


