Export all MySQL users

Question

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?

Answer 1

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

Answer 2

Current versions of mysqldump (such as bundled with MariaDb) now allow you to export user grants directly;

mysqldump --system=users

(To a file: mysqldump --system=users >sqlusergrants.sql )

*--system=name       Dump system tables as portable SQL. Any combination of:
                      all, users, plugins, udfs, servers, stats, timezones*

Answer 3

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

Answer 4

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.

Answer 5

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