MySQL Multi Master replication with Galera

Galera is a synchronous multi-master cluster for MySQL/InnoDB databases. Some features and benefits of Galera are:

• Synchronous replication.
• Multi master topology.
• Read/Write to any cluster node.
• Automatic membership control.
• Data consistency between replica nodes.
• Read and write nodes scalability.
• Distributed transactions and lock tables.

The motivation to implement Galera instead MySQL master/slave replication, comes that the reads to a database can be balanced between the nodes, but not the writers, that have to be executed by the master node. Another reason to use Galera is for the synchronous replication, in a scenario with a master/slave the replication is asynchronous this means that the binary log files may be different on the different nodes.

To install galera cluster it’s necessary a MySQL version patched with wsrep API, provided by codership. wsrep is a project to develop a generic replication plugin interface for databases, defining a set of application callbacks and replication library calls. Wsrep can load dynamically different wsrep providers, that is simply a library working under wsrep and calling to the different functionalities of wsrep API’s, one example of it is Galera used in this post.

For galera, an application state is a set of data that the application decides to replicate (databases) and is identified by a global transaction id (GTID), consists of:
– State UUID which uniquely identifies the state and the sequence of changes.
– An ordinal sequence number to denote the position of the change in the sequence.

There are different ways to transfer a state from one node to another, one possibility may be mysqldump but can be the slowest method due to mysqldump block the tables of the server from possible changes during the transfer. Another method can be rsync (used in this post) or xtrabackup, that consist in copying data files directly and is the fastest method.

The scenario proposed in this post is with three nodes (minimum recomended to avoid a split brain situation). The ip address used are:

• mysql1: 192.168.1.138/24
• mysql2: 192.168.1.139/24
• mysql3: 192.168.1.140/24

In the configuration cluster mysql2 is connected with mysql1 and mysql3 will connect with mysql2. When the connection between mysql2 and mysql3 was established, the group communication address for mysql1 will configured with the address of mysql3 creating an unidirectional communication with all the nodes in the cluster.

Implementation of MySQL Galera on MySQL 1, MySQL 2 and MySQL 3

– Installing some mysql dependencies:

# apt-get install libaio1 libdbi-perl libdbd-mysql-perl mysql-client rsync

– Install MySQL server with wsrep patch:

– 32 bits:

# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-i386.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-i386.deb

– 64 bits:

# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-amd64.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb

– Download and install Galera :

– 32 bits:

# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-i386.deb && dpkg -i galera-23.2.2-i386.deb

– 64 bits:

# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-amd64.deb && dpkg -i galera-23.2.2-amd64.deb

– Preparing initial mysql setup:

# /etc/init.d/mysql start
# mysql -u root

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'P@ssw0rd';
mysql> UPDATE mysql.user SET Password=PASSWORD('P@ssw0rd') WHERE User='root';
mysql> GRANT ALL ON *.* to sst@'%' IDENTIFIED BY 'sstpasswd';

– Start mysql at boot time:

# update-rc.d mysql defaults

Configuring MySQL 1

# vi /etc/mysql/conf.d/wsrep.cnf

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

# Group communication system handle
wsrep_cluster_address="gcomm://"

# State Snapshot Transfer method
wsrep_sst_method=rsync

# 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=sst:sstpasswd

# /etc/init.d/mysql restart

– For the first node gcomm:// address is empty to create the new cluster. Later We’ll reconnect with the MySQL3 node.

Configuring MySQL 2

# vi /etc/mysql/conf.d/wsrep.cnf

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

# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.138:4567"

# State Snapshot Transfer method
wsrep_sst_method=rsync

# 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=sst:sstpasswd

# /etc/init.d/mysql restart

Configuring MySQL 3 

# vi /etc/mysql/conf.d/wsrep.cnf

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

# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.139:4567"

# State Snapshot Transfer method
wsrep_sst_method=rsync

# 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=sst:sstpasswd

# /etc/init.d/mysql restart

 Reconfiguring MySQL 1

# vi /etc/mysql/conf.d/wsrep.cnf

wsrep_cluster_address="gcomm://192.168.1.140:4567"

# mysql -u root -p

mysql> set global wsrep_cluster_address='gcomm://192.168.1.140:4567';

Checking wsrep variables

mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 1 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 10 |
| wsrep_received_bytes | 1039 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.1.138:3306,192.168.1.140:3306,192.168.1.139:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON |
+----------------------------+----------------------------------------------------------+
40 rows in set (0.00 sec)

The most important variables are wsrep_ready, if the value is ON it means that the cluster is working, and wsrep_cluster_size that is equals to the number of nodes that is composed the cluster.

Sources

http://www.codership.com/wiki/doku.php?id=info
http://codership.com/products/mysql_galera
http://www.codership.com/wiki/doku.php?id=galera_parameters
http://www.codership.com/content/5-tips-migrating-your-mysql-server-galera-cluster