Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- galera-beispiel_fuer_clustereinstellungen.cnf [2015-05-13 09:21:47] – manfred
+++ galera-beispiel_fuer_clustereinstellungen.cnf [2017-11-02 14:25:50] (aktuell) – [ab Version 5.7] manfred
@@ Zeile 1: / Zeile 1: @@
+====== 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
+<file>
+# 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                        = ""
+</file>
+===== 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!