Easy way of modifying the mysql config files

[iglocska]

If I am not mistaken, currently there is no easy way via the docker-compose / mapped config files to modify mysql settings and the innodb_buffer_pool_size seems to default to 128M (though to be fair this is on a v2.4.137 instance iirc). Could we expose these settings?

[ostefano]

Not in the same way we do with the other settings. MySQL runs as a separate docker image (the official one released by MariaDB). If that docker image allows those settings to be configured via env vars (substantially medium sized 'if'), the it's just a matter of updating our docker-compose.yml.

[iglocska]

Seems like it's a dead end, there are almost no env vars exposed in the mariadb docker image. Bloody hell.

[HugeekMcGill]

Hi @iglocska,

we ran into the same problem and we were able to changes those value. It made my hair turn gray but it works.

Are you using MySQL or MariaDB ?

We are switching to MariaDB but I can help.

Are those the parameter you are trying to change ?

[ostefano]

Yes, those are the values.

[ostefano]

@iglocska something looks doable, see here https://stackoverflow.com/questions/64825998/how-to-change-the-default-config-for-mysql-when-using-docker-image

[ostefano]

Tested command: --innodb-buffer-pool-size=250M and it seems to work

[HugeekMcGill]

Ahh now i remember.

Following is the command i use to start mysqld inside the container.

command=/entrypoint.sh mysqld --innodb_buffer_pool_size=2147483648 --innodb_change_buffering=none --innodb_io_capacity=1000 --innodb_io_capacity_max=2000 --innodb_log_file_size=629145600 --innodb_log_files_in_group=2 --innodb_read_io_threads=16 --innodb_stats_persistent=ON --innodb_write_io_threads=4 --max_allowed_packet=500M

[HugeekMcGill]

@ostefano do you want me to turn this into a PR ? I guess it would be beneficial since the base parameters are not meeting recommendations.

[ostefano]

@HugeekMcGill go for it if you have something ready, or just paste here your compose file and the default you added

[HugeekMcGill]

@ostefano I'm using supervisord inside the MySQL container so it will be different.

Since the project is using mariaDB is it compatible ?

I'm testing MariaDB integration next week.

[ostefano]

Share everything here. Interested in the format used to specify the default.

[HugeekMcGill]

Let's see how it goes in here, we have to remember the golden rule "If it works, don't look at it in a bad way or it will break lol"

Note: I'm building using docker multistage so i can add my layer of service on top of this misp-docker project

Dockerfile.multistage `FROM mysql/mysql-server:5.7 AS mysql_core

FROM mysql_core AS mysql_db_canssoc ARG LOCAL_HOME_FOLDER="." ARG LOCAL_SERVICE_HOME_FOLDER="/usr/local/lib/services/"

Switch to root user to modify system-wide configuration

USER root

Import MySQL GPG key for the current year

RUN set -eux; \ CURRENT_YEAR=$(date +'%Y'); \ KEY_URL="https://repo.mysql.com/RPM-GPG-KEY-mysql-${CURRENT_YEAR}"; \ if ! rpm --import "$KEY_URL"; then \ echo "Failed to import GPG key for ${CURRENT_YEAR}. Trying previous year."; \ PREVIOUS_YEAR=$((CURRENT_YEAR - 1)); \ PREVIOUS_KEY_URL="https://repo.mysql.com/RPM-GPG-KEY-mysql-${PREVIOUS_YEAR}"; \ rpm --import "$PREVIOUS_KEY_URL"; \ fi

Enable the base repository

RUN yum-config-manager --enable base

RUN yum -y update

RUN yum install -y nano

Install pip and setuptools

RUN yum -y install python3-pip python3-setuptools

Install Supervisor using pip

RUN pip3 install supervisor

Set max_allowed_packet

RUN echo 'max_allowed_packet = 256M' >> /etc/my.cnf

For debug and monitoring

RUN apt-get install -y net-tools netcat iputils-ping procps syslog-ng nano mlocate git cron tzdata logrotate

RUN yum -y install net-tools

Install cron

RUN yum -y install cronie

Install the "procps" package which provides the "ps" command

RUN yum -y install procps

Create a directory to store custom scripts

RUN mkdir -p /usr/local/scripts

Copy your supervisord.conf file

COPY ${LOCAL_HOME_FOLDER}/conf/supervisord/supervisord.conf /etc/supervisor/supervisord.conf RUN mkdir -p /etc/supervisor/conf.d/

RUN mkdir -p /var/log/supervisor/ RUN touch /var/log/supervisor/supervisord.log

Set proper permissions for the script

USER root RUN chown mysql:mysql /usr/local/scripts/mrnet_cleanup.sh RUN chmod +x /usr/local/scripts/mrnet_cleanup.sh

Create directories for the script to use

USER root RUN mkdir -p /usr/local/scripts/tmpdir RUN chown mysql:mysql /usr/local/scripts/tmpdir

Copy Mysqld supervisord and script

COPY ${LOCAL_HOME_FOLDER}/conf/supervisord/mysqld.conf /etc/supervisor/conf.d/mysqld.conf

USER mysql

ENTRYPOINT ["/bin/sh", "-c", "/usr/local/bin/supervisord -c /etc/supervisor/supervisord.conf"] CMD ["/entrypoint.sh", "mysqld"]

`

docker-compose `db: image: misp_db container_name: misp_db hostname: mysql_db_canssoc restart: unless-stopped cap_add:

• SYS_NICE # CAP_SYS_NICE Prevent runaway mysql log environment:
• MYSQL_DATABASE=${MYSQL_DATABASE:-misp}
• MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD:-password}
• MYSQL_USER=${MYSQL_USER:-misp}
• MYSQL_PASSWORD=${MYSQL_PASSWORD:-example}
• TIMEZONE=${TIMEZONE:-America/Montreal} volumes:
• misp_db:/var/lib/mysql networks:

• container_network healthcheck: test: mysqladmin --user=$$MYSQL_USER --password=$$MYSQL_PASSWORD status interval: 2s timeout: 1s retries: 3 start_period: 30s`

  my.cnf `[mysqld]

Performance Tuning Parameters

innodb_buffer_pool_size = 4G # Set to 50-75% of total RAM innodb_log_file_size = 1G # Redo log size for better write performance innodb_flush_log_at_trx_commit = 2 # Set to 2 for improved write performance innodb_flush_method = O_DIRECT # Direct I/O for InnoDB tables innodb_doublewrite = 0 # Disable InnoDB doublewrite buffer for performance innodb_file_per_table = 1 # Use separate tablespace files for each InnoDB table innodb_stats_on_metadata = 0 # Disable InnoDB stats updates on metadata changes innodb_buffer_pool_instances = 8 # Number of buffer pool instances, match to CPU cores innodb_io_capacity = 2000 # Adjust I/O capacity for SSD storage innodb_io_capacity_max = 4000 # Adjust max I/O capacity for SSD storage innodb_flush_neighbors = 0 # Disable flushing for neighboring pages innodb_adaptive_flushing = 1 # Enable adaptive flushing for better performance innodb_read_io_threads = 8 # Number of read I/O threads innodb_write_io_threads = 8 # Number of write I/O threads innodb_purge_threads = 4 # Number of purge threads to improve transaction handling innodb_max_dirty_pages_pct = 90 # Percentage of dirty pages before a checkpoint occurs innodb_log_buffer_size = 128M # Log buffer size for transaction logging innodb_thread_concurrency = 0 # Set to 0 to disable thread concurrency control innodb_lock_wait_timeout = 120 # Maximum time to wait for a lock (seconds)

Query Cache Configuration

query_cache_type = 0 # Disable query cache for better performance query_cache_size = 0 # Set query cache size to 0 to disable it

Connection Parameters

max_connections = 1000 # Adjust based on expected number of concurrent connections max_allowed_packet = 64M # Maximum allowed packet size for client/server communication wait_timeout = 600 # Wait timeout for idle connections (seconds) interactive_timeout = 600 # Interactive timeout for idle connections (seconds)

Logging and Monitoring

slow_query_log = 1 # Enable slow query logging slow_query_log_file = /var/log/mysql/mysql-slow.log # Location of slow query log file long_query_time = 1 # Time in seconds to classify a query as slow log_error = /var/log/mysql/error.log # Location of error log file

General Optimization

tmp_table_size = 256M # Temporary table size for complex queries max_heap_table_size = 256M # Maximum size for in-memory temporary tables table_open_cache = 2000 # Number of table descriptors to cache thread_cache_size = 128 # Thread cache size for improving connection handling `

[HugeekMcGill]

The format is not ideal, I'll fix in the morning.

[ostefano]

No need, I am limiting to what is displayed in MISP as a starting point. Will push a first version today @iglocska

[ostefano]

@iglocska done, will commit now