HostOnNet Blog

Reset MySQL root password

To reset MySQL root password, first stop MySQL server

Stop MySQL Server

systemctl stop mariadb

If that did not work, one of the following command may work for you.

service mysql stop
service mariadb stop
systemctl stop mysql

Start MySQL with out Password

mysqld_safe --skip-grant-tables

Connect to MySQL

Now open another terminal, run

mysql -u root

To connect to MySQL server.

Set MySQL Password

To set MySQL root password, run

update mysql.user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit

If you are using MySQL 5.7 or latest, you get error

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

You need to use following SQL statements

update mysql.user set authentication_string=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit

Stop MySQL Server

This can be done by CTRL+C on the terminal window where you run command “mysqld_safe –skip-grant-tables” or just kill the process.

Start MySQL Normally

systemctl start mariadb

Now you will be able to connect to MySQL with NEW-ROOT-PASSWORD.

mysql -u root -pNEW-ROOT-PASSWORD

Posted in MySQL