Author: neoX

Modifying the default options for the docker (podman) MySQL server is essential. The default MySQL options are too conservative and even for simple (automation?) tests the options could be .
For example, modifying only one or two of the default InnoDB configuration options may lead to boosting multiple times faster execution of SQL queries and the related automation tests.

Here are three simple ways to modify the (default or current) MySQL my.cnf configuration options:

• Command-line arguments. All MySQL configuration options could be overriden by passing them in the command line of mysqld binary. The format is:

  --variable-name=value
  

  and the variable names could be obtained by

  mysqld --verbose --help
  

  and for the live configuration options:

  mysqladmin variables
  

• Options in a additional configuration file, which will be included in the main configuration. The options in /etc/mysql/conf.d/config-file.cnftake precedence.
• Replacing the default my.cnf configuration file – /etc/mysql/my.cnf.

Check out also the official page – https://hub.docker.com/_/mysql.
Under CentOS 8 docker is replaced by podman and just replace the docker with podman in all of the commands below.

OPTION 1) Command-line arguments.

This is the simplest way of modifying the default my.cnf (the one, which comes with the docker image or this in the current docker image file). It is fast and easy to use and change, just a little bit of much writing in the command-line. As mentioned above all MySQL options could be changed by a command-line argument to the mysqld binary. For example:

mysqld --innodb_buffer_pool_size=1024M

It will start MySQL server with variable innodb_buffer_pool_size set to 1G. Translating it to (for multiple options just add them at the end of the command):

• docker run

  root@srv ~ # docker run --name my-mysql -v /var/lib/mysql:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=111111 \
  -d mysql:8 \
  --innodb_buffer_pool_size=1024M \
  --innodb_read_io_threads=4 \
  --innodb_flush_log_at_trx_commit=2 \
  --innodb_flush_method=O_DIRECT
  1bb7f415ab03b8bfd76d1cf268454e3c519c52dc383b1eb85024e506f1d04dea
  root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
  | innodb_buffer_pool_size                                  | 1073741824
  

• docker-compose:

  # Docker MySQL arguments example
  version: '3.1'
  
  services:
  
    db:
      image: mysql:8
      command: --default-authentication-plugin=mysql_native_password --innodb_buffer_pool_size=1024M --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=2 --innodb_flush_method=O_DIRECT
      restart: always
      environment:
        MYSQL_ROOT_PASSWORD: 111111
      volumes:
       - /var/lib/mysql_data:/var/lib/mysql
      ports:
        - "3306:3306"
  

  Here is how to run it (the above text file should be named docker-compose.yml and the file should be in the current directory when executing the command below):

  root@srv ~ # docker-compose up
  Creating network "docker-compose-mysql_default" with the default driver
  Creating my-mysql ... done
  Attaching to my-mysql
  my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
  my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
  my-mysql | 2020-06-16 09:45:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
  my-mysql | 2020-06-16T09:45:36.293747Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
  my-mysql | 2020-06-16T09:45:36.293906Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
  my-mysql | 2020-06-16T09:45:36.307654Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  my-mysql | 2020-06-16T09:45:36.942424Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  my-mysql | 2020-06-16T09:45:37.136537Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
  my-mysql | 2020-06-16T09:45:37.279733Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
  my-mysql | 2020-06-16T09:45:37.306693Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
  my-mysql | 2020-06-16T09:45:37.353358Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
  

  And check the option:

  root@srv ~ # docker exec -it my-mysql mysqladmin -p111111 variables|grep innodb_buffer_pool_size
  | innodb_buffer_pool_size                                  | 1073741824
  

OPTION 2) Options in a additional configuration file.

Create a MySQL option file with name config-file.cnf:

[mysqld]
innodb_buffer_pool_size=1024M
innodb_read_io_threads=4
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT

1. docker run

The source path must be absolute path!

docker run --name my-mysql \
-v /var/lib/mysql_data:/var/lib/mysql \
-v /etc/mysql/docker-instances/config-file.cnf:/etc/mysql/conf.d/config-file.cnf \
-e MYSQL_ROOT_PASSWORD=111111 \
-d mysql:8

2. docker-compose
   The source path may not be absolute path.

   # Docker MySQL arguments example
   version: '3.1'
   
   services:
   
     db:
       container_name: my-mysql
       image: mysql:8
       command: --default-authentication-plugin=mysql_native_password
       restart: always
       environment:
         MYSQL_ROOT_PASSWORD: 111111
       volumes:
        - /var/lib/mysql_data:/var/lib/mysql
        - ./config-file.cnf:/etc/mysql/conf.d/config-file.cnf
       ports:
         - "3306:3306"
   

OPTION 3) Replacing the default my.cnf configuration file.

Add the modified options to a my.cnf template file and map it to the container on /etc/mysql/my.cnf. When overwriting the main MySQL option file – my.cnf you may map the whole /etc/mysql directory (just replace /etc/mysql/my.cnf with /etc/mysql below), too. The source file (or directory) may be any file (or directory) not the /etc/mysql/my.cnf (or /etc/mysql)

• docker run:
  The source path must be absolute path.

  docker run --name my-mysql \
  -v /var/lib/mysql_data:/var/lib/mysql \
  -v /etc/mysql/my.cnf:/etc/mysql/my.cnf \
  -e MYSQL_ROOT_PASSWORD=111111 \
  --publish 3306:3306 \
  -d mysql:8
  

  Note: here a new option “–publish 3306:3306” is included to show how to map the ports out of the container like all the examples with the docker-compose here.

• docker-compose:
  The source path may not be absolute path, but the current directory.

  # Use root/example as user/password credentials
  version: '3.1'
  
  services:
  
    db:
      container_name: my-mysql
      image: mysql:8
      command: --default-authentication-plugin=mysql_native_password
      restart: always
      environment:
        MYSQL_ROOT_PASSWORD: 111111
      volumes:
       - /var/lib/mysql_data:/var/lib/mysql
       - ./mysql/my.cnf:/etc/mysql/my.cnf
      ports:
        - "3306:3306"