The SkySQL™
Administration Console
Version 1.1
Draft Documentation
Overview
The SkySQL Administration Console is a web based application for the administration and monitoring of MySQL1 databases. It provides the ability to monitor key metrics of the database and to perform maintenance operations on those databases from the Administration Console web interface and also to integrate third party tools into the Administration Console framework.
The Administration Console comprises a number of elements; the user connects to an application front end, which in turn connects to an API module, the job of which is to contain the interface to the monitoring database.
The monitoring and control database is updated by both the action of the API layer and also the external monitor application which probes the monitored MySQL servers. Administration tasks are initiated directly from the console API via a set of control step scripts.
Installing The SkySQL Administration Console
The SkySQL Administration Console is available either as part of the SkySQL Cloud Data Suite or as a separate download form the SkySQL website. When a system is provisioned via the Cloud Data Suite provisioning application the console is automatically included in that system and configured to manage and monitor that system. The installation of the console using the download is a more complex operation, the software must be installed and then manually configured, using scripts provided in the distribution, to allow it to monitor and manage your system.
Installing The RPM Package
The installation of the RPM package is a very simple process, the package should simply be downloaded from the SkySQL website and stored on your local machine. Execute the yum package installer to install the package and the packages it depends upon
# yum install skysql-console-1.1-1.x86_64.rpm
This will install the basic software packages but will not configure or start those packages, this most be done by manually installing a post installation script that will prompt for the necessary configuration information.
# /usr/local/skysql/scripts/postinstall
The post install script will first create a random password that will be used to connect to the administration console
Setting administration console login to admin with a password of 1QlZpEwAVR
Setting the MONyog password to 1QlZpEwAVR
In order for the administration console to be used there must be at least one server for it to monitor. The information required is stored in a SQLite database, however a script is available to ease the population of the SQLite configuration database.
Now create the first system to monitor
Please enter a SystemID for the new system you are creating: 12345 Please enter a name for your new system: test1
Is the new system you are creating a replication cluster (Y/n): n Enter details for your server.
Enter a name for this node: localhost
Enter a hostname for our server [localhost]:
Please enter the private IP address for this node [127.0.0.1]: Please enter the public IP address for this node [127.0.0.1]: Please enter a username on this database that can be used to gather monitoring data: monitor
Please enter the password for this account: monitor Add another system to monitor [y/N]:
Once the system has been created the post installation script will setup the services
required to start the administration console to start on boot of the server. The services will then be started in order to allow the console to be used without the need to reboot the server.
Starting the services... Starting Services
Starting httpd: [ OK ] Using CATALINA_BASE: /usr/share/apache-tomcat-7.0.32
Using CLASSPATH: /usr/share/apache-tomcat-7.0.32/bin/bootstrap.jar:/ usr/share/apache-tomcat-7.0.32/bin/tomcat-juli.jar
Starting MONyog:
MONyog started! [ OK ] MONyog started successfully!
Starting SkySQL ClusterMonitor:
[ OK ]
Finally a message will be printed that will describe how to connect to the administration console.
To connect to the SkySQL Administration console point your browser to
http://domU-12-31-39-0E-B4-A4:8080/ConsoleV
You may add further nodes at any time by running the script /usr/local/skysql/bin/CreateSystem.sh
Using The Console
In operation the console is designed to give an intuitive interface, it is divided into two main section, the upper section gives an overview of the nodes, or database servers that are running, whilst the lower panel contains a set of tabs.
The contents of the tab varies depending upon the item that is selected in the upper panel, the icon to the extreme left represents the set of servers as an entire system and will show information or allow control of the system as a whole, the cylindrical icons to the left of this represents the individual servers being administered.
A server is selected by clicking on the icon for the server, a background yellow highlight will be added to indicate that the server has been selected. Once selected the set of tabs in the lower section will be altered to reflect the node that has been selected.
The Info Tab
It is possible to add other graphs to the info tab by modifying the control database for the administration console. The information is collected by a small Java application that runs continually collecting data from the monitored server, this monitor is automatically installed and executed when the administration console is installed.
The Control Tab
To run a command, the command is first selected from the list of commands to run, this will display the steps involved in running the command but not yet run that command. To
execute the command you simply click on the execute icon to the right of the command list. While a command is executing it will show feedback by means of a marker on a command timeline.
The Backup Tab
The backup tab is very similar to the Control tab, except that it offers backup and restore commands only and provides a mechanism for browsing past backup and logs of those backups.
The Tools Tab
Customization
It is possible to customize the behavior of the Administration Console in a number of ways, from the look and feel of the interface to the actual administration and monitoring functions available.
Customizing Administration Tasks
The administration tasks, which are accessed from the Control and Backup tabs on the interface are actually implemented as a step of steps, the steps themselves and the
actions for each step are defined in the SQLite database. The actions are implemented by shell scripts that may be modified to fit a particular installation.
The main table in the SQLite database that drives the entries in the Control and Backup tabs is the Commands table.
TABLE Commands (
CommandID smallint primary key,
Name varchar(40), /* Short name of the command */ Description varchar(255), /* Textual description */
Icon varchar(200), /* Name of icon */
UIOrder smallint, /* Display order in UI */ UIGroup varchar(40) /* Display group in UI */ );
As an example, the “stop” command is defined with the following row
CommandID Name Description Icon UIOrder UIGroup
2 Stop Stop node stop 2 control
This defines the Stop command to appear on the Control tab, as the second command in the list of commands. The Icon column gives the name of an icon file that can be found in ConsoleV Apache Web-App tree.
The steps that are used to build the commands are defined in the Step table.
TABLE Step (
StepID smallint primary key,
Script varchar(255), /* Script to execute for this step */ Icon varchar(200), /* Icon to represent the step */ Description varchar(255) /* Textual description */
);
This table defines the name of the script to execute, in the directory /var/www/html/
consoleAPI/shell/steps, an icon to display on the screen for the execution of the step and a description of the step.
The CommandStep table links the steps in a command together, giving an order in which to execute the steps.
TABLE CommandStep (
CommandID smallint, /* Command ID */
StepID smallint, /* ID of the step to execute */ StepOrder smallint /* Order to execute the steps in */ );
Adding Monitors & Graphs
The Administration Console is capable of displaying graphs of any data that is collected by the external monitoring tool, this tool is driven by a table in the SQLite control database. The Monitors table allows for the different monitors to be created
TABLE Monitors (
MonitorID smallint primary key,
Name varchar(80), /* Displayed name of this monitor */ SQL varchar(200), /* SQL to run on MySQL to get the
* current value of the monitor. */
Description varchar(255), /* tooltip description of monitor */ Icon varchar(200), /* icon associated to monitor */ ChartType varchar(40), /* type of chart to be used for
* rendering */
UIOrder smallint, /* Display order in UI */
delta boolean, /* This monitor reports delta values */
MonitorType varchar(20), /* Type of the monitor */
SystemAverage boolean /* Value averaged over nodes */ );
The MonitorType column determines how the data will be collected, there are currently four types of monitor that can be created; a ping monitor, a SQL monitor, a shell command monitor and a CRM monitor.
The ping monitor performs a simple ICMP ping and returns either an up or down status for the node.
The SQL monitor will execute an SQL statement on the remote server, the value returned in that query is then taken as the value of the probe and plotted on the appropriate graph. This is normally used to extract data from the
information schema of the monitored database. The text of the SQL statement is taken form the SQL column in this row.
A shell monitor will execute a command on the host machine of the node being monitored. The data returned by that command is then taken as the value of the probe and plotted on the appropriate graph. The name of the shell script to execute is read from the SQL column in this row.
A CRM monitor is a specialized monitor used to check the status of nodes within a cluster. It does not result in any graph being plotted by will simply update the status of the node within the system. This is is done by execution of the crm status bynode command on the current machine, this machine must be part of the same CRM cluster that is being monitored.
New monitors, and graphs, can be added to the info tab simply by adding rows to the Monitors table. The UIOrder column will determine the order in which graphs are displayed in the tab, leaving this column null will hide a particular graph from display but still enable collection of the monitoring data.
Setting the SystemAverage column to true will cause the value reported by the System icon to be an average of all the nodes in the system, otherwise the value reported is the sum of all the nodes in the system.
Adding Custom Controls
The section above detailed how the controls are implemented within the control database and described the mechanism to tailor those controls to a particular installation.It is also possible to add new commands using either a combination of steps already available, or by adding new steps that can be utilized within a command, possibly including some steps that already exists.
Creating a step
To create a step you require
An Icon to represent the step in the timeline display.
A shell script that executes the relevant commands required to implement the action the step should perform.
A row in the Steps table that allocates the step a unique id and associates the script, icon and name of the step.
Creating a command
In order to create a new command you require
An Icon for the command for display purposes. A name to be displayed in the command list.
A row in the Commands table to allocate a unique command id to the command.
A set of rows in the CommandStep table to link the steps required to execute the command and to give the order of execution of the steps.
Command permissions
The system only presents commands for execution that are valid for the current state of the node that is selected. In order to control this a ValidCommands table exists that contains a row for each node state and command id combination that is valid.
TABLE ValidCommands (
State smallint, /* Current state */ CommandID smallint /* Command */
);
