MySQL Database password recovery notes

Objective:

In Linux installed MySQL, just finished when the root empty password changed, then also changed a good remember the password, did not think after one hours to forget, so with this recovery password process notes

Because the MySQL password is stored in the user table in the database MySQL

So just copy the user table from my MySQL in Windows 2003 to cover it.

In C:mysqldatamysql (Linux is generally in/var/lib/mysql/mysql/) directory has three user table related files user.frm, user. MyD, user. Myi

User.frm//user Table Style file

User. MyD//user Table Data file

User. Myi//user Table Index file

To be on the safe side, three copies are copied, but in fact if you have not changed the table structure on the MySQL that you want to restore, just copy user. MyD on the line.

And 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 Windows 2003 MySQL password.

Mysql>use MySQL mysql>update user set Password=password (' xxxxxx ') where user= ' root ';

There will be an error, prompting the user table with Read permission only

I analyzed the reason, just like this, because the user.* file's permission assignment is under Windows 2003, under Windows 2003 I ls-l see the permission is 666

When I look at Linux, cuff after the permission to become 600 (in fact, the normal situation 600 on the line, but here is the file is not MySQL, the owner of the copy came to root, so there will be insufficient permissions, this time if you change to a permission of 666 can, of course, this is not good, Did not solve the problem of the substance), under the/var/lib/mysql/mysql/Ls-l looked at again

#chown-R mysql:mysql user.* #chmod user.*

Ok,done

Re-start MySQL

Re-connect

Mysql>use MySQL mysql>update user set Password=password (' xxxxxx ') where user= ' root '; Mysql>flush privileges;

One thing to note: If you have windows under MySQL if it is the default configuration, note that you have to perform

Mysql>delete from user where user= '; Mysql>delete from user where host= '% '; Mysql>flush privileges;

All right, here's the procedure to recover the password.

This method is a bit of a limitation, you must also have another user table file

There are several other ways

Other methods one (this is a popular online method, MySQL Chinese reference manual)

1. Send Kill command to MYSQLD server to turn off MYSQLD server (not kill-9), and the file that holds the process ID is usually in the directory where the MySQL database resides.

Killall-term mysqld

You must be a UNIX root user or an equivalent user on the server you are running to perform this operation.

2. Use the '--skip-grant-tables ' parameter to start the mysqld. (Linux under/usr/bin/safe_mysqld--skip-grant-tables, Windows C:mysqlbinmysqld--skip-grant-tables)

3. Then login to mysqld Server without a password,

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

You can also do this: '

Mysqladmin-h hostname-u user password ' new password '

4. Loading permission table: '

mysqladmin-h hostname flush-privileges '

or use SQL command '

FLUSH privileges '

5.

Killall-term mysqld

6. Login with new password

Other method Two

Edit user directly with the hexadecimal editor. MyD file

But here I want to make it clear that I found a problem when I edited it, some of the encrypted cipher strings were stored continuously, some of the last two bits were cut, The latter two bits are stored in the back of the other place. I'm not sure about that yet. Also note that editing is the encrypted password string, which means you still need to have a user table file. The difference between this approach and the one I described above is that this method directly edits the user table file under Linux without having to change the file owner and permissions.