12

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?

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
Nathan Osman
  • 2,705
  • 7
  • 31
  • 46

3 Answers3

18

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")
Zoredache
  • 128,755
  • 40
  • 271
  • 413
4

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

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
3

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

pkaeding
  • 790
  • 2
  • 12
  • 23
  • In particular the mysql.user table – Coops Jan 23 '10 at 09: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 should be the accepted answer. The other answers just explain "GRANTS" but that is missing users and passwords. – John Oct 23 '20 at 22:27