It’s a pretty common situation when you lost your MySQL root password. In earlier versions there was a possibility to open my.cnf file and get the password. But in our situation it’s not that easy. Anyway, we can stop MySQL service, then start with certain key, reset password and restart the service. That’s method advised on the official website.
At first you need to stop MySQL service. To do it just type in this command:
systemctl stop mysqld
Now, we need to start MySQL service in safe mode. Run the command below:
mysqld_safe --skip-grant-tables &
After that you have to connect to MySQL client over SSH. Type in this command:
mysql -u root
It’s time to reset MySQL password. You need to perform multiple commands:
use mysql; update user set password = PASSWORD ( "NewPassword") where User = 'root'; flush privileges; quit
If you did everything right, your terminal should look like that:
mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 39 Server version: 5.5.37-MariaDB-wsrep MariaDB Server, wsrep_25.10.r3980 Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> update user set password=PASSWORD("NewPassword") where User='root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]> quit Bye
Once it is done, just press CTRL+C to stop MySQL service.
That’s pretty much it. Now you can start it again by using:
systemctl start mysqld
Connect to it by using your new password:
mysql -u root -p