Posts | About | Tools

Laravel App With MySQL In Docker

May 5, 2020 by Areg Sarkissian

In this article I will show you how to run a MySQL database server in a local docker container. I will also show how you can configure the container to persist the database data using docker volumes so that the data is persistent across container restarts.

Creating the docker-compose file

Note: skip to the next section if you already have a docker-compose.yml file in the root directory

In this section we will create a new docker-compose.yml file in the Laravel project root directory:

touch docker-compose.yml
echo 'version: "3.1"' >> docker-compose.yml
echo 'services:' >> docker-compose.yml

Creating the data directory

Note: skip to the next section if you already have a data directory in the root directory

In this section we will create a new /data directory in the Laravel project root directory:

echo '/data' >> .gitignore
mkdir data

The data directory is where the MySQL data files will persist. Since we don’t want to commit the data files to our code repository, we need to add the the directory to our .gitignore file.

Adding the mysql service to docker-compose

In this section we will add the MySQL docker compose service configuration to the docker-compose.yml file.

mysql:
  image: mysql:8.0
  container_name: myapp-mysql
  volumes:
    - ./data/mysql:/var/lib/mysql
  environment:
    - MYSQL_ROOT_PASSWORD=myapp
    - MYSQL_DATABASE=myapp
    - MYSQL_USER=myapp
    - MYSQL_PASSWORD=myapp
  ports:
    - "8001:3306"

Note that the MySQL server port 3306 internal to the docker network is mapped to port 8001 on our localhost so that we can connect to the instance on localhost:8000.

Also Note that we have docker volumes mapping that maps the docker directory /var/lib/mysql where MySQL stores its data to the ./data/mysql directory in our project data directory that we created on localhost. This is how the data is persisted to our local machine.

When we run the container for the first time, MySQL will automatically create a database named myapp since we provided the MYSQL_DATABASE=myapp environment variable.

Adding environment variables to connect to the mysql service

In order for our application to connect to the MySQL server running in the docker container we need to configure environment variables and setup the application database connection configuration.

First need to set the following environment settings in the .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=8001
DB_DATABASE=myapp
DB_USERNAME=myapp
DB_PASSWORD=myapp

Next we need to update the mysql configuration settings in the config\database.php file:

# the default database connection setting
'default' => env('DB_CONNECTION', 'mysql'),

'connections' => [
# the default database connection
'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '8001'),
        'database' => env('DB_DATABASE', 'myapp'),
        'username' => env('DB_USERNAME', 'myapp'),
        'password' => env('DB_PASSWORD', 'myapp'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],
]

Run the Docker services

Now we are ready to run the MySQL container by running the following bash command:

docker-compose up -d

All the containers should startup as can be seen by running the following command:

docker ps -a

In the following sections, I list various ways of connecting to the MySQL server running in the container. These include connecting with command line clients, connecting from php code from within our Laravel project as well as connecting with artisan Tinker and artisan database migrations.

Connecting using mysqlsh cli

Note: make sure no space between the -p option an password

Using mysqlsh cli:

sudo mysqlsh --sql -h localhost -P 8001 -u root -pmyapp -D myapp
sudo mysqlsh --sql -h localhost -P 8001 -u myapp -pmyapp -D myapp

Connecting using mysql cli

Note: make sure no space between the -p option an password

sudo mysql -h localhost -P 8001 -u root -pmyapp myapp
sudo mysql -h localhost -P 8001 -u myapp -pmyapp myapp

Note you may be asked to type in your macOS password to execute the command

Connecting with TablePlus to running mysql container

Open TablePlus and create a connection with the following:

click create a new connection select MySQL database option click create type in myapp for the connection name

Enter the following credentials from the .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=8001
DB_DATABASE=myapp
DB_USERNAME=myapp
DB_PASSWORD=myapp

click on the test button to test connection click connect button to connect to MySQL running in the container

databases view as root user

Once connected as root we can execute a the show databases command to see all application and system databases. We would not see the system databases if we were connected as a non root user.

SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| radar              |
| sys                |
+--------------------+

Test Connection using artisan tinker

php artisan tinker
>>> DB::connection()->getPdo();
=> PDO {#3043
     inTransaction: false,
     attributes: {
       CASE: NATURAL,
       ERRMODE: EXCEPTION,
       AUTOCOMMIT: 1,
       PERSISTENT: false,
       DRIVER_NAME: "mysql",
       SERVER_INFO: "Uptime: 59  Threads: 2  Questions: 9  Slow queries: 0  Opens: 116  Flush tables: 3  Open tables: 37  Queries per second avg: 0.152",
       ORACLE_NULLS: NATURAL,
       CLIENT_VERSION: "mysqlnd 7.4.4",
       SERVER_VERSION: "8.0.20",
       STATEMENT_CLASS: [
         "PDOStatement",
       ],
       EMULATE_PREPARES: 0,
       CONNECTION_STATUS: "127.0.0.1 via TCP/IP",
       DEFAULT_FETCH_MODE: BOTH,
     },
   }
>>>

Test connection from Laravel application

   try {
        var_dump(DB::connection()->getPdo());
    } catch (PDOException $e){
        var_dump($e->getMessage());
    }

Running authentication database migrations

php artisan migrate

This command will user authentication tables by running the applications existing migration files against the MySQL database.

If we created the project UI using the --auth flag the application will contain the UI for registering a user and log in.

So we should be able to run the application and launch a browser to register a user and log in.

/Applications/Google\ Chrome.app/Contents/MacOS/Google\ Chrome -a myapp.test

Note: I am using Laravel Valet to host Laravel applications so I did not have to explicitly run the artisan serve command to serve the application.

Persisting data between docker container runs

To check that the database data is persisted on our local machine we need to stop then restart the containers and check if the data is still available.

We can stop the contaier by running:

docker-compose down

If we try to connecting to the MySQL server now we should see it fail to connect since the server is not running anymore.

We need to check the ./data/mysql directory where we should see the persisted database files.

We can then bring the MySQL service back up by running:

docker-compose up -d

And now we should see that the database connection is working again and that the tables and data that we previously had in the database should still be there.

Executing and Testing the MySQL the container from the command line

#run mariadb container and run the mysql cli in the running container
docker-compose exec mysql mysql -u myapp -p myapp
# will display my_app_name database that was created upon starting the mysql service
show databases;

While mysql container is running cd into your laravel app that is configured to connect to the mysql instance running on this localhost container.

Now open a new terminal window and run:

php artisan tinker
DB::connection()->getPdo();

You should see the connection info.

Switch to the terminal window where the mysql container is running and type exit to exit the mysql cli and consequently stop the running container.

If this was the first time running the mysql container, back in the project root terminal window cd into the ./data/mysql/ directory to see the saved database files.