We'll cover using a MySQL container in development.

Spin Up A MySQL Container

We'll use docker-compose to create the mysql container. While we only spin up one container, this still makes it much easier since we need to pass it a decent number of options.

Note that we create a data volume.

version: '3'
services:
  mysql:
    image: mysql:5.7
    ports:
     - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      MYSQL_DATABASE: "app"
      MYSQL_USER: "app"
      MYSQL_PASSWORD: "app"
    volumes:
     - mysqltest:/var/lib/mysql
volumes:
  mysqltest:
    driver: "local"

Volume

Let's see the created volume:

docker volume ls

Sequel Pro

We can see how to use Sequel Pro to connect to the database. We simply connect to it using 127.0.0.1 and port 3306, since the container is sharing port 3306 to our host file system.

Logs

Let's see inside the container and see where logs belong. We'll grab the logs from conf.d so we can recreate them, as we'll be recreating and customizing these.

# bash into it
docker-compose exec mysql bash

> cd /var/log/mysql
# Error but maybe we want other logs

> cd /etc/mysql/conf.d
# Anything loaded here is used, so lets grab docker.cnf
# and then mount the directory into mysql

# NOTE: WE GRAB THIS SO IT'S STILL THERE, SINCE WE'LL BE OVER-RIDING IT
> exit

docker-compose exec mysql cat /etc/mysql/conf.d/docker.cnf > docker.cnf
mkdir logs
mkdir conf.d
mv docker.cnf conf.d/

Custom .cnf file (Enable general log)

Let's screate two .cnf files to customize how MySQL behaves (we'll be enabling the general database log).

File conf.d/docker.cnf:

[mysqld]
skip-host-cache
skip-name-resolve

File conf.d/query-log.cnf:

[mysqld]
general-log=On
general-log-file=/var/log/mysql/query.log

Docker-Compose Update

We'll update docker-compose.yml to share both the local logs directory and conf.d directory. We'll get these files on our local system when the container is running.

version: '3'
services:
  mysql:
    image: mysql:5.7
    ports:
     - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "root"
      MYSQL_DATABASE: "app"
      MYSQL_USER: "app"
      MYSQL_PASSWORD: "app"
    volumes:
     - mysqltest:/var/lib/mysql
     - ./logs:/var/log/mysql
     - ./conf.d:/etc/mysql/conf.d
volumes:
  mysqltest:
    driver: "local"

Restart the container to suck in the new configuration:

# Restart:
docker-compose restart

# Or:
docker-compose down
docker-compose up -d

Dynamically, can run commands:

We can make sure our changes took place by running the following SQL within Sequel Pro against the database.

SET GLOBAL general_log = 'On';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';

mysqldump

# Unfortunately, we need to put the password into
# the command directly here :/
docker-compose exec mysql mysqldump -u root -proot app | gzip > app.sql.gz

Without Password

Let's see how to use mysqldump without having to add the password.

File conf.d/mysqldump.cnf

[mysqldump]
user=root
password=root

Then restart it to make sure it takes effect:

docker-compose exec mysql mysqldump app | gzip > app.sql.gz