Mysql deletes the root user by mistake or forgets the root password. solution:

Solution 1:

Go to another server with Mysql installed (the premise is to know the root user password of Mysql on the server), open [Mysql installation directory/var/mysql], and set the user. frm, user. MYD, user. copy the MYI files to the [Mysql installation directory/var/mysql] directory on the problematic server. Then restart the server.

Solution 2:

Modify Your my. ini or my. cnf file and add the following line under [mysqld]
Skip-grant-tables
Then save and restart the MySQL service. Now you can log on to the database with any user name and password.

(1) If the root user is accidentally deleted, use the following statement to create the root user:

Insert into user set Host = 'localhost', User = 'root', Password = Password ('000000'), 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', region = 'y ';

(2) If you forget the password of the root user, use the following statement to change the root password:

Update user set Password = Password ('000000') where User = 'root ';

Mysql deletes the root user by mistake

1. Stop the mysqld process first.

# Killall mysqld entering 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 user set user = 'root', ssl_cipher = ", x509_issuer =", x509_subject = ";

(Add a table as the root user)
Query OK, 1 row affected, 3 warnings (0.00 sec)
(A field such as ssl_cipher does not have a default value. If you add ssl_cipher = ", the field will be OK 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 ';

(The root user permission is modified here)

Mysql> quit;

2. Start mysql normally with the permission
# Service mysqld start

3. Log On with the root user (if phpadmin is available, log on with the root empty password and then change the password or permission)

# Mysql-u root-p

Mysql> update user set password = password ('000000') where user = 'root ';

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

Mysql> flush privileges;

Mysql> quit