Go 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 Balan CED between the nodes, but not the writers, which has to be executed by the master node. Another reason to use Galera are for the synchronous replication, in a scenario with a master/slave the replication is Asyn Chronous This means, the binary log files is different on the different nodes.

To install Galera cluster It's necessary a MySQL version patched with Wsrep APIs, provided by Codership. Wsrep is a project to develop a generic replication plugin interface for databases, defining a set of application callback s and replication library calls. Wsrep can load dynamically different wsrep providers, that's simply a library working under Wsrep and calling to the diff Erent 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, 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 is different ways to transfer a state from one node to another, one possibility could be mysqldump but can is the SLO West method due to mysqldump blocks the tables of the server from possible changes during the transfer. Another method can be rsync (used in this post) or xtrabackup, which consist in copying data files directly and is the fast Est method.

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

• Mysql1:192.168.1.138/24
• Mysql2:192.168.1.139/24
• Mysql3:192.168.1.140/24

The configuration cluster MYSQL2 is connected with MYSQL1 and MYSQL3 would connect with MYSQL2. When the connection between MYSQL2 and MYSQL3 is established, the group communication address for MYSQL1 would configured With the address of mysql3 creating a 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:

-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

-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:

-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

-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> DELETEFROM mysql.user WHERE user=‘‘;

mysql> GRANTALL ON *.* TO [email protected]‘%‘ IDENTIFIED BY ‘[email protected]‘;

mysql> UPDATEmysql.user SET Password=PASSWORD(‘[email protected]‘) WHERE User=‘root‘;

mysql> GRANTALL ON *.* to [email protected]‘%‘ 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> setglobal 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 rowsin set (0.00 sec)

The most important variables was wsrep_ready, if the value is on it means that the cluster is working, and wsrep_cluster_s Ize that's equals to the number of nodes which 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

Go MySQL Multi Master replication with Galera