Galera-Beispiel für clustereinstellungen.cnf

> vi /etc/mysql/conf.d/clustereinstellungen.cnf

# All files in this package is subject to the GPL v2 license
# More information is in the COPYING file in the top directory of this package.
# Copyright (C) 2011 severalnines.com

[MYSQLD]
bind_address                    = 0.0.0.0
report_host                     = beispdb01
user                            = mysql
basedir                         = /usr
datadir                         = /var/lib/mysql/db_beispdb
socket                          = /var/run/mysqld/mysqld_beispdb.sock
pid_file                        = mysqld.pid
port                            = 3308
#log_error                      = error.log
log_error                       = /var/log/mysql_beispdb.log
slow_query_log_file             = /var/log/mysql-slow_beispdb.log
#log_output                     = FILE
log_warnings                    = 1
#log_warnings                    = 2

### INNODB OPTIONS 
innodb_buffer_pool_size         = 7164M
innodb_additional_mem_pool_size = 20M
### für mehr Geschwindigkeit wurde innodb_flush_log_at_trx_commit auf "0" gesetzt
#innodb_flush_log_at_trx_commit = 2
innodb_flush_log_at_trx_commit  = 0
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    = 4
innodb_log_files_in_group       = 2
innodb_thread_concurrency       = 0
#innodb_file_format             = barracuda
### für mehr Geschwindigkeit wurde innodb_flush_method auskommentiert
#innodb_flush_method            =  O_DIRECT
# innodb_locks_unsafe_for_binlog ist veraltet (diese Option erzwingt die Nutzung von binlog_format=ROW)
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
### mit der Option engine_condition_pushdown=1 startet die DBMS nicht
#engine_condition_pushdown      = 1
default_storage_engine          = innodb

# damit die Zeitformate in Version 5.6 genauso funktionieren wie in Version 5.5
explicit_defaults_for_timestamp = 1

# CHARACTER SET
collation_server                = utf8_unicode_ci
#init_connect                   = 'SET NAMES utf8'
character_set_server            = utf8
#skip_character_set_client_handshake

# REPLICATION SPECIFIC - GENERAL
#server_id must be unique across all mysql servers participating in replication.
server_id                       = 2101
#auto_increment_increment       = 2
#auto_increment_offset          = SERVERID

# REPLICATION SPECIFIC
binlog_format                   = ROW
#log_bin                        = binlog
#relay_log                      = relay-bin
#expire_logs_days               = 7
#log_slave_updates              = 1

# 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
skip_name_resolve
memlock                         = 0
sysdate_is_now                  = 1
max_connections                 = 2000
thread_cache_size               = 512
query_cache_type                = 0
query_cache_size                = 0
table_open_cache                = 1024
lower_case_table_names          = 0

#slave_skip_errors               = 1032,1062

[MYSQL]
socket                          = /var/run/mysqld/mysqld_beispdb.sock
default_character_set           = utf8

[client]
socket                          = /var/run/mysqld/mysqld_beispdb.sock
default_character_set           = utf8

[mysqldump]
max_allowed_packet              = 512M
default_character_set           = utf8

[MYSQLD_SAFE]
pid_file                        = mysqld.pid
log_error                       = /var/log/mysql_beispdb.log
basedir                         = /usr/
datadir                         = /var/lib/mysql/db_beispdb

# All files in this package is subject to the GPL v2 license
# More information is in the COPYING file in the top directory of this package.
# Copyright (C) 2011 severalnines.com

[MYSQLD]
##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider                  = /usr/lib/libgalera_smm.so

# Provider specific configuration options
wsrep_provider_options          = "gcache.size=8192M ; gmcast.listen_addr=tcp://0.0.0.0:4568"

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name              = yourfonedb

# Group communication system handle
#wsrep_cluster_address           = gcomm://
wsrep_cluster_address           = gcomm://beispdb02.datenbank.de:4568,beispdb03.datenbank.de:4568

# Human-readable node name (non-unique). Hostname by default.
wsrep_node_name                 = beispdb01
wsrep_node_address              = beispdb01.datenbank.de

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address     = 
wsrep_node_incoming_address     = beispdb01

# How many threads will process writesets from other nodes
#wsrep_slave_threads            = 1
wsrep_slave_threads             = 4

# 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
wsrep_debug                     = 1

# 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               = xtrabackup
wsrep_sst_method                = rsync

# 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:<root password>
wsrep_sst_auth                  = tron:streng_geheim

# Desired SST donor name.
#wsrep_sst_donor                = 

# Protocol version to use
# wsrep_protocol_version        = 

[server]
### http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
sql_mode                        = ""

ab Version 5.7

https://www.percona.com/doc/percona-xtradb-cluster/LATEST/wsrep-system-index.html

ACHTUNG!
Wenn man wsrep_replicate_myisam = ON haben möchte, dann muss man pxc_strict_mode = DISABLED schalten!