Linux redundancy: MariaDB master-slave database

In this part, we configure a MariaDB master slave setup to be used for synchronising the databases hosted on your VPSs. This is combined in further articles with MaxScale and a virtual IP. The goal is to create an SQL cluster, where two or more servers contain an identical database. In the event of downtime on one or more servers, the other will automatically or manually (depending on your own preference) take over the functionality of the offline server.

In these tutorials, we use this SQL cluster to be able to use a database in combination with one or more websites, for example, a WordPress or PHP website. Later in this tutorial series, we will explain how you link the SQL cluster to a WordPress or PHP website. You can however use the described SQL cluster for a much wider range of use cases.

To set up an SQL cluster, we use a MariaDB master-slave configuration. In the described master-slave setup, one of the VPSs in your SQL cluster is the master and executes write queries (write, drop, create, etc) to itself, and to all slave VPSs linked to your cluster. The slave VPS(s) perform read-queries (select from).

In short, a write query is an action where data is written to your database and a read query is an action where data is retrieved from your database without changing it.

Setting up the master-slave replication 

To set up your master-slave setup, some adjustments are needed in the MariaDB configuration, for example, to ensure that your VPSs can correctly identify each other. In addition, you create an SQL user that is specifically used by the slave vps to communicate with the master. 

Step 1 

Connect to both VPSs via SSH or the VPS console in your control panel. Then choose one of your VPSs that will serve as the master of your database cluster from this point on and note which one this is. Now open your MySQL configuration file on this VPS:

CentOS:

nano /etc/my.cnf.d/server.cnf

Ubuntu: 

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Step 2 

In your configuration file, under [mysqld], you'll find the line #bind-address=0.0.0.0 or #bind-address=127.0.0.1 (with or without #). MariaDB only listens to TCP/IP connections of the localhost. This means that by default, the slave(s) in your cluster does not have permission to connect to your master. Remove the # and adjust the 0.0.0.0 to : :  

bind-address=::

Please note: if you see the skip-networking option in this file, make sure that thisoption is not set to 1. Otherwise, slave(s) cannot connect to your master.

Step 3

Add the content below directly under [mariadb]: 

log-bin
log_basename=dbmaster
server_id=1
wsrep_gtid_mode=1
gtid_strict_mode=1

•  log-bin: activates binary logging. All changes to the databases are kept in binary logs. An index file and binary log files are used for this.
• log_basename: is the name that is added to the name of all log files that your cluster creates (not just the binary logs). If you do not use this option, your hostname will be used, which causes problems if it's changed.
• server_id: is the number by which your VPS is identified in the cluster. This has to be a unique number for each VPS.
• wsrep_gtid_mode: enables the use of global transaction IDs (GTID). These have been introduced in MariaDB as of MariaDB 10.0. Here, events (i.e. numbers) are linked to each event group in the binary logs (bin logs). When replicating event groups, the global transaction ID is kept intact. This makes it easier to identify the same binlogs events. For a more detailed explanation, see the MariaDB documentation.
• gtid_strict_mode: by setting this option to 1, the GTIDs of the master and slave are connected to each other. Suppose that the original master has GTID 0-1-500 and then becomes unreachable, the slave then writes the new changes from 0-2-501 (the 1 and 2 identify which server is performing actions).

Step 4

Save your changes and close the file (ctrl + x > y > enter). Repeat the above steps on your other VPS that you will use as a slave. Give a unique server_id in step 3. 

The reason you set this on your slave VPS as well, is that this is necessary for the failover functionality that you set up in the next section. This way ensures your setup that if your master fails and a slave becomes the new master, you can connect the old master (and any other slave(s)) when it is available again.

Step 5

Restart MariaDB on all VPSs: 

systemctl restart mariadb

Step 6 

Log in to an SQL shell on the master and Slave VPS (possibly change root to the actual user name). A password from your root user will be requested:

mysql -u root -p

Step 7

Create a MariaDB user on your master and slave VPS using the steps below. This user will be used by the slave(s) to connect to the master. 'Slavename' and 'password' are replaced by the username and password that you want to use for your slave(s) to connect to the master.

CREATE USER 'slavename'@'LOCALHOST' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slavename'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Step 8

Run the following command and repeat it on each VPS in your database cluster.

Adjust the private network IP address to that of the other VPS than the VPS on which you execute the command. Suppose you have two VPSs with IPs 192.168.1.1 and 192.168.1.2 and you execute the command from the VPS with the IP 192.168.1.1, then change the IP in this command to 192.168.1.2. 

GRANT REPLICATION SLAVE ON *.* TO 'slavename'@'192.168.1.1' IDENTIFIED BY 'password' WITH GRANT OPTION;

Step 9 

Check the status of your Master with the following command: 

 SELECT @@global.gtid_binlog_pos;

The output will look something like this:

+--------------------------+
| @@global.gtid_binlog_pos |
+--------------------------+
| 0-1-237                  |
+--------------------------+

You need the GTID (here 0-1-111) in step 19 to indicate the slave(s) from which point they have to replicate the database. 

Background information: Befor MariaDB 10.0, the value of file & position from this overview was used to indicate the slave(s) where they should start with the replication of the databases. With the arrival of global transaction IDs (see step 3), this is no longer necessary and the global transaction ID (GTID) is used.

Step 10

If you have not made any changes to your MariaDB database since you started this tutorial, skip this step and proceed to step 15. If there have been changes, first, copy your database to your slave(s) by using the following steps. 

Place a temporary lock on your database on both VPSs so that it becomes read-only. This prevents you from making changes in your database(s) while transferring your database(s).

FLUSH TABLES WITH READ LOCK;

Step 11 

Transfer the backup from command-line. Below, we explain the command-line steps (or use phpMyAdmin to export the database on the master and import it to the slave). First check which databases you use to make sure that you use the correct name for step 12: 

SHOW DATABASES;
exit

Step 12

Then you back up your database (s). Again, your root password will be requested: 

mysqldump -u root -p wordpress > /var/lib/mysql/wordpress-dump.sql

Run this command for every database that you want to back up to your VPS slave. Replace wordpress by the actual name of the databases and wordpress-dump.sql by the desired backup file name.

Step 13

Connect to your slave VPS via SSH and copy the backup with an Rsync command (see the previous tutorial if you have not set up Rsync yet):

rsync -e "ssh -p 22 -o StrictHostKeyChecking=no" transip@192.168.1.1:/var/lib/mysql/wordpress-dump.sql /var/lib/mysql/ --no-perms --no-owner --no-group --no-times

Replace 22 by the number of your SSH port, the username and the IP by your actual VPS' username and IP, and the backup file name and location by the name and location you used in the steps above.

Step 14

Import the backup by executing the following command on your slave VPS (change the database name and location to the actual name and location): 

mysql -u root -p wordpress < /var/lib/mysql/wordpress-dump.sql

Do not forget to restore the lock on your master: 

mysql -u root -p
FLUSH TABLES WITH READ LOCK;

Step 15

Should you not have done so already, then open SQL port 3306 now for all your SQL servers as well as the (web) servers which are going to connect to your database.

In the commands below, replace 192.168.1.0/24 with the IP range the addresses of your private network belong to. Alternatively, you could whitelist the specific IP addresses one by one instead.

Firewalld (CentOS, AlmaLinux, Rocky Linux)

firewall-cmd --permanent --zone=public --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=3306 protocol=tcp accept'
firewall-cmd --reload

UFW (Ubuntu/Debian):

ufw allow from 192.168.1.0/24 to any port 3306

Step 16

We will now configure the slave to use the master which we configured in the previous steps for write queries. First start a SQL shell on your slave if you are not already on it: 

mysql -u root -p

Step 17

Stop the slave with the command:

STOP SLAVE;

Step 18

We now tell the slave to use the master for write-actions, and where it can find the master: 

CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_USER='slavename',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_USE_GTID=current_pos;

• MASTER_HOST: (Preferably) use the internal IP of your master here.
• MASTER_USER: The username that you created in step 6.
• MASTER_PASSWORD: The password which belongs to this user.
• MASTER_PORT: The port that MariaDB uses. Add it to your firewall if you have not done so already.
• MASTER_CONNECT_RETRY: The number of attempts used to connect to the master if the first attempt fails.
• MASTER_USE_GTID: When your master fails, one of your linked slave(s) automatically becomes the new master (we will get back to this later). The old master will be a slave once it can be reached again. By using the current_pos option, the old master (which is now a slave) will know from which global transaction ID the replication has to be started.

Step 19

Close the MySQL shell and upgrade the system tables on all VPSs in your cluster by executing the following commands on the VPSs:

exit
mysql_upgrade -u root -p

Step 20

Your slave probably has another gtid than your master (see step 9). Correct your slave VPS with the commands below to synchronize from the gtid of the master. Replace the value x-x-x with the value you noted in step 9: 

mysql -u root -p
RESET MASTER;
SET GLOBAL gtid_slave_pos='x-x-x';
START SLAVE UNTIL master_gtid_pos='x-x-x';

You will see a 'Query OK' message. However, this does not indicate whether your slave is well connected. For this, you use the command: 

SHOW SLAVE STATUS\G;

Solving problems

All forms of database clusters are complicated, and it is therefore impossible to exclude that you experience a problem because of circumstances, for example, if a slave has been offline for a while and tries to use an incorrect GTID.

In this article several of the more common errors are explained.

Your database cluster is now configured to automatically synchronize your databases. This brings us to the end of this part of the tutorial series. Click here to continue with part 4: setting up MaxScale and MariaDB monitor in which you, among other things, configure the automatic failover functionality of your database cluster.