Ubuntu 14.04 - Setup MariaDB Cluster 5.5.x

In this tutorial we are going to set up a MariaDB 5.5.38 cluster on Ubuntu 14.04 LTS servers. I found this to be much easier than setting up a MySQL cluster, and you can keep your InnoDB tables rather than having to switch over to NDBCLUSTER

. One really can think of MariaDB as a "drop in" replacement of the MySQL database system with no need to change your codebase or learn new skills. It's even being led by some of the original creators of MySQL.

I will be using the
editor
command to edit files, so it is useful if you set your favourite editor before starting this tutorial so that your CLI will automatically go to vim or nano based on what you want.

Cluster Limitations

MariaDB Clusters (and MySQL clusters) don't support table locking. MySQL does support it on the node you query, but that's it.

Preparation

For this tutorial, I am going to be setting up and joining three MariaDB nodes to form a cluster. To do this, I am going to clone an Ubuntu 14.04 64bit Server "Base" Virtualbox instance three times, ensuring to generate new MAC addresses each time, and assign different static IPs.

Why Three Nodes?

In order to avoid a split-brain condition, the minimum recommended number of nodes in a cluster is three. This also handles the failure of a node. However feel free to add more, just no fewer.

I will assign the hostnames

db1.programster.com
,
db2.programster.com
, and
db3.programster.com
appropriately.

I do not own the programster.com domain, hence this only works on my internal LAN on computers that are set to use my internal DNS server.

Steps

    Install the software on all three instances.

    Maria DB 5.5

    sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
    sudo add-apt-repository 'deb http://mirror.jmu.edu/pub/mariadb/repo/5.5/ubuntu precise main'
    sudo apt-get update
    sudo apt-get install mariadb-galera-server galera -y
    
    # You should already have rsync, but this just ensures you do
    sudo apt-get install rsync -y
    
    When prompted, enter an administrative password

    Configure The First Node

    sudo editor /etc/mysql/conf.d/cluster.cnf
    Enter the following details (swapping out db1.programster.com with your own domains or IPs accordingly.
    [mysqld]
    binlog_format=ROW
    default-storage-engine=innodb
    innodb_autoinc_lock_mode=2
    query_cache_type=0
    query_cache_size=0
    bind-address=0.0.0.0
    
    # Galera Provider Configuration
    wsrep_provider=/usr/lib/galera/libgalera_smm.so
    #wsrep_provider_options="gcache.size=32G"
    
    # Galera Cluster Configuration
    wsrep_cluster_name="my_db_cluster"
    wsrep_cluster_address="gcomm://db1.programster.com,db2.programster.com,db3.programster.com"
    
    # Galera Synchronization Congifuration
    wsrep_sst_method=rsync
    #wsrep_sst_auth=user:pass
    
    # Galera Node Configuration
    wsrep_node_address="192.168.1.11"
    wsrep_node_name="db1.programster.com"
    

    Configure The Secondary Nodes

    Now copy that file (
    /etc/mysql/conf.d/cluster.cnf
    ) onto the other two nodes, changing the last two variables,
    wsrep_node_address
    , and
    wsrep_node_name
    accordingly.

    Stop All Services

    Stop all the services on all of the nodes:
    sudo service mysql stop
    sudo killall mysqld

    Start The Primary Node

    To start the cluster the first node needs to know that there are not any other nodes to connect to, so start it with the following command:
    sudo service mysql start --wsrep-new-cluster

    Add The Other Nodes

    Once a cluster node is already up, other nodes can now join the cluster.
    sudo service mysql start
    Your cluster should now be online and communicating.

    Extra Step - Copy The Debian Maintenance Configuration

    As we copied the data from the first node to the others, the credentials in
    /etc/mysql/debian.cnf
    don’t match. This means we will not be able to shutdown mysqld on either of the secondary nodes. To fix this, copy the
    /etc/mysql/debian.cnf
    file from the primary node into the secondary nodes.
    sudo editor /etc/mysql/debian.cnf

Test It's Working

    Log into one of the nodes.
    mysql -u root -p [password you entered at installation]
    Enter the command below and you should see the value 3 for 3 connected nodes.
    SHOW STATUS LIKE 'wsrep_cluster_size%';
    

References

No comments:

Post a Comment