Updated : Apr 03, 2018 in Linux

Resetting MySQL/MariaDB root password in Ubuntu 16.04

In case you have forgotten the ‘root’ password in MySQL/MariaDB(10.0.x) you can reset the password as follows:
Ref: https://kofler.info/root-login-problem-mit-mariadb/

Stop the currently running MySQL/MariaDB
service mysql stop
Start MySQL/MariaDB in non-protected mode:
mysqld_safe --skip-grant-tables --skip-networking &
Login as root in MySQL/MariaDB
mysql -u root
Set the new root password:
update mysql.user set password=password('new-root-password-here') where user='root';
For MariaDB:
I case of MariaDB, it normally checks that the system user is root and the mysql root user has the proper password before granting access. In case of trying to login as root via PHPMyadmin this would fail even with the right MariaDB root password. To remedy to that, we need to disable the ‘unix_socket’ plugin as follows:
update mysql.user set plugin='' where user='root';
Confirm the new changes
select user,host,password,plugin from mysql.user;
The column ‘pluging’ should be empty for the user ‘root@localhost’

Exit MySQL/MariaDB:

Kill the MySQL/MariaDB non-protected process:
killall mysqld
Wait a few seconds to let it finish.
Restart MySQL/MariaDB service as normal:
service mysql start

Now root login should work for PHPMyadmin as for mysql client.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: