Changing the MySQL Root Password

Changing the Password If the Current One Is Lost

If you have lost the current password of the database root user, you can change it by following these instructions:

1. First, connect to the server via SSH.
2. Then stop the MySQL service:

/etc/init.d/mysql stop

3. To change the MySQL service settings and allow it to start without checking access privileges, edit the mysqld.cnf file. Follow these steps:

3.1. Open the file in a text editor:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

3.2. Add the following line to the [mysqld] section:

skip-grant-tables

As a result, your file should look something like this:

3.3. Save the changes (nano: Ctrl O -> Enter -> Ctrl X, or Ctrl X -> Y -> Enter).

4. Now restart MySQL:

service mysql restart

5. Open the mysqld.cnf file again:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Remove the line you added earlier, then save the file.

6. Now connect to MySQL as root without a password:

mysql

7. Select the active database:

use mysql;

8. Update the password:

update user set authentication_string=password('your_new_password') where user='root';

9. Apply the changes:

flush privileges;

10. Exit MySQL with the \q command
11. Restart the service in normal mode:

/etc/init.d/mysql start

12. To make sure the password has been changed successfully, connect to MySQL as root with the new password:

mysql -u root -p

Then enter your new password.

Changing the Password If the Current One Is Known or Was Never Set

If you need to change the current MySQL root password, or set one if it was never set, you can do it as follows:

1. First, connect to the server via SSH.
2. Then open the MySQL console:

If the root password has not been set yet, use this command:

mysql

If you already have a current password, run the following:

mysql -u root -p

Then enter the current password.

3. Now run the following query, replacing "password" with the new password you want:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');

4. Exit MySQL using the \q command.
5. To verify the changes, connect with the new password:

mysql -u root -p

Then enter your new password.