Cluster Example Configuration

The following displays a sample server.cnf configuration:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
#
# 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
syslog

[mysqld]
#
# * Basic Settings
#
user      = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket     = /var/run/mysqld/mysqld.sock
port      = 3306
basedir     = /usr
datadir     = /var/lib/mysql

# Configure the tmp directory to reside on the data partion where we have enough space for SSTs.
# (Xtrabackup will use this variable.)
tmpdir = /var/lib/mysql/tmp

character_set_server=utf8
collation_server=utf8_unicode_ci

lc_messages_dir = /usr/share/mysql
lc_messages   = en_US
skip-external-locking

wsrep_on=ON

# gcache.size is how much data we will cache and use for IST.
# If more data has been produced since the node was disconnected an SST will be triggered.
# By setting it to 16G a customer can issue about 0.5M certs before connecting a second node without SST.
# (Nothing wrong with SSTs, but during IST it easer to follow the sync progress and it looks more user friendly.)
# pc.npvo=true: Recent primary component overrides older ones in case of conflicting prims.
# ## Can only be set in URL due to a bug: pc.wait_prim=false: The node waits for primary component forever
# pc.weight=2: Default weight for the node that can be overridden by parameters in the gcomm URL

#Uncomment this and you are setting up Galera recommendations with the default paths in the EJBCA Cloud Galera Clustering Guide otherwise add your own
#wsrep_provider_options="socket.ssl_key=/etc/mysql/localhost-Key.pem;socket.ssl_cert=/etc/mysql/localhost.pem;socket.ssl_ca=/etc/mysql/localhost-CA.pem;gcache.size=6G;gcache.page_size=512M; pc.npvo=true;"

# Galera Cluster Configuration
wsrep_cluster_name=ejbca_cluster
#wsrep_cluster_address="gcomm://"
wsrep_cluster_address="gcomm://172.16.0.103"
wsrep_node_name=EJBCANode1
wsrep_node_address="172.16.0.36"

# Galera Synchronization Configuration
wsrep_sst_auth=repl_user:i-04811bfcfa454383e
wsrep_sst_method=mariabackup
#
# 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      = 0.0.0.0
#
# * Fine Tuning
#
max_connections     = 200
connect_timeout     = 5
# The number of seconds the server waits for activity on a noninteractive connection before closing it. (Default 28800s)
wait_timeout      = 3600
max_allowed_packet   = 256M
thread_cache_size    = 128
sort_buffer_size    = 4M
bulk_insert_buffer_size = 16M
tmp_table_size     = 32M
max_heap_table_size   = 32M

# Turn off reverse DNS lookup of clients
skip-name-resolve

#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover     = BACKUP
key_buffer_size     = 32M
#open-files-limit    = 2000
table_open_cache    = 400
myisam_sort_buffer_size = 128M
concurrent_insert    = 2
read_buffer_size    = 2M
read_rnd_buffer_size  = 1M

#
# * Query Cache Configuration
#

# Disable to query cache to avoid locking
query_cache_limit=0
query_cache_type=0
query_cache_size=0

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# 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/mysql.log
general_log       = 0
#
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings      = 2
#
# Disable slow log
slow_query_log=0
#slow_query_log_file  = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit  = 1000
#log_slow_verbosity   = query_plan
#log-queries-not-using-indexes
#log_slow_admin_statements

# Disable bin-logging, since we don't use regular replication
sync_binlog      = 0
#log_bin            = /var/lib/mysql/mariadb-bin
#log_bin_index     = /var/lib/mysql/mariadb-bin.index
#expire_logs_days    = 10
#max_binlog_size     = 100M

# ROW is required by Galera (intercepts binlogs, but binlogs does not have to be written to disk)
binlog_format=ROW

# * InnoDB
default_storage_engine = InnoDB

innodb_buffer_pool_size=2G

# Use one pool per GiB of 'innodb_buffer_pool_size'
innodb_buffer_pool_instances = 2

# If SHOW GLOBAL STATUS LIKE 'innodb_log_waits'; starts returning a non-zero value
# transactions are to large to fit in the innodb_log_buffer_size and uses disk IO.
innodb_log_buffer_size = 32M

# Recommended to be 25% of innodb_buffer_pool_size. Larger file however means slower recovery.
# Changing this value requires a delete of the old files while shutdown.
# (sudo rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1)
innodb_log_file_size=512M
innodb_file_per_table  = 1
innodb_open_files    = 400
innodb_io_capacity   = 400
innodb_flush_method   = O_DIRECT

# Always flush it to Galera
innodb_flush_log_at_trx_commit=1

# Parallel slave thread processing requires the following settings:
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1

# Galera Provider Configuration
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Number of threads on the "slave" side applying incoming data.
# wsrep_slave_threads = 4*number of cores
wsrep_slave_threads=16

# In case of conflict during the full state transfer, overwrite the slave
slave_exec_mode=IDEMPOTENT

[mysqldump]
quick
quote-names
max_allowed_packet = 256M

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

[isamchk]
key_buffer       = 16M

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]