Hi I have a new Ubuntu 20.04 server, and an old 18.04 server which I am retiring. 18.04 runs MySQL 5.7 and 20.04 runs MySQL 8.0. In the old days, I'd simply do a mysqldump --all-databases and then import it all, including the mysql.* db & schema. Easy. Obviously this is no longer possible with the differences between 5.7 and 8.0.

I have scoured the web and so far I cannot find anything that says how I can upgrade from 5.7 to 8.0. I can't ask all my database customers for their passwords, so how do I migrate the mysql.users data?

  • Is there something in the documentation that is unclear?
    – mustaccio
    Jul 27 at 23:57
  • I believe FB is struggling with the same problem. They too may lack some basic knowledge of MySQL, since they seem to think it was developed by Oracle (engineering.fb.com/2021/07/22/data-infrastructure/mysql). Jul 28 at 8:28
  • Start a 5.7 container on 20.04 with /var/lib/mysql volume mapped in, import the data there into the container. Stop the container, the install mysql-8.0. Let the inplace upgrade handle it.
    – danblack
    Jul 28 at 9:00
  • 1
    The dump+relaod is possible, but there are a lot of incompatible changes that you might stumble over.
    – Rick James
    Jul 28 at 17:58

You don't need to know the user's original password, you only need to know the hashed version of their password that is stored in the MySQL system tables.

You can use SHOW CREATE USER on MySQL 5.7 for each user, which dumps their user creation options, including the password in its hashed form. Like mysqldump, the output of SHOW CREATE USER is valid syntax that you can run on another instance of MySQL to recreate the user with their password.

You would also need to run SHOW GRANTS FOR for each user. This outputs a statement with one or more GRANT statements to recreate all their privileges. You can then execute these statements against the upgraded MySQL instance.

This would be tedious if you had many users, because SHOW CREATE USER and SHOW GRANTS work for one user at a time. I recommend using Percona's free tool pt-show-grants, which dumps all the users with their privileges so you can migrate them all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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