##
#   MySQL configuration for Copernica servers.
#   Any change to this file may require a change in the apparmor file.
#  
#   Any option override can be set at a new file located in /etc/mysql/conf.d/
#   
#   For more information: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html   
#                    and: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
##


##
#   MySQL client CLI settings. 
#   Used for automatic login using the mysql cli program on the local server
##
[mysql]

# Same as -A, client will not scan for table schematics
no-auto-rehash

##
#   Settings for MySQL clients, including the CLI program.
##
[client]
user                            = xxx
password                        = xxx
port                            = xxx

##
#   MySQL Server settings
#   The settings for the MySQL daemon(s) on this server.
##
[mysqld]

# Owner of the mysqld process (should have UID 500)
user                            = mysql

# Socket for local connections
socket                          = /var/run/mysqld/mysqld.sock

# Bind to all posssible IP's
bind-address                    = 0.0.0.0

# The tcp port to listen to
port                            = 3306 

# MySQL server install directory
basedir                         = /opt/mysql/server-5.6

# Mostly used for disk-based sorts (Mostly when sort_buffer_size is not enough)
tmpdir                          = /var/lib/mysql/mysql-tmp

# The actual data of MySQL and the mount point for a SAN volume
datadir                         = /var/lib/mysql    

# Default charset for this server
character_set_server            = utf8

# Default collation for this server
collation_server                = utf8_general_ci

# default storage engine for newly created tables
default_storage_engine          = MyISAM
default_tmp_storage_engine      = MyISAM

# External locking is used when multiple processes use the same file data. When external locking is enabled, any other process can
# lock a table by applying a lock to a file. For example: myisamchk can be ran on the files while the mysql process is still running.
skip-external-locking

# The ca certificate
ssl-ca                          = /etc/mysql/ca-cert.pem
# The ssl certificate
ssl-cert                        = /etc/mysql/server-cert.pem
# The ssl key
ssl-key                         = /etc/mysql/server-key.pem

# delayed queries tweak
delayed_queue_size              = 10000000
delayed_insert_limit            = 10

# enable slow queries log
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow-queries.txt

##
#   MySQL replication master settings
##

# The location where the binlogs are stored.
log_bin                         = /var/log/mysql/master-bin

# We keep the log one day at most.
expire_logs_days                = 1

# Log rotate each 100mb
max_binlog_size                 = 100M

# Do not replicate any table that has '.norep' in the name
replicate-wild-ignore-table = %.norep%

##
#   MySQL replication slave settings
##

# The location where we keep the relay-log received from the master.
relay_log                       = /var/log/mysql/relay-bin

# Temporary directory used by replication for LOAD DATA INFILE, use a real disk for this that survives a server reboot.
slave_load_tmpdir               = /tmp/mysql

# All queries that are replicated from the master, should also be stored in the binlog of the slave to allow chaining
log-slave-updates

# Do not automatically start the slave thread.
skip-slave-start

# Use 16 worker threads for the replication
slave-parallel-workers          = 16


##
#   Tuning options which may be different for different hardware.
##

# Maximum number of simultaneously opened files
open_files_limit                = 500000

# When to declare it timed-out
connect_timeout                 = 30

# Maximum length for group-concatenated strings
group_concat_max_len            = 1024M

# Size of the MyISAM key/index buffer that is shared by the threads.
# The size given in the variable is directly requested. But the server allocates as much memory as possible up to this amount.
# MySQL says: 25% of the physical memory, not more then 50% of the physical memory.
key_buffer_size                 = 2G

# The maximum size for any generated string. The value should be a multiple of 1024.
# The protocol is internally limited to 1G
# MySQL says: As large as your largest BLOB
max_allowed_packet              = 1G

# It limits the complexity of SQL statements that the server can handle.
# It is also used for recursion depth of store procedures and other memory consuming actions.
thread_stack                    = 512K  # Default 256kb

# The number of threads that should be cached so that they can be re-used.
# The thread will be cached when a client disconnects. When a new client connects a cached thread will be used.
thread_cache_size               = 128 # Default 0

# Global connection limit
max_connections                 = 4096 # Default 100

# The maximum number of open tables for all threads. This number will increase the number of filedescriptors.
table_open_cache                = 50000

# Each thread that needs to do a sort allocates a buffer of this size.
# Increase this to spead up ORDER BY and GROUP BY sorts.
sort_buffer_size                = 32M # Maximum of 4GB, default 2MB

# Each thread that does a sequential scan allocates a buffer of this size for each table it scans.
read_buffer_size                = 32M # Maximum of 2GB, default 128kb

# When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.
# Mainly improves ORDER BY statements.
read_rnd_buffer_size            = 32M # Maximum of 2GB, default 256kb

# The buffer size allocated when doing a REPAIR TABLE or when creating indexes using CREATE INDEX or ALTER TABLE
myisam_sort_buffer_size         = 512M # Maximum of 4GB, default 8M

# Do not cache results that are larger then this number of bytes.
query_cache_limit               = 8M # Default 1M

# The total amount of memory allocated for caching query results.
query_cache_size                = 256M # Default 0 (disabled)

# The number of table definitions (.frm) that can be stored in the definition cache.
# The value is numeric numeric and represents the maximum number of tables in the cache.
table_definition_cache          = 50000 # Default 400, minimum 400.

# don't be stricter than we were in 5.5, because our code hinges on many mysql idiosyncracies
sql_mode                        = 'NO_ENGINE_SUBSTITUTION'

##
#   InnoDB specific options
##

# cache for table structures
innodb_additional_mem_pool_size = 100M

# keep a logfile with 5 MB for crash recovery
innodb_log_file_size            = 128M

# flush using the OS cache
innodb_flush_method             = O_DSYNC

# run a maximum of 16 threads on a table
innodb_thread_concurrency       = 16

# innodb should not put all its tables in one huge file, but split them up over different files
innodb_file_per_table           = ON

# use latest and greatest innodb format
innodb_file_format              = Barracuda

# flush only once a second, and disable the double write buffer we do not do crash recovery anyway
innodb_flush_log_at_trx_commit  = 0
innodb_doublewrite              = 0

##
#   Import all *.cnf files from the conf directory.
##
!includedir /etc/mysql/conf.d/