Solution for mysql to accidentally delete the root user or forget the root password

1. First stop the mysqld process # killallmysqld does not load grant_tables and enters mysql # mysqld_safe -- skip-grant-tables to enter mysql # using * fromuser; Emptyset (0.00sec) mysqlinsertintouser

1. First stop the mysqld process # killall mysqld does not load grant_tables and enters mysql # mysqld_safe -- skip-grant-tables to enter mysql #/usr/local/mysql/bin/mysql use mysql Database changed mysql select * from user; empty set (0.00 sec) mysql insert into user

1. Stop the mysqld process first.

# Killall mysqld

Enter mysql without loading grant_tables
# Mysqld_safe -- skip-grant-tables &


Go to mysql
#/Usr/local/mysql/bin/mysql

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 has no default value., Ssl_cipher = ''; if any of its fields is prompted, add them one by one.)

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 ('20140901 ') Where user = 'root ';

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

Mysql> flush privileges;

Mysql> quit

Mysql
# Mysql-u root
ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)


Use the password to enter mysql
# Mysql-u root-p 123456
Welcome to the MySQL monitor. Commands end with; or/g.
Your MySQL connection id is 25 to server version: 4.1.20

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

Mysql>