This user guide covers ClusterControl with MySQL-based clusters, namely:

  • Galera Cluster for MySQL 
  • Percona XtraDB Cluster 
  • MariaDB Galera Cluster
  • MySQL Cluster
  • MySQL Replication

 

For MongoDB user, please refer to ClusterControl for MongoDB user guide.

 

Agenda:

  1. Main Page - List of Database Clusters
  2. Overview
  3. Nodes
  4. Query Monitor
  5. Performance
  6. Backup
  7. Manage
  8. Alarms
  9. Logs
  10. Settings
  11. Administration

 

1. Main Page - List of Database Clusters

 

Once logged in, the user is presented with the list of database clusters. 

 

This section provides a quick overview of the current state and performance of your database clusters. Click on a database to drill down into your cluster.

The page includes the following functional areas:

Provides interactive step-by-step explanation of the functionality in the current page.

Provides a form to register your database cluster with the ClusterControl UI. For each cluster, you need to provide the ClusterControl API URL and token.

Provides tools to manage your Amazon Web Services (AWS) credentials. This only applies to AWS users.

Cluster, organization and user administration page.

Deploy a new database cluster using the embedded Configurator.

NOTE: You need an internet connection from your ClusterControl node.

Cluster types supported are:

  • MySQL Galera Cluster
  • Percona XtraDB Cluster
  • MariaDB Galera Cluster
  • MySQL Cluster
  • MySQL Replication
  • MongoDB Sharded Cluster
  • MongoDB Replica Set

This allows you to create an exact copy of your database cluster onto a new set of hosts. The source cluster is unaffected by the cloning procedure.

This button will only appear next to a cluster that has been cloned (either the source cluster or the actual clone). It allows you to move nodes between your cloned clusters.

Un-register a database cluster from the ClusterControl UI. This action will not uninstall the actual database cluster.

Quick overview of the current state and performance of your database clusters. It could be:

  • ACTIVE - The cluster is up and running. All cluster nodes are running normally.
  • DEGRADED - The full set of nodes in a cluster is not available. One or more node(s) is down or unreachable.
  • FAILURE - The cluster is down. It can be that all or most of the nodes are down or unreachable, resulting in the cluster not being able to operate.

The counters next to Performance and Jobs/Alarms indicate the number of alerts. Click on a database cluster to drill down into it.

 

1.1 Cluster Registration

 

This allows the user to register a database cluster with the ClusterControl UI. For each cluster, you need to provide the ClusterControl API token and URL. The page includes the following functional areas:

Enter the 40-character ClusterControl API token here. This token is generated as part of the deployment process, it is displayed on the last page of the Configurator.

Enter the ClusterControl API URL with no trailing slash at the end. It should be: https://<ClusterControl_IP_address>/cmonapi

Registers the cluster with the ClusterControl UI.

 

1.2 Service Providers

 

This feature is specifically built for users of Amazon Web Services (AWS). It allows ClusterControl to spin EC2 instances and deploy a database cluster on these. It also allows some simpler management of EC2 instances, such as listing or terminating instances. The page includes the following functional areas:

Manage your AWS credentials under this tab.

Create new keypair or delete a keypair.

Keypair Name: You must use the same keypair's name in your AWS console.

Access Key ID: Your AWS Access Key ID as described on this page.

Secret Access Key: Your AWS Secret Access Key as described on this page.

Private Key File: Upload the key file.

Comment: Description of the keypair (optional).

Manage your AWS instances under this tab.

Choose which keypair to use to access your AWS resources.

List of EC2 instances.

Stop - Shutdown the instance.

Reboot - Restart the instance.

Terminate - Shutdown and terminate the instance.

 

1.3 Clone

 

This feature allows you to create, in one click, an exact copy of your Galera Cluster onto a new set of hosts. The most common use case for cloning a deployment is for setting up a staging deployment for further development and test. Cloning is a ‘hot’ procedure and does not affect the operations of the source cluster. You may refer to this blog post for detailed explanation on how to clone a cluster in ClusterControl.

 

The page includes the following functional areas:

Source cluster name.

Cloned cluster name.

Choose one of the existing Galera nodes in the source cluster as target node. In this case, note that when you detach the clone, you will be effectively moving the node from the source cluster to the cloned cluster.

The number of nodes on the cloned cluster.

Tick to immediately detach the cloned cluster from the source cluster. Once detached, the clone is totally separated from the source cluster.

Enter the IP addresses of the remaining DB nodes that will be part of the cloned cluster.

Create Clone - Start the cloning process.

Close - Close this window.

Once the clone operation is complete, you will see the new cluster is listed on the main page:

 

NOTE: ClusterControl will generate the required files for the cloned instance:

  • /etc/cmon_CLUSTERID.cnf
  • /etc/init.d/cmon_CLUSTERID
  • /var/log/cmon_CLUSTERID.log
  • /var/run/cmon_CLUSTERID.pid

 

1.4 Move Node

When cloning clusters, the cloning process will create a new ClusterControl process for the cloned cluster. This process will reside on the Source Cluster’s ClusterControl host. So, a Source Cluster and all clones will share the same ClusterControl host. 

For clusters sharing the same controller host, it is possible to move nodes between the clusters. The page includes the following functional areas:

Choose the node that need to move from the list.

Choose the target cluster from the list.

Move Node - Start the move node operation.

Close - Close this window.

 

 

2. Overview

 

The Overview page provides an aggregated view of the state and load on your database cluster. By default, it shows:

  • Cluster Load
  • Queries/s and the number of connections
  • Galera Nodes (MySQL Galera Cluster/Percona XtraDB Cluster/MariaDB Galera Cluster)
  • Specific variables specific to Galera or NDB
  • Hosts status
  • SQL Nodes (MySQL Cluster)
  • Data Nodes (MySQL Cluster)
  • Management Nodes (MySQL Cluster)

 

All graphs and tables share the same characteristics:

Time range of recorded data to be displayed.

Select the columns to be shown in the table, as well as the sort order. 

Refresh the graph. It is possible to enable or disable auto-refresh.

 

Rollover on a specific point in a graph to get the actual value.

To zoom into a specific part of a graph, just highlight the region.

Reset the zoom factor to 1:1.

Page navigation tools.

 

 

2.1 Dashboard

 

You can customize your dashboard in the Overview page by selecting the graphs to display.

Open the dashboard settings window.

Create a new dashboard or remove the selected dashboard.

Dashboard Name: Name of the dashboard.

Metric: Select an available metric from the list.

Graph Scale: Choose between linear or logarithmic.

Selected as default graph: Choose Yes if you want to set the graph as default when viewing the Overview page.

Save - Save the dashboard.

Cancel - Close the dashboard settings window.

List of created dashboard. Double click to the item to edit.

Open the Load Balancer management window to add or remove a load balancer to your cluster. For more information, please refer to the Manage Load Balancer section of this documentation.

 

 

3. Nodes

 

Nodes page provides detailed information of all nodes in the cluster. On the left hand column, you can find all nodes belonging to your cluster. If you installed HAProxy, Memcached or Garbd (Galera Arbitrator) using our deploy scripts, these will also be listed.

 

The page includes the following functional areas:

Scale out your cluster by adding more database nodes. The new node will automatically join and synchronize itself with the rest of the cluster.

When adding a new node to the cluster, a template my.cnf file will be rolled out on the node. The template my.cnf file can be found at Manage -> Configurations. Users can EDIT the template before adding new nodes.

Shutdown Node: Stop the database instance in this host. This is not shutting down the host.

Restart Agent: Restart ClusterControl agent service in this host.

Reboot Host: Initiate a system reboot in this host.

The remove icon will only appear when you rollover the node on the side-menu. This removes the database node from cluster.

 

Database node status indicator:

OK: Indicates the node is working fine.

WARNING: Indicates the node is degraded and not performing as expected.

PROBLEMATIC: Indicates the node is unreachable.

 

These tabs show performance and/or resource usage for a specific node. There are also database specific tabs depending on the type of database running on the node:

  • CPU, disk, network and memory usage.
  • Running processes (top)
  • IO stats (disk)
  • Logs (database logs, like the MySQL error.log)
  • Database specific performance and variables, for example MySQL performance graphs and status variables.

 

4. Query Monitor

 

4.1 Top Queries

 

This is an aggregated list of all your top queries running on all the nodes of your database cluster. The list can be ordered by Occurence or Execution Time, depending if you want to know about the most common or slowest queries respectively. It is also possible to filter and review queries from one particular node.

You can see the explain output of your queries by selecting a query in the list. You can also email the query/explain output if you have setup email notifications in the General Settings page. Review the Query Monitor Settings to configure what queries to log (e.g. only log queries that take more than 0.5 seconds to execute).

 

4.2 Running Queries

 

View current running queries on your database cluster similar to SHOW PROCESSLIST command in MySQL. You can stop a running query by selecting to kill the connection that started the query.

 

4.3 Query Histogram

 

Use this feature to filter the query activity for a certain time period.

Filter the output by database server.

Specify the time frame.

Email the query/explain output if you have setup email notifications in the General Settings page.

 

 

4.4 Workload Simulator

 

This allows you to benchmark individual SQL requests on your MySQL nodes. The simple use case is to specify the SQL query you want to benchmark, the number of threads (database connections), and how long it should be executed. It can be used to quickly evaluate the performance of your SQL, and iterate the optimization of your queries.

 

 

The page includes the following functional areas:

Opens the workload settings page.

Configure database and workload details:

  • Database connections: Database connections: number of connections to be made on the DB node.

  • Runtime (secs): The time when the query is actually running.

  • Interval between executions: Number of seconds to wait before running the query again.

Queries are specified in Probes. Add up to 5 probes and individually enable/disable them.


 
 

View the benchmark results at the end of the page. You can view graphs for TPS (transactions/sec) and Latency.

 

 

5. Performance

 

5.1 Overview

 

You can view graphs of different database counters on this page. You can record up to 24 different MySQL counters. 

 

The page includes the following functional areas:

Choose which counters to record.

Filter the status variables available in the counter list.

Choose the status variables that you want to track. Check the 'On' box to the left of the counter that you want to record. For detailed explanation on status variables of your cluster, you can refer to following pages:

Choose the enabled counter you want to graph.

 

5.2 Health Report

 

ClusterControl uses a number of best practice advisors to create a Health Report for your cluster. These advisors automatically examine your node configurations and performance levels, and identify any deviations from best practice rules. This tab provides a quick overview of the overall health of your database nodes. Below is a description of the different categories of advisors:

Note that a new tab will appear for each category of Advisors, in case there are alarms in that Category. In the example here, we have 3 alerts in the Memory Usage category, these show up under a separate tab.


Area

Rules

Query optimization

Full Table Scans, Select Full Join, Select Range Check, Sort Passes

InnoDB

Buffer Pool Hit Ratio, Wait for Checkpoint, Wait for REDO log

Connections

Current Connections, Maximum Connections Seen

Database Memory usage

Current Memory Usage, Max Memory Usage Seen

Open files

Open Files Ratio

Cache usage

Table Cache Hit Ratio, Table Cache Usage, Thread Cache Hit Ratio

Locks

Table Lock Contention

Temporary tables usage

Temporary Disk Table Ratio


 

 

5.3 DB Status

 

DB Status provides a quick overview of the status variable across all your database nodes. You can use the “Search Stat” text field to filter the values as example below:

 

 

5.4 DB Variables

 

DB Variables provide a quick overview of the variables that are set across all your database nodes. You can use the “Search Stat” text field to filter the values as example below:

Note: RED text means that the variable setting is different. In some cases that is acceptable (e.g., IP address of the node).

 

 

5.5 DB Growth

 

DB Growth provides a summary of your database and table growth for the last 30 days. Click on a database listed for further details on growth summary per table.

 

 

6. Backup

 

6.1 Overview

 

This allows you to schedule backups for your database cluster. The page includes the following functional areas:

Open the backup wizard to start a backup immediately.

Choose the database node that you want to perform a backup on, as well as the backup method.

If you are using xtrabackup, you can chose to store the backup on the running node or ClusterControl node. 

Backups using mysqldump are stored on the ClusterControl node.


 

Schedule a backup on specific day(s).

Backup start time.

NOTE: The time is UTC on the ClusterControl server.

Enter a backup directory or use default path provided by ClusterControl.

Backup tool:

  • full backup with mysqldump
  • full backup with xtrabackup
  • incremental backup with xtrabackup
  • NDB backup (for MySQL Cluster)

Host to run the backups if using xtrabackup and Galera Cluster.

Schedule a backup based on the selected date, time and backup method.

List of your current backup schedule.

 

6.2 Reports

 

Backup Status Report provides a list of finished backup jobs. The status can be either ‘completed’ or ‘failed’.

 

 

7. Manage

 

7.1 Hosts

 

Manage all the provisioned hosts available under this cluster. The page includes the following functional areas:

List of hosts provisioned by ClusterControl.

New host to be provisioned by ClusterControl. You must make sure that ClusterControl node is able to SSH into the new node without password. An agent will be installed on the new host, and host statistics (CPU/IO/RAM/Network) will be available.

When adding a new node to the cluster, a template my.cnf file will be rolled out on the node. The template my.cnf file can be found at Manage -> Configurations. Users can EDIT the template before adding new nodes.

Remove the selected host from the provisioned host list. This will also remove the agent from that host.

 

 

7.2 Configurations

 

This features allows you to manage the configuration of your database nodes. From here you can edit and/or detect whether your database configuration files are in sync and do not diverge. Any changes will not take effect until the database server/processes is restarted.

When adding a new node to the cluster, a template my.cnf file will be rolled out on the node. The template my.cnf file can be found here. Users can EDIT the template before adding new nodes. The page includes the following functional areas:

Provides configuration information on the last generated version, deployed version and sync status.

Edit and view your database configuration files. Any changes will not take effect until the database server/processes is restarted.

This button will only appear under “Action” when ClusterControl detects configuration changes. Click to restart the database service for selected node.

Re-import configuration if you have:

  • Performed local configuration changes directly on the configuration files. 
  • Restarted the mysql servers/performed a rolling restart after a configuration change.

 

7.3 Load Balancer

 

You can manage and setting up load balancers (HAproxy), virtual IP (Keepalived) and Galera arbitrator daemon (Garbd) node through this interface. Once executed, it will trigger a job under Jobs so you could monitor on the status of the execution.

Open the HAproxy installation tab.

  1. Select MySQL servers in your cluster that will be included in the load balancing set.

  2. Select the host on which to add a load balancer. If the host is not provisioned in ClusterControl (see Manage -> Hosts), type in the IP address.  The required files will be installed on the new host. Note that ClusterControl will access the new host using passwordless SSH.

  3. Name the load balancer.

  4. Tick “Build from source” if you want to compile HAproxy from source. This option is only required if you intend to use the latest version of HAproxy or if you are having problem with the package manager of your OS distribution. Some older OS versions do not have HAproxy in their package repositories.

  5. Click the button to start the installation process.


Once the installation is complete, MySQL will listen on port 33306. You can get further details on HAproxy by reviewing this tutorial.

Remove the selected HAproxy or Garbd node.

Open the Keepalived installation page.

Keepalived requires 2 HAproxy nodes in order to provide virtual IP failover. 

  1. Select installed HAproxy nodes.

  2. Assign a virtual IP address. The IP address should not exist in any node in the cluster.

  3. Click the button to start installation of Keepalived.


By default, this IP will be assigned to HAProxy1. If HAProxy1 is down, the IP will be automatically assigned to HAProxy2.

Open the Garbd installation page.

Galera arbitrator daemon (garbd) can be installed to avoid split-brain scenarios by acting as an odd node. 

  1. Select a host from the list. That host cannot be an existing Galera node.

  2. Click the button to start installation of garbd.

 

7.4 Custom Scripts

 

Execute custom scripts in ClusterControl controller node. The script or program name must be owned by root, executable and must be existed under /usr/bin directory. Once executed, it will trigger a job under Jobs so you can monitor status.

Specify the program name or full path to the program name.

Specify the arguments.

Click to start execute the specified script.

 


 

7.5 Processes

 

You can configure ClusterControl to monitor external processes that are not part of the cluster, e.g. a web server or an application server. ClusterControl will actively monitor these processes and make sure that they are always up and running by executing the check expression command.

The example below shows the Apache process being monitored by ClusterControl:

 

The page includes the following functional areas:

Open the Add Custom Managed Process window.

Host/Group: Select the provisioned host.

Process Name: Enter the process name.

Start Command: OS command to start the process.

Pidfile: Full path to the PID file.

GREP Expression: OS command to check the existence of the process.

Remove the managed process from the list of processes managed by ClusterControl.

Disable the managed process.

 

7.6 Schema and Users

 

ClusterControl provides a simple interface to manage database schemas. It is possible to create databases, upload dump files, manage users and privileges. All of the changes are automatically synched to all database nodes in the cluster.

Upload the schema and the data files. Currently only mysqldump is supported and must not contain sub-directories. 

The following formats are supported:

  • dumpfile.sql
  • dumpfile.sql.gz
  • dumpfile.sql.zip

Upload - Start the uploading process.

Reset - Reset the file name specified.

List of uploaded dump files.

Install - Install the selected dump file into the database.

Remove - Remove the selected file from the ClusterControl repository.

Enter the name of the database to be created.

Click to create a database.

Opens the Create User wizard.

User: MySQL username.

Host: Host that the user can connect from. You can specify a wildcard, hostname or IP address.

Password: The password for this user and host.

Opens the Assign Privileges To User wizard.

Select privileges for a user.

Specify which database to apply the privileges to.

Specify the host from which the user will connect.

Save - Save the privilege settings.

Close - Close this window.

 

7.7 Software Packages

 

This allows users to manage packages, upload new versions to ClusterControl’s repository, and select which package to use for deployments. In order to use this feature, set post_max_size and upload_max_filesize in php.ini to 256M or more. Make sure you restart Apache to apply the PHP changes. Location of php.ini might vary depending on your operating system, infrastructure type and PHP settings.

The page includes the following functional areas:

Assign a name for the new package.

Create the new package.

Upload files to an existing package.

List of softwares and packages. The package 'Selected for Deployment' will be rolled out to new nodes, and used for upgrades. 

Check Delete and click Save, to delete the selected package from ClusterControl server.

 

7.8 Upgrades

 

Performs software upgrade based on Software Packages. ClusterControl will use the package you specified to perform upgrade on all active database nodes. The page includes the following functional areas:

Select package to perform the upgrade.

Installs the selected package on the active nodes.

Performs a rolling node restart. This stops each node one at a time, waits for it to restart with the new version, before moving to the next node. The cluster is upgraded while it is online and available.

If an online upgrade using rolling restart is not supported, e.g., if it is a major version upgrade with incompatible changes, you will need to perform an offline stop/start. This will stop the cluster and then let ClusterControl automatically recover the cluster. 

 

 

8. Alarms

 

8.1 Alarm

 

Alarms are categorised under database, process and host alarms. An alarm provides a detailed explanation on the problem, and recommended action (if available) to resolve the problem. You can dismiss an alarm and it will not show up again.

 

9. Logs

 

9.1 Jobs

 

Any action performed by the user via the ClusterControl UI will submit a job to a job queue. ClusterControl then schedules each job in the order they are added to the queue. When a job is executed, detailed workflow messages related to the execution of the job are logged. This is useful for troubleshooting, especially if a job gets executed but fails to complete. 

 

ClusterControl job status indicator:

The jobs is successfully executed. It should return "Command ok" result with "Exit Code: 0".

The jobs is currently running or facing error. If error, it should return “The job failed” with "Exit Code: 1".

 

9.2 CMON Log

 

This is a centralised list of log events from the ClusterControl server and agents. You can filter the output by hostname and log level (ALL, DEBUG, INFO, WARNING, ERROR, CRITICAL, ALERT).

 

 

9.3 User Event Log

 

This describes important events/actions that were initiated by a user during the lifetime of the database cluster. An Event belongs to a Category. This is helpful in understanding, for example, who and when performed a version upgrade, configuration change or schema tuning that affected the performance of the cluster.

Example below shows database administrator’s activities reported in User Event Log:

 

10. Settings

 

10.1 General Settings

 

Provides global settings for ClusterControl. The page includes the following functional areas:

Name your cluster! This name will appear in the database cluster list and database cluster summary.

Configure how email notification should be sent. ClusterControl supports 2 options of sending out email notification, either using local mail command via sendmail or using an SMTP server.

Use this option to enable sendmail to send notifications. Instructions on how to set it up can be found here.

SMTP mail server that you are going to use to send email.

SMTP port for mail server. Usually this value is 25 or 587, depending on your SMTP mail server configuration.

SMTP username. Leave empty if no authentication required.

SMTP password. Leave empty if no authentication required.

Specify the sender of the email. This will appear in the From field of mail header.

Test the mail settings. If successful, an email will be sent to all users in the Email Notification page. Do not forget to add a recipient before pressing this button.

You can get email notifications for alarms generated for your database cluster. Add your alarm recipients in this page.

Add recipient first name, last name and email address.

Remove selected recipient.

View the database server and ClusterControl version installed.

 

10.2 Subscription

 

For users with a valid Enterprise subscription, enter your license information here to enable additional enterprise features. The license key is validated at runtime.

Register the subscription license. Reload your web browser after registering the license.

 

10.3 Thresholds

 

Provides thresholds for warnings and criticals event. Thresholds specify the threshold level at which an alarm will be triggered and notification will be sent via email to the list of recipients configured in the Email Notifications page. Set your alarm thresholds:

  • CPU, RAM, DISK usage
  • MySQL Server Memory usage
  • Data, Index, Tablespace, REDO Log and Buffer usage (NDB)

Set your warning threshold in percentage for specific event.

Set your critical threshold in percentage for specific event.

 

10.4 Graphs

 

Manage graph settings and data capturing. From here, it is possible to record up to 24 different MySQL counters. You can view up to 8 counters on this page. The page includes the following functional areas:

Choose counters you want to record and graph.

Filter the status variables available in the counter list.

Choose the status variables that you want to track. Check the 'On' box to the left of the counter that you want to record. For detailed explanation on status variables of your cluster, you can refer to following pages:

Choose the enabled counter to be graphed.

 

 

10.5 Query Monitor

 

Manage the query monitoring settings at Query Monitor page. The page includes the following functional areas:

How many days you want to keep historic data. Please make sure you have enough disk space on the ClusterControl server.

How many minutes between automatic error.log retrieval.

How many seconds between Query Sampling. Queries are sampled in 30 sec chunks. This time sets how long the interval should be between each chunk.-1 means disabled (if you want to manage the query monitoring yourself), 0 all the time. SQL performance is affected if sampling often. You can also set the Long Query Time to catch only queries taking longer than a certain time.

Yes - means that the local my.cnf settings for long_query_time and log_queries_not_using_indexes will be used.

No - settings below for Long Query Time and Log queries not using indexes will be used across all MySQL Servers.

Only collect queries taking longer than Long Query Time seconds.

0 - means all queries.

0.1 - means only queries taking more than 0.1 seconds will be accounted.

Yes - log queries not using indexes.

No - ignore queries not using indexes (will not be accounted for in Query Monitoring/Histogram).

 

10.6 Backup

 

Manage the backup directory and retention period. The page includes the following functional areas:

Default backup directory. This directory is relative to the ClusterControl server.

Backup retention period in days. Backups older than the retention period (days) will be deleted. Set to 0 for no retention.

 

 

11. Administration

 

From any page you can access Admin page to manage clusters, organizations and users for ClusterControl. Users created here will be able to login and see specific cluster depending on their organization and cluster that has been assigned to.

 

11.1 Clusters

 

Manage database clusters inside ClusterControl. The page includes the following functional areas:

Same functionality with Create Database Cluster as mentioned above.

Un-register selected database cluster from the ClusterControl UI. This action will not delete the actual database cluster.

Change organization for selected database cluster from the organization list created in Organizations/Users tab.

 

11.2 Organizations/Users

 

Manage organizations and users inside ClusterControl. The page includes the following functional areas:

Create a new organization/user.

Delete selected organization/user.

Filter all fields in organization/user.

 

11.3 Roles

 

There are 3 types of roles for users:

Super Admin

Able to see all clusters that are registered in the UI. 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 is able to see all clusters registered in that organization.

User

Belongs to a specific organization, and is only able to see the cluster(s) that he/she registered.