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?
MySQLpermissionsusers
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?
Best Answer
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, copyingmysql.user
brings the user and the password.Then, you would have to copy the following
mysql.db
for database-level grantsmysql.tables_priv
for table-level grantsmysql.columns_priv
for column-level grantsHere 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
SUGGESTION #2
If you don't have Percona Toolkit and can't wait, here is my personal emulation of it
EPILOGUE
On the new DB Server, after migrating the data, you login to mysql as root and run
GIVE IT A TRY !!!