22.2 Connecting to a MySQL Database with an ODBC Driver

This documentation shows how to set up and configure Moab to connect to a MySQL database using an ODBC driver. This document assumes the necessary MySQL and ODBC drivers have already been installed and configured.

To set up and configure Moab to connect to a MySQL database using the MySQL ODBC driver, do the following:

This solution has been tested and works with these file versions: libmyodbc - 5.1.6 unixodbc - 2.2.14 For a Debian-based system, unixodbc-dev is required, but it may not be for Red Hat flavors (such as CentOS andRHEL).

1. Download and install the ODBC version of Moab. Install and configure Moab as normal but add the following in the Moab configuration file (moab.cfg):

   USEDATABASE             ODBC
   
   # Turn on stat profiling
   USERCFG[DEFAULT]        ENABLEPROFILING=TRUE
   GROUPCFG[DEFAULT]       ENABLEPROFILING=TRUE
   QOSCFG[DEFAULT]         ENABLEPROFILING=TRUE
   CLASSCFG[DEFAULT]       ENABLEPROFILING=TRUE
   ACCOUNTCFG[DEFAULT]     ENABLEPROFILING=TRUE
   NODECFG[DEFAULT]        ENABLEPROFILING=TRUE

2. Create the database in MySQL using the MySQL database dump contained in the moab-db.sql file. This file is located in the contrib/sql directory in the root of the binaries.

   • Run the following command:

     mysql -u root -p < moab-db.sql

3. Configure the MySQL and ODBC driver. The /etc/odbcinst.ini file should contain content similar to what follows:

   [MySQL]
   Description = ODBC for MySQL
   Driver = /usr/lib/odbc/libmyodbc.so

4. Configure Moab to use the MySQL ODBC driver. Moab uses an ODBC datastore file to connect to MySQL using ODBC. This file must be located in the Moab home directory (/opt/moab by default) and be named dsninfo.dsn, which is used by Moab. If the following content, which follows the standard ODBC driver file syntax, is not already included in the /etc/odbc.ini file, make sure that you include it. Also, include the same content in the dsninfo.dsn file.

   [ODBC]
   Driver = MySQL
   USER = <username>
   PASSWORD = <password>
   Server = localhost
   Database = Moab
   Port = 3306

   The user should have read/write privileges on the Moab database.

The preceding example file tells ODBC to use the MySQL driver, username user, mypassword password, and to connect to MySQL running on the localhost on port 3306. ODBC uses this information and connects to the database called Moab.

5. Test the ODBC to MySQL connection by running the isql command, which reads the /etc/odbc.ini file:

   $ isql -v ODBC
   +---------------------------------------+
   | Connected! |
   | |
   | sql-statement |
   | help [tablename] |
   | quit |
   | |
   +---------------------------------------+
   SQL> show tables;
   +-----------------------------------------------------------------+
   | Tables_in_Moab |
   +-----------------------------------------------------------------+
   | EventType |
   | Events |
   | GeneralStats |
   | GenericMetrics |
   | Moab |
   | NodeStats |
   | NodeStatsGenericResources |
   | ObjectType |
   | mcheckpoint |
   +-----------------------------------------------------------------+
   SQLRowCount returns 10
   10 rows fetched
   SQL>
   

   If you encounter any errors using the isql command, then there were problems setting up the ODBC to MySQL connection.

6. With the ODBC driver configured, the database created, and Moab configured to use the database, start Moab for it to begin storing information in the created database.

> moabd is a safe and recommended method of starting Moab if things are not installed in their default locations.

Importing Statistics from stats/DAY.* to the Moab Database

The contrib/stat_converter folder contains the files to build mstat_converter, an executable that reads file-based statistics in a Moab stats directory and dumps them into a database. It also reads the Moab checkpoint file (.moab.ck) and dumps that to the database as well. It uses the $MOABHOMEDIR/etc/moab.cfg file to connect to the appropriate database and reads the statistics files from $MOABHOMEDIR/stats.

To run, execute the program mstat_converter with no arguments.

The statistics converter program does not clear the database before converting. However, if there are statistics in the database and the statistics files from the same period, the converter overwrites the database information with the information from the statistics files.