Locked out of MySQL!

Few days ago, I had been assigned the task to migrate an application to a new server, without being given the MySQL (Actually MariaDB) credentials.

I tried the usual suspects (root, blank and root, root) but in vain, so I had to reset the root password.

To do that, it takes 5 steps

1- Stop MySQL service

sudo systemctl stop mariadb.service

2- Start MySQL service without login

sudo mysqld_safe –skip-grant-tables –skip-networking &

Note the parameters –skip-grant-tables, which allows you to login to mysql without password and –skip-networking which prevents logins from network, since any attacker can login as root without even trying to guess the password.

Now that the password checking is disabled, you can login as root without password

mysql -uroot

3- Update the password in users table

update mysql.user set password=PASSWORD(“NEW PASSWORD HERE“) where User=’root’;

4- Tell MySQL to re-read logins from users table

flush privileges;

Now exit out of mysql console back to server’s console.

5- Stop MySQL and restart as usual

sudo systemctl stop mariadb.service

sudo systemctl start mariadb.service

You can now try to login again with your new password, and mysql should be available from network if it’s already been configured to.