Replication with MariaDB Galera Cluster - MariaDB Cookbook (2014)

MariaDB Cookbook (2014)

Chapter 7. Replication with MariaDB Galera Cluster

In this chapter, we will cover the following recipes:

· Installing MariaDB Galera Cluster

· Dropping a node from MariaDB Galera Cluster

· Shutting down MariaDB Galera Cluster

Introduction

Two of the primary reasons for replicating data between MariaDB servers are to provide greater performance and more redundancy. The traditional master-slave replication covered in Chapter 6, Replication in MariaDB, provides for great read performance by having several read-only slave servers. However, it only solves the redundancy issue partially. In classic replication, there is only one master server node, and if it fails, then one of the slave server nodes must be promoted to become a master server node for the others. Getting this to work correctly in an automated way is difficult.

An easier way to configure replication will be if every node was a master server node. Reads and writes can happen to any of the nodes and the replication component will make sure that everything just works.

MariaDB Galera Cluster makes this sort of replication easy to set up and use. Every node in a Galera Cluster is equal, so if any single node fails it is alright. The cluster will continue running and we can repair or replace the faulty node without worrying about whether it is a master or a slave server.

Note

MariaDB Galera Cluster is only available on Linux-based operating systems, so all the recipes in this chapter are Linux-only.

Installing MariaDB Galera Cluster

MariaDB Galera Cluster is a separate product from MariaDB. So, installing it is similar, but not quite the same as installing MariaDB. In particular, the package names for MariaDB's server components are different and there is an extra galera package that needs to be installed.

Getting ready

For this recipe, we'll assume that we have three servers named db01, db02, and db03, with IP addresses 192.168.1.101, 192.168.1.102, and 192.168.1.103, respectively. We'll further assume that they are all running fresh installs of Ubuntu 12.04 LTS.

How to do it...

1. On all the three hosts, install MariaDB Galera Cluster using the following commands:

2. sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

3. sudo add-apt-repository 'deb http://ftp.osuosl.org/pub/mariadb/repo/10.0/ubuntu precise main'

4. sudo apt-get update

5. sudo apt-get install mariadb-galera-server

6. On all the three hosts, stop MariaDB using the following command so that we can add modify the configuration:

7. sudo service mysql stop

8. On all the three hosts, create a galera_common.cnf file at /etc/mysql/conf.d/ with the following content:

9. # Galera-common configuration

10.[mysqld]

11.wsrep-cluster-name = "test_cluster"

12.wsrep-provider = /usr/lib/galera/libgalera_smm.so

13.wsrep-provider-options = "gcache.size=256M;

14.gcache.page_size=128M"

15.wsrep-sst-auth = "galera:mypassword"

16.binlog-format = row

17.default-storage-engine = InnoDB

18.innodb-doublewrite = 1

19.innodb-autoinc-lock-mode = 2

20.innodb-flush-log-at-trx-commit = 2

innodb-locks-unsafe-for-binlog = 1

21. On db01, create a galera_db01.cnf file at /etc/mysql/conf.d/ with the following content:

22.# Galera-specific configuration

23.[mysqld]

24.wsrep-node-name = "db01"

25.wsrep-new-cluster

26.wsrep-sst-receive-address = 192.168.1.101

wsrep-node-incoming-address = 192.168.1.101

27. On db01, start MariaDB using the following command:

28.sudo service mysql start

29. On db01, launch the mysql command-line client and run the following commands to create a galera user:

30.GRANT ALL ON *.* TO 'galera'@'192.168.1.%' IDENTIFIED BY 'mypassword';

31.FLUSH PRIVILEGES;

32. On db02 and db03, back up the /etc/mysql/debian.cnf file:

33.sudo cp -avi /etc/mysql/debian.cnf /etc/mysql/debian.cnf.dist

34. On db02 and db03, edit the /etc/mysql/debian.cnf file so that the passwords match those in that file on db01.

35. On db02, create a galera_db02.cnf file at /etc/mysql/conf.d/ with the following content:

36.# Galera-specific configuration

37.[mysqld]

38.wsrep-node-name = "db02"

39.wsrep-cluster-address = gcomm://192.168.1.101

40.wsrep-sst-receive-address = 192.168.1.102

wsrep-node-incoming-address = 192.168.1.102

41. On db03, create a galera_db03.cnf file at /etc/mysql/conf.d/ with the following content:

42.# Galera-specific configuration

43.[mysqld]

44.wsrep-node-name = "db03"

45.wsrep-cluster-address = gcomm://192.168.1.101

46.wsrep-sst-receive-address = 192.168.1.103

wsrep-node-incoming-address = 192.168.1.103

47. Start MariaDB on db02 and db03 using the following command:

48.sudo service mysql start

49. On all the three hosts, launch the mysql command-line client and run the following command:

50.SHOW STATUS LIKE 'wsrep%';

51. In the output, the wsrep_incoming_addresses variable should have the IP addresses of all the three servers, the wsrep_cluster_size variable should be 3, and the wsrep_connected and wsrep_ready variables should both be ON.

52. On db01, edit the /etc/mysql/conf.d/galera_db01.cnf file. Remove the wsrep-new-cluster line and replace it with the following line of code:

wsrep-cluster-address = gcomm://192.168.1.101,192.168.1.102,192.168.1.103

53. On db02 and db03, edit the /etc/mysql/conf.d/galera_db*.cnf files and change the wsrep-cluster-address line to match the line we added to db01 in the previous step.

54. Test out the cluster by creating databases and tables on one server, inserting data on a different server, and reading the data on the third server. All changes will be replicated to all the three servers regardless of which server the change is made on.

How it works...

As mentioned previously, MariaDB Galera Cluster is a separate product from MariaDB; when installing it on Ubuntu and Debian, we specify the mariadb-galera-server package instead of mariadb-server. The mariadb-galera-server package will pull in all of the correct packages, including the important galera package, which contains the external program that handles the replication between the hosts.

Tip

The minimum size of MariaDB Galera Cluster is three, so it is used in this recipe. However, more nodes are recommended so that if a node (or two) fail, the total number of nodes in the cluster never dips below three.

There are only a few settings that are unique to each individual cluster node; so in this recipe, we put the common settings in one configuration file and the unique ones into a separate file. All of these files are available to download from the book's website.

Whenever a cluster node starts, it needs to know whether it should join an existing cluster or start a new cluster. This is controlled via the wsrep-new-cluster and wsrep-cluster-address variables. When the wsrep-new-cluster variable exists in our configuration, MariaDB Galera Cluster knows to bootstrap a new cluster. When that line doesn't exist and wsrep-cluster-address is set to gcomm://192.168.1.101, the node will try to connect to that server and join the cluster that it is a part of. As nodes join, their addresses are shared among all the cluster members so that every node knows where every other node is.

Note

Older versions of MariaDB Galera Cluster used an empty gcomm:// value to indicate that a new cluster should be created. This behavior has been deprecated. Now, we should always use the wsrep-new-cluster variable to indicate when we want to create a new cluster. Also, to avoid problems, we should only have wsrep-new-cluster or wsrep-cluster-address defined, never both and we should never use an empty gcomm:// value.

After the initial bootstrapping, it is a good idea to update our configuration to have the entire cluster addresses listed in case we need to restart any of the nodes. We want our initial node to connect to the running cluster instead of creating a new cluster, and we want our existing nodes to have more connection options than just the first node (on the chance that it is down when new nodes are trying to start up).

There's more...

The instructions in this recipe are specific to Ubuntu Linux, but they can easily be adapted to Debian, CentOS, Red Hat, and Fedora. For Debian, the only difference is to change the add-apt-repository line to point at a Debian repository instead of an Ubuntu repository. The installation and configuration steps are the same.

For CentOS, Red Hat, and Fedora, apart from configuring the MariaDB Yum repository, the major change is that the configuration files are placed under /etc/my.cnf.d/ instead of /etc/mysql/conf.d/. Also, the packages to install on them are MariaDB-Galera-server andMariaDB-client.

Additional nodes can be easily added by repeating the steps for db02 and db03, and updating the configuration files with the appropriate IP addresses.

Use the repository configuration tool available at https://downloads.mariadb.org/mariadb/repositories/ to generate the appropriate repository configurations.

Configuring MariaDB Galera Cluster

To make things simple, Galera-specific status and configuration variables are all prefaced with wsrep, so you can view them with the following commands:

SHOW STATUS LIKE 'wsrep%';

SHOW VARIABLES LIKE 'wsrep%'\G

There are also a few non-Galera-specific variables that should be set for MariaDB Galera Cluster to run properly. The binlog-format, default-storage-engine, and innodb-% variables from the recipe are the most important ones.

See also

· The documentation of the various configuration and STATUS variables is available at https://mariadb.com/kb/en/mariadb-galera-cluster-configuration-variables/ and https://mariadb.com/kb/en/mariadb-galera-cluster-status-variables/

· The full documentation of MariaDB Galera Cluster is available at https://mariadb.com/kb/en/galera/

· The Codership group at https://groups.google.com/forum/?fromgroups#!forum/codership-team is a great place to talk with other Galera cluster users

· The Galera wiki available at http://www.codership.com/wiki/ also contains lots of information

Dropping a node from MariaDB Galera Cluster

MariaDB Galera Cluster exists so that we can eliminate single points of failure in our infrastructure. Once set up, a single node can be taken out of the cluster for maintenance without impacting the rest of the cluster or causing downtime for our applications.

Getting ready

Create MariaDB Galera Cluster as described in the Installing MariaDB Galera Cluster recipe earlier in this chapter. In this recipe, we'll be shutting down db03.

How to do it...

1. On db03, run the following command to check whether the node is up to date:

2. mysql -e "SHOW STATUS LIKE 'wsrep_local_state_comment'"

3. If the value is synced, then the node is up to date and we can safely shut down the node with the following command:

4. sudo service mysql stop

5. On the other cluster nodes, run the following command to check whether the node has been destroyed:

6. mysql -e "SHOW STATUS LIKE 'wsrep_%'"

7. We'll know the node is destroyed if the number of nodes in wsrep_cluster_size is 2 and the IP address of db03 is not listed in wsrep_incoming_addresses.

How it works...

All the nodes in MariaDB Galera Cluster are equal. We can read and write to any node we wish and the changes will be replicated to all nodes. Because of this, dropping a node is easier than with a traditional replication setup where we need to worry about which node is the master server that the slave server nodes read. All nodes in MariaDB Galera Cluster are primary master server nodes, so our only concern is that the node is up to date before shutting it off like we would any single non-clustered MariaDB server.

There's more...

Nodes are usually taken down for maintenance reasons. Adding the node back is as easy as starting it back up with the following command:

sudo service mysql start

There will be a period of time as it brings itself back up to date with the other nodes in the cluster, but after a few seconds or minutes (depending on how far behind it is), it will be good to go. We just check the value of the wsrep_local_state_comment variable on the joining node and when it is synced, we know that the node is up to date.

See also

· Information on various ways of monitoring our MariaDB Galera Cluster is available at http://www.codership.com/wiki/doku.php?id=monitoring

Shutting down MariaDB Galera Cluster

There can be a time when we want to shut down our entire cluster; maybe for a move to a different facility, or because we are replacing it completely. Whatever the reason, this recipe outlines the preferred way to do so.

Getting ready

Create MariaDB Galera Cluster as described in the Installing MariaDB Galera Cluster recipe earlier in this chapter.

How to do it...

1. Ensure that any applications using the cluster are shut down.

2. On db03, run the following command to check whether the node is up to date:

3. mysql -e "SHOW STATUS LIKE 'wsrep_local_state_comment'"

4. If the value is synced, run the following command to shut down the node:

5. sudo service mysql shutdown

6. On db02 and db01, repeat the same steps, first on db02, and then on db01.

How it works...

Shutting down MariaDB Galera Cluster is just like shutting down MariaDB. To be careful, we should make sure that the applications which use the cluster are shut down so that the cluster is idle.

Then, it is just a simple matter of shutting down the nodes one at a time.

The only thing left to do after all nodes are shut down is to make a note of which node was shut down last, as it will need to start up first if we want to restart the cluster. In our recipe, we shut down db01 last, as it was the first one we started when creating the cluster. In practice, it doesn't matter; we just need to know which one was shut down last as it will be the cluster node that is most up to date. It may also be a good idea to remove the wsrep-cluster-address line and add the wsrep-new-cluster line to the config on db01 (or whichever server we shut down last) so that we don't have to remember to do it prior to starting the cluster up again.

The danger with starting the cluster up again using a server that was not the last one to be turned off is that it will not have the modifications or additions to our data that were made in the time between when it was shut down and when the last node in the cluster was shut down.

See also

· The full documentation of MariaDB Galera Cluster is available at https://mariadb.com/kb/en/galera/

· The Codership group at https://groups.google.com/forum/?fromgroups#!forum/codership-team is a great place to talk with other Galera cluster users

· The Galera wiki available at http://www.codership.com/wiki/ also contains lots of information