When MySQL’s root password is lost and must be reset, there are two popular ways to create a new password. One of the options is far too popular, in my opinion.
The preferred way of setting a root’s password is by using an init-file. The process for doing this is well explained in MySQL’s manual. Using this method requires creating a simple text file, in which the required
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFY BY '****' WIth GRANT OPTION;
(or, alternatively, SET PASSWORD ...) statement is written.
An entry must be written to my.cnf, or supplied via command line parameters:
init-file=/tmp/my-init-file.sql
MySQL must then be restarted. Upon restart, and before opening any outside connections, the init-file is executed. Once MySQL is up and running, the init-file entry should be dropped.
The bad way
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.
So, init-file: one restart; no security issues. skip-grant-tables: two restarts, security breach possible. We have a winner.
(psst…IDENTIFIED BY, not IDENTIFY BY)
The reason the latter approach is so popular is that everybody is able to make it work. Countless folks show up on #mysql on Freenode and say they’ve tried the init file and can’t make it work; we have them try the skip-grant-tables, and a minute later they’re in. I don’t know why the first method proves so difficult – maybe it’s because the manual shows it in 6 or 8 steps that scroll off the screen, but the skip-grant-tables is only 3 steps that all fit in one screen.
Hi Scott,
I agree that the steps described in the manual seem somewhat overwhelming. I always found the init-file to be an easy and fast setup. The general practice, however, shows that people think otherwise.
Shlomi
Nicely .
Nice post！
if you only use skip-grant-tables, you can execute “flush privileges” and remove the skip-grant-tables form my.cnf to bring back the privileges system(mysql 5.6)。
if skip-networking is added to my.cnf, you have to restart the mysqld server.