Once in a while it happens that I forget the root password of a MySQL server. Several methods are explained in details on the MySQL website.
Until now I’ve always used the
--skip-grant-tables method, and I was surprised to see a new method using the
--init-file option. I tried it, but it didn’t work for me.
So I’ve used once more the proven traditional method. The first step is to stop the service and start MySQL without authorisation checks.
sudo service mysql stop sudo mysqld --skip-grant-tables --skip-networking &
At this point mysqld is running without checking any authorisation, so I can connect without a password, and change the password.
mysql -u root mysql> UPDATE mysql.user SET Password=PASSWORD('newPassword') WHERE User='root'; mysql> FLUSH PRIVILEGES; mysql> exit
The final step is to stop the MySQL instance with the
--skip-grant-tables option, and start the service normally, which at this point will be using the new password.
killall mysqld sudo service mysql start