1

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?

1
  • 1
    What are the MySQL versions for both server? I had experience like this when I migrate from MySQL 4.1 to newer versions (MySQL 5.7 and above or MariaDB that's using the same core).
    – FanoFN
    Feb 24, 2021 at 0:20

1 Answer 1

0

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)

2
  • Thank you for your thoughtful answer. I have some follow up questions. 1) If mysql version is the same is it ok to dump with --all-databases (that would create the desired instructions, right?). 2) Does your Create User method incorporate everything else in mysql schema that's necessary (I assume so, just checking)? 3) Is it ok/desirable to dump tables like information_schema and phpmyadmin? I am doing all of this to create a replica but maybe I'm going about it incorrectly.
    – Bix
    Feb 25, 2021 at 18:07
  • 1) Yes. 2) CREATE USER IF NOT EXISTS works for 5.7 and 8.0 only. If you are using 5.6 and below, you have to add --force to the first mysql command.3) No need to dump information_schema. (See a lot of my posts where I bypass dumping mysql, information_schema and performance_schema dba.stackexchange.com/…) Feb 25, 2021 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.