Chapter 6. Replication in MariaDB

In this chapter, we will cover the following recipes:

• Setting up replication

• Using global transaction IDs

• Using multisource replication

• Enhancing the binlog with row event annotations

• Configuring binlog event checksums

• Selectively skipping the replication of binlog events

Introduction

Replication is what allows MariaDB to scale to thousands of servers, millions of users, and petabytes of data. But let's not get ahead of ourselves. Replication on a small scale is a great way to grow the number of users our application can support with minimal effort. As we gain users, we can grow the number of replication servers to match.

There are many different ways to set up how we do replication. In this chapter, we'll only touch on a couple of basic ones: a single master to multiple slaves, and multiple masters to a single slave.

Note

Historically, replication source servers have been called masters and replication target servers have been called slaves. To avoid confusion, we'll be using these names.

Setting up replication

Setting up replication is not hard as long as all the various bits are in place. This recipe is all about the most basic concept of replication topologies; a single master server replicating to multiple slaves is shown in the following diagram:

Getting ready

This recipe assumes that we have three servers. The servers are named db01, db02, and db03, and they reside on the 192.168.4.0 network with IP addresses 192.168.4.101.to 192.168.4.103. One server, db01, will be our replication master, and the other two will be our replication slaves.

For the purposes of this recipe, the servers are assumed to contain fresh installs of MariaDB, with just the default databases set up.

How to do it...

1. On all three hosts, launch the mysql command-line client and connect to the local MariaDB server with the root user (or another user with the GRANT privilege) and run the following command:

   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
   TO replicant@'192.168.4.%'
   IDENTIFIED BY 'sup3rs3kr37p455w0rd...

Using global transaction IDs

Global Transaction ID (GTID) is a new feature in MariaDB 10.0 and above. It helps us achieve greater reliability and flexibility with our replication.

Getting ready

This recipe builds upon the previous one, so to get ready for this recipe, simply set up a basic replication as described in the Setting up replication recipe.

How to do it...

1. On both our replication slave servers, launch the mysql command-line client and run the following commands:

   STOP SLAVE;
   CHANGE MASTER TO MASTER_USE_GTID = SLAVE_POS;
   START SLAVE;
   

2. Check on the status of our replication slave servers with the following command:

   SHOW ALL SLAVES STATUS\G
   

3. Look at the bottom of the output for the following lines (the Gtid_Slave_Pos value will likely be different and the lines are separated by several other lines in the output):

   Using_Gtid: Slave_Pos
   Gtid_Slave_Pos: 0-101-2320
   

4. Insert more data into our temp.doctors table on the replication master server, and then run the following SELECT statement on...

Using multisource replication

A familiar replication topology is one where we have a single master server and several slave servers. Another alternative topology is where we have a single slave server connected to multiple master servers. This is called multisource replication.

Getting ready

For this recipe, we'll be working on the assumption that we have three servers, db01, db02, and db03, which are each running a fresh install of MariaDB. The first two will be our replication masters, and the last one will be our replication slave as shown in the following diagram:

We'll further assume that all three servers are on the same subnet, 192.168.4.0, with the final part of their individual IP addresses being 101, 102, and 103, respectively.

How to do it...

1. On all three servers, launch the mysql command-line client and run the following command to add our replication user:

   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
     TO replicant@'192.168.4.%' IDENTIFIED BY 'sup3rs3kr37p455w0rd';
   

2. Quit the...

Enhancing the binlog with row event annotations

When using replication, it's popular to set binlog_format to row. The only issue with this is that when we look at the binlog, it is harder to read because the statements aren't included. We can see the changes but not the SQL statement that made the changes.

Getting ready

For this recipe, we'll assume that we've set up replication as described in either the Setting up replication or Using multisource replication recipes earlier in this chapter. Pick a master server and a slave server to use. In this recipe, we'll call the master server db01 and the slave server db03.

How to do it...

1. On db01, edit the system my.cnf or my.ini file and add the following to the [mysqld] section:

   binlog_format = row
   binlog_annotate_row_events
   

2. On db03, edit the system my.cnf or my.ini file and add the following to the [mysqld] section:

   binlog_format = row
   replicate_annotate_row_events
   

3. Restart MariaDB on both servers.

4. On db01, launch the mysql command-line client and...

Configuring binlog event checksums

A rare, but still possible, problem can occur if the filesystem where we store our binary and relay logs gets corrupted. It can be especially damaging if we don't detect it early on. Event checksums are a way to detect this quickly.

Getting ready

For this recipe, we'll assume that we've set up replication as described in either the Setting up replication or the Using multisource replication recipes earlier in this chapter. Pick a master server and a slave server to use. In this recipe, we'll call db01 as the master server and db03 as the slave server.

How to do it...

1. On db01, launch the mysql command-line client and run the following commands:

   SET GLOBAL BINLOG_CHECKSUM = 1;
   SET GLOBAL MASTER_VERIFY_CHECKSUM = 1;
   

2. On db03, launch the mysql command-line client and run the following command:

   SET GLOBAL SLAVE_SQL_VERIFY_CHECKSUM = 1;
   

How it works...

When checksums are enabled on our master and slave servers, it adds an extra layer of checking as events are copied...

Selectively skipping the replication of binlog events

Sometimes, we want to skip replicating certain events to our replication slave servers. MariaDB lets us do this dynamically.

Getting ready

For this recipe, we'll assume that we've set up replication as described in either the Setting up replication or Using multisource replication recipes earlier in this chapter. Pick a master server and a slave server to use. In this recipe, we'll call db01 as the master server and db03 as the slave server.

How to do it...

1. On db01, launch the mysql command-line client and run the following command to turn on replication skipping:

   SET @@skip_replication=1;
   

2. On db01, create an empty database:

   CREATE DATABASE w;
   

3. On db03, run the following commands to turn off replication of skipped events:

   STOP SLAVE;
   SET GLOBAL REPLICATE_EVENTS_MARKED_FOR_SKIP = FILTER_ON_MASTER;
   START SLAVE;
   

4. On db01, create another empty database:

   CREATE DATABASE wx;
   

5. On db03, switch to filtering on the slave:

   STOP SLAVE;
   SET GLOBAL REPLICATE_EVENTS_MARKED_FOR_SKIP...