Galera MySQL Recommended Cluster Configuration

Galera MySQL Recommended Cluster Configuration

Galera/MySQL recommended cluster configuration

Category: Template Information &nbsp

For practical purposes Galera/MySQL vendor recommend to reserve a “Reference Node” in the
cluster.
We propose the cluster looks like:

Reference Node cluster

Reference Node cluster

A “Reference Node” is a node that does not receive SQL load. You should not provide an access to it for others.

The red one (Node A) on the image is “Reference Node”. Others (Node B … Node N) are usual nodes.
Having such node in a cluster serves several purposes:

♦ Data consistency: since this node does not process any SQL load on its own, it has the lowest probability of transaction conflicts and therefore – in-deterministic conflict resolution. In the event of discovered database inconsistencies in the cluster this node will have the most relevant database.

♦ Data safety: since this node does not process any SQL load on its own, it has the lowest probability of failing with catastrophic consequences. In the event of total cluster failure (e.g. blackout) this will be the best node to restore cluster from.

♦ High availability: a reference node can serve as a dedicated state snapshot donor. Since it does not serve any clients, they won’t experience service interruptions and load balancer won’t need reconfiguration during SST.

Even with the current TCP-based group communication the overhead of having one extra silent node is negligible for most loads.

The cluster should include two or more VPSes based on CentOS 5.4 x64 MySQL/Galera and phpMyAdmin or Debian 5.0 (Lenny) x64 MySQL/Galera and phpMyAdmin templates.

Here is step by step instructions on how to setup the cluster like this.

1. Get required number of Virtual Private Servers installed based on Galera/MySQL templates and destributives you prefer.

Please note if you plan to connect the cluster as cPanel “Remote MySQL Server” you must use CentOS Galera/MySQL template for the “Reference Node” VPS. The Debian distributive is not supported by cPanel.

2. Configure Node A to be the “Reference node”.
Start new cluster with the command:

 

# mysql -e "set global wsrep_cluster_address='gcomm://';"

 

3. Configure Node B to join existing cluster (interact with Node A)
Set wsrep_cluster_address value on the /etc/mysql/conf.d/wsrep.cnf file

 

wsrep_cluster_address="gcomm://<<em>Node A IP</em>>:4567"

 

Restart MySQL service for CentOS

 

# service mysqld restart

 

and for Debian

 

# /etc/init.d/mysql restart

 

4. Configure Node C … Node N to join existing cluster (interact with Node A and B “via” Node B)
The nodes are configured same as Node B by pointing IP addresses of corresponded previous one. Like for Node C -> Node B, for Node D -> Node C, … , Node N -> …
Set wsrep_cluster_address value on the /etc/mysql/conf.d/wsrep.cnf file

 

wsrep_cluster_address="gcomm://<<em>Node B or C or ... IP</em>>:4567"

 

Restart MySQL service (look at Step 3)

5. Re-configure Node A (the “Reference Node“) to join existing cluster
Set wsrep_cluster_address value on the /etc/mysql/conf.d/wsrep.cnf file

 

wsrep_cluster_address="gcomm://<<em>Node N IP</em>>:4567"

 

Restart MySQL service (look at Step 3)

    • Related Articles

    • Configure MySQL Master-Master replication

      Configure MySQL Master-Master replication Category: Databases &nbsp Configuring MySQL in a master-slave replicate model adds complexity to the application utilizing the database. The application has to be programmed to send the write queries only to ...

    • MySQL table marked as crashed and should be repaired

      MySQL table marked as crashed and should be repaired Category: Recovery Interface &nbsp If you are getting the following error message: Table ‘jos151_session’ is marked as crashed and should be repaired this means mysql service was terminated while ...

    • How to add a MySQL database to Cloud Hosting

      How to add a MySQL database to Cloud Hosting (also, adding a user, and adding a user to the db) Category: Getting Started &nbsp This how-to will show you how to: ♦ Add a MySQL Database to our cloud hosting ♦ Add a user to the mysql userbase ♦ Add a ...

    • How to add a MySQL database to Cloud Hosting

      How to add a MySQL database to Cloud Hosting (also, adding a user, and adding a user to the db) Category: Getting Started &nbsp This how-to will show you how to: ♦ Add a MySQL Database to our cloud hosting ♦ Add a user to the mysql userbase ♦ Add a ...

    • Configuring SMTP Relay Server

      If you have been affected by the recent block of outgoing mail on port 25 for our SoftLayer locations (Singapore, San Jose, Dallas, Washington DC and Seattle), you will be able to use the following to configure another server in Salt Lake City, New ...