MySQL Server root Password Recovery

The most important user of a server MySQL is definitely user "root". The user who has administrative privileges for databases (MySQL databases).

If you do not know what the password is set to the root user of MySQL, you should follow some simple steps to reset the password. We did this on a MySQL installed on CentOS 5.6.

Reset / Recover MySQL root Passowrd

1. We login with the root user on the system on which MySQL server. (The root user of the system, not MySQL). Log directly from the console or remotely through connection ssh (With putty on Windows).

2. Stop process / MySQL.

service mysqld stop

or

/ Etc / init.d / mysqld stop

The result will be:

Stopping MySQL: [OK]

3. We start the MySQL server with "-skip-grant-tables". Starting MySQL server / daemon process with no password.

mysqld_safe-skip-grant-tables &

The result after the command line above:

[1] 3041
[Root @ server ~] # Starting mysqld daemon with databases from / var / lib / mysql

4. We connect to mysql server with the root user, using the command:

mysql-u root

The output:

Welcome to the MySQL monitor. Commands end with; or g
Your MySQL connection id is 1
Server version: 5.0.77 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

5. Select the mysql and set the new password for the root user.

use mysql;
update user set password = PASSWORD ('new-password ") WHERE User =' root ';
flush privileges;
quit

Resulting from orders above:

mysql> use mysql;
Reading table information for completion of table and column names
You CAN turn off this feature to get a quicker startup with-A

Changed Database
mysql> update user set password = PASSWORD ('123456 ') WHERE User ='root';
Query OK, 0 Affected rows (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 Affected rows (0.01 sec)

mysql> quit
Bye
[Root @ server ~] #

6. Stop and restart the service / MySQL

service mysqld stop
service mysqld start

Result:

Starting MySQL: [OK]

In the picture below, is shown all password reset process user's root of MySQL.

Now we can connect to MySQL (by phpMyAdmin for example.) with the root user, using the new password set above.

Tutorial tested CentOS 5.6 See 14.12 MySQL Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1.