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.

wsrep-architecture

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.

galera-overview

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

MySQL Multi Master replication with Galera
Tagged on:             

6 thoughts on “MySQL Multi Master replication with Galera

  • May 4, 2013 at 13:45
    Permalink

    Buenas!
    Con esto se consigue hacer la replicacion con Galera.
    Como puedo hacer un proxy para que cuando se conecten a 192.168.1.141 reparta las conexiones a:
    mysql1: 192.168.1.138/24
    mysql2: 192.168.1.139/24
    mysql3: 192.168.1.140/24

    Es posible hacer esto?
    Es posible que puedas hacer algun tutorial para aclararnos las cosas a los que estamos interesados en infraestructuras así?

    Muchas gracias!

    Reply
    • May 6, 2013 at 18:37
      Permalink

      Buenas Jordi,

      Hice un post de balanceo de carga con haproxy: http://opentodo.net/2012/12/https-load-balancer-with-haproxy-stunnel/ en el escenario configurado utilizo stunnel para aceptar peticiones por https. Lo unico que tendras que adaptarlo a tu escenario para que haga el balanceo por tcp/3306 a tus máquinas con mysql.
      Espero que te sirva de ayuda! Saludos!

      Reply
  • August 24, 2013 at 21:07
    Permalink

    in my cluster have the following output, any ideas?

    wsrep_cluster_status Disconected
    wsrep_connected off

    thx

    Reply
  • November 3, 2013 at 09:14
    Permalink

    So as balancing tool should use tool like haproxy? Right?

    Reply
    • November 7, 2013 at 21:24
      Permalink

      Hi Yuriy, yeah you can use any balancing software that you want actually.

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow

Get every new post delivered to your Inbox

Join other followers: