# # my.cnf template for clustercontroller # Copyright (C) 2011-2015 severalnines.com # [MYSQLD] ##### MAIN ##### user=mysql basedir=/usr/ datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock pid_file=/var/lib/mysql/mysql.pid port=3306 log_error=/var/log/mysql/mysqld.log log_warnings=2 # log_output = FILE ##### SLOW LOGGING ##### slow_query_log_file=/var/log/mysql/mysql-slow.log long_query_time=2 slow_query_log=ON log_queries_not_using_indexes=OFF ##### INNODB OPTIONS ##### #innodb_buffer_pool_size=6442450944 innodb_buffer_pool_size=8589934592 innodb_flush_log_at_trx_commit=2 innodb_file_per_table=1 innodb_data_file_path = ibdata1:100M:autoextend ## You may want to tune the below depending on number of cores and disk sub innodb_read_io_threads=4 innodb_write_io_threads=4 innodb_doublewrite=1 innodb_log_file_size=512M innodb_log_buffer_size=64M innodb_buffer_pool_instances=8 innodb_log_files_in_group=2 innodb_thread_concurrency=16 # innodb_file_format = barracuda innodb_flush_method = O_DIRECT # innodb_locks_unsafe_for_binlog = 1 innodb_autoinc_lock_mode=2 ## avoid statistics update when doing e.g show tables innodb_stats_on_metadata=0 default_storage_engine=innodb ##### CHARACTER SET ##### # collation_server = utf8_unicode_ci # init_connect = 'SET NAMES utf8' # character_set_server = utf8 ##### REPLICATION SPECIFIC ##### server_id=2 binlog_format=ROW # log_bin = binlog # log_slave_updates = 1 # gtid_mode = ON # enforce_gtid_consistency = 1 # relay_log = relay-bin # expire_logs_days = 7 ##### OTHER THINGS ##### # (buffers, etc.) # key_buffer_size = 24M tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 512M # sort_buffer_size = 256K # read_buffer_size = 256K # read_rnd_buffer_size = 512K # myisam_sort_buffer_size = 8M interactive_timeout = 300 wait_timeout = 300 #interactive_timeout = 600 #wait_timeout = 720 memlock=0 sysdate_is_now=1 max_connections=750 thread_cache_size=512 query_cache_type = 1 query_cache_size = 104857600 table_open_cache=1024 lower_case_table_names=0 # 5.6 backwards compatibility (FIXME) # explicit_defaults_for_timestamp = 1 ##################### ## WSREP options ## ##################### performance_schema = ON performance-schema-max-mutex-classes = 0 performance-schema-max-mutex-instances = 0 # Full path to wsrep provider library or 'none' wsrep_provider=/usr/lib/galera/libgalera_smm.so ############################################### # IN CASE OF EMERGENCY DEACTIVATE REPLICATION # AND SET wsrep_on=OFF wsrep_on=OFF #wsrep_on=ON ############################################### wsrep_node_address=db1b.XXXXXX.XXX.XX # Provider specific configuration options #wsrep_provider_options="base_port=4567; gcache.size=10240M; gmcast.segment=0; gcs.fc_limit=160; gcs.fc_factor=0.8;pc.bootstrap=YES" wsrep_provider_options="base_port=4567; gcache.size=10240M; gmcast.segment=0; gcs.fc_limit=160; gcs.fc_factor=0.8 " # Logical cluster name. Should be the same for all nodes. wsrep_cluster_name="my_wsrep_cluster" # Group communication system handle wsrep_cluster_address=gcomm://db1a.XXXXXX.XXX.XX,db1b.XXXXXX.XXX.XX,db1c.XXXXXX.XXX.XX #wsrep_cluster_address=gcomm://db1b.XXXXXX.XXX.XX,db1c.XXXXXX.XXX.XX # Human_readable node name (non-unique). Hostname by default. wsrep_node_name=db1b.XXXXXX.XXX.XX # Address for incoming client connections. Autodetect by default. #wsrep_node_incoming_address= # How many threads will process writesets from other nodes wsrep_slave_threads=32 # DBUG options for wsrep provider #wsrep_dbug_option # Generate fake primary keys for non-PK tables (required for multi-master # and parallel applying operation) wsrep_certify_nonPK=1 # Location of the directory with data files. Needed for non-mysqldump # state snapshot transfers. Defaults to mysql_real_data_home. #wsrep_data_home_dir= # Maximum number of rows in write set wsrep_max_ws_rows=131072 # Maximum size of write set wsrep_max_ws_size=1073741824 # to enable debug level logging, set this to 1 wsrep_debug=0 # convert locking sessions into transactions wsrep_convert_LOCK_to_trx=0 # how many times to retry deadlocked autocommits wsrep_retry_autocommit=1 # change auto_increment_increment and auto_increment_offset automatically wsrep_auto_increment_control=1 # replicate myisam wsrep_replicate_myisam=1 # retry autoinc insert, which failed for duplicate key error wsrep_drupal_282555_workaround=0 # enable "strictly synchronous" semantics for read operations wsrep_causal_reads=0 # Command to call when node status or cluster membership changes. # Will be passed all or some of the following options: # --status - new status of this node # --uuid - UUID of the cluster # --primary - whether the component is primary or not ("yes"/"no") # --members - comma-separated list of members # --index - index of this node in the list #wsrep_notify_cmd= #################################### ## WSREP State Transfer options ## #################################### # State Snapshot Transfer method # ClusterControl currently DOES NOT support wsrep_sst_method=mysqldump wsrep_sst_method=mariabackup # Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!! # (SST method dependent. Defaults to the first IP of the first interface) #wsrep_sst_receive_address= # SST authentication string. This will be used to send SST to joining nodes. # Depends on SST method. For mysqldump method it is root: # IMPORTANT: The user/password in wsrep_sst_auth must match # user/password in [xtrabackup] # Desired SST donor name. #wsrep_sst_donor= # Protocol version to use # wsrep_protocol_version= # log conflicts wsrep_log_conflicts=1 query_cache_limit=262144 skip_name_resolve=1 [MYSQL] socket=/var/lib/mysql/mysql.sock # default_character_set = utf8 [client] socket=/var/lib/mysql/mysql.sock # default_character_set = utf8 [mysqldump] max_allowed_packet = 512M # default_character_set = utf8 # IMPORTANT: The user/password in wsrep_sst_auth must match # user/password in [xtrabackup] [xtrabackup] databases-exclude=lost+found ssl_mode=DISABLED [MYSQLD_SAFE] # log_error = /var/log/mysqld.log basedir=/usr/ # datadir = /var/lib/mysql !include /etc/mysql/secrets-backup.cnf