How to Change and Reset MySQL 8 Root Password on Ubuntu 18.04

MySQL is one of the most popular database management systems in use today. Changing or resetting the root password is a common administrative task to ensure the security of your MySQL installation. This tutorial will guide you through the process of changing and resetting the root password for MySQL 8 on Ubuntu 18.04.

Prerequisites

• Ubuntu 18.04 server with MySQL 8 installed.
• Access to the terminal with sudo privileges.
• Basic knowledge of command line usage.

Main Steps

Step 1 — Stop the MySQL Service

Before making any changes to the MySQL installation, ensure that the MySQL service is stopped. This can be done using the following command:

sudo systemctl stop mysql

Step 2 — Start MySQL in Safe Mode

Next, start MySQL in safe mode without password validation. This allows you to access the database without needing the current root password.

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

The & at the end runs the command in the background, allowing you to continue using the terminal.

Step 3 — Log in to MySQL

Now you can log into the MySQL shell as root without a password:

mysql -u root

Step 4 — Change the Root Password

Once you have access to the MySQL shell, you can change the root password using the following commands:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Replace new_password with your desired password. Ensure that it is strong and secure.

Step 5 — Exit MySQL Shell

After changing the password, exit the MySQL shell:

exit;

Step 6 — Stop MySQL Safe Mode

To stop the MySQL safe mode, find the process ID (PID) and kill it. You can do this using the following command:

sudo pkill mysqld

Step 7 — Restart MySQL

Finally, restart the MySQL service to apply the changes:

sudo systemctl start mysql

Advanced Configuration

After resetting the password, it is advisable to enhance your MySQL security settings. You can use the MySQL security script, which helps configure security-related options:

sudo mysql_secure_installation

This script will prompt you to make several security-related changes, such as removing anonymous users, disallowing root login remotely, and removing the test database.

Best Practices

• Always use strong, unique passwords for database accounts.
• Regularly update your MySQL version to patch any security vulnerabilities.
• Restrict MySQL access based on IP addresses to enhance security.
• Perform regular backups of your database to prevent data loss.

Troubleshooting

If you encounter issues during the password reset process, consider the following:

• Ensure that no other MySQL instances are running before starting in safe mode.
• Check the MySQL error logs for any indications of what might be wrong.
• Ensure that you are using the correct username and host ('root'@'localhost').

Conclusion

Changing and resetting the MySQL root password on Ubuntu 18.04 is a straightforward process when following the steps outlined in this tutorial. Maintaining secure access to your database is crucial for protecting sensitive data. By following best practices and regularly updating your security settings, you ensure that your MySQL installation remains secure.