Change Mysql root password – The Right Way

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

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

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.

 

1 comment

  1.  ( 2012.12.20 05:13 ) : 感谢博主,前几次借助博主的分享成功卸载了几次,但是反复多卸载重装个几次,不管用了,卸载不干净。后来用这个搞定了如果运行这些第二步、apt-get rveome mysql-server purge第三步、apt-get rveome mysql-common purge 您没卸载干净,那你可以 第一步、apt-get -f autoremove 第二步、apt-get rveome mysql-server purge 第三步、apt-get rveome mysql-common purge

Leave a Reply

Your email address will not be published.