MySQL forgot password or mistakenly delete root user effective solution

When we use MySQL, sometimes forget the user's password, especially the root user's password, this time we need to walk the back door to change the password, the idea and the Linux root user password is almost the first bypass the permission authentication phase and then use an update user command updated password, If you want to restore the root user to mistakenly delete the need to add root to MySQL's user table must be manually joined in order to give you a valid way to share:

1. Document Replacement Method:

to other MySQL-installed servers (provided that you know the root user password for MySQL on the server), open "MySQL installation directory/var/mysql", which will be user.frm, user. MYD, user. Myi three files are copied to the "MySQL installation directory/var/mysql" Directory of the problematic server. Then restart the server.

2. Manually add the root user method:

1, if the root user is mistakenly deleted, then use the following statement to create the root user:

End the mysqld process first

Killall mysqld

do not load grant_tables and enter MySQL
[[email protected] ~] #mysqld_safe –-skip-grant-tables &

mysql> use mysql
Database changed

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 ';

then exit exits,Restart the database normally, do not add that one skip parameter

With the new password 111111 login, after the author test, the trial is uncomfortable

2. If you have forgotten the root user's password, use the following statement to modify the root password:

Update user Set Password=password (' 111111 ') where user= ' root ' and host= ' localhost '; 

The above command to change the password to 111111 others to keep the same one can not be poor or there will be problems

3, use the root user login (if you have phpadmin directly with the root blank password login and then modify the password or permissions on it)

#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> qui

This article is from the "loss is Blessing" blog, please be sure to keep this source http://dragonfly.blog.51cto.com/2928582/1866952

MySQL forgot password or mistakenly delete root user effective solution