====== Galera-Beispiel für clustereinstellungen.cnf ====== * **[[http://www.severalnines.com/galera-configurator/|Configurator for Galera Replication]]** * [[http://galeracluster.com/documentation-webpages/?id=galera_parameters]] > 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: 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!