X

How To Reset A MySQL Root Password

MySQL contains it own ‘root’ password independent of the system root password, this is a guide on how to reset the MySQL root password. To reset it you will need root access on the server that has the MySQL instance. The same process applies to percona and mariadb servers as well, the only differences will be the stop and start commands (mariadb for mariadb)

Reset A MySQL Root Password

If you already know the root password,  you can also connect to directly to MySQL and reset the password that way. This can be used for resetting any users MySQL password as well.

Connect to MySQL:

mysql -uroot -p

Select the mysql database:

use mysql;

Update the root password:

update user set password=PASSWORD("newpass") where User='root';

Load the new privileges:

flush privileges;

Exit MySQL:

quit;

Thats it for resetting a user password in mysql.

Recover a MySQL Root Password

This covers how to reset the mysql root password if you do not know the current password.

Stop MySQL

First you will need to stop the mysql service

On CentOS 6:

/etc/init.d/mysql stop

On Centos/RHEL 7:

systemctl stop mysql

Start mysqld_safe

You will then want to run mysql_safe with the skip grant tables option to bypass passwords with MySQL:

mysqld_safe --skip-grant-tables &

Reset MySQL Root Password

You will now want to connect to MySQL as root:

mysql -uroot

Then use the mysql database:

use mysql;

Set a new password:

update user set password=PASSWORD("newpass") where User='root';

You will want to replace newpass with the password you want to use

Flush the privileges:

flush privileges;

Exist mysql:

exit;

Restart MySQL

On Centos 6:

/etc/init.d/mysql restart

On Centos 7:

systemctl restart mysql

Test New Root MySQL Password:

mysql -u root -p

You should now be able to connect  successfully to mysql as root using the new password you set.

LinuxAdmin.io
0 0 votes
Article Rating
LinuxAdmin.io:
Related Post