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.
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.
service mysqld stop
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:
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.
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:
service mysqld start
systemctl start mariadb.service
Debian and Ubuntu:
service mysql start