Reset root MySQL password in Docker

Bummer 🤬

Recently I had to restore a MySQL backup from a client. The backup was the Docker and MySQL files. Unfortunately the password was unknown and I had to reset it.

On the internet I found a lot of tutorials on how to do it if the MySQL server is on your local machine. Unfortunately, none helped me with the dockerized version. So here I compiled the steps to do it.

So, I had the following (simplified) docker-compose.yml file:

services:
  mysql:
    image: mysql:5.6
    ports:
      - '3306:3306'
    volumes:
      - ./storage/docker/mysql:/var/lib/mysql

We need to create a mysql-init.sql file with the SQL queries to reset the root password. Tip: this file can contain all kind of queries. Note: if you are using MySQL 5.6 or older, you need to replace `authentication_string` with `Password`.

USE mysql;
UPDATE user SET authentication_string=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
FLUSH PRIVILEGES;

Now, change the docker-compose.yml to mount the file in the docker container.

services:
  mysql:
    # ...
    volumes:
      # ...
      - ./mysql-init.sql:/tmp/mysql-init.sql

To reset the root user’s password it is necessary to run the following commands.

# stop the container if it is running
docker-compose stop mysql
# enter the docker container without starting mysql server
docker-compose run mysql bash
# start the MySQL server with the init file previously created
mysqld_safe --init-file=/tmp/mysql-init.sql &
# wait 10 seconds for the server to finish starting up
# test if the password has been changed
mysql -u root -p
# type the new password