When you have finished installing Galera Cluster on your server hardware, you are ready to configure the database itself to serve as a node in your cluster. To do this, you will need to edit the MySQL configuration file.
Using your preferred text editor, edit the /etc/my.cnf file.
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql binlog_format=ROW bind-address=0.0.0.0 default_storage_engine=innodb innodb_autoinc_lock_mode=2 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=122M wsrep_provider=/usr/lib/libgalera_smm.so wsrep_provider_options="gcache.size=300M; gcache.page_size=300M" wsrep_cluster_name="example_cluster" wsrep_cluster_address="gcomm://IP.node1,IP.node2,IP.node3" wsrep_sst_method=rsync [mysql_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
There are certain basic configurations that you will need to set up in the /etc/my.cnf file. Before starting the database server, edit the configuration file for the following:
Ensure that mysqld is not bound to 127.0.0.1. This is IP address for localhost. If the configuration variable appears in the file, comment it out:
# bind-address = 127.0.0.1
Ensure that the configuration file includes the conf.d/.
Ensure that the binary log format is set to use row-level replication, as opposed to statement-level replication.
Do not change this value, as it affects performance and consistency. The binary log can only use row-level replication.
Ensure that the default storage engine is InnoDB
Galera Cluster will not work with MyISAM or similar nontransactional storage engines.
Ensure that the InnoDB locking mode for generating auto-increment values is set to interleaved lock mode, which is designated by a 2 value.
Do not change this value. Other modes may cause INSERT statements on tables with AUTO_INCREMENT columns to fail.
Warning: When innodb_autoinc_lock_mode is set to traditional lock mode, indicated by 0, or to consecutive lock mode, indicated by 1, in Galera Cluster it can cause unresolved deadlocks and make the system unresponsive.
Ensure that the InnoDB log buffer is written to file once per second, rather than on each commit, to improve performance.
Warning: While setting innodb_flush_log_at_trx_commit to a value of 0 or 2 improves performance, it also introduces certain dangers. Operating system crashes or power outages can erase the last second of transaction. Although normally you can recover this data from another node, it can still be lost entirely in the event that the cluster goes down at the same time, (for instance, in the event of a data center power outage).
After you save the configuration file, you are ready to configure the database privileges.
The InnoDB storage engine uses a memory buffer to cache data and indexes of its tables, which you can configure through the innodb_buffer_pool_size parameter. The default value is 128MB. To compensate for the increased memory usage of Galera Cluster over the standalone MySQL database server, you should scale your usual value back by 5%.
Memory requirements for Galera Cluster are difficult to predict with any precision. The particular amount of memory it uses can vary significantly, depending upon the load the given node receives. In the event that Galera Cluster attempts to use more memory than the node has available, the mysqld instance crashes.
The way to protect your node from such crashing is to ensure that you have sufficient swap space available on the server, either in the form of a swap partition or swap files. To check the available swap space, run the following command:
$ swapon --summary Filename Type Size Used Priority /dev/sda2 partition 3369980 0 -1 /swap/swap1 file 524284 0 -2 /swap/swap2 file 524284 0 -3
If your system does not have swap space available or if the allotted space is insufficient for your needs, you can fix this by creating swap files.
Create an empty file on your disk, set the file size to whatever size you require.
# fallocate -l 512M /swapfile
Alternatively, you can manage the same using dd.
# dd if=/dev/zero of=/swapfile bs=1M count=512
Secure the swap file.
# chmod 600 /swapfile
This sets the file permissions so that only the root user can read and write to the file. No other user or group member can access it. You can view the results with ls:
$ ls -a / | grep swapfile -rw------- 1 root root 536870912 Feb 12 23:55 swapfile
Format the swap file.
# mkswap /swapfile
Activate the swap file.
# swapon /swapfile
Using your preferred text editor, update the /etc/fstab file to include the swap file by adding the following line to the bottom:
/swapfile none swap defaults 0 0
After you save the /etc/fstab file, you can see the results with swapon.
$ swapon --summary Filename Type Size Used Priority /swapfile file 524284 0 -1