How to reset root password for MySQL

I will give the option of changing the root password of the user for MySQL.

Stop the MySQL server:

sudo /etc/init.d/mysql stop
sudo service mysql stop

Run mysqld without privilege and network support:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
jobs

Connect as root user without entering a password:

mysql -u root

Change password:

use mysql;
UPDATE mysql.user SET Password=PASSWORD('NEW_PASSWORD') WHERE User='root';
FLUSH PRIVILEGES;
exit;

Or so:

use mysql;
UPDATE user SET authentication_string=PASSWORD("NEW_PASSWORD") WHERE User='root';
UPDATE user SET plugin="mysql_native_password" WHERE User='root';
FLUSH PRIVILEGES;
exit;

Stop the mysqld process:

sudo pkill mysqld
jobs

Let’s start the MySQL server in normal mode:

sudo /etc/init.d/mysql start
sudo service mysql start

Done.

See also my articles:
How to change root password for MySQL
How to create a MySQL user and configure access rights

Leave a comment

Leave a Reply