Database Automation – Private DBaaS for MySQL, MariaDB and MongoDB with ClusterControl

Installing, configuring, deploying databases and performing repetitive administrative tasks are all part of a DBA’s or sysadmin’s job. This can get pretty repetitive and overwhelming if you are part of a centralized IT team, running multiple databases for your organization’s different departments, or a managed hosting provider responsible for setting up and operating databases for external clients. One way to get out of this ‘manual, repetitive task’ business is through a Database as a Service (DBaaS).

DBaaS is a way of delivering database functionality as a service to one or more consumers. A DBaaS platform would provide automated procedures for database deployment, monitoring, backups, recovery/repair, scaling, security/multi-tenancy, etc. This type of automation is especially useful where agility is needed, e.g. for systems that require elasticity by scaling out or scaling back at short notice, or for temporary deployments associated with dev/test/QA. Now that you’ve automated the repetitive stuff, you can start using your time and skills to optimize your schemas and configurations, help developers write better queries that scale, and work on system architecture or strategic database initiatives.

In this post, we’ll have a look at how enterprise companies or managed hosters can use ClusterControl to implement a DBaaS for MySQL, MariaDB and MongoDB.

Multitenancy – Organizations, Users, Roles and Clusters

Some basics first, let’s have a look at how ClusterControl handles multiple users and clusters. ClusterControl has an admin module through which an admin can create users, specify their roles and the organization each user belongs to.

By default, ClusterControl provides three types of roles:

• Super Admin – Sees all clusters/DBs that are registered with ClusterControl. The Super Admin can also create organizations and users. Only the Super Admin can transfer a cluster from one organization to another.
• Admin – Belongs to a specific organization, and sees all clusters registered in that organization.
• User – Belongs to a specific organization, and only sees the cluster(s) that she registered.

It is also possible to create custom roles with specific access control, for more fine-grained access to functionality.

As a roundup, here is how the different entities relate to each other:

More information can be found in the ClusterControl User Guide. 

Example Organizational Structure

Let’s assume we have an organization with separate departments, each having their own applications and databases. An IT department is responsible for running all the databases, but the Marketing and HR departments also have their own developers who need access to their respective databases.

Importing already deployed databases

Once ClusterControl has been installed, it is possible to import running instances (or clusters) of MySQL, MariaDB or MongoDB. Use the Add Existing Cluster/Server feature.

The imported databases will be assigned to a default organization called ‘Admin’. We imported a MariaDB Cluster, a MongoDB Replica Set and a single instance MySQL server, and this is what the cluster list looks like:

Create New Database Node

It is also possible to deploy new MySQL/MariaDB/MongoDB databases using the Create Single Database Node wizard:

When deploying a MySQL or MariaDB node, ClusterControl deploys a single-instance Galera node.

Single-instances can be easily scaled into ‘proper’ clusters of at least 3 Galera nodes by using the Add Node functionality in ClusterControl.

Creating Custom Roles

Now, let’s create a custom role for our application developers. We do not want our developers to manage the cluster, but they need access to the query monitor and other monitoring data (read-access only). Roles can be created from ClusterControl > Admin > Access Control.

Managing Users and Organizations

We will organize our users in their respective departments, by going to ClusterControl > Admin > Organizations/Users > Organizations.

When selecting an organization in the left-hand pane, only the users of that organization will be listed in the right-hand pane.

Assigning Databases to Organizations

To assign a database instance (or cluster) to an organization, go to ClusterControl > Admin > Clusters > Change Organization.

More about this in the user guide.

From now on, our users can only see clusters that have been assigned to their organization. The following screenshot shows what [email protected] (Application Developer in the Human Resource department) would see when he logs into ClusterControl:

LDAP Integration

For larger organizations who have a centralized LDAP-compliant authentication system, users might want to use their corporate credentials instead of a separate password. LDAP groups can be mapped onto ClusterControl user groups to apply roles to the entire group.

Please refer to the User Guide or this blog post for more details.

Audit Logging – Who did what?

Audit trails might be required in an organization for compliance reasons, and ClusterControl maintains a record of actions performed by ClusterControl users. Any action performed by a user is logged into a Job log. It is now possible to see who performed the action, what that action actually entailed and from which IP the action originated.

There are lots more to explore. You’ll now be able to see all your databases through one app, schedule backups, receive alerts, understand performance through Performance Advisors, see all queries, upgrade to new versions, deploy new databases or scale existing ones with just one click. ClusterControl will also manage the availability of all your database servers and is able to fix broken nodes or clusters. In the age of cloud computing, automation is no longer a nice to have. Give it a try folks!