How to copy user privileges with MySQL?

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?

Solution 1:

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")

Solution 2:

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:

pt-show-grants
mk-show-grants

Give it a Try !!!

How to copy user privileges with MySQL?

Solution 1:

Solution 2:

Related

Recent Posts