Configuring a MariaDB Cluster with Galera

Updated by James Stewart

Contribute on GitHub

View Project | View File | Edit File

MariaDB replication with Galera adds redundancy for a site’s database. With database replication, multiple servers act as a database cluster. Database clustering is particularly useful for high availability website configurations. This guide uses three separate Linodes to configure database replication, each with private IPv4 addresses on Debian and Ubuntu.

This guide assumes that your Linodes are each configured with a Private IP Address.

Install Required Packages

  1. To install the required packages, first add the keys for the Galera repository by running:

    1
    2
    sudo apt-get install python-software-properties
    sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
    
  2. Add the repository for your distribution:

    • Debian Repository:

      1
      sudo add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/debian wheezy main'
      
    • Ubuntu Repository:

      1
      add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/ubuntu trusty main'
      
  3. Install MariaDB, Galera, and Rsync:

    1
    sudo apt-get update && sudo apt-get install -y rsync galera mariadb-galera-server
    

Configuring Galera

  1. Create the file /etc/mysql/conf.d/galera.cnf on each of the Linodes with the following content. Replace the IP addresses in the wsrep_cluster_address section with the private IP addresses of each of the Linodes:

    /etc/mysql/conf.d/galera.cnf
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    [mysqld]
    #mysql settings
    binlog_format=ROW
    default-storage-engine=innodb
    innodb_autoinc_lock_mode=2
    query_cache_size=0
    query_cache_type=0
    bind-address=0.0.0.0
    #galera settings
    wsrep_provider=/usr/lib/galera/libgalera_smm.so
    wsrep_cluster_name="my_wsrep_cluster"
    wsrep_cluster_address="gcomm://192.168.1.1,192.168.1.2,192.168.1.3"
    wsrep_sst_method=rsync
    
  2. Reboot both of your non-primary servers in the cluster to enable the new galera.cnf file settings.

  3. Stop the MariaDB service on each of your Linodes:

    1
    sudo service mysql stop
    
  4. Restart the MariaDB service on the primary Linode, with the --wsrep-new-cluster flag:

    1
    sudo service mysql start --wsrep-new-cluster
    
  5. Confirm that the cluster has started:

    1
    mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
    

    You should receive an output of the current cluster size:

    1
    2
    3
    4
    5
    6
    7
    MariaDB [(none)]> SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size";
    +--------------+
    | cluster size |
    +--------------+
    | 1            |
    +--------------+
    1 row in set (0.00 sec)
    
  6. Start the MariaDB service on the other two Linodes. Re-run the command from step 5 to ensure that each system has joined the cluster:

    1
    sudo service mysql start
    
  7. To prevent repeated errors on startup, copy the /etc/mysql/debian.cnf file from your primary Linode in the cluster to each of your other Linodes, overwriting the existing copies.

  8. Reboot both of your secondary Linodes to apply the new debian.cnf settings.

Testing database replication

  1. Log in to MariaDB on each of the Linodes:

    1
    mysql -u root -p
    
  2. To test, create a database and insert a row on your primary Linode:

    1
    2
    create database test;
    create table test.flowers (`id` varchar(10));
    
  3. From each of the other servers, list the tables in your test database:

    1
    show tables in test;
    

    You should receive an output of the database and row that you created in the previous step:

    1
    2
    3
    4
    5
    6
    7
    MariaDB [(none)]> show tables in test;
    +----------------+
    | Tables_in_test |
    +----------------+
    | flowers        |
    +----------------+
    1 row in set (0.00 sec)
    

Congratulations, you have now configured a MariaDB cluster with Galera.

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

This guide is published under a CC BY-ND 4.0 license.