Resetting the MySQL Root Password

MySQL is a popular relational database management system that requires authentication for access. The root user is the default administrative account with full privileges. If you forget the root password or need to change it, you can reset it using several methods. In this tutorial, we will cover how to reset the MySQL root password on Ubuntu-based systems.

Method 1: Using dpkg-reconfigure (for MySQL versions prior to 5.7)

If you are running an older version of MySQL (prior to 5.7), you can use the dpkg-reconfigure command to reset the root password. This method is only applicable for MySQL versions 5.5 and earlier.

1. Open a terminal and run the following command:

sudo dpkg-reconfigure mysql-server-5.5

   Replace `mysql-server-5.5` with your installed MySQL server version.
2. Follow the prompts to set a new root password.

### Method 2: Updating the Root Password Using SQL Commands

This method involves updating the root password directly using SQL commands.

1. Stop the MySQL service:
   ```bash
sudo service mysql stop

2. Create a temporary directory for MySQL to use (if it doesn’t exist):

sudo mkdir -v /var/run/mysqld && sudo chown mysql: /var/run/mysqld

3. Start the MySQL server with the `--skip-grant-tables` option:
   ```bash
sudo mysqld --skip-grant-tables &

4. Log in to the MySQL shell as root:

mysql -u root mysql

5. For MySQL versions prior to 8.0, update the root password using:
   ```sql
UPDATE mysql.user SET authentication_string = PASSWORD('YOURNEWPASSWORD') WHERE User = 'root';
FLUSH PRIVILEGES;

Replace YOURNEWPASSWORD with your desired new password.
6. For MySQL version 8.0 and later, use:

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

7. Exit the MySQL shell:

exit

8. Stop the temporary MySQL process (if applicable):
   ```bash
sudo killall -9 mysqld

9. Start the MySQL service normally:

sudo service mysql start

### Method 3: Using the ALTER USER Statement with Native Password Plugin

For MySQL version 5.7 and later, you can use the `ALTER USER` statement to change the root password while specifying the authentication plugin.

1. Log in to the MySQL shell as root (if you know the current password):

mysql -u root -p

2. Use the following SQL command to update the root password:
   ```sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURNEWPASSWORD';
FLUSH PRIVILEGES;

3. If you don’t know the current password, follow Method 2.

Additional Tips

• Always use strong passwords for your MySQL root account.
• Consider securing your MySQL installation by running sudo mysql_secure_installation.
• Keep your MySQL server version up to date to ensure you have the latest security patches and features.

By following these methods, you should be able to reset the MySQL root password on Ubuntu-based systems. Remember to choose a secure password and keep it confidential.