MySQL Multi Master replication with Galera | root@opentodo#

MySQL Multi Master replication with Galera

Ivan Mora Pérez | Clustering, Database, GNU/Linux | 6

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:

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

- Install MySQL server with wsrep patch:

- 32 bits:

1	# wget https:///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:

1	# wget https:///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:

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

- 64 bits:

1	# wget https:///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:

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

1 2 3 4

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:

1	# update-rc.d mysql defaults

Configuring MySQL 1

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# 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

1	# /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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# 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

1	# /etc/init.d/mysql restart

Configuring MySQL 3 

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# 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

1	# /etc/init.d/mysql restart

 Reconfiguring MySQL 1

1 2 3

# vi /etc/mysql/conf.d/wsrep.cnf wsrep_cluster_address="gcomm://192.168.1.140:4567"

1	# mysql -u root -p

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

Checking wsrep variables

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

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 <info@> | | 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./wiki/doku.php?id=info
http:///products/mysql_galera
http://www./wiki/doku.php?id=galera_parameters
http://www./content/5-tips-migrating-your-mysql-server-galera-cluster

Tweet

Google+

Ivan Mora Pérez

Related Posts

• Configuring Galera arbitrator
• configuring MySQL Sandbox
• Configuring an IMAP/POP Proxy with perdition and MySQL
• ProFTPD + MySQL

galera, multi-master, mysql, wsrep

Making Iptables easier with Shorewall Configuring Galera arbitrator

6 thoughts on “MySQL Multi Master replication with Galera”

• jordi says:

  May 4, 2013 at 13:45

  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
  • ivanmp91 says:

    May 6, 2013 at 18:37

    Buenas Jordi,

    Hice un post de balanceo de carga con haproxy: http:///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
• Rafael says:

  August 24, 2013 at 21:07

  in my cluster have the following output, any ideas?

  wsrep_cluster_status Disconected
  wsrep_connected off

  thx

  Reply
• Yuriy says:

  November 3, 2013 at 09:14

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

  Reply
  • ivanmp91 says:

    November 7, 2013 at 21:24

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

    Reply
    • Yuriy says:

      December 9, 2013 at 09:38

      thanks!

      MySQL Multi Master replication with Galera

      Ivan Mora Pérez | Clustering, Database, GNU/Linux | 6

      

      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:

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

      - Install MySQL server with wsrep patch:

      - 32 bits:

      1	# wget https:///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:

      1	# wget https:///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:

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

      - 64 bits:

      1	# wget https:///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:

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

      1 2 3 4

      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:

      1	# update-rc.d mysql defaults

      Configuring MySQL 1

      1 2 3 4 5 6 7 8 9 10 11 12 13 14

      # 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

      1	# /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
      

      1 2 3 4 5 6 7 8 9 10 11 12 13 14

      # 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

      1	# /etc/init.d/mysql restart

      Configuring MySQL 3 

      1 2 3 4 5 6 7 8 9 10 11 12 13 14

      # 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

      1	# /etc/init.d/mysql restart

       Reconfiguring MySQL 1

      1 2 3

      # vi /etc/mysql/conf.d/wsrep.cnf wsrep_cluster_address="gcomm://192.168.1.140:4567"

      1	# mysql -u root -p

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

      Checking wsrep variables

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

      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 <info@> | | 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./wiki/doku.php?id=info
      http:///products/mysql_galera
      http://www./wiki/doku.php?id=galera_parameters
      http://www./content/5-tips-migrating-your-mysql-server-galera-cluster

      Tweet

      Google+

      Ivan Mora Pérez

      Related Posts

      • Configuring Galera arbitrator
      • configuring MySQL Sandbox
      • Configuring an IMAP/POP Proxy with perdition and MySQL
      • ProFTPD + MySQL

      galera, multi-master, mysql, wsrep

      Making Iptables easier with Shorewall Configuring Galera arbitrator

      6 thoughts on “MySQL Multi Master replication with Galera”

      • jordi says:

        May 4, 2013 at 13:45

        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
        • ivanmp91 says:

          May 6, 2013 at 18:37

          Buenas Jordi,

          Hice un post de balanceo de carga con haproxy: http:///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
      • Rafael says:

        August 24, 2013 at 21:07

        in my cluster have the following output, any ideas?

        wsrep_cluster_status Disconected
        wsrep_connected off

        thx

        Reply
      • Yuriy says:

        November 3, 2013 at 09:14

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

        Reply
        • ivanmp91 says:

          November 7, 2013 at 21:24

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

          Reply
          • Yuriy says:

            December 9, 2013 at 09:38

            thanks!