MySQL database password recovery _ MySQL

The password of the MySQL database was restored to MYSQL under LINUX, and the ROOT empty 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 I took a note of the password recovery process.

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.

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 mysql

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

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

This is the only reason, because the user. * file permission is assigned in windows 2003, and the ls-l permission in windows 2003 is 666.

In linux, after copying the file, the permission is changed to 600 (in fact, under normal circumstances, 600 is enough, but the file owner here is not mysql. after copying the file, the owner becomes 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 and there is no solution to 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 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 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: mysql bin mysqld -- skip-grant-tables)

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

> Use mysql

> Update user set password = password ("artian") 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.