#
# The MySQL database server configuration file.
#
# You can copy this 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.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe-mysqld]. Both versions are currently parsed.
[mysqld-safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
open-files-limit = 64K

[mysqld]

#########################################################################
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
#   You may also need to modify the UPSTART configuration file:
#   /etc/init/mysql.conf to increase the number resources available to MySQL.
#   Here are the suggested entries and these lines need to be BEFORE the
#   PRE-START block:
#
#      limit nofile 72000 72000
#      limit nproc 32000 32000
#

user		= mysql
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /d01/mysql/data
tmpdir		= /d01/mysql/tmp
skip-external-locking

#########################################################################
# Networking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
#bind-address		= 172.27.101.46
skip-name-resolve	= 1

#########################################################################
# Enable the event scheduler
#event-scheduler = ON

#########################################################################
# * Fine Tuning
#
interactive-timeout = 28800
join-buffer-size = 2M
lower-case-table-name = 1
max-allowed-packet = 192M
max-connections = 200
max-heap-table-size = 64M
max-sp-recursion-depth = 25
open-files-limit = 64K
# NOTE: In 5.6, table-open-cache effects performance-schema size!
performance-schema = off
query-cache-limit = 2M
query-cache-size = 64M
query-cache-type = 1
query-prealloc-size = 1M
read-buffer-size = 1M
sort-buffer-size = 2M
# NOTE: In 5.6, table-open-cache effects performance-schema size!
table-open-cache = 4K
table-open-cache-instances = 1
# thread-cache-size = 8 + (max_connections / 100)
thread-cache-size = 10
thread-stack = 192K
wait-timeout = 28800


#########################################################################
# MyISAM
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP

#########################################################################
# * Logging
# Both location gets rotated by the cronjob.

# Log output FILE, TABLE
log-output = FILE

# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general-log-file = /var/log/mysql/general.log
general-log = 0

# SQL Error Log
log-error = /var/log/mysql/error.log
log-warnings = 1

# Here you can see queries with especially long duration
slow-query-log-file = /var/log/mysql/slow.log
slow-query-log = 0
long-query-time = 3
#log-queries-not-using-indexes

#########################################################################
# REPLICATION
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.

# GTID Global Transactional ID's
gtid-mode = on
enforce-gtid-consistency = true

# Sever ID - must be unique across entire MySQL farm!
report-host = atl-mysql01
server-id = 2001

###############################
# Replication: MASTER
##auto_increment_offset = 2
##auto_increment_increment = 2
binlog-cache-size = 256K
binlog-checksum = CRC32
binlog-format = row # Changed to row 2014-06-29 VSJR
binlog-row-image = minimal
binlog-rows-query-log-events = 1
expire-logs-days = 07
log-bin = master-bin.log
log-bin-index = master-bin.index
log-bin-trust-function-creators = 1
master-info-repository = TABLE
master-verify-checksum = 1
max-binlog-size = 1G
sync-master-info = 1

# WARNING! There are serious performance consequences to this setting!
# Best read about it first!
sync-binlog = 0

# Perform binary logging ONLY FOR the following databases.
# ONLY these DBs can be replicated.
#binlog-do-db = 

# DO NOT perform binary logging for the following databases.
# This keeps them from being replicated to a SLAVE too.
# NOTICE: This should match the other replication servers.
binlog-ignore-db = eci_elite_import
binlog-ignore-db = information_schema
binlog-ignore-db = oti_elite_import
binlog-ignore-db = performance_schema
binlog-ignore-db = test
binlog-ignore-db = wbsys

###############################
# Replication: SLAVE
log-slave-updates = 1
read-only = 1
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
relay-log-info-repository = TABLE
relay-log-recovery = 1 # Added 2014-05-07 VSJR
replicate-same-server-id = 0
skip-slave-start = 0
slave-net-timeout = 15
slave-parallel-workers = 0 # Disabled 2014-05-13 VSJR
slave-pending-jobs-size-max = 32M # default 16M
slave-skip-errors=1053 # Added 2014-05-08 VSJR
slave-sql-verify-checksum = 1
slave-max-allowed-packet = 194M # Added 2014-08-09 VSJR

# ONLY replicate changes from the master for the following db:
#replicate-do-db =

# Do NOT replicate changes from the master for the following db:
#replicate-ignore-db =


#########################################################################
# * 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!

# If using just InnoDB set buffer pool size to 50-80% of
# your computer's memory but make sure on Linux x86
# total memory usage is < 2GB.
# (Plus key-buffer-size = 10M, small, but not zero.) 
# EXCEPT when using ALOT of MyISAM tables.
# See MyISAM section below.
innodb-buffer-pool-instances = 2 # one per physical core
innodb-buffer-pool-size = 4G
#innodb-buffer-pool-dump-at-shutdown = ON
#innodb-buffer-pool-load-at-startup = ON
innodb-use-sys-malloc = 1

# Set the log file size to about 25% of the buffer pool size
# but a maximum of 1G.
innodb-log-file-size = 1G
innodb-log-buffer-size = 8M

# Fine Tuning
innodb-file-per-table = 1
innodb-flush-method = O_DIRECT # Applies to Linux/Unix only.
innodb-flush-log-at-trx-commit = 2
innodb-lock-wait-timeout = 50
innodb-open-files = 4K
innodb-print-all-deadlocks = true
innodb-read-io-threads = 64
innodb-support-xa = 0
innodb-thread-concurrency = 0
innodb-use-sys-malloc = 1
innodb-write-io-threads = 64

# Special Case for servers running on VM systems.
innodb-use-native-aio = 0 # Applies to Linux only.

#################################
# * MyISAM
#
# If using just MyISAM, set key-buffer-size to 20%
# of -available- RAM. (Plus innodb-buffer-pool-size=0) 
# Data block caching (from .MYD file) is left to the OS,
# so be sure to leave a bunch of free space for this. 
key-buffer-size = 128M


#########################################################################
#
# * Security Features
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max-allowed-packet = 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key-buffer-size = 16M

#########################################################################
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
#!includedir /etc/mysql/conf.d/

# (end of file)