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

MySQL forgot password how to solve

I once mistakenly deleted mysqlroot user, what should I do?

The previous solution was to restart MySQL by ignoring the authorization table and then inserting relevant data to solve the problem, but this way requires restarting MySQL, which will affect the existing business, so is there any other way to solve it without restarting MySQL?

Because mysql user represents MyISAM engine, we can fix this problem by modifying the corresponding file. The following is my test environment in a drill, for reference only.

1. View existing users 04:18:34 root@localhost [mysql]>select user,host from user;+-------------+--------+| user | host |+---------------+-----------+| mysql.session | localhost || mysql.sys | localhost || root | localhost |+-------------+---------+3 rows in set (0,00 sec) 2. Delete local root user 04:18:59 root@localhost [mysql]>drop user root @'localhost '; 3. View user again 04:20:02 root@localhost [mysql]>select user,host from user;+---------------| user | host |+---------------+-----------+| mysql.session | localhost || mysql.sys | localhost |+---------------+-----------+

Now we begin to recover user data

4. Move user table related files [root@localhost mysql]# cp user.* /vagrant/mysql/3307/data/test/5. Log in to another instance and view the user table 04:23:53 root@localhost [(none)]>use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed04:23:56 root@localhost [test]>show tables;+-------------+| Tables_in_test |+----------------+| user |+----------------+1 row in set (0,00 sec)04:23:58 root@localhost [test]>select * from user\G*************************** 1. row *************************** Host: localhost User: mysql.session Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: Y Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *C7A1AAE2D250AFD864050FAF4935EF6F5D185A92 password_expired: N password_last_changed: 2018-02-23 13:19:12 password_lifetime: NULL account_locked: Y*************************** 2. row *************************** Host: localhost User: mysql.sys Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *C7A1AAE2D250AFD864050FAF4935EF6F5D185A92 password_expired: N password_last_changed: 2018-02-23 13:19:26 password_lifetime: NULL account_locked: Y2 rows in set (0,00 sec) VI. Insert the root user of this instance into the user table just moved 04:25:03 root@localhost [test]>insert into user select * from mysql.user where user = 'root' and host ='localhost'; Query OK, 1 row affected (0,02 sec)Records: 1 Duplicates: 0 Warnings: 0 VII. Move the user file again to the original location [root@localhost test]# cp user.* The requested URL/data/mysql/3306/was not found on this server.

mysql will not restart through this step and will not affect the application.

9. Log in again, OK[root@localhost test]# mysql -uroot -p -S /tmp/mysql3306.sockEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 13Server version: 5.7.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.04:30:57 root@localhost [(none)]>

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.