I installed MySQL 8.x using yum, but I cannot find or reset the root password

Question

I had to install mysql 8.0 because previous version were crashing.
Now I'm struggling with setting root password. The default empty password doesn't work, I've tried root, mysql as passwords but they are not working.
I've created the init file to reset password. Unfortunately, my passwords are not accepted, here is my log:

2018-02-16T10:12:22.962733Z 0 [Warning] [MY-010139] Changed limits: max_open_files: 5000 (requested 8161)
2018-02-16T10:12:22.962815Z 0 [Warning] [MY-010142] Changed limits: table_open_cache: 2419 (requested 4000)
2018-02-16T10:12:23.160066Z 0 [System] [MY-010116] /usr/sbin/mysqld (mysqld 8.0.4-rc-log) starting as process 20059 ...
2018-02-16T10:12:24.013727Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-16T10:12:24.026122Z 0 [Warning] [MY-010319] Found invalid password for user: 'root@localhost'; Ignoring user
2018-02-16T10:12:24.043758Z 6 [Warning] [MY-010319] Found invalid password for user: 'root@localhost'; Ignoring user
2018-02-16T10:12:24.050668Z 0 [System] [MY-010931] /usr/sbin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL).

Here is my current init file content:

SET GLOBAL validate_password.policy = 'LOW';
UPDATE mysql.user
    SET authentication_string = PASSWORD('new_password'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

I've tried so much different passwords, none worked. I've tried to create passwords longer than 16 characters with special characters and numbers, nothing. Any advices what I could do to reset the password and actually start using DB?

Answer 1

I had the same problem. Looks like they changed the initial authentication steps with the new version. Instructions are given here. Basically:

$ sudo service mysqld start
$ sudo grep 'temporary password' /var/log/mysqld.log

(use that password in the following - or to log into mysql_secure_installation)

$ mysql -uroot -p
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPasswd';

Note: they also switched from the validate_password "plugin" to a "component" - so if you don't need the levels of security they insist on and want a password you can remember, you'll have to run the following within mysql:

> UNINSTALL COMPONENT 'file://component_validate_password';

Edit: They've also changed the authentication plugin to support better encryption. If you're using Workbench or other add-ons and get an error along these lines

Authentication plugin 'caching_sha2_password' cannot be loaded:
/usr/local/mysql/lib/plugin/caching_sha2_password.so not found

Then use the following to adjust the user password:

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newPasswd';

You should probably also add (or uncomment if already there) the following line to /etc/my.cnf

default-authentication-plugin=mysql_native_password