Asle Benoni

MySQL 8 reset root password

Asle Benoni -

I had to reset my root password on MySQL 8 on a CentOS7  installation. None of the methods I found were working. The solution was to use the special --init-file option.

The method is to restart MySQL in safe mode and set a new root password.

First this does not work as in earlier MySQL versions:

mysqld_safe --skip-grant-tables &
but then I get this
mysqld_safe:command not found

So I tried the methods in this post

This did not help me since when I tried to reset the root password
`mysql> select user(), current_user();
+--------+-----------------------------------+
| user() | current_user() |
+--------+-----------------------------------+
| root@ | skip-grants user@skip-grants host |
+--------+-----------------------------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPassWord'
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> FLUSH PRIVILEGES;`

I still get this error when running the ALTER USER statement.
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Here is what worked for me:

  1. Stop MySQL. systemctl stop mysqld

  2. Find and delete the mysql pid file. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name. On my CentOS7 it was in /var/run/mysqld/mysql.pid - shell> sudo kill cat /var/run/mysqld/mysql.pid

  3. Create a text file containing the password-assignment statement on a single line. Replace the password with the password that you want to use. The file contains this text:
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

  4. My file is in /home/user so I ran this command:
    shell> mysqld --init-file=/home/user/mysql-init &

  5. MySQL server starts with executing this file and resets your root password.

  6. Delete the mysql-init file

  7. Start MySQL and you should have your new root password:
    systemctl start mysqld - You may have to run restart or stop mysqld first.