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.