In this article, we give you some general tips for the management of SQL clusters, especially aimed at security, but also to prevent complex problems (e.g. split-brain) as much as possible. This article is focussed on what the measures are. In the future, we will also include documentation on how to apply the tips.
In the context of this article, we refer to MariaDB / MySQL when we speak of SQL. Though many tips also apply to other SQL solutions.
General tips
- If a VPS in your SQL cluster becomes unreachable, do not make any adjustments to other VPSs in the cluster. Always make sure that the unreachable VPS is accessible again and is part of your SQL cluster. This way, you prevent a number of SQL servers becoming unreachable due to a human error and that the restoring of your SQL cluster becomes a complex challenge.
- Back up your SQL databases regularly.
- Make sure your SQL servers are not accessible via the public WAN, but only over a private network. Suppose you store privacy-sensitive data, you prevent the MySQL servers themselves from forming a direct point that can be attacked by hackers. You can achieve this by either closing the public WAN on the SQL servers completely, or by blocking all but specific IP's in your firewall.
- When installing a new SQL server, do not forget to secure it from command-line:
- Consider changing the default port 3306 in the configuration file of your SQL server for extra security. In Linux, you do this in your SQL configuration file under the heading mysqld:
- Change your passwords frequently.
- Update your servers regularly.
- Be specific in SQL queries: try to prevent * as much as possible in your queries and replace them with specific queries.
- Encrypt sensitive data.
MaxScale
Below are some specific tips for using MariaDB MaxScale. This does not apply to other SQL solutions.
-
Failover: MaxScale offers you the option to use automatic failover or not at a master-slave setup (i.e. this does not apply to a Galera cluster). Make sure you are aware of the consequences of a split-brain situation and choose whether 100% certain database integrity or the accessibility of your services is more important to you. If you work with sensitive data, always opt for database integrity over accessibility.
- Integrity: If you choose integrity, do not use automatic failover, but only perform a manual failover. If you choose this approach, make the following adjustments in /etc/maxscale.cnf:
For more information about MaxScale and manual failover, see this article.
-
Accessibility: If you choose accessibility, use the automatic failover feature for MaxScale master-slave setups as described in our tutorials.
- Consider using a database firewall filter, see the MariaDB documentation.
- Use logging and audits, see this article. That way, you have clear insight into queries for, for example, troubleshooting purposes.
Do you have any additional tips or are you still looking for practical tips for specific aspects of your database cluster (e.g. for MsSQL)? Please let us know in a response to this article under 'Comments'.
Should you have any questions left regarding this article, do not hesitate to contact our support department. You can reach them via the ‘Contact Us’ button at the bottom of this page.