Step-by-step tutorial to learn how to recover the root password of a SQL server.
The most important user of the server MySQL is definitely the user "root". This user has it all administrative privileges databases (MySQL databases).
In case you no longer know what the MySQL root user password is, you will need to follow a few simple steps to reset this password.
The tutorial is made for a server MySQL installed on the operating system CentOS, but the process is the same for other distributions Linux.
How to recover the password for the root user of a SQL server
To recover the password for the root user of a SQL server, the simplest method is by SSH (Secure Shell).
1. We log in with the user "root” to the system on which the MySQL server is. (the root user of the system, not of the MySQL server). Login directly from the console or remotely via connection ssh (with putty from the systems Windows).
2. The stop / MySQL.
service mysqld stop
or
/etc/init.d/mysqld stop
The result will be:
Stopping MySQL: [ OK ]
3. We start the MySQL server with the option "--skip-grant-tables
". Starting MySQL server/daemon process without password.
mysqld_safe --skip-grant-tables &
The output after the command line above:
[1] 3041
[root@server ~]# Starting mysqld daemon with databases from /var/lib/mysql
4. We connect to the mysql server with the user "root", using the command:
mysql -u root
The output of:
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 database and set the new password for the user "root".
use mysql;
update user set password=PASSWORD("New_Password") where User='root';
flush privileges;
quit
Results from the above commands:
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
Database changed
mysql> update user set password=PASSWORD("123456") where User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@server ~]#
6. We stop and restart the service / server MySQL
service mysqld stop
service mysqld start
Result:
Starting MySQL: [ OK ]
The picture below shows everything the password reset the user's "root” of the server MySQL.
Now we can connect to MySQL (by phpMyAdmin e.g.) with the "root" user, using the new password set above.
Tutorial tested CentOS 5.6 with MySQL Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1.