Copy mysql_native_password to new server

Question

I did a mysqldump of my entire database mysqldump --defaults-file="/home/ubuntu/.my.conf" -u api --all-databases > full-dump.sql . I setup a new mysql and phpmyadmin and loaded the mysqldump in mysql -u root -p < full-dump.sql

Then my root password stopped working (it's the same on both servers). I was able to reset the password and regain access. I am now comparing authentication_strings on my new server to my old server. I noticed the strings changed on the new server when I reset the passwords. My presumption is that the new and old server use different hashes for the same password and hence aren't compatible with each other.

Is there a way to copy the mysql users over and have the existing passwords work? Or will I always have to manually reset them like I did in this case?

Answer 1

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)