Linux redundancy: additional tips

In this last part, we conclude with some additional topics and tips for your SQL cluster:

• Additional options for MariaDB Monitor
• Useful SQL management commands
• MaxCtrl
• Maintenance on your SQL cluster

Additional options for MariaDB Monitor

In the previous tutorials, we have set up a specific configuration for the MariaDB monitor, but maybe you need more functionality yourself, or you want to disable an option. Below, we mention and explain some useful MariaDB Monitor options that are not enabled by default (the complete overview can be found here):

• detect_replication_lag=true/false: Detects replication lag between the master and slaves and lets new read queries go to up-to-date slaves. By default, this option is disabled. The MariaDB Monitor user requires INSERT, UPDATE, DELETE and SELECT permissions on the maxscale_schema.replication_heartbeat table and CREATE permissions on the maxscale_schema database
• failcount: The number of errors that have to occur on servers, before a standalone server is seen as a master. By default, this happens 5 times, but you can adjust it with this option.
• failover_timeout: The number of seconds before a timeout occurs in case of a failover. By default, this is 90 seconds.
• switchover_timeout: The number of seconds before a timeout occurs in case of a failover.
• verify_master_failure=true/false: This way, MariaDB performs an additional check for an automatic failover by testing whether slaves still receive events from the master.
• servers_no_promotion: Exclude servers from promotion to master VPS. The syntax looks like this:

servers_no_promotion=server1,server2

• promotion_sql_file & demotion_sql_file: Two very important files if you want absolute control over what your servers do in case of a failover. If you use one of these, the corresponding SQL file will be executed line by line in case of a promotion/demotion, or more specifically:
  promotion_sql_file=/home/root/scripts/promotion.sql ensures that in case of an automatic failover, the new master executes the SQL commands from this file, to prevent replication of a possible external master. With this you can, for example, carry out a change master to command to promote a specific master.
  demotion_sql_file=/home/root/scripts/demotion.sql is executed by the old master when the old master returns to the cluster, before it starts replicating as a slave, and is added to the cluster for the standalone server. Unfortunately, therefore, you cannot use some queries, such as a 'change master to'.

Useful management commands & MaxCtrl

In order to manage the master-slave configuration of your database, a thorough knowledge of MySQL is important. The first step to managing your master-slave configuration is always to start an SQL shell.

mysql -u root -p

Below follows an overview of useful commands and an explanation of exactly what they do:

SHOW MASTER STATUS\G;

• Run this command on the current master. It shows bin log information, such as the binlog file name that the master uses and the position within this file.
   

SHOW MASTER STATUS\G;

• Shows an extensive status overview of the slave. The Slave_IO_State will show the 'Waiting for master to send event' status for a well-functioning cluster.

SHOW DATABASES;

• Shows an overview of the databases hosted on your VPS

SELECT user FROM mysql.user;

• Shows all user accounts within MariaDB. These are not the user accounts of your OS itself, although the same names may be found between them.

STOP SLAVE;
STOP MASTER;
RESET SLAVE;
RESET MASTER;
START SLAVE;
START MASTER;

• Stop: Stops the slave/master respectively.
  Reset: A reset of the master clears the bin log and restores the slave/master to a zero value. For example, if you reset the master, the global transaction id will start again at 0-1-1. The slave/master must be stopped before you perform a reset.
  Start: Restart a previously stopped slave/master.

SHOW VARIABLES LIKE '%gtid%';

• Shows all variables related to the global transaction ID (GTID). This is one of the most important commands for managing your master-slave configuration. The following information is especially important here:
  • gtid_binlog_pos: the GTID of the last event group written in the binary log.
  • gtid_binlog_state: the internal state of the binlog. The master uses these to determine whether a given GTID has already been recorded in the bin log.
  • The gtid_binlog_pos and gtid_binlog_state are used exclusively by the master. The data you see here will therefore differ per VPS.
  • gtid_current_pos: the GTID associated with the last modification of a database.
  • gtid_slave_pos: the GTID of the last modification replicated by the slave. By comparing the gtid_current_pos, the slave knows whether he has made all changes to the database.

MaxCtrl

MaxScale has its own tool called MaxCtrl which you can use to manage MaxScale (which also includes the MariaDB Monitor). You use this tool simply with the command:

maxctrl

The MaxScale commands are mainly focused on checking the current status of your cluster. For actual problem solving it is advisable to look in the logs of your VPS.

maxctrl list servers

• Shows a short overview of all servers in your master-slave setup with the name, IP, port, active connections, and master/slave status.

maxctrl show servers

• A more detailed overview of the servers in your master-slave setup. Generally, the list servers command suffices to quickly check the status of your cluster.

maxctrl list services

• Shows which MaxScale services are active on which servers. Think of the Read-Write service, for example.

maxctrl list monitors

• Shows the status of the active monitors. This means there is one monitor if you went through these tutorials.

Maintenance on your cluster

MaxScale supports the possibility to bring a server into maintenance mode so you can temporarily remove a server/database from the cluster for maintenance. You put a server in maintenance mode with the commands: 

maxctrl set server servername maintenance

The maxadmin command opens the maxadmin shell. You recognize this by the line MaxScale>. In the second command, replace servername by the actual server name that'll be put in maintenance. If you are not sure what your server name is, check it first with the 'list servers' command. 

No new write or read queries are now sent to the server that has been put in maintenance mode. Previously pending queries will still be executed. Once you have finished your maintenance, you take your server out of maintenance mode with the commands:

maxctrl clear server servername maintenance

This concludes our Linux Redundancy Tutorials. You now have the tools at your disposal to set up a high available VPS environment!

Should you have any questions left regarding this article, do not hesitate to contact our support department. You can reach us via the ‘Contact Us’ button at the bottom of this page.