Reset SQL DB password

If you’ve forgotten the root password for a MySQL instance running in a Docker Compose environment, you can reset it by entering the container and using the MySQL shell. Below are the steps to achieve this.

Step 1: Stop the MySQL Container

First, stop the MySQL container managed by Docker Compose:

docker-compose stop <mysql_service_name>

Step 2: Start the Container with --skip-grant-tables

Modify your docker-compose.yml file to include the --skip-grant-tables flag in the MySQL service.

Here’s an example:

services:
  mysql:
    image: mysql:8.0.40
    environment:
      MYSQL_ROOT_PASSWORD: dummy
    command: mysqld --skip-grant-tables
    ports:
      - "3306:3306"

Save the changes and restart the container:

docker-compose up -d

Step 3: Access the MySQL Container

Bash into the running container:

docker exec -it <mysql_service_name> bash

Once inside the container, log in to the MySQL shell without a password:

mysql -u root

Step 4: Reset the Root Password

While in the MySQL shell, run the following commands to reset the root password:

Generate the hashed password:

SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('new_password')))));

Copy the resulting hash (e.g., *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29) and update the password:

UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' WHERE User = 'root';

Apply the changes:

FLUSH PRIVILEGES;

Step 5: Restart the Container Normally

Exit the MySQL shell:bashCopy code

exit

Exit the container:bashCopy code

exit

Stop the container:

docker-compose stop

Remove the --skip-grant-tables flag from your docker-compose.yml file.

Restart the container normally:

docker-compose up -d

Step 6: Verify the New Password

After restarting the container, test the new root password:

mysql -u root -p

Enter the new password to confirm the reset was successful.

Conclusion

Using Docker Compose simplifies managing your MySQL container. Following these steps, you can securely reset your MySQL root password without requiring external tools.

Always remember to remove --skip-grant-tables after completing the reset to avoid potential security vulnerabilities.