Export all MySQL users
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 grantsmysql.tables_priv
for table-level grantsmysql.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