This chapter presents the mandatory and recommended settings for Galera Cluster installation and use. It may be possible to start the cluster after only setting the wsrep_provider and wsrep_cluster_address variables. However, the best results can be achieved by fine-tuning the configuration to best match the use case.
See also
Chapter Galera Parameters.
Unless you are upgrading an already installed mysql-wsrep package, you must configure the installation to prepare the server for operation.
Edit the my.cnf configuration file as follows:
Make sure that the system-wide my.cnf file does not bind mysqld to 127.0.0.1. To be more specific, if you have the following line in the [mysqld] section, comment it out:
#bind-address = 127.0.0.1
Make sure that the system-wide my.cnf file contains the line below:
!includedir /etc/mysql/conf.d/
Edit the /etc/mysql/conf.d/wsrep.cnf configuration file as follows:
When a new node joins the cluster, it will have to receive a state snapshot from one of the peers. This requires a privileged MySQL account with access from the rest of the cluster. Set the mysql login/password pair for SST in the /etc/mysql/conf.d/wsrep.cnf configuration file as follows:
wsrep_sst_auth=wsrep_sst:wspass
Restart the MySQL server and connect to it as root to grant privileges to the SST account. Furthermore, empty users confuse MySQL authentication matching rules. Delete them:
$ mysql -e "SET wsrep_on=OFF; DELETE FROM mysql.user WHERE user='';"
$ mysql -e "SET wsrep_on=OFF; GRANT ALL ON *.* TO wsrep_sst@'%' IDENTIFIED BY 'wspass'";
The MySQL-wsrep server must be accessible from other cluster members through its client listening socket and through the wsrep provider socket. See your distribution and wsrep provider documentation for details. For example, on CentOS you could use these settings:
# iptables --insert RH-Firewall-1-INPUT 1 --proto tcp --source <my IP>/24 --destination <my IP>/32 --dport 3306 -j ACCEPT
# iptables --insert RH-Firewall-1-INPUT 1 --proto tcp --source <my IP>/24 --destination <my IP>/32 --dport 4567 -j ACCEPT
# iptables --insert RH-Firewall-1-INPUT 1 --proto tcp --source <my IP>/24 --destination <my IP>/32 --dport 4568 -j ACCEPT
If there is a NAT firewall between the nodes, configure it to allow direct connections between the nodes (for example, through port forwarding).
If you have SELinux enabled, it may block mysqld from carrying out the required operations. Disable SELinux or configure it to allow mysqld to run external programs and open listen sockets at unprivileged ports (that is, things that an unprivileged user can do). See SELinux documentation for more information.
To disable SELinux, proceed as follows:
AppArmor is always included in Ubuntu. It may prevent mysqld from opening additional ports or run scripts. See AppArmor documentation for more information on its configuration.
To disable AppArmor, proceed as follows:
$ cd /etc/apparmor.d/disable/
$ sudo ln -s /etc/apparmor.d/usr.sbin.mysqld
$ sudo service apparmor restart
See below for an example my.cnf file:
[mysqld]
# 1. Mandatory settings: these settings are REQUIRED for proper cluster operation
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
# 2. Optional mysqld settings: your regular InnoDB tuning and such
datadir=/mnt/mysql/data
innodb_buffer_pool_size=28G
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
# 3. wsrep provider configuration: basic wsrep options
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=32G; gcache.page_size=1G"
wsrep_cluster_address=gcomm://192.168.0.1,192.168.0.2,192.168.0.3
wsrep_cluster_name='my_galera_cluster'
wsrep_node_address='192.168.0.2'
wsrep_node_name='node2'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=root:rootpa$$
# 4. additional "frequently used" wsrep settings
wsrep_node_incoming_address='192.168.10.2'
wsrep_sst_donor='node3'
wsrep_slave_threads=16
In the example above, there are 11 wsrep configuration variables. This is usually all that is needed for clustering.
Note
Always customize the settings in section 3 before taking the cluster into production.
You must give values to the settings below:
For better performance, you can give values to the settings below:
datadir=/mnt/mysql/data—The MySQL data directory.
innodb_buffer_pool_size=28G—The size in bytes of the buffer pool, that is, the memory area where InnoDB caches table and index data.
innodb_log_file_size=100M—The size in bytes of each log file in a log group.
innodb_file_per_table—When innodb_file_per_table is enabled, InnoDB stores the data and indexes for each newly created table in a separate .ibd file, rather than in the system tablespace.
innodb_flush_log_at_trx_commit—This parameter improves performance. The parameter defines how often the log buffer is written out to the log file and how often the log file is flushed onto disk. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it, but it takes place once per second.
Compared with the default value 1, you can achieve better performance by setting the value to 2, but an operating system crash or a power outage can erase the last second of transactions. However, this risk is handled by synchronous replication—you can always recover the node from another node.
Warning
With innodb_flush_log_at_trx_commit=2, some transactions can be lost if the entire cluster goes down, for example, due to a datacenter power outage.
Set:
``innodb_flush_log_at_trx_commit=2``
The basic wsrep provider settings are:
For better performance, you can also give values to the settings below:
In normal operation, a Galera Cluster node does not consume much more memory than a regular MySQL server. Additional memory is consumed for the certification index and uncommitted write sets, but usually this is not noticeable in a typical application. However, writeset caching during state transfer makes an exception.
When a node is receiving a state transfer, it cannot process and apply incoming write sets because it has no state to apply them to yet. Depending on the state transfer mechanism (for example, mysqldump), the node that sends the state transfer may not be able to apply write sets. Instead, the node must cache the write sets for a catch-up phase. The Writeset Cache (GCache) is used to cache write sets on memory-mapped files on disk. These files are allocated as needed. In other words, the limit for the cache is the available disk space. Writing on disk reduces memory consumption.
However, if you want to adjust flow control settings, adjust the Galera Cluster parameters below: