"MySQL" MySQL root password forgot what to do?

MySQL forgot the password how to solve

I have once mistakenly deleted the Mysqlroot user, how to do?

The previous solution is to restart MySQL by ignoring the authorization table and then inserting the relevant data to solve the problem, but this way needs to restart MySQL, will affect the existing business, then there is no other way can not restart MySQL to solve it?

Because MySQL user represents the MyISAM engine, we can fix the problem by modifying the corresponding file. Here is a walkthrough of my test environment, for informational purposes only.

First, view existing users

04:18:34 [email protected] [mysql]>select user,host from user;+---------------+-----------+| user          | host      |+---------------+-----------+| mysql.session | localhost || mysql.sys     | localhost || root          | localhost |+---------------+-----------+3 rows in set (0,00 sec)

Second, delete the local root user

04:18:59 [email protected] [mysql]>drop user [email protected]‘localhost‘;

Third, see the user again

04:20:02 [email protected] [mysql]>select user,host from user;+---------------+-----------+| user          | host      |+---------------+-----------+| mysql.session | localhost || mysql.sys     | localhost |+---------------+-----------+

Now we start restoring user data

Iv. Moving user table related files

[[email protected] mysql]# cp user.* /vagrant/mysql/3307/data/test/

Five. Log in to another instance and view the user table

04:23:53 [email protected] [(none)]>use test; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changed04:23:56 [email protected] [test]>show tables;+----------------+| Tables_in_test |+----------------+| User |+----------------+1 row in Set (0,00 sec) 04:23:58 [email protected] [test]>select * from user\g*** 1. Row *************************** host:localhost User:mysql.session select_pri V: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 Gra            Nt_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_v Iew_priv:n create_routine_priv:n alter_routine_priv:n create_user_priv:n event_priv:n T Rigger_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 refere Nces_priv:n index_priv:n alter_priv:n show_db_priv:n super_priv:n Create_tm P_table_priv:n lock_tables_priv:n execute_priv:n repl_slave_priv:n repl_client_priv:n C            Reate_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: *c7a1aae2d250afd864050faf4935ef6f5 D185a92 password_expired:n password_last_changed:2018-02-23 13:19:26 password_lifetime:null account_loc Ked:y2 rows in Set (0,00 sec) 

Insert the root user of this instance into the user table just moved.

04:25:03 [email protected] [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

Seven, move the user file to the original location again

[[email protected] test]# cp user.* /vagrant/mysql/3306/data/mysql

Eight, reload configuration to memory

[[email protected] test]# kill -HUP `pidof mysqld`

MySQL does not restart with this step and will not affect the application

Nine, re-login, OK

[[email protected] 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 [email protected] [(none)]>

"MySQL" MySQL root password forgot what to do?