How to change DB user/password with Laravel sail?

Question

I'm using Laravel with Sail/Docker, on MacOs.

I'd like to change my DB user and password, just to try and test. If I change them in .env config (sail2/password2), then shut down sail and then sail up -d (or even completely rebuild the containers), I see that:

• Laravel sees the new credentials (sail2), since if I tinker this:

   DB::select("select * from users");
  

It's going to complain like this:

SQLSTATE[HY000] [1045] Access denied for user 'sail2'@'172.20.0.7'

So Laravel reads the new credentials from the environment variables, but the user has no access to the database.

• Docker itself does sees the new environment varialbes, since by inspecting the mysql container I can see this:

Therefore, what I can say is that it all works, except the fact that the new user seems not to be assigned to the current database.

Please note I also tried to connect with Sequel Ace and Table Plus, but doesn't work. Instead, they will connect with the original credentials (sail/password).

Answer 1

You cannot use the MYSQL_* environment variables to change the credentials of an existing database.

Laravel Sail is just a wrapper, and underneath it simply uses the MySQL official docker image (or the MariaDB one, maybe, but it's the same to these effects).

From the readme on that image:

Environment variables

[...] When you start the mysql image, you can adjust the configuration of the MySQL instance by passing one or more environment variables on the docker run command line. Do note that none of the variables below will have any effect if you start the container with a data directory that already contains a database: any pre-existing database will always be left untouched on container startup.

(emphasis mine)

If you are reusing an existing data directory by mounting it within the image, even rebuilding the image from scratch will end you up with an existing database, and the environment variables will do nothing.

To change the credentials (or add new credentials), you need to use the regular methods (e.g. GRANT statements, executed via your client of choice).

Answer 2

I've just found at least a partial solution. Basically, from Docker I can log into the database server as root and from there I can manually add whichever user I want. So, from Docker > click the mysql container and the click exec.

Then log in as root. Then add the new user and grant privileges.

mysql> create user 'sail2'@'%' identified by 'password2';
mysql> grant all on mytestsite.* to 'sail2'@'%';

That will work. Still, not sure if this is the correct/best way to solve my issue.

UPDATE

In the meantime I received some more explanations from the laracasts guys, and I can post a couple of other solutions. But in the end, I still think that the best way remains to log in from docker and change/update user settings from there. Anyway, here's theother ways:

a. Delete the Docker volume attached to the mysql container. This will force mysql to re-create the user from scratch, but will delete everything else too.

b. Edit the docker-compose.yml file and specify a new volume for the mysql container.

mysql:
        image: 'mysql/mysql-server:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ROOT_HOST: '%'
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 1
        volumes:
            - 'sail-mysql:/var/lib/mysql' # change sail-mysql to something else.
            - './vendor/laravel/sail/database/mysql/create-testing-database.sh:/docker-entrypoint-initdb.d/10-create-testing-database.sh'
        networks:
            - sail
        healthcheck:

This will be the same thing as option 2 but without deleting your data, it's just a fresh start. But you still won't have access to your existing data. As a variant to this approach: just delete the volume row > this also will delete data.