I have some websites running with mysql as the database backend. When I initially set the databases up, I didn’t bother setting up a root password and instead instructed mysql to use a random root password (MYSQL_RANDOM_PASSWORD=yes). I also didn’t bother to write down the generated password. Now I ran into a situation where I needed the password, so I did some research on how to reset the root password. There’s plenty of resources out there, but depending on the mysql/mariadb server version, they are a bit different.
For mysql versions 8.1 or greater, this did work for me:
Restart the database without the grant table
Shut down the database. Im running everything as docker containers with a docker-compose setup, so I just issued
docker compose down
to stop and remove the db container. Afterwards, we need to start the database with these parameters:
This will disable authentication and grant all users full access to the database. We’ll also disable networking for the time being, just to be sure. Since I’m running with docker-compose, I overwrote the mysql container command in the compose file:
command: "mysqld --skip-grant-tables --skip-networking"
Now restart the compose stack:
docker compose up -d
Log in to the database and remove the root password
Now, open a shell into the container:
docker compose exec -it <service_name> /bin/bash
Inside the container execute mysql -u root. We do not require a password, now.
Since you cannot issue an ALTER USER statement when the server runs with the --skip-grant-tables option, we will set the root user password to null, for now. This enables us to login without a password when we remove the --skip-grant-tables option again.
UPDATE mysql.user set authentication_string=null WHERE user="root";
Reset the root password
Log out of the database, shut down and restart the database server without the options added above. For me:
- Shutdown the database container
docker compose down, - Remove the
--skip-grant-tablesoption from the custom container command from above - Restart the stack with
docker compose up -d.
We’ll still leave networking disabled, for now. After all, there’s no root password present.
Again, log in to the database as above. Although the grant tables are not skipped, anymore, we now can still login without a password:
mysql -u root
Set a new root password for both root users (one for localhost, one for the rest):
ALTER user 'root'@'localhost' identified with caching_sha2_password by 'super_secure_password!';
ALTER user 'root'@'%' identified with caching_sha2_password by 'super_secure_password!';
Log out of the database and test the new password:
mysql -u root -p
Enable networking
Shutdown the database and remove the --skip-networking option from the server command.
That’s it, you’ve reset the root password for your mysql server.