preamble
Always thought there was only one solution for forgetting MySQL root password-skip-grant-tables.
Asked the group of experts, the first reaction is also skip-grant-tables. through the search engine simple search, whether Baidu, or Google, as long as the Chinese search, the first page is this solution. It can be seen that this solution to some extent has occupied the user's mind. Here's a look.
Solution for skip-grant-tables
First, close the instance
Here, it can only be done by kill mysqld process.
Attention:Not a mysqld_safe process, also never use kill -9.
# ps -ef |grep mysqld root 6220 6171 0 08:14 pts/0 00:00:00 /bin/sh bin/mysqld_safe --defaults-file= mysql 6347 6220 0 08:14 pts/0 00:00:01 /usr/local/mysql57/bin/mysqld --defaults-file= --basedir=/usr/local/mysql57 --datadir=/usr/local/mysql57/data --plugin-dir=/usr/local/mysql57/lib/plugin --user=mysql --log-error= --pid-file= --socket=/usr/local/mysql57/data/ --port=3307 root 6418 6171 0 08:17 pts/0 00:00:00 grep --color=auto mysqld # kill 6347
Use the --skip-grant-tables parameter to restart the instance
# bin/mysqld_safe --defaults-file= --skip-grant-tables --skip-networking &
With this parameter set, the instance skips the loading of the permission table during startup, which means that any user can log in and perform any operation, which is quite insecure.
It is recommended to also add the --skip-networking parameter. It will make the instance close the listening port, so naturally it will not be able to establish a TCP connection, but only through a local socket.
MySQL 8.0 does this, and automatically turns on --skip-networking while the --skip-grant-tables parameter is set.
change your password
# mysql -S /usr/local/mysql57/data/
mysql> update set authentication_string=password('123456') where host='localhost' and user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Attention:
The update statement here is for MySQL 5.7 operations, if it was in 5.6, the password field would have been modified, not the authentication_string.
update set password=password('123456') where host='localhost' and user='root';
In MySQL version 8.0.11, this approach is essentially infeasible, since it has removed thePASSWORD()function and no longer supports theSET PASSWORD ... = PASSWORD ('auth_string')Grammar.
It is not difficult to find that the portability of this approach is really poor, three different versions, it has experienced a change in the column name, and the command is not available.
Below, another, more generalized approach is presented, still based on skip-grant-tables.
Unlike the above, it will first be passed through theflush privilegesoperation triggers the loading of the permissions table, which then uses thealter userstatement to change the root user's password, for example:
# bin/mysql -S /usr/local/mysql57/data/ mysql> alter user 'root'@'localhost' identified by '123'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> alter user 'root'@'localhost' identified by '123'; Query OK, 0 rows affected (0.00 sec)
After logging in without a password, you can directly execute thealter useroperation won't work because the permissions table is not loaded at this point. This can be done by first passing theflush privilegesoperation triggers the loading of the permissions table, then executes thealter userOperation.
It is important to note that byalter userChanging the password only applies to MySQL 5.7 and 8.0, in case of MySQL 5.6, this can be written as
update set password=password('123456') where host='localhost' and user='root';
Final restart of the instance
mysql> shutdown; # bin/mysqld_safe --defaults-file= &
Note that if the --skip-networking parameter is not specified during startup, there is no need to restart the instance. However, the vast majority of scenarios I've seen online do not specify the parameter, but restart the instance, which is really not necessary.
Here's a summary of the program:
1. If you only add --skip-grant-tables, you don't really need to reboot after changing the password, just perform flush privileges.
2. From a security point of view, it is recommended to add --skip-networking. but since it is a static parameter, excluding it requires a restart of the instance.
3. Add --skip-networking, although you can block the TCP connection, but for other local users, as long as they have read access to the socket file, they can log in without a password. There is still a security risk.
4. It is not recommended that you change your password by updating it; the more common method is actually alter user.
A more elegant solution
As opposed to the skip-grant-tables solution, let's take a look at another, more elegant solution that will only reboot once and is essentially free of security risks.
First, still shutting down the instance
Next, create a sql file
Write the password change statement
# vim alter user 'root'@'localhost' identified by '123456';
Finally, use the --init-file parameter to start the instance
# bin/mysqld_safe --defaults-file= --init-file=/usr/local/mysql57/ &
After the instance starts successfully, the password is changed.
If the mysql instance is managed through a service script, the whole operation can be simplified to one step, except for the creation of the sql file.
# service mysqld restart --init-file=/usr/local/mysql57/
Attention:This operation only applies to /etc//mysqld, which is a service management method, and does not apply to systemd, which is new to RHEL 7.
summarize
Above is the entire content of this article, I hope that the content of this article on your learning or work has a certain reference learning value, if there are questions you can leave a message to exchange, thank you for my support.