# # 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 pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /d01/mysql/data tmpdir = /d01/mysql/tmp lc-messages-dir = /usr/share/mysql skip-external-locking # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 ######################################################################### # 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 = 16M # 20131015-1; was 2M lower-case-table-names = 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 read-rnd-buffer-size = 16M # 20131015-1 sort-buffer-size = 2M # NOTE: In 5.6, table-open-cache effects performance-schema size! table-open-cache = 4K table-open-cache-instances = 16 # 20131015-1; was 1 # thread-cache-size = 8 + (max_connections / 100) thread-cache-size = 10 thread-stack = 192K wait-timeout = 28800 ######################################################################### # * 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-file = /d01/mysql/logs/general.log general-log = 0 # SQL Error Log #log-error = /var/log/mysql/error.log log-error = /d01/mysql/logs/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-file = /d01/mysql/logs/slow.log slow-query-log = 1 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 # Server ID - must be unique across entire MySQL farm! report-host = mysql01 server-id = 1001 ############################### # Replication: MASTER # auto-increment-increment = 4 # max number of masters (same across farm!) auto-increment-offset = 1 # unique across farm! 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 = FILE # Changed TABLE->FILE 2014-09-19 VSJR 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 = 0 relay-log = slave-relay-bin relay-log-index = slave-relay-bin.index relay-log-info-repository = FILE # Changed TABLE->FILE 2014-09-19 VSJR relay-log-recovery = 1 # Added 2014-05-07 VSJR replicate-same-server-id = 0 skip-slave-start = 1 slave-max-allowed-packet = 194M # Added 2014-08-09 VSJR slave-net-timeout = 15 slave-parallel-workers = 4 # 2014-11-04 VSJR slave-pending-jobs-size-max = 32M # default 16M slave-skip-errors=1053 # Added 2014-05-08 VSJR slave-sql-verify-checksum = 1 # 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 = 8 # one per physical core innodb-buffer-pool-size = 32G innodb-buffer-pool-dump-at-shutdown = OFF innodb-buffer-pool-load-at-startup = OFF # 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-checksum-algorithm = CRC32 innodb-file-per-table = 1 innodb-flush-method = O_DIRECT # Applies to Linux/Unix only. innodb-flush-neighbors = 0 innodb-flush-log-at-trx-commit = 2 innodb-io-capacity = 2000 # 20131015-1 innodb-io-capacity-max = 6000 # 20131015-1 innodb-lock-wait-timeout = 50 innodb-lru-scan-depth = 2000 # 20131015-1 innodb-monitor-enable = '%' # 20131015-1 innodb-open-files = 4K innodb-print-all-deadlocks = true #innodb-read-ahead = 0 # test innodb-read-io-threads = 8 innodb-support-xa = 0 innodb-thread-concurrency = 0 innodb-use-native-aio = 1 # Applies to Linux only. innodb-use-sys-malloc = 1 innodb-write-io-threads = 8 ################################# # * 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 myisam-recover-options = BACKUP,FORCE ######################################################################### # # * 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)