Each version of MySQL features a specific number of columns for the mysql.users
table
I mentioned this back on April 12, 2012
: Cannot GRANT privileges as root
You cannot mysqldump the mysql schema from one major version and load it into a different major version.
What you need is to generate the SQL to create the users
I wrote a version for MySQL 5.6 and back on Mar 22, 2015
: Export all MySQL users
I had suggested using pt-show-grants. I also suggested an emulation of it.
Here is the emulation code from that post
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}
WHAT ABOUT MySQL 5.7+ ???
pt-show-grants will not generate the SQL for the password to be visible. (PCI auditors will be crawling the walls looking for password exposures)
Here is a script to to dump create user commands with their password hashes
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1024 * 1024 * 10;"
SQL="${SQL} SELECT CONCAT('CREATE USER IF NOT EXISTS ',QUOTE(user),'@',QUOTE(host),' IDENTIFIED WITH mysql_native_password AS ',QUOTE(authentication_string),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>'';"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_FILE}
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}
If you want this to works for MySQL 5.5 and 5.6, change the word authentication_string
to password
, and adjust the CREATE USER syntax as needed, perhaps something like this
GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1024 * 1024 * 10;"
SQL="${SQL} SELECT CONCAT('CREATE USER ',QUOTE(user),'@',QUOTE(host),' IDENTIFIED AS ',QUOTE(password),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>'';"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_FILE}
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}
I did not test these. So, please test them and see if it produces it for you.
WHAT NEXT ???
You then load the MyDatabaseUSers.sql into the new MySQL instance
Please avoid using --all-databases
when dumping everything. You shouuld dump everything except the mysql
schema (See my old post How do you mysqldump specific table(s)? on how to do that)