mysql accidentally deleted root users or forgot the root password solution

Solution 1:

Go to another server where Mysql is installed (if you know the root user password for Mysql on that server), open the Mysql installation directory /var/mysql, and copy the user.frm, user.MYD, user.MYI3 files into the Mysql installation directory /var/mysql directory of the server in question. Then restart the server.

Solution 2:

Modify your my.ini or my.cnf file by adding the following line under section [mysqld]
skip-grant-tables
Then save and restart the MySQL service. Now you can log into the database with any username or password.

(1) if the root user is deleted by mistake, the root user is created using the following statement:

insert into user set Host='localhost',User='root',Password=Password('111111'),select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y';

(2) if the password of root user is forgotten, the root password can be modified with the following statement:

update user set Password=Password('111111') where User='root';

mysql deleted root users by mistake

1. Finish the mysqld process first

#killall mysqld enters mysql
#/usr/local/mysql/bin/mysql

Enter mysql without loading grant_tables
# mysqld_safe � skip grant - tables &

mysql > use mysql
Database changed

mysql > select * from user;
Empty set (0.00 sec)

mysql > insert into set user='root',ssl_cipher= ",x509_issuer= ",x509_subject= ";

(add root user table)
Query OK, 1 row affected, 3 warnings (0.00 sec)
(a field such as ssl_cipher may be prompted with no default value, just add,ssl_cipher= "; The fields are added in sequence if prompted.)

update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y', Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';

(this is to modify the permissions of the root user)

mysql > quit;

2. Start mysql normally with permission
#service mysqld start

3. Log in with root user (if you have phpadmin, log in with root blank password and then change the password or permission)

#mysql -u root -p

mysql > update user set password=password('123456 ') where user='root';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql > flush privileges;

mysql > quit