MySQL Database Password Recovery notes

MYSQL was installed in LINUX, and the empty ROOT password was changed when it was just installed. At that time, a memorable password was also changed. I did not expect to forget it in an hour, so with this password recovery process note, because the MySQL password is stored in the user table in the mysql database, you only need to copy the user table in MySQL under windows2003 to overwrite it.

MYSQL was installed in LINUX, and the empty ROOT password was changed when it was just installed. At that time, a memorable password was also changed. I did not expect to forget it in an hour, so with this password recovery process note, because the MySQL password is stored in the user table in the mysql database, you only need to copy the user table in MySQL under windows 2003 to overwrite it.

Preface:

I installed MYSQL in LINUX and changed the empty ROOT password when I first installed it. At that time, I also changed a memorable password. I did not expect to forget it in an hour, so I took notes on the password recovery process.

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

So you just need to copy the user table in MySQL under windows 2003 and overwrite it.

In c: mysqldatamysql (linux, there are three user table related files in the/var/lib/mysql/) Directory: user. 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 mysqlmysql> 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 root, so there will be insufficient permissions. At this time, if you change to 666 permissions, It will be okay, of course, this is not good, there is no substance to solve the problem ), check the ls-l in/var/lib/mysql /.

# Chown-R mysql: mysql user. * # chmod 600 user .*

// OK, DONE

Restart MYSQL

Reconnect

Mysql> use mysqlmysql> 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 root user or an equivalent user on the SERVER you run to perform this operation.

2. Use the '-- skip-grant-tables' parameter to start mysqld. (LINUX/usr/bin/safe_mysqld -- skip-grant-tables, windows c: mysqlbinmysqld -- skip-grant-tables)

3. log on to the 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. 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.