Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to restore MySQL users who mistakenly delete root". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

When a friend was asked by his leader to delete users such as root@127.0.0.1,root@'%' and keep only root@localhost.

He wrote an order similar to delete from mysql.user where user='root'.

Note that he did not write the condition of "and host=", which led to the tragedy, and also flush the authorization.

The following simulates the erroneous deletion operation and attempts to restore:

MySQL version:

MySQL 5.5.49

Simulate erroneous deletion operation:

Mysql > DELETE FROM mysql.user WHERE user='root'

Query OK, 1 row affected (0.01sec)

Mysql > FLUSH PRIVILEGES

Query OK, 0 rows affected (0.01 sec)

The solution is as follows:

New installation or initialization of a new instance (preferably consistent with the MySQL version of the erroneous deletion operation)

After initialization, start the instance, log in as root@localhost, and set the password:

On the new instance:

Mysql > SELECT current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > SET PASSWORD=password ('123456')

Query OK, 0 rows affected (0.00 sec)

Mysql > FLUSH PRIVILEGES

Query OK, 0 rows affected (0.00 sec)

Query the root@localhost user information stored in mysql.user:

Mysql > SELECT * FROM mysql.user WHERE user='root' AND host='localhost' INTO OUTFILE'/ tmp/root.txt'

Query OK, 1 row affected (0.00 sec)

For an example of mistakenly deleted operation:

First of all, the previously queried / tmp/root.txt file will be transferred to the machine, here to the same directory, the operation is brief.

Then stop mysqld and start by bypassing the authorization table:

You may not be able to stop it through mysqladmin shutdown. Here, you can simply kill mysqld_safe and mysqld.

Then start:

[root@vm02] # mysqld_safe-- skip-grant-tables &

[1] 2957

[root@vm02 ~] # 160819 17:00:30 mysqld_safe Logging to'/ data/mysql_log/err-log.err'.

160819 17:00:30 mysqld_safe Starting mysqld daemon with databases from / data/mysql

Enter mysql:

[root@vm02 ~] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.5.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > SELECT user (), current_user ()

+-+ +

| | user () | current_user () |

+-+ +

| | root@ | @ | |

+-+ +

1 row in set (0.00 sec)

You can check the mysql.user table. There are no root users deleted by mistake, only business users like xxx@'ip1',yyy@'ip2',:

Mysql > SELECT user,host FROM mysql.user

+-+ +

| | user | host |

+-+ +

| | xxx | 192.168.1.185 |

| | yyy | 192.168.1.187 |

+-+ +

2 rows in set (0.00 sec)

Import the root@localhost information from the mysql.user table of the previous new instance into mysql.user:

Mysql > LOAD DATA INFILE'/ tmp/root.txt' INTO TABLE mysql.user

Query OK, 1 row affected (0.04 sec)

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Mysql > FLUSH PRIVILEGES

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT user,host FROM mysql.user WHERE user='root' AND host='localhost'

+-+ +

| | user | host |

+-+ +

| | root | localhost |

+-+ +

1 rows in set (0.00 sec)

Exit to the shell environment and close the mysqld that was started as skip-grant-tables:

At this point, you can use mysqladmin to turn off mysqld:

[root@vm02 tmp] # mysqladmin-uroot-p123456 shutdown

160819 17:08:08 mysqld_safe mysqld from pid file / data/mysql/mysql-pid ended

[1] + Done mysqld_safe-- skip-grant-tables (wd: ~)

(wd now: / tmp)

[root@vm02 tmp] # ps-ef | grep mysql

Root 3938 1973 0 17:08 pts/0 00:00:00 grep mysql

Restart mysqld again:

[root@vm02 tmp] # mysqld_safe &

[1] 3939

[root@vm02 tmp] # 160819 17:08:53 mysqld_safe Logging to'/ data/mysql_log/err-log.err'.

160819 17:08:53 mysqld_safe Starting mysqld daemon with databases from / data/mysql

It can be used normally. The password was previously set in the initialized new instance:

[root@vm02 tmp] # mysql-uroot-p123456

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.5.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > SELECT user (), current_user ()

+-+ +

| | user () | current_user () |

+-+ +

| | root@localhost | root@localhost |

+-+ +

1 row in set (0.00 sec)

Take a look at the permissions, and you can compare them with the previous ones:

Mysql > SHOW GRANTS

+- -+

| | Grants for root@localhost |

+- -+

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |

| | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION |

+- -+

2 rows in set (0.00 sec)

This is the end of the content of "MySQL mistakenly deleted root users". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.