#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
port = 3306
socket = /run/mysqld/mysqld.sock

[mysqld]
# ============================================
# General Server Settings
# ============================================

# Unique server ID for replication. Each server in a replication setup
# must have a unique ID. Default is 0, which is invalid for replication.
server-id = 1

# Path to the database files. This is where all database data will be stored.
datadir = /var/lib/mysql/

# Location of the PID file. This file contains the process ID of the running MySQL server.
pid-file = /var/run/mysqld/mysqld.pid

# Disable DNS lookups when clients connect to the server, improving connection speed.
# Note: With this setting enabled, only IP addresses will be logged.
skip-name-resolve

# Set the default storage engine for new tables. InnoDB is generally recommended
# for its ACID compliance and support for transactions.
default-storage-engine = InnoDB

# Define the transaction isolation level. REPEATABLE-READ is a common default,
# ensuring consistent reads within a transaction.
transaction-isolation = REPEATABLE-READ

# Prevent the use of symbolic links to ensure data security.
skip-symbolic-links

# ============================================
# Performance Optimizations
# ============================================

# Enable native asynchronous I/O for improved performance in InnoDB.
innodb-use-native-aio = 0

# Set the size of the InnoDB buffer pool. This should be 70-80% of the available memory.
# Larger values allow more data to be cached in memory, improving performance.
innodb-buffer-pool-size = 1G

# Minimum InnoDB buffer pool size when auto-shrinking under memory pressure.
# Shrinks pool halfway between current size and this value. 0 = no minimum.
innodb-buffer-pool-size-auto-min = 0

# Configure the size of the InnoDB redo log files. Larger log files can improve
# performance for write-heavy workloads but require more recovery time after a crash.
innodb-log-file-size = 1G

# Define the size of the InnoDB log buffer. This buffer holds transaction logs
# in memory before they are written to disk. Larger buffers reduce disk I/O.
innodb-log-buffer-size = 32M

# Flush logs to disk after each transaction. This ensures ACID compliance,
# but may reduce performance. Setting this to 2 can improve performance at the
# cost of potential data loss during crashes.
innodb-flush-log-at-trx-commit = 1

# Number of background threads for read and write operations in InnoDB.
# Increase these values for high I/O workloads.
innodb-read-io-threads = 8
innodb-write-io-threads = 8

# Configure the I/O capacity for background operations such as flushing.
# Set this based on your disk's capabilities.
innodb-io-capacity = 6000
innodb-io-capacity-max = 8000

# Disable query caching as it is not beneficial for most modern workloads.
# Consider enabling it only if your application benefits from repeated identical queries.
query-cache-type = 0

# Set the maximum size for temporary tables stored in memory.
tmp-table-size = 64M

# Configure the maximum size for internal temporary tables stored in memory.
max-heap-table-size = 64M

# Define the maximum number of simultaneous client connections. Adjust based
# on your application's concurrency requirements.
max-connections = 200

# Number of threads to cache for reuse. Higher values reduce the overhead of
# creating new threads for each connection.
thread-cache-size = 50

# Configure the cache size for table definitions and open tables. Larger caches
# improve performance for workloads with many tables.
table-definition-cache = 4000
table-open-cache = 4000

# Set the buffer size for the Aria storage engine, used in MariaDB-specific workloads.
aria-pagecache-buffer-size = 128M

# ============================================
# Security Settings
# ============================================

# Restrict file imports and exports to a secure directory to prevent unauthorized access.
secure-file-priv = /var/lib/mysql/

# Load the password validation plugin to enforce strong password policies.
# plugin-load-add = validate_password.so

# Enforce a strong password policy with specific requirements.
# validate-password-policy = STRONG

# Minimum password length for increased security.
# validate-password-length = 12

# Require at least one uppercase letter in passwords.
# validate-password-mixed-case-count = 1

# Require at least one numeric character in passwords.
# validate-password-number-count = 1

# Require at least one special character in passwords.
# validate-password-special-char-count = 1

# ============================================
# Binary Logging and Replication
# ============================================

# Enable binary logging for replication and point-in-time recovery.
# The log file will be named 'speedtest-tracker_binlog'. You can specify a different name if desired.
log-bin = speedtest-tracker_binlog

# Set the maximum size for each binary log file. Once the file size reaches this limit,
# a new binary log file is created. A value of 0 means there is no size limit for the binary logs.
max-binlog-size = 500M

# Define the number of days after which binary logs will be automatically purged.
# This helps prevent the binary log files from growing indefinitely.
expire-logs-days = 7

# Enable checksums for binary logs to ensure data integrity.
binlog-checksum = CRC32

# Configure the format for binary logging. ROW-based replication is recommended
# for ensuring data consistency.
binlog-format = ROW

# Enable compression for binary logs to reduce disk usage.
log-bin-compress = 1

# Disable binary log encryption for better performance. Enable it if data security
# requires encrypted logs.
encrypt-binlog = 0

# Automatically purge relay logs to save disk space.
relay-log-purge = 1

# Enable relay log recovery to ensure consistency during replication recovery.
relay-log-recovery = 1

# Configure whether replication slave connections are required before purging logs.
slave_connections_needed_for_purge = 0

# ============================================
# Character Set and Encoding
# ============================================

# Set the server's default character set to UTF-8 with full Unicode support.
character-set-server = utf8mb4

# Set the default collation for the server to match the UTF-8 character set.
collation-server = utf8mb4_general_ci

# Ensure proper encoding when clients connect to the server.
init-connect = 'SET NAMES utf8mb4'

# ============================================
# Monitoring and Debugging
# ============================================

# Enable the Performance Schema for detailed diagnostics.
performance-schema = 1

# Enable specific consumers in the Performance Schema for tracking events.
performance-schema-consumer-events-statements-history = 1
performance-schema-consumer-events-transactions-history = 1
performance-schema-consumer-events-waits-history = 1

# Enable detailed InnoDB status output for monitoring locks and transactions.
innodb-status-output = 0
innodb-status-output-locks = 0

# Disable the general query log by default to avoid excessive disk usage.
# Enable it only for debugging purposes.
general-log = 0

# ============================================
# Temporary Files
# ============================================

# Specify a directory for temporary files. Storing temporary files on an SSD
# can improve performance.
# tmpdir = /tmp

# ============================================
# SSL/TLS Security
# ============================================

# SSL/TLS-instellingen
# ssl-ca = /etc/mysql/ssl/ca-cert.pem
# ssl-cert = /etc/mysql/ssl/server-cert.pem
# ssl-key = /etc/mysql/ssl/server-key.pem
# require-secure-transport = 1

# ============================================
# Inclusion of Additional Configuration Files
# ============================================

# Import all .cnf files from the specified configuration directories.
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/