Skip to content

Containerized Database Settings

Description

On environments that are running the database server in a containerized environment (Docker), software version and server options can be managed using /opt/ebh/config.ini configuration file.

Usage

❗Only Infrastructure approved versions and settings should be used here to ensure compatibility with the hosting environment

Confirm database server is running in a container

$ systemctl status mysql.service
● mysql.service
    Loaded: loaded (/etc/systemd/system/mysql.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mysql.service.d
            └─override.conf
    Active: active (running) since Tue 2023-12-19 12:35:29 CST; 2 days ago
    Process: 864287 ExecStartPre=/usr/bin/docker kill mysql (code=exited, status=1/FAILURE)
    Process: 864299 ExecStartPre=/usr/bin/docker rm mysql (code=exited, status=1/FAILURE)
    Process: 864305 ExecStartPre=/usr/bin/docker pull ${DB_SERVER}:${DB_VERSION} (code=exited, status=0/SUCCESS

The relevant line is ExecStartPre=/usr/bin/docker

Modify the configuration file

Location /opt/ebh/config.ini

# MySQL/Mariadb service
# mariadb 10.3,4,5
# mysql 5.7 (will pull latest)
DB_SERVER="mysql"
DB_VERSION="8.0"
DB_BUFFER_POOL_SIZE="4G" # may need adjustment
# sql settings
sort_buffer_size="8M" # default 2 MB, Each thread that needs to do a sort allocates a buffer of this size.
join_buffer_size="262144" # use default 256k, larger values may significantly slow down memory allocation.
read_buffer_size="2M" # default 128k, increased for sequential table scan buffering.
max_connection="5000" # default 151, more needed in case application does not properly close connections.
max_allowed_packet="64M" # default 16M, allow larger product uploads.
#innodb_io_capacity="3000" # default setting recommended
#innodb_read_io_threads="64" # default setting recommended
#innodb_write_io_threads="64" # default setting recommended

Modifying the DB_VERSION directive will allow the database version to modified while keeping the database intact. EX:

DB_VERSION="5.7" 

to

DB_VERSION="8.0"

Stop and start the service (restart will not trigger the update)

$ systemctl stop mysql.service
$ systemctl start mysql.service

Check the service and version

$ systemctl status mysql.service

$ mysql -e "status"
mysql  Ver 8.0.34-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:          27316
Current database:
Current user:           [email protected]
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.35 MySQL Community Server - GPL
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 2 days 8 hours 31 min 18 sec

Threads: 2  Questions: 1336240  Slow queries: 4  Opens: 5035  Flush tables: 3  Open tables: 2984  Queries per second avg: 6.566