Using Galera Cluster

After you have successfully started all cluster nodes, the cluster is ready to use.

From the client point of view each cluster node works like a usual MySQL server - client-side application does not have to be changed in any way. Each node can be accessed independently and asynchronously. Just direct SQL load to one or several the cluster nodes. For most practical purposes you can treat MySQL/Galera cluster as a single MySQL server listening on multiple interfaces with the exception that you might see transaction deadlocks where you previously hadn't.


Loading Data to Cluster


Initially demo database is empty. You can populate it by loading the dump of your data to any one of the nodes. It will be automatically replicated to other nodes. Please note that this demo supports only InnoDB storage engine.


Connecting Application to Cluster


As was mentioned above, for the client application each node looks like a normal MySQL server and can be used independently.This creates considerable flexibility in the way the cluster can be utilized. The approaches can be categorized in three groups:



  1. Seeking High Availability only. In this case client application connects to only one node, the rest serving as hot backups: 
    
   ,-------------.
   | application |
   `-------------'
        | | |        DB backups
      ,-------. ,-------. ,-------.
      | node1 | | node2 | | node3 |
      `-------' `-------' `-------'
       <===== cluster nodes =====>

    In the case of primary node failure application can instantly switch to another node.


  2. Seeking High Availability and improved performance through uniform load distribution. If there are several client connections to the database, they can be uniformly distributed between cluster nodes resulting in better performance. The exact degree of performance improvement depends on application's SQL profile. Note, that transaction rollback rate may also increase. 
    
             ,-------------.
             | application |
             `-------------'
             /      |      \
      ,-------. ,-------. ,-------.
      | node1 | | node2 | | node3 |
      `-------' `-------' `-------'
       <===== cluster nodes =====>

    In the case of a node failure application can keep on using the remaining healthy nodes. In this setup application can also be clustered with a dedicated application instance per database node, thus achieving HA not only for the database, but for the whole application stack:

    
             ,-------------.
             |   clients   |
             `-------------'
             /      |      \
      ,------.   ,------.   ,------.
      | app1 |   | app2 |   | app3 |
      `------'   `------'   `------'
         |          |          |
     ,-------.  ,-------.  ,-------.
     | node1 |  | node2 |  | node3 |
     `-------'  `-------'  `-------'
      <====== cluster nodes ======>


  3. Seeking High Availability and improved performance through smart load distribution. Uniform load distribution can cause undesirably high rollback rate. Directing transactions which access the same set of tables to the same node can considerably improve performance by reducing the number of rollbacks. Also, if your application can distinguish between read/write and read-only transactions, the following configuration may be quite efficient: 
    
             ,---------------------.
             |     application     |
             `---------------------'
       writes /         | reads    \ reads
      ,-------.     ,-------.     ,-------.
      | node1 |     | node2 |     | node3 |
      `-------'     `-------'     `-------'
       <======== cluster nodes ==========>


Load Balancer


If your application cannot utilize several database servers (most don't) you will need to use SQL proxy or a TCP load balancer to distribute load between the MySQL/Galera nodes. This is needed not only to increase performance, but also for a quick switch in case of a node failure. If performance of your application is DBMS-bound, you can run the balancer on the same machine as application/client. Be aware, however, that SQL load balancing might be a CPU hungry operation: usually SQL traffic consists of many small packets. For best results we recommend to carefully examine CPU consumption of the balancer and if needed dedicate a separate machine for it.

Unlike traditional MySQL master-slave cluster MySQL/Galera cluster does notrequire any SQL traffic filtering, it is highly transparent and plain TCP connection balancer would suffice.

TCP connection balancers that were successfully used with MySQL/Galera:


Adding New Node to Cluster


This demo does not support automatic node provisioning (yet), so adding new node to a working cluster is a somewhat disruptive operation. You will need to take
the following steps to do it:



  1. Install demo on a new node as usual - but don't start mysqld.
  2. Connect the node to a cluster network. Make sure it can connect to arbitrator.
  3. Stop SQL load on cluster.
  4. Connect with mysql client to one of the nodes and issue:

    FLUSH TABLES WITH READ LOCK;

    command. Don't close this session.
  5. Copy data directory from that server to the new node.
  6. Start mysqld on the new node.
  7. Close the locking session from step 4.
  8. Resume SQL load.