Shulou(Shulou.com)06/01 Report--

Preface

For a long time, to forget the MySQL root password, I thought there was only one solution-skip-grant-tables.

Asked the next group of big shots, the first reaction is also skip-grant-tables. A simple search through the search engine, whether Baidu, or Google, as long as the search is in Chinese, the home page is this solution. It can be seen that this solution has occupied the mind of the user to some extent. Let's take a look at the details below.

The solution of skip-grant-tables

First, close the instance

Here, it can only be done through the kill mysqld process.

Note: it is not a mysqld_safe process, and do not use kill-9.

# ps-ef | grep mysqldroot 6220 6171 0 08:14 pts/0 00:00:00 / bin/sh bin/mysqld_safe-- defaults-file=my.cnfmysql 6347 6220 0 08:14 pts/0 00:00:01 / usr/local/mysql57/bin/mysqld-defaults-file=my.cnf-- basedir=/usr/local/mysql57-- datadir=/usr/local/mysql57/data-- plugin-dir=/usr/local/mysql57/lib/plugin-- user=mysql-- log-error=slowtech.err-- pid-file=slowtech.pid- -socket=/usr/local/mysql57/data/mysql.sock-- port=3307root 6418 6171 0 08:17 pts/0 00:00:00 grep-- color=auto mysqld# kill 6347

Restart the instance using the-- skip-grant-tables parameter

# bin/mysqld_safe-defaults-file=my.cnf-skip-grant-tables-skip-networking &

If this parameter is set, the instance skips the loading of the permission table during startup, which means that any user can log in and do anything, which is quite unsafe.

It is recommended to add the-- skip-networking parameter at the same time. It will cause the instance to close the listening port, so that the TCP connection cannot be established and can only be connected through the local socket.

This is what MySQL8.0 does, turning on-skip-networking automatically when the-- skip-grant-tables parameter is set.

Modify the password

# mysql-S / usr/local/mysql57/data/mysql.sockmysql > update mysql.user 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: 1mysql > flush privileges;Query OK, 0 rows affected (0.00 sec)

Note:

The update statement here is for the operation of MySQL 5.7. if it is in version 5.6, the password field should be modified, not authentication_string.

Update mysql.user set password=password ('123456') where host='localhost' and user='root'

In MySQL 8.0.11, this approach is basically not feasible because it has removed the PASSWORD () function and no longer supports the SET PASSWORD... = PASSWORD ('auth_string') syntax.

It is not difficult to find that the portability of this method is so poor that three different versions have experienced the change of listing and the unavailability of commands.

Next, a more general approach is introduced, which is based on skip-grant-tables.

Unlike the above, it first triggers the loading of the permission table through the flush privileges operation, and then uses the alter user statement to change the password of the root user, such as:

# bin/mysql-S / usr/local/mysql57/data/mysql.sockmysql > alter user 'root'@'localhost' identified by' 123 investors error 1290 (HY000): The MySQL server is running with the-- skip-grant-tables option so it cannot execute this statementmysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > alter user 'root'@'localhost' identified by' 123 transactions query OK, 0 rows affected (0.00 sec)

After logging in without a password, it is not possible to directly perform the alter user operation, because the permission table has not been loaded at this time. You can first trigger the loading of the permission table through the flush privileges operation, and then perform the alter user operation.

It should be noted that password modification via alter user is only applicable to MySQL5.7 and 8.0. if it is MySQL 5.6, it can be written here as

Update mysql.user set password=password ('123456') where host='localhost' and user='root'

Finally restart the instance

Mysql > shutdown;# bin/mysqld_safe-- defaults-file=my.cnf &

It is important to note that if the-- skip-networking parameter is not specified during startup, there is no need to restart the instance. However, most of the solutions seen on the Internet do not specify this parameter, but restart the instance, which is really not necessary.

Here is a summary of this plan:

1. If you only add-- skip-grant-tables, you don't need to restart after you change the password, just execute flush privileges.

two。 From a security point of view, it is recommended to add-- skip-networking. However, because it is a static parameter, it is necessary to restart the instance to remove it.

3. Plus-- skip-networking, although the TCP connection can be blocked, but for other local users, as long as they have readable access to the socket file, they can log in without a password. There are still security risks.

4. It is not recommended to change the password through update. What is more general is alter user.

A more elegant solution

Compared to the skip-grant-tables solution, let's take a look at a more elegant solution, which will only restart once, and there is basically no security risk.

First of all, the instance is still closed.

Second, create a sql file

Write down the password change statement

# vim init.sql alter user 'root'@'localhost' identified by' 123456'

Finally, start the instance using the-- init-file parameter

# bin/mysqld_safe-defaults-file=my.cnf-init-file=/usr/local/mysql57/init.sql &

After the instance is started successfully, the password is modified.

If the mysql instance is managed through a service script, the whole operation can be simplified to one step in addition to creating the sql file.

# service mysqld restart-init-file=/usr/local/mysql57/init.sql

Note: this operation only applies to the service management method of / etc/init.d/mysqld, not to the new systemd of RHEL 7.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.