How to reset MySQL root password

Forget MySQL root password ? No problem! Here in this article will explain how to reset MySQL root password using CLI.

`
You must have root user access on your server in order to reset MySQL root password.

Resetting MySQL Root Password.


Follow below steps to reset MySQL root password:

Step-1: Log in with root user using SSH.

Remember we need to run all below commands as root user, you can either login directly with root user or use su or sudo to have root power.

Step-2: Stop MySQL Server service using appropriate command. It depends which Linux distribution you have.

CentOS 6

service mysqld stop
CentOS 7

systemctl stop mariadb.service

Debian and Ubuntu:

service mysql stop

Step-3: Now restart MySQL service with '--skip-grant-tables' option. This option will enables anyone to connect without a password and with all privileges.

a) You must be aware of, running MySQL with '--skip-grant-tables' option is highly insecure so we added '--skip-networking' option in conjunction with '--skip-grant-tables' to prevent remote clients from connecting. It must be run for brief period while you resetting root password. After resetting MySQL root password we will see how to stop mysqld_safe instance and restart MySQL server securely.

a) Do not forget to add (&) sing at the end of command that will allow this command to run in background so we can type next command into same shell to reset password.

Run following command

mysqld_safe --skip-grant-tables  --skip-networking &

Step-4: Log into MySQL Server using the below command:

mysqld

Step-5: Reset MySQL root password

You will see mysql> prompt, at this prompt enter the following command; replacing YOUR-NEW-PASSWORD with the new root password you wish to keep.

UPDATE mysql.user SET Password=PASSWORD('YOUR-NEW-PASSWORD') WHERE User='root';

Step-6: Perform a flush-privileges operation and exit the prompt.

FLUSH PRIVILEGES;
exit;

Step-7: Shutdown the MySQL server

Enter your newly update MySQL root password in order to shutdown MySQL server.

mysqladmin -u root -p shutdown

Step-8: Start MySQL Server in normal mode using the following command:

CentOS 6

service mysqld start

CentOS 7

systemctl start mariadb.service

Debian and Ubuntu:

service mysql start

Similar Posts