Way to regain access to Mysql in Docker (Laradock) when database was change from Mysql 8 to 9

solution.md

Way to regain access to mysql in Docker (Laradock)

It's short tutorial for you if your database was created in Mysql 8, and now it upgraded to Mysql 9 where native password plugin was removed.

How it happened:

I was using Mysql 8. But today I noticed that my Docker has grown too much, so I decided to clean up the outdated files. But I accidentally deleted the built images.

After that, I've re-build my Laradock stack and noticed that the Mysql container started to give an error, as it turned out it was an error about the unknown variable mysql_native_password.

I tried different approaches that I used to fix it before and nothing helped. Later, I searched a ton of material, but nothing really worked.

Then I noticed that I had a MYSQL_VERSION variable in the .env file of laradock with the value latest and it installed Mysql 9 for me.

I thought that a simple downgrade would help, but after that I was faced with the following version conflict. Somehow, the file ~/.laradock/data/mysql/mysql.ibd (from variable DATA_PATH_HOST) changed to Mysql 9 and thus prevented the existing databases from being pulled up.

I still don't understand why. But if anyone has a similar problem. Here's how I solved it.

Step 1: Remove deprecated/removed configs from Mysql config and Docker

• Laradock: Remove Run condition

RUN if [ "${MYSQL_VERSION}" \> "8.4.0-0.000" ]; then \
echo 'mysql_native_password=on' >> /etc/mysql/conf.d/my.cnf; \
else \
echo 'default-authentication-plugin=mysql_native_password' >> /etc/mysql/conf.d/my.cnf; \
fi

• Remove default-authentication-plugin=mysql_native_password and mysql_native_password=on from my.cnf file

Step 2: Open to my.cnf and add skip-grant-tables to access database without password

[mysqld]
skip-grant-tables

Step 3: Add new root user

1. If you use laradock, so rebuild and run mysql container with skip-grant-tables. Run: docker-compose up -d --build mysql
2. Enter mysql container: docker-compose exec mysql bash
3. Enter mysql by typing mysql in console
4. FLUSH PRIVILEGES;
5. Create new root user with caching_sha2_password plugin CREATE USER 'admin'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'admin';
6. Grant all privileges to MySQL User on all databases GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

Step 4: Change password from mysql_native_password to caching_sha2_password for existing users

1. Select all users with native password: SELECT user, host, plugin FROM mysql.user WHERE plugin LIKE '%native%';
2. FLUSH PRIVILEGES;
3. And regenerate password for root and other users with native password ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'root';