# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# 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.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

[mysqld]
######################
# * Basic Settings * #
######################
user                         = mysql                             #Default: mysql
port                         = 3306                              #Default: 3306
basedir                      = /usr                              #Default: /usr
datadir                      = /var/lib/mysql                    #Default: /var/lib/mysql
tmpdir                       = /tmp                              #Default: /tmp
lc_messages_dir              = /usr/share/mysql                  #Default: /usr/share/mysql
lc_messages                  = en_US                             #Default: en_US

# If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner.
lower_case_table_names       = 1                                 #Default: (Unix:0) (Windows:1)

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address                 = 0.0.0.0

performance_schema           = ON

###################
# * Fine Tuning * #
###################

# The maximum number of simultaneous client connections. 
max_connections              = 1000                               #Default: 151

# The maximum number of simultaneous client connections per account. 
max_user_connections         = 500                 #Previous 700   #Default: 0 (No Limit)

# Time in seconds that the server waits for a connect packet before returning a 'Bad handshake'.
connect_timeout              = 5                                 #Default: 10

# Time in seconds that the server waits for a connection to become active before closing it
wait_timeout                 = 11111                              #Default: 28800 (8 hours)

# Maximum size in bytes of a packet or a generated/intermediate string.
# Set as large as the largest BLOB, in multiples of 1024
max_allowed_packet           = 16M                               #Default: 16M

# Number of threads server caches for re-use.
thread_cache_size            = 8                                 #Default: 0

# Each session performing a sort allocates a buffer with this amount of memory
sort_buffer_size             = 2M                                #Default: 2M

# The largest size for temporary tables in memory (not MEMORY tables) TODO: Remove.Maybe only relevant for MyISAM and Aria
tmp_table_size               = 32M               #Previous 32M   #Default: 16M

# Maximum size in bytes for user-created MEMORY tables
max_heap_table_size          = 32M               #Previous 32M   #Default: 16M

# Stack size for each thread. 
thread_stack                 = 256K                              #Default: 299008 B

# Minimum size in bytes of the buffer used for queries that cannot use an index, and instead perform a full table scan. 
join_buffer_size             = 256K               #Previous 8M    #Default: 256kB

#Size of the buffer for the index blocks used by MyISAM tables and shared for all threads. See Optimizing key_buffer_size for more on selecting the best val$
key_buffer_size              = 32M

#Number of table definitions that can be cached. Table definitions are taken from the .frm files, and if there are a large number of tables increasing the c$
table_definition_cache       = 50000

# The number of file descriptors available to MariaDB. 
# If you are getting the Too many open files error, then you should increase this limit.
# If set to 0, then MariaDB will calculate a limit based on the following:
# MAX(max_connections*5, max_connections +table_open_cache*2) 
# open-files-limit             = 2000                            #Default: Autosized 

# Maximum number of open tables cached in one table cache instance
table_open_cache             = 4000                              #Default: 2000 


#################################
# * Query Cache Configuration * #
#################################

# Cache only tiny result sets, so we can fit more in the query cache.
# Size in bytes for which results larger than this are not stored in the query cache.
query_cache_limit            = 0                                 #Default: 1M

#Size in bytes available to the query cache. 
query_cache_size             = 0                                 #Default: 1M

# Enable/Disable Query Cache
# for more write intensive setups, set to DEMAND or OFF
query_cache_type             = OFF                               #Default: OFF (0)


###############################
# * Logging and Replication * #
###############################

# Must be unique for each Node in a Replication Domain
server_id                    = 1

#The GTID strict mode is an optional setting that can be used to help the DBA enforce a strict discipline about keeping binlogs
# identical across multiple servers replicating using global transaction ID.
gtid_strict_mode             = ON

#Determines whether replication is row-based(ROW), statement-based(STATEMENT) or mixed(MIXED)
binlog_format                = ROW                               #Default: MIXED

#Whether binary logging is enabled or not. Needed for cluster setups.    
#If no path is specified, the log will be written to the data directory.    
log_bin                                                          #Default: OFF (line does not exist)

#The full path of the binary log file names, excluding the extension. Its value is derived from the rules specified in log_bin system variable.
log_basename                 = master-slave

#The default is 0, in which case the operating system handles flushing the file to disk.
#1 is the safest, but slowest, choice, since the file is flushed after each write.
sync_binlog                  = 0                                 #Default: 0

#Number of days after which the binary log can be automatically removed.    
#By default 0, or no automatic removal. When using replication, should always be set higher than the maximum lag by any replica.
expire_logs_days             = 5                                 #Default: 0

#If the binary log exceeds this size after a write, the server rotates it by closing it and opening a new binary log.
max_binlog_size              = 100M                              #Default: 1073741824 (1G)

#If set to 0, the default, updates on a replica received from a primary during replication are not logged in the replica's binary log. If set to 1, they are.
log_slave_updates            = ON                                 #Default: Off (0)

#Determines which additional warnings are logged. (See MariaDB-Documentation)
log_warnings                 = 2                                 #Default: 2

# Enable the slow query log to see queries with especially long duration
slow_query_log              = OFF                                #Default: Off (0)

#Path for slow_query_log file.
slow_query_log_file          = /var/log/mysql/mariadb-slow.log   #Default: <host_name>-slow.log

# If a query takes longer than this many seconds to execute (microseconds can be specified too), 
# the Slow_queries status variable is incremented and, if enabled, the query is logged to the slow query log. 
long_query_time              = 10                                #Default: 10

#The slow query log will log every this many queries
#log_slow_rate_limit         = 1000                              #Default: 1

#Controls information to be added to the slow query log. (See MariaDB-Documentation)
log_slow_verbosity           = query_plan                        #Default: (EMPTY). Values possible: query_plan, innodb, explain 

#If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode               = NO_ENGINE_SUBSTITUTION,TRADITIONAL

#Semi-Synchronous replication
#This node can be semi-sync master
rpl_semi_sync_master_enabled=ON
#This node can be semi-sync slave
rpl_semi_sync_slave_enabled=ON
#Primary waitpoint
rpl_semi_sync_master_wait_point=AFTER_SYNC

##############
# * InnoDB * #
##############

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

#The default storage engine. The default storage engine must be enabled at server startup or the server won't start. 
default_storage_engine       = InnoDB                            #Default: InnoDB

#Size in bytes of each InnoDB redo log file in the log group.
# you can't just change log file size, requires special procedure
innodb_log_file_size         = 2500M                                #Default: 96MB

#The primary value to adjust on a database server with entirely/primarily InnoDB tables, can be set up to 80% of the total memory in these environments.
innodb_buffer_pool_size      = 10G                             #Default: 128M

#Size in bytes of the buffer for writing InnoDB redo log files to disk.
innodb_log_buffer_size       = 32M                               #Default: 16M

#If set to ON, then new InnoDB tables are created with their own InnoDB file-per-table tablespaces.
innodb_file_per_table        = ON                                #Default: 1 (ON)

#Maximum .ibd files MariaDB can have open at the same time.
#Only applies to systems with multiple XtraDB/InnoDB tablespaces, and is separate to the table cache and open_files_limit.
#innodb_open_files            = 400                               #Default: autosized

#Should be set to around the number of I/O operations per second that system can handle
innodb_io_capacity           = 400                               #Default: 200

#On Unix, before MariaDB 10.6.0, by default fsync() is used to flush data and logs. 
#Adjusting this variable can give performance improvements, but behavior differs widely on different filesystems, and changing from the default has caused problems in some situations, so test and benchmark carefully before adjusting.
innodb_flush_method          = O_DIRECT                          #Default: O_DIRECT

#Number of I/O threads for XtraDB/InnoDB reads.
innodb_read_io_threads       = 4                                 #Default: 4

#Deprecated and ignored from MariaDB 10.5.1, as the original reasons for for splitting the buffer pool have mostly gone away. 
#innodb_buffer_pool_instances = 8                                #Default: 8 (for 8GB in innodb_buffer_pool_size)


#######################
# * Spezifisch * #
#######################
# 
log_bin_trust_function_creators=ON

# 
skip-name-resolve

# 
sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'