Solutions for restoring the MySQL root Password

The following article mainly describes how to accidentally lose the MySQL ROOT Password or change the initial password after the MySQL database is downloaded in the LINUX operating system, the actual solution for forgetting the new password is described below. I hope it will be helpful in your future study.

Because the MySQL password is stored in the user table in MySQL, you only need to copy the user table in MySQL under windows 2003 to overwrite it.

In c: \ MySQL \ data \ MySQL \ (linux, there are three user table related files in the/var/lib/MySQL/) directory. frm, user. MYD, user. MYI

User. frm // user Table Style File

User. MYD // user table data file

User. MYI // user table index file

For the sake of insurance, all three are copied. However, if the table structure has not been changed on the MySQL to be restored, just copy user. MYD.

Then

 
 

  
  
#. /etc/rc.d/init.d/MySQL stop  
  
  
#. /etc/rc.d/init.d/MySQL start  
  
  
#MySQL -u root -p XXXXXX 
 
 

Okay, you can log in with the MySQL password in windows 2003.

 
 

  
  
MySQL>use MySQL  
  
  
MySQL>update user set Password=PASSWORD('xxxxxx') where User='root'; 
 
 

An error occurs, prompting that the user table has only the read permission.

I analyzed the reason. This is the only reason, because the permissions assigned to the user. * file are in windows 2003, and in windows 2003, my ls-l permission is 666.

In linux, I can see that after copying the file, the permission is changed to 600 (in fact, under normal circumstances, only the file owner here is not MySQL, the copied owner is changed to MySQL root, so there will be insufficient permissions. At this time, if you change the permission to 666, It will be okay, of course, this is not good, there is no substance to solve the problem ), check ls-l under/var/lib/MySQL /.

Again

 
 

  
  
#chown -R MySQL:MySQL user.*  
  
  
#chmod 600 user.*  
  
  
//OK,DONE 
 
 

Restart MySQL

Reconnect

 
 

  
  
MySQL>use MySQL  
  
  
MySQL>update user set Password=PASSWORD('xxxxxx') where User='root';  
  
  
MySQL>FLUSH PRIVILEGES; 
 
 

NOTE: If MySQL is configured by default in windows, you must execute

 
 

  
  
MySQL>delete from user where User='';  
  
  
MySQL>delete from user where Host='%';  
  
  
MySQL>FLUSH PRIVILEGES; 
 
 

Now, the password recovery process is complete.

This method has some limitations. You must have another user table file.

There are several other methods

Other methods 1 (this is a widely spread method on the internet, MySQL Chinese Reference Manual)

1. Send the kill command to the MySQLd server to disable the MySQLd server (not kill-9). The files that store the process ID are usually located in the directory of the MySQL database.

Killall-TERM MySQLd

You must be a UNIX MySQL root User or an equivalent user on the SERVER you are running.

2. Use the '-- skip-grant-tables' parameter to start MySQLd. (LINUX/usr/bin/safe_MySQLd -- skip-grant-tables, windows c: \ MySQL \ bin \ MySQLd -- skip-grant-tables)

3. log on to the MySQLd server without a password,

 
 

  
  
>use MySQL  
  
  
>update user set passwordpassword=password("new_pass") where user="root";  
  
  
>flush privileges; 
 
 

You can also do this:

 
 

  
  
`MySQLadmin -h hostname -u user password 'new password''。 
 
 

4. Load the permission table: 'mysqladmin-h hostname flush-privileges ', or use the SQL command 'flush privileges '.

5. killall-TERM MySQLd

6. log in with a new password

Other method 2

Directly use the hexadecimal editor to edit the user. MYD File

But here I want to explain that I found a problem when editing here. Some encrypted password strings are stored continuously, and some of the last two digits are cut, the last two digits are stored in other places. I haven't understood this yet. note that the encrypted password string is edited, that is, you still need to have another user table file. The difference between this method and the method described above is that this method directly edits the user table file in linux, and you do not need to change the file owner and permissions.