How to reset / recover a MySQL root password

Update February 2022

Resetting MySQL password remains a nightmare due to all the differences between MySQL 7 and 8, and MariaDB.

If you’re running the latest MySQL 8, see this article, in summary:

mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

If you get an error such as `You are not allowed to create a user with GRANT`

create user 'root'@'localhost' identified by 'password';

IF you get this error `Operation CREATE USER failed for ‘root’@’localhost’`

Perhaps do this:

drop user 'root'@'localhost'

Update December 2021

What if you can’t find mysqld_safe command not found error? Try this:

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

[user@machine ~]$ mysql -u root

Then

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 'sup3rPw#'
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'sup3rPw#'
Query OK, 0 rows affected (0.08 sec)

mysql> exit
Bye
[user@machine ~]$ systemctl stop mysqld
[user@machine ~]$ sudo systemctl unset-environment MYSQLD_OPTS
[user@machine ~]$ systemctl start mysqld

Reference

https://stackoverflow.com/questions/33510184/how-to-change-the-mysql-root-account-password-on-centos7

Old Article

The fact is changing MySQL root really depends a lot on the version of MySQL and there is no exact way. That’s why googling it will always bring up new answers.

You could try this:

sudo /etc/init.d/mysql stop

sudo mysqld_safe –skip-grant-tables &

mysql -u root mysql

Now in MySQL:

 FLUSH PRIVILEGES;

Try this first:
update mysql.user set password=password('secret') where user='root';

Otherwise this:
SET PASSWORD FOR root@’localhost’ = PASSWORD(‘password’);

FLUSH PRIVILEGES;

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql stop

mysql -uroot -ppassword

Reference:

Be warned, multiple references and not all methods work.

https://help.ubuntu.com/community/MysqlPasswordReset

Possible Caveats:

MariaDB [mysql]> SET PASSWORD FOR root@’localhost’ = PASSWORD(‘secret’);
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings

Try this command instead:
update mysql.user set password=password(‘secret’) where user=’root’;

Be careful when testing, you might have to sudo mysql to test! If sudo mysql works but without sudo doesn’t work, you need an additional grant statement:

user@hostname:~/Code/api$ mysql -uroot -ppassword
ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

user@hostname:~/Code/api$ sudo mysql -uroot -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37

Server version: 10.1.38-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> grant all privileges on *.* to ‘root’@’localhost’ identified by ‘password’ with grant option;
Query OK, 0 rows affected (0.00 sec)

Another caveat:

190213 12:31:31 mysqld_safe A mysqld process already exists

You tried this already and now there is an open process. Use kill to continue. First

ps -ef | grep mysql

Then kill -9 one by one.