How can I retrieve the root password and root password I forgot in mysql?

I do not remember that the root password is something that many friends do, but I accidentally delete the root user. This problem is rarely encountered by some friends, let's take a look at how to deal with the problem of forgetting the root password and deleting the root account.

I do not remember that the root password is something that many friends do, but I accidentally delete the root user. This problem is rarely encountered by some friends, let's take a look at how to deal with the problem of forgetting the root password and deleting the root account.

Solution 1:

Go to another Mysql-installed server (the premise is to know the Mysql root user Password on the server), open [Mysql installation directory/var/mysql tutorial], 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. Next, you can use any user name and password to log on to the database.

(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 you have a php tutorial admin, 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