Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

3 Answers 3

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

#!/bin/bash
# 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
do
  echo "# $user @ $host"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"
done

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"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"
  echo ""
done < <(mysql --batch --skip-column-names -e "SELECT user, host FROM mysql.user")
share|improve this answer

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
2  
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

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="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.