1

I'm in the process of testing MySQL 8.0.

I have set up a test database, and the first thing I want to do is import my user account from MySQL 5.7.

So to do this I run:

/*ON MySQL 5.7*/
SHOW CREATE USER `username`@`1.2.3.4`;

This gives me:

CREATE USER 'username'@'1.2.3.4' IDENTIFIED WITH 'mysql_native_password' AS '*1BDAB0F57D8247DF5EFE4927F144138B3B7F2C6B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

I run this on MySQL 8.0, and it creates a user. I check I can log in fine. However, the default in MySQL 8.0 is to use the caching_sha2_password plugin for authentication. So how can I upgrade my login to use the new plugin?

(n.b. this is my login, but at some point I will need to move all my colleagues logins, so assume I don't know what the pasword is).

I have looked all over the web, and found lots of docuemntation about the plugin, but none that say how to convert from one type to the other (apart from delete the user and start again, but that's no use if I don't know the password).

Improve this question

1 Answer 1

Reset to default
3

Without knowing the user passwords, you can:

  1. Migrate the user accounts as you have described in your question (SHOW CREATE USER)
  2. Set the passwords to 'expired' ALTER USER user@host PASSWORD EXPIRE;
  3. Ask the users to login and change the passwords. On an expired password, login is possible but the only action possible is to SET the password.
Improve this answer
7
  • Sorry, it still says plugin=mysql_native_password. Plus, it seems a very inefficient way of migrating many user accounts to the new database, a lot of which are for applications rather than individuals.
    – IGGt
    Jun 17, 2019 at 12:36
  • 1
    Unfortunately I am not aware of any automated migration methods since encrypted passwords cannot be decrypted (or very hard to do if I say so). For application users, you'll have the actual text password in config files and so could write a bash script or something similar in your favourite scripting language. For other "users" they will have to reset the password to be able to use the newer caching_sha2_password that is default in MySQL8. Using the output of SHOW CREATE USER, you should be able to get the users across from the old MySQL ver. Once on the new server, change the plugin ...
    – thatsaru
    Jun 17, 2019 at 12:48
  • cont from the above... Once on the new server, change the plugin column value to be mysql_native_password as described in dev.mysql.com/doc/refman/5.6/en/account-upgrades.html Having changed the mysql.user.plugin column, when the password is changed by the users manually, it will be using the new plugin for encrypting the password in DB. You can force the users to change their passwords by expiring it as I described in my answer earlier. I am also in the process of migrating to MySQL 8 and faced similar challenges!
    – thatsaru
    Jun 17, 2019 at 12:58
  • OK cheers, that seems like an oversight by Oracle, not allowing a live migration of users in that way. I raised a feature request to see what they say about it (95841).
    – IGGt
    Jun 17, 2019 at 14:03
  • One option that has been presented is to use ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password; Although this expires the password (not what you want if it is your busy website you are migrating).
    – IGGt
    Jun 17, 2019 at 15:13

Your Answer

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

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