7

I need export all of a MySQL database users (including permissions) for a migration. There is over 5,000 users, what is the best way to do this?

Should I just dump the mysql.user table?

4 Answers 4

13

If you are moving the users to another DB Server running the same major version of MySQL, copying mysql.user is not sufficient. If the users have access to specific databases, copying mysql.user brings the user and the password.

Then, you would have to copy the following

  • mysql.db for database-level grants
  • mysql.tables_priv for table-level grants
  • mysql.columns_priv for column-level grants

Here is logical way to dump it: as SQL GRANT commands !!!

SUGGESTION #1

If you already have Percona Toolkit installed, run pt-show-grants to a text file

GRANT_FILE=MyDatabaseUSers.sql
pt-show-grants -uroot -p > ${GRANT_FILE}

SUGGESTION #2

If you don't have Percona Toolkit and can't wait, here is my personal emulation of it

GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' > ${GRANT_FILE}

EPILOGUE

On the new DB Server, after migrating the data, you login to mysql as root and run

mysql> source MyDatabaseUSers.sql

GIVE IT A TRY !!!

3
  • Thanks so much. pt-show-grants -uroot -p > ${GRANT_FILE} worked perfectly.
    – Justin
    Mar 24, 2015 at 1:05
  • Useful! I think you have to add the -N also in the last mysql command. Jan 14, 2016 at 10:05
  • Thank you so much @RolandoMySQLDBA! You second suggestion has helped me extract user information from a broken system that had backed up with mysqldump but where the latest version of MySQL no longer recognizes restoring a mysql.user table.
    – Rebecka
    Apr 10, 2022 at 15:02
1

Here is the above script by Rolando, but more concise and unix-ish. Just pass your options on the command as you would MySQL. It outputs to stdout. The output can be fed directly back into mysql.

#!/bin/sh
mysql -AN <<'SQL' "$@" | mysql "$@" | sed 's/$/;/;/^Grants for /d'

SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
FROM mysql.user WHERE user<>'' AND host<>''
ORDER BY user, host
;

SQL
0

Use the MySQL tool : mysqluserclone

mysqluserclone  --source=root:PASSWORD@localhost --list -d

You will get a list of all users and the corresponding GRANT statements.

You could also use --destination parameter if you have remote access to your new database.

0

I wanted to thank John K. N. for mentioning mysqluserclone. Unfortunately, it's not available as a package for Ubuntu 20.04 due to python 2 being deprecated but I did find a workaround here

2
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Feb 16, 2023 at 18:03
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review Feb 16, 2023 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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