blog
How to Manage MariaDB 10.3 with ClusterControl
MariaDB Server is no longer a straight imitate of MySQL. It grew into a mature fork, which implements new functionalities similar to what proprietary database systems offer in the upstream. MariaDB 10.3 greatly extends the list of enterprise features, and with new SQL_MODE=Oracle becomes an exciting choice for companies that would like to migrate their Oracle databases to an open source database. However, operational management is an area where there is still some catching up to do, and MariaDB requires that you build your own scripts.
Perhaps a good opportunity to look into an automation system?
Automated procedures are accurate and consistent. They can give you much-needed repeatability so you can minimize the risk of change in the production systems. However, as modern open source databases develop so fast, it’s more challenging to keep your management systems on par with all new features.
The natural next step is to look for automation platforms. There are many platforms that you can use to deploy systems. Puppet, Chef, and Ansible are probably the best examples of that new trend. These platforms are suitable for the fast deployment of various software services. They are perfect for deployments, but still require you to maintain the code, cover feature changes, and usually, they cover just one aspect of your work. Things like backups, performance, and maintenance still need external tools or scripts.
On the other side, we have cloud platforms, with polished interfaces and a variety of additional services for a fully managed experience. However, it may not be feasible; for instance, hybrid environments where you might be using the cloud, but with still a significant on-prem footprint.
So, how about a dedicated management layer for your MariaDB databases?
ClusterControl was designed to automate the deployment and management of MariaDB as well as other open-source databases. At the core of ClusterControl is functionality that lets you automate the database tasks you have to perform regularly, like deploying new database instances and clusters, managing backups, high availability and failover, topology changes, upgrades, scaling new nodes and more.
ClusterControl installation
To start with ClusterControl, you need a dedicated virtual machine or host. The VM and supported systems requirements are described here. At the minimum you can start from tiny VM 2 GB RAM, 2 CPU cores and 20 GB storage space, either on-prem or in the cloud.
The primary installation method is to download an installation wizard that walks you through all the steps (OS configuration, package download and installation, metadata creation, and others).
For environments without internet access, you can use the offline installation process.
ClusterControl is agentless so you don’t need to install additional software. It requires only SSH access to the database hosts. It also supports agent-based monitoring for higher resolution monitoring data.
To set up passwordless SSH to all target nodes (ClusterControl and all database hosts), run the following commands on the ClusterControl server:
$ ssh-keygen -t rsa # press enter on all prompts
$ ssh-copy-id -i ~/.ssh/id_rsa [ClusterControl IP address]
$ ssh-copy-id -i ~/.ssh/id_rsa [Database nodes IP address] # repeat this to all target database nodesOne of the most convenient ways to try out cluster control maybe the option to run it in docker container.
docker run -d --name clustercontrol
--network db-cluster
--ip 192.168.10.10
-h clustercontrol
-p 5000:80
-p 5001:443
-v /storage/clustercontrol/cmon.d:/etc/cmon.d
-v /storage/clustercontrol/datadir:/var/lib/mysql
-v /storage/clustercontrol/sshkey:/root/.ssh
-v /storage/clustercontrol/cmonlib:/var/lib/cmon
-v /storage/clustercontrol/backups:/root/backups
severalnines/clustercontrolAfter successful deployment, you should be able to access the ClusterControl Web UI at {host’s IP address}:{host’s port}, for example:
HTTP: http://192.168.10.100:5000/clustercontrol
HTTPS: https://192.168.10.100:5001/clustercontrolInstallation of MariaDB Cluster
Once we enter the ClusterControl interface, the first thing to do is to deploy a new database or import an existing one. The version 1.7.2 introduced support for version 10.3 (along with 10.0,10.1,10.2). In 1.7.3 which was released this week, we can see the improved deployment of installation in the cloud.
At the time of writing this blog, the current versions are 10.3.16. Latest packages are picked up by default. Select the option “Deploy Database Cluster” and follow the instructions that appear.
Now is the time to provide data needed for the connection between ClusterControl and DB nodes. At this step, you would have clean VM’s or images of OS that you use inside your organization. When choosing MariaDB, we must specify User, Key or Password and port to connect by SSH to our servers.
After setting up the SSH access information, we must enter the data to access our database, for MariaDB that will be the superuser root. We can also specify which repository to use. You can have three types of repositories when deploying database server/cluster using ClusterControl:
- Use Vendor Repository. Provision software by setting up and using the database vendor’s preferred software repository. ClusterControl will install the latest version of what is provided by the database vendor repository.
- Do Not Setup Vendor Repositories. No repositories will be set up by ClusterControl. ClusterControl will rely on the system configuration (your default repository files).
- Create and mirror the current database vendor’s repository and then deploy using the local mirrored repository. This allows you to “freeze” the current versions of the software packages.
When all is set, hit the deploy button. The deployment process will also take care of the installation of additional tools provided by MariaDB like mariabackup and tools from external vendors, popular in database administration.
Import a New Cluster
We also have the option to manage an existing setup by importing it into ClusterControl. Such an environment can be created by ClusterControl or other methods (puppet, chef, ansible, docker …). The process is simple and doesn’t require specialized knowledge.
First, we must enter the SSH access credentials to our existing database servers. Then we enter the access credentials to our database, the server data directory, and the version. We add the nodes by IP or hostname, in the same way as when we deploy, and press on Import. Once the task is finished, we are ready to manage our cluster from ClusterControl. At this point, we can also define the options for the node or cluster auto recovery.
Scaling MariaDB, Adding More Nodes to DB Cluster
With ClusterControl, adding more servers to the server is an easy step. You can do that from the GUI or CLI. For more advanced users, you can use ClusterControl Developer Studio and write a resource base condition to expand your cluster automatically.
ClusterControl supports an option to use an existing backup, so there is no need to overwhelm the production master node with additional work.
Securing MariaDB
The default MariaDB installation comes with relaxed security. This has been improved with the recent versions however production-grade systems still require tweaks in the default my.cnf configuration. ClusterControl deployments come with non-default my.cnf settings (different for different cluster types).
ClusterControl removes human error and provides access to a suite of security features, to automatically protect your databases from hacks and other threats.
ClusterControl enables SSL support for MariaDB connections. Enabling SSL adds another level of security for communication between the applications (including ClusterControl) and database. MariaDB clients open encrypted connections to the database servers and verify the identity of those servers before transferring any sensitive information.
ClusterControl will execute all necessary steps, including creating certificates on all database nodes. Such certificates can be maintained later on in the Key Management tab.
With ClusterControl you can also enable auditing. It uses the audit plugin provided by MariaDB. Continuous auditing is an imperative task for monitoring your database environment. By auditing your database, you can achieve accountability for actions taken or content accessed. Moreover, the audit may include some critical system components, such as the ones associated with financial data to support a precise set of regulations like SOX, or the EU GDPR regulation. The guided process lets you choose what should be audited and how to maintain the audit log files.
Monitoring and Alerting
When working with database systems, you should be able to monitor them. That will enable you to identify trends, plan for upgrades or improvements or react effectively to any problems or errors that may arise.
The new ClusterControl is using Prometheus as the data store with PromQL query language. The list of dashboards includes Server General, Server Caches, InnoDB Metrics, Replication Master, Replication Slave, System Overview, and Cluster Overview Dashboards.
ClusterControl installs Prometheus agents, configures metrics and maintains access to Prometheus exporters configuration via its GUI, so you can better manage parameter configuration like collector flags for the exporters (Prometheus).
As a database operator, we need to be informed whenever something critical occurs in our database. The three main methods in ClusterControl to get an alert includes:
- email notifications
- integrations
- advisors
You can set the email notifications on a user level. Go to Settings > Email Notifications. Where you can choose between criticality and type of alert to be sent.
The next method is to use the Integration services. This is to pass the specific category of events to the other service like ServiceNow tickets, Slack, PagerDuty, etc. so you can create advanced notification methods and integrations within your organization.
The last one is to involve sophisticated metrics analysis in the Advisor section, where you can build intelligent checks and triggers.
SQL Monitoring
The SQL Monitoring is divided into three sections.
- Top Queries – presents the information about queries that take a significant chunk of resources.
Query Monitor: Top queries - Running Queries – it’s a process list of information combined from all database cluster nodes into one view. You can use that to kill queries that affect your database operations.
Query Monitor: Running Queries - Query Outliers – present the list of queries with execution time longer than average.
Query Monitor: Query Outliers
Backup and Recovery
Now that you have your MariaDB up and running, and have your monitoring in place, it is time for the next step: ensure you have a backup of your data.
ClusterControl provides an interface for MariaDB backup management with support for scheduling and creative reports. It gives you two options for backup methods.
- Logical backup (text): mysqldump
- Binary backups: xtrabackup (lower versions), mariabackup
A good backup strategy is a critical part of any database management system. ClusterControl offers many options for backups and recovery/restore.
ClusterControl backup retention is configurable; you can choose to retain your backup for any time period or to never delete backups. AES256 encryption is employed to secure your backups against rogue elements. For rapid recovery, backups can be restored directly into a new cluster – ClusterControl handles the full restore process from the launch of a new database setup to the recovery of data, removing error-prone manual steps from the process.
Backups can be automatically verified upon completion, and then uploaded to cloud storage services (AWS, Azure and Google). Different retention policies can be defined for local backups in the data center as well as backups that are uploaded in the cloud.
Node and cluster auto-recovery
ClusterControl provides advanced support for failure detection and handling. It also allows you to deploy different proxies to integrate them with your HA stack, so there is no need to adjust application connection string or DNS entry to redirect the application to the new master node.
When the master server is down, ClusterControl will create a job to perform automatic failover. ClusterControl does all the background work to elect a new master, deploy failover slave servers, and configure load balancers.
ClusterControl automatic failover was designed with the following principles:
- Make sure the master is really dead before you failover
- Failover only once
- Do not failover to an inconsistent slave
- Only write to the master
- Do not automatically recover the failed master
With the built-in algorithms, failover can often be performed pretty quickly so you can assure the highest SLA’s for your database environment.
The process is highly configurable. It comes with multiple parameters that you can use to adopt recovery to the specifics of your environment. Among the different options you can find replication_stop_on_error, replication_auto_rebuild_slave, replication_failover_blacklist, replication_failover_whitelist, replication_skip_apply_missing_txs, replication_onfail_failover_script and many others.
Failover is the process of moving to a healthy standby component, during a failure or maintenance event, in order to preserve uptime. The quicker it can be done, the faster you can be back online. If you’re looking at minimizing downtime and meet your SLAs through an automated approach for TimescaleDB, then this blog is for you.
MaxScale Load Balancer
In addition to MariaDB 10.3, ClusterControl adds an option of MaxScale 2.3 load balancer. MaxScale is a SQL-aware proxy that can be used to build highly available environments. It comes with numerous features, however, the main goal is to enable load balancing and high availability.
MaxScale can be used to track the health of the master MariaDB node and, should it fail, perform a fast, automatic failover. Automated failover is crucial in building up a highly available solution that can recover promptly from the failure.
Load Balance Database Sessions
Read-write splitting is a critical feature to allow read scaling. It is enough for the application to connect to the MaxScale, and it detects the topology, determine which MariaDB acts as a master and which act as slaves. It routes the traffic accordingly to this.
Summary
We hope that this blog helps you to get familiar with ClusterControl and MariaDB 10.3 administration modules. The best option is to download ClusterControl and test each of them.
