This is the sixth and final part of our Tutorial Series 'Setting up a redundant VPS environment'. If you are setting up a new redundant VPS environment, we recommend starting with part 1 and to not skip any parts.
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
- Maintenance on your SQL cluster
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:
- 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'.
To reduce the chances of errors when choosing this approach, it is advisable not to execute queries which change data in databases.
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:
- 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 MASTER STATUS\G;
- Shows an overview of the databases hosted on your VPS
- 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.
SELECT user FROM mysql.user;
- Stop: Stops the slave/master respectively.
STOP SLAVE; STOP MASTER; RESET SLAVE; RESET MASTER; START SLAVE; START MASTER;
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.
- 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:
SHOW VARIABLES LIKE '%gtid%';
- 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.
MaxScale has its own shell from which you manage MaxScale (which also includes the MariaDB Monitor). You open this with the command:
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.
- Shows a short overview of all servers in your master-slave setup with the name, IP, port, active connections, and master/slave status.
- 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.
- Shows which MaxScale services are active on which servers. Think of the Read-Write service, for example.
- Shows the status of the active monitors. This means there is one monitor if you went through these tutorials.
- Close the MaxAdmin shell.
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:
maxadmin 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:
maxadmin 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.
If you want to discuss this article with other users, please leave a message under 'Comments'.