MariaDB MaxScale — Replica Rebuild – Part 9

Part 9 — Live connection tests

This multi-part series breaks down each section into easy logical steps.

If you have not completed part 1, start here.

Failover tests

Whilst this mini-series is about rebuilding replicas automatically with MaxScale, it also brings some amazing protection against failure of your database servers or the ability for you to take a node offline to carry out maintenance. With one simple command, we can swap which server is the primary (write) server in our asynchronous cluster.

To start this test, on the Primary Server, server1, we need to create a database, table and user:

CREATE DATABASE demo;
CREATE TABLE demo.test (id SERIAL PRIMARY KEY, host VARCHAR(50) NOT NULL, created DATETIME) ENGINE=INNODB DEFAULT CHARSET=utf8;
GRANT ALL PRIVILEGES ON demo.* TO 'demouser'@'%' IDENTIFIED BY 'SecurePWD_123';

From the MaxScale Server, we can test the user. Replacing the IP address as appropriate (your MaxScale server):

mariadb -udemouser -pSecurePWD_123 -h10.106.0.5

Hopefully, you will be presented with a database prompt, you can go ahead and exit from here.

To test MaxScale, you will need three terminals (connections) to the MaxScale Server. This is to allow us to run some monitoring, a script and execute commands against MaxScale.

On terminal 1 run this command:

watch -n0.5 "maxctrl list servers"

This will continuously, every .5 seconds check the status of the servers.

You should see some output like this:

On terminal 2 run this script, which is a basic application, that will insert data into the database. Replace the IP address as appropriate (your MaxScale server):

for ((i=1;i<=600;i++)); do mariadb -udemouser -pSecurePWD_123 -h10.106.0.5 -P3306 -e 'insert into demo.test SET host='@@hostname', created=now()'; [[ $? -eq 0 ]] && sleep 1 || { echo "Down at `date`"; sleep 1; } ; done

On each database server, in turn, stop the service:

systemctl stop mariadb

It does not matter which order you do it. But if you stop the master server first, you will see it switchover within your monitor:

You will notice that the server that was the master, is replaced with another. Shut the remaining two servers down.

When there are no more database servers, our application will error as there are no more nodes to take the requests. These errors appear slow, but this is due to the connection timeout.

Restart any one of the database servers, and your application will carry on inserting data and your monitor will show you which server is taking the work. 

Restart the other two database servers, in any order you would like. Do not restart the third server, until the second is back online. You will notice the remaining two servers start as slaves and resync with the cluster.

Rebuild test

On one of the database servers that is currently a slave, connect with the mariadb command and delete the database we are using for the test:

DROP DATABASE demo;

You will notice almost immediately, that the monitor on the MaxScale server changes:

In my case, on server 3 where I dropped the database, it is no longer a slave, the server is however still running. The remaining two servers remain synchronised. This is because we have broken replication on server 3. We can fix this using the replica rebuild command, on the MaxScale Server:

maxctrl call command mariadbmon async-rebuild-server Server-Monitor server3

After a few moments, our server 3 will be rebuilt, you will notice that it goes down first. 

When the process is taking place, it is likely the your other slave server will just report as Running, this is because it is creating the backup for the server that needs to be rebuilt.

This is very powerful and allows a DBA to rebuild any failed slave server quickly and efficiently.

Remove a node for maintenance

Many organisations are scared to stop a database server so do not carry out important maintenance activities. MaxScale makes this very easy for us, without any downtime to our applications.

Select which server you would like to carry out maintenance on, and put it into maintenance mode. I am going to choose Server 1:

maxctrl set server server1 maintenance

You will see that the server is marked as maintenance mode, but replication carries on running:

On the database server, we can then stop MariaDB and carry out any maintenance activities we wish to do:

systemctl stop mariadbyum update -y
systemctl start mariadb

Once we have completed the maintenance of our server, you need to remove this mode from MaxScale:

maxctrl clear server server1 maintenance

MaxScale will join the server back to the cluster and will start distributing traffic to this server:

Switchover test

We might want to move whichever server is currently the master, to one of the other nodes. You can do that with the following command, to make server 3 the new master:

maxctrl call command mariadbmon switchover Server-Monitor server3 server2

You have to specify the new server and the server that you are switching over from.

On our monitor, we will see that the switchover has occurred, and our insert script should still be running error-free.

Conclusion

In conclusion, MaxScale is a very powerful tool and is a must for any Enterprise who is serious about the uptime of their MariaDB database. 

Let me know how you get on!

Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7 | Part 8 | Part 9