[kr-en01] Recover MySQL root password

In case you've forgotten your MySQL root password, we'll show you some easy steps to set a new one. The process will be carry on with CentOS commands but it will be similar on another distro.

Stop MySQL service

# service stop mysqld

Start service with special parameters in wich you wont need any password

# mysqld_safe --skip-grant-tables --skip-networking --user=mysql &
[1] 29800
# Starting mysqld daemon with databases from /var/lib/mysql

Verify that the process had been started with previous options

# ps aux | grep mysql
root 29800 0.0 0.0 4484 1112 pts/0 S 18:30 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --skip-networking --user=mysql
mysql 29852 0.0 0.4 136268 17420 pts/0 Sl 18:30 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock --skip-grant-tables --skip-networking
root 29889 0.0 0.0 3940 732 pts/0 S+ 18:30 0:00 grep mysql

Acces MySQL without password

# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

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

mysql>

Change root password and quit

mysql> update user set password=password('0p3nMySQLGr0up') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

Check access with new password

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45 Source distribution

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

mysql>