How to deploy a MariaDB Galera Cluster on Ubuntu

on cluster, high availability, mariadb

mariadb-logoLet’s see how to start up a MariaDB cluster on Ubuntu from scratch and how to manage it

Tips

  • You need at least  3 servers for high availability and performance.
  • Yeah, you can run it with 2 servers but you may find a split-brain situation.
  • Servers can be globally distributed and in this case, it is recommendable to use a load balancer like HAProxy
  • You can configure each node manually
  • OR better: configure one node, make a snapshot and then deploy and configure the rest from that snapshot

Installation

Install dependencies

sudo apt-get install python-software-properties

Add the apt repository. You can pick another version, distro or mirror.

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db sudo add-apt-repository 'deb http://mirror.i3d.net/pub/mariadb/repo/5.5/ubuntu precise main'

Update repository

sudo apt-get update

Install MariaDb Cluster

sudo apt-get install -y galera  mariadb-galera-server-5.5 mariadb-server-5.5 mariadb-client-5.5 libmariadbclient18 mariadb-client-core-5.5

During the last step, you will be prompted to input the database root password.

If you are installing each node manually, make sure you put the same on all servers.

Configuration

Open the database configuration file, usually located at /etc/mysql/my.cnf and:

Comments these lines:

#bind-address 
#default_storage_engine 
#query_cache_limit #query_cache_size

Under the mysqld directive, add/edit:

  • Cluster Node #1 (example IP 11.11.11.11)
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://
#wsrep_cluster_address=gcomm://11.11.11.11,22.22.22.22,33.33.33.33
wsrep_cluster_name="example_cluster"
wsrep_node_address="11.11.11.11"
wsrep_node_name="db1"
wsrep_sst_method=rsync
wsrep_sst_auth="root:yourPassword"
wsrep_node_incoming_address=11.11.11.11
wsrep_sst_receive_address=11.11.11.11
wsrep_slave_threads=16
  • Cluster Node #2 (example IP 22.22.22.22)
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://11.11.11.11,22.22.22.22,33.33.33.33
wsrep_cluster_name="example_cluster"
wsrep_node_address="22.22.22.22"
wsrep_node_name="db2"
wsrep_sst_method=rsync
wsrep_sst_auth="root:yourPassword"
wsrep_node_incoming_address=22.22.22.22;
wsrep_sst_receive_address=22.22.22.22;
wsrep_slave_threads=16
  • Cluster Node #3 (example IP 33.33.33.33)
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://11.11.11.11,22.22.22.22,33.33.33.33
wsrep_cluster_name="example_cluster"
wsrep_node_address="33.33.33.33"
wsrep_node_name="db3"
wsrep_sst_method=rsync
wsrep_sst_auth="root:yourPassword"
wsrep_node_incoming_address=33.33.33.33
wsrep_sst_receive_address=33.33.33.33
wsrep_slave_threads=16

Note:

  • Node 1 has the line wsrep_cluster_address=gcomm:// without IPs. When it is in blank, it means this node will create the cluster.
  • You can put a single IP or several like above
  • If you put more than one, it will try to connect to the first IP. If it can’t to connect it will try with the next one and so on.
  • You need to restart mysql to run every change in my.conf

Starting the cluster

First, login on Node 1 and let’s startup the cluster:

sudo mysql restart

Then, login on the rest of nodes and do the same to connect to the cluster:

sudo mysql restart

Now, login into MariaDb to test (any node)

mysql -u root -p

Write your password and press enter.

Let’s get the status of the cluster

SHOW STATUS LIKE 'wsrep%';

If everything is OK you will see:

[...]
wsrep_incoming_addresses 11.11.11.11:3306,22.22.22.22:3306,33.33.33.33:3306
wsrep_cluster_size 3
wsrep_ready ON
[...]

Once the cluster is running, login on Node 1 and set this:

#wsrep_cluster_address=gcomm://
wsrep_cluster_address=gcomm://11.11.11.11,22.22.22.22,33.33.33.33

So if node 1 is rebooted it will connect to the rest of nodes and will not create another cluster.

Now, if you put data into any node, you should see the data on the rest of nodes. Test it!

FAQ

How to add a new node?

Deploy the snapshot on a new server or configure it manually and follow the above configuration instructions.

You should update the configuration file of the others nodes to include the new one.

Finally, when you start the new node, it will connect and copy the database so if you have a large database maybe it will late a bit.

What happens if a node goes down?

If you are connecting directly to the node you will loose the connection.

You should use a load balancer like HAProxy –see how to do it– and if a node goes down, the balancer connects you to another node automatically. So nothing happens.

Anyway, you can restart the node or when it will be available again it will syncronize to the cluster

How to make backups?

There are several ways:

  • If you are using a load balancer, you can have a node for backup services or with less load. So you can do hot backup using mysqldump.
  • If not, you can also use mysqldump but be aware of the users connected and the database size!
  • Disconnect a node and use mysqldump.
  • Use XtraBackup
How to recover from a backup?

No problem, login on any node and restore it like a single database server. It will propagate to the cluster.

Remote database access denied

If can’t connect from one database node to another one, you should add remote users:

Node 1 – Access for Node 2 and Node 3

CREATE USER 'root'@'22.22.22.22' IDENTIFIED BY 'yourPassword'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'22.22.22.22' WITH GRANT OPTION;
CREATE USER 'root'@'33.33.33.33' IDENTIFIED BY 'yourPassword'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'33.33.33.33' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Node 2 – Access for Node 1 and Node 3

CREATE USER 'root'@'11.11.11.11' IDENTIFIED BY 'yourPassword'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'11.11.11.11' WITH GRANT OPTION;
CREATE USER 'root'@'33.33.33.33' IDENTIFIED BY 'yourPassword'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'33.33.33.33' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Node 3 – Access for Node 1 and Node 2

CREATE USER 'root'@'11.11.11.11' IDENTIFIED BY 'yourPassword'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'11.11.11.11' WITH GRANT OPTION;
CREATE USER 'root'@'22.22.22.22' IDENTIFIED BY 'yourPassword'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'22.22.22.22' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Sources and docs

https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/installing-mariadb-deb-files/#installing-mariadb-galera-cluster-with-apt-get

http://www.sebastien-han.fr/blog/2012/04/01/mysql-multi-master-replication-with-galera/

http://edin.no-ip.com/blog/hswong3i/mariadb-galera-mastermaster-replication-ubuntu-12-04-howto

David Burgos

Read more posts by this author.