# # FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server # Location: /etc/my.cnf or /etc/mysql/my.cnf # This template is intended to work with MySQL/MariaDB 5.5 and newer # Get most recent updated from here: # http://www.fromdual.com/mysql-configuration-file-sample # [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysql] no_auto_rehash max_allowed_packet = 16M prompt = '\u@\h [\d]> ' default_character_set = utf8 # Possibly this setting is correct for most recent Linux systems [mysqldump] quick quote-names single-transaction hex-blob disable-keys routines triggers max_allowed_packet = 16M [mysqld_safe] open_files_limit = 65000 #open_files_limit = 29000 innodb_open_files = 65000 #innodb_open_files = 29000 #open_files_limit = 8192 # You possibly have to adapt your O/S settings as well user = mysql log-error = /var/lib/mysql/ecrm-db1_error.log log-warnings = 2 #pid-file = /var/run/mysqld/mysqld.pid #socket = /var/run/mysqld/mysqld.sock #nice = 0 [mysqld] # Connection and Thread variables user = mysql default_password_lifetime = 0 pid-file = /var/run/mysqld/mysqld.pid basedir = /usr datadir = /var/lib/mysql/ecrm/ tmpdir = /tmp lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp port = 3306 socket = /var/run/mysqld/mysqld.sock max_allowed_packet = 256M # MK 18.04.2016 - See bug A18MI298: Customer wants to save notes with big images. default_storage_engine = InnoDB # Defaults since 5.5 # character_set_server = utf8 # If you prefer utf8 # collation_server = utf8_general_ci max_connections = 80 # Values < 1000 are typically good max_user_connections = 75 # Limit one specific user/application thread_cache_size = 80 # Up to max_connections makes sense # Query Cache query_cache_type = 1 # Set to 0 to avoid global QC Mutex query_cache_size = 32M # Avoid too big (> 128M) QC because of QC clean-up lock! # Session variables sort_buffer_size = 4M # Could be too big for many small sorts tmp_table_size = 200M # Make sure your temporary results do NOT contain BLOB/TEXT attributes read_buffer_size = 384k read_rnd_buffer_size = 1M join_buffer_size = 3M # Other buffers and caches table_definition_cache = 29000 # As big as many tables you have table_open_cache = 29000 # connections x tables/connection (~2) # MySQL error log log_error = /var/lib/mysql/ecrm-db1_error.log log_warnings = 2 # innodb_print_all_deadlocks = 1 # wsrep_log_conflicts = 1 # for Galera only! # Slow Query Log slow_query_log_file = /var/lib/mysql/ecrm-db1_slow.log slow_query_log = 1 #slow_query_log = 0 log_queries_not_using_indexes = 1 long_query_time = 5 min_examined_row_limit = 100 lower_case_table_names = 1 symbolic-links = 0 # UTF-8 support #collation_server=utf8_general_ci collation_server=utf8_german2_ci character_set_server=utf8 # enable crash safe slaves; see http://www.mysqlperformanceblog.com/2013/09/13/enabling-crash-safe-slaves-with-mysql-5-6/ relay_log_recovery=ON relay_log_info_repository=TABLE # write master replication info in table master_info_repository=TABLE # General Query Log general_log_file = /var/lib/mysql/ecrm-db1_general.log general_log = 0 # Binary logging and Replication server_id = 1 gtid_mode=ON log-slave-updates enforce-gtid-consistency report-host=ecrm-db1 report-port=3306 relay-log-recovery=1 slave_exec_mode=IDEMPOTENT #replicate-same-server-id = 0 auto-increment-increment = 2 #Amount of replication nodes auto-increment-offset = 1 log_bin = /var/lib/mysql/ecrm-db1_binlog relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index # master_verify_checksum = ON # Since 5.6 binlog_cache_size = 1M binlog_stmt_cache_size = 1M max_binlog_size = 512M # Make bigger for high traffic to reduce number of files sync_binlog = 0 # Set to 1 or higher to reduce potential loss of binary-log data expire_logs_days = 5 # We will survive easter holidays binlog_format = ROW # Use MIXED if you experience some troubles # binlog_row_image = MINIMAL # Since 5.6 # auto_increment_increment = 2 # For Master/Master set-ups use 2 for both nodes # auto_increment_offset = 1 # For Master/Master set-ups use 1 and 2 # Slave variables log_slave_updates = 1 # Use if Slave is used for Backup and PiTR read_only = 0 # Set to 1 to prevent writes on Slave #skip_slave_start = 0 # To avoid start of Slave threads # Security variables # local_infile = 0 # If you are security aware # secure_auth = 1 # If you are security aware # sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER # Be careful changing this afterwards # skip_name_resolve = 0 # Set to 1 if you do not trust your DNS or experience problems # MyISAM variables key_buffer_size = 8M # Set to 25 - 33 % of RAM if you still use MyISAM myisam_recover_options = 'BACKUP,FORCE' # MEMORY variables max_heap_table_size = 64M # Should be greater or equal to tmp_table_size # InnoDB variables innodb_strict_mode = ON innodb_file_format_check = 1 # innodb_file_format = Barracuda # For dynamic and compressed InnoDB tables innodb_buffer_pool_size = 19G # Go up to 80% of your available RAM innodb_buffer_pool_instances = 8 # Bigger if huge InnoDB Buffer Pool or high concurrency innodb_file_per_table = 1 # Is the recommended way nowadays # innodb_flush_method = O_DIRECT # O_DIRECT is sometimes better for direct attached storage # innodb_write_io_threads = 8 # If you have a strong I/O system or SSD # innodb_read_io_threads = 8 # If you have a strong I/O system or SSD # innodb_io_capacity = 1000 # If you have a strong I/O system or SSD innodb_flush_log_at_trx_commit = 0 # 1 for durability, 0 or 2 for performance innodb_log_buffer_size = 8M # Bigger if innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 256M # Bigger means more write throughput but longer recovery time thread_stack = 256k