MySQL root More elegant solution after password forgetting

Preface

For a long time, I thought there was only one solution for forgetting MySQL root cipher-skip-grant-tables.

I asked the big coffee in the next group, and the first reaction was skip-grant-tables. Simple search through the search engine, whether it is Baidu, or Google, as long as it is searched in Chinese, the home page is this solution. It can be seen that this solution has occupied the user's mind to some extent. Let's take a look at it in detail.

Solution of skip-grant-tables

First, close the instance

Here, only through the kill mysqld process.

Note: It is not an mysqld_safe process and should 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=my.cnf
mysql  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=3307
root  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 will skip 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 make the instance close the listening port, and naturally it will not be able to establish an TCP connection, but can only be connected through the local socket.

This is what MySQL8.0 does, and when the--skip-grant-tables parameter is set, it will automatically turn on--skip-networking.

Change password

# mysql -S /usr/local/mysql57/data/mysql.sock

mysql> 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: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Note:

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

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

In MySQL version 8.0. 11, this approach is basically not feasible because it has been removed PASSWORD() Function and is no longer supported SET PASSWORD ... = PASSWORD ('auth_string') Grammar.

It is not difficult to find that the portability of this method is too poor. Three different versions have experienced the change of column names and the unavailability of commands.

Next, we introduce another more general approach, which is based on skip-grant-tables.

Different from the above, it will pass first flush privileges Operation triggers the loading of the permission table, and then uses the alter user Statement to modify the password of an root user, such as:

# bin/mysql -S /usr/local/mysql57/data/mysql.sock

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 password, execute it directly alter user The operation will not work because the permission table has not been loaded yet. Can pass first flush privileges Operation triggers the loading of the permission table, and then executes alter user Operation.

It should be noted that through alter user Change passwords only apply to MySQL 5.7 and 8.0, and 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 &

Note that if the--skip-networking parameter is not specified during startup, there is no need to restart the instance. However, most schemes seen on the Internet do not specify this parameter, but it is unnecessary to restart the instance.

The following is a summary of this scheme:

1. If only-skip-grant-tables is added, after modifying the password, there is no need to restart, just execute flush privileges.

2. For safety reasons, it is recommended to add-skip-networking. However, because it is a static parameter, eliminating it requires restarting the instance.

3. Adding--skip-networking, although TCP connection can be blocked, other local users can log in without password as long as they have the readable permission of socket file. There are still potential safety hazards.

4. It is not recommended to change the password by update. The more common one is alter user.

A more elegant solution

Compared with skip-grant-tables scheme, let's take a look at another more elegant solution, which will only restart once, and basically has no potential safety hazard.

First, still close the instance

Second, create an sql file

Write a password modification statement

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

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

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

After the instance starts successfully, the password is modified ~

If the mysql instance is managed through a service script, the whole operation can be simplified to one step except to create an sql file.

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

Note: This operation is only applicable to the service management mode/etc/init. d/mysqld, not the new systemd of RHEL 7.

Summarize