Sometime MySQL root password is lost, because of last admin did not leave you any notes on the server or you are given your client server to fix some database or script related errors where MySQL root password is required.
I always knew one way of resetting it, but recently came up with another which is The Right Way.
It works both for linux and win32 OSes, in my example it is linux. It requires just one MySQL restart and is more secure.
# How to reset MySQL root password - The Right Way
Create text file with following SQL statements
``` GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '[NEW_PASS]' WIth GRANT OPTION; ```Replace [NEW_PASS] with desired new password and save this file somewhere like /tmp/pwd.sql
Update config my.cnf
Usually it is located in /etc/ or /etc/mysql/. Find section called [mysqld] and add following line[mysqld]
init-file = /tmp/pwd.sql
Restart MySQL. Voila. Your new password should be in place.
Do not forget remove /tmp/pwd.sql and comment "init-file" option in config file, because without init-file found MySQL won't start next at all.
Ubuntu users warning! In Ubuntu, MySQL is protected with Apparmor and is prohibited to read files from /tmp. In Ubuntu name file /etc/mysql/pwd.cnf, this way it is read by server process.
# The other way - The Bad One
For some reason, the following method seems to be far more popular, starting MySQL with "--skip-grant-tables".
When MySQL is started with this parameter, it completely avoids checking its grant tables upon connection and upon query. This means anyone can log in from anywhere, and do anything on the database.
While the manual does mention this is a less preferred way of doing it, it does not elaborate. Starting MySQL with this parameter is a huge security breach. This is why one may wish to add the --skip-networking parameter, to only allow connection from the localhost (using Unix socket, for example).
Moreover, after MySQL starts, and the necessary GRANT or CHANGE PASSWORD take place, the server is still unsuitable for connections. This is why it needs to be restarted again, this time without --skip-grant-tables.