Changing the MySQL password-root

Working with MySQL databases is a common task when managing websites and applications on a VPS or a dedicated server. It’s important to know how to properly change the MySQL root password or regain access if the password has been lost.

---

Changing the MySQL root password

To change the MySQL root password, first connect to your server via SSH.

Then log in to MySQL as the root user with the following command:

mysql –u root –p

Press Enter, enter your current password, and press Enter again.

Now run the following commands:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
quit

The root password will now be changed to newpass.

---

Resetting the MySQL root password

If the root password has been lost, you can reset it. To do this, MySQL needs to be started in a special mode that bypasses password authentication. The steps vary depending on your operating system.

---

Ubuntu

Stop the MySQL service:

service mysql stop

Create the /var/run/mysqld directory and set ownership to the mysql user and group:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

Start MySQL without password checks:

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Check that the process is running:

jobs

Connect to MySQL without a password:

mysql -u root

Reset the password with the following commands:

FLUSH PRIVILEGES;
USE mysql;
UPDATE user SET authentication_string=PASSWORD("new_root_password") WHERE User='root';
UPDATE user SET plugin="mysql_native_password" WHERE User='root';
quit

Kill the process and restart MySQL:

sudo pkill mysqld
sudo service mysql start

Now you can connect with your new password:

mysql -u root --password=1111

---

Debian

Stop the MySQL service:

service mysql stop

Start MySQL in safe mode:

/usr/bin/mysqld_safe --skip-grant-tables --user=root &

Connect to MySQL:

mysql -u root

Reset the password:

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

Restart MySQL:

sudo service mysql stop
sudo service mysql start

---

CentOS 7

Stop the MySQL service:

sudo /etc/init.d/mysqld stop

Start MySQL in safe mode:

sudo mysqld_safe --skip-grant-tables &

Connect to MySQL:

mysql -u root

Change the password:

use mysql;
update user set password=PASSWORD("new_root_password") where User='root';
flush privileges;
quit;

Restart MySQL:

/etc/init.d/mysql stop
/etc/init.d/mysql start