salesforce Dreamforce On-Demand
Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Management Console (3.7)
  3. Setting Up MMC
  4. Persisting MMC Data On External Databases

  5. Persisting MMC Data to MySQL

Persisting MMC Data to MySQL

MMC Version 3.4.2 and Later

This document will cover how to persist environment and transaction data to MySQL databases. Follow the instructions to MySQL Installation, then follow the specific instructions for Persisting Environment Data or Persisting Transaction Data persistence to set up your user, create your database, and configure your MMC instance to persist your data.

Remember that environment data and transaction data must exist in two separate databases (regardless of whether they are on the same server or use the same platform).

Jump to:

Assumptions

This document assumes that you have an understanding of the data persistence options for environment and transaction data and that you are familiar with the basic Architecture of the Mule Management Console. It also assumes that you have installed:

  • MMC Version 3.5.0. Download it from the customer portal if you need to upgrade to this version.

  • a Web application server such as Tcat, Tomcat, or JBoss. Please refer to the Hardware and Software Requirements for the latest information about hardware requirements and supported application servers, Java Runtime Environments, and browsers.

For practical purposes, this document mentions Tomcat as the servlet container; however, the instructions contained here apply also to Tcat or JBoss. If you are using Tcat or JBoss, make sure to unpack the Management Console .war file in the appropriate directory. For additional information, see Installing the Production Version of MMC.

In this document, <MMC_HOME> refers to the directory where MMC is installed.

If you are using the trial version, this directory is inside the root of the embedded Tomcat server from which MMC runs. Depending on where you placed the bundled installation, the path will be similar to <installation_root>/mmc-distribution-mule-console-bundle-3.5.0/mmc-3.5.0/apache-tomcat-7.0.52/webapps/mmc-3.5.0.

If you are using a production installation in an application server such as Tomcat, the path should be similar to <Tomcat root>/webapps/mmc-console-3.50. For example /var/lib/tomcat6/webapps/mmc-console-3.5.0.

MySQL Installation

The MySQL installation outlined here is basic: the database is installed on the local machine on the default port of 3306, with security deactivated (using an unsecured connection to the database, and a user with full admin rights).

Tools

  • MySQL Community Server (Download)

  • mysql command-line utility

  • phpMyAdmin, a Web-based database administration tool (optional) (Download)

This configuration was tested with MySQL Community Server 5.5.

Creating a database and database user in MySQL is relatively simple. You can perform these tasks either with the mysql command-line utility, or with the phpMyAdmin Web-based administration tool. This document does not describe how to set up or install MySQL, or how to create the tables with phpMyAdmin; however, it does list the commands to create the database, user, and tables for the environment data persistence example below. See the expandable section in Creating the Tables for details.

Persisting Environment Data

To set up MySQL to persist your MMC environment data, you need to complete these steps:

  1. Create the database.

  2. Create the database user.

  3. Create the tables.

  4. Set up MMC.

  5. If your MySQL is on a remote host, perform some additional configuration.

The embedded database files for the Mule console reside at one of the following directories:

  • if on a Web servlet container such as Tomcat:` <MMC_HOME>/mmc-data/db`

  • if on a trial version: <Mule install path>/.mule/mmc/mmc-data/db

After you configure MMC to use an external database to store its persistent data, do not delete the mmc-data folder. The db subfolder will not be used anymore, but the repository, version and workspaces subfolders will be in use by Java Content Repository (JCR) internal settings.

Creating the Database

Create a new database with the following parameters:

  • Database name: mmc_persistency_status

  • Database owner: MMC_STATUS

Creating the Database User

Create a new database user with default permissions and the following parameters:

  • Username: MMC_STATUS

  • Password: mmc123

Creating the Tables

Navigate to the following directory: <MMC_HOME>/WEB-INF/classes/quartz. In this directory you will find two scripts for MySQL:

  • tables_mysql.sql for MySQL server versions newer than 4.1

  • tables_mysql_version_4_1_and_older.sql for MySQL server versions 4.1 and older

Depending on your MySQL server version, execute the appropriate script in the target database, mmc_persistency_status.

Creating the database, user and tables with the mysql utility

  1. Open a terminal and run the mysql utility with the following command.

  2. mysql prompts you for root’s user password. Enter the password, and you should see the following mysql prompt.

  3. Enter the following commands in the order in which they appear below. Each command is terminated by a semicolon (;). When you press Enter after each semicolon, the terminal displays a message that begins with Query OK , which indicates successful completion of the command.

  4. After you exit mysql , locate the relevant script for MySQL provided with the Management Console (by default at <MMC_HOME>/WEB-INF/classes/quartz ). In your terminal, change to the directory containing the script by running the cd command. An example command follows.

  5. From this directory, run mysql again, this time with the parameters shown below.

  6. After you enter the password, you are logged in to database mmc_persistency_status as user MMC_STATUS .

  7. Run the MySQL script by issuing the following command.

  8. Depending on your MySQL server version, the script name will be tables_mysql.sql or tables_mysql_versions_4_1_and_above.sql . When you run the script, mysql will display a long list of status messages as per the following.

  9. The status messages listed above indicate that the tables have been successfully created. To verify, issue the following command (below, top), which yields the following output (below, bottom). .To see the contents of a particular table, run the following command. The example below includes the command output for table QRTZ_BLOB_TRIGGERS: At this point, you’ve finished configuring MySQL. You can proceed to set up the Management Console to use the database you’ve just created.

Setting Up MMC to use MySQL for Persisting Environment Data

This example uses the parameters employed Persisting Environment Data when creating the MySQL database.

  • MySQL listening host and port: localhost port 3306

  • Database name: mmc_persistency_status

  • Database user: MMC_STATUS

  • Password: mmc123

Obtaining MySQL Drivers

  1. Download the latest MySQL driver. (You need a free Oracle account to download.) The driver is called mysql-connector-java-<version>, such as mysql-connector-java-5.1.26. You can download the driver as a zip or tar.gz file.

  2. Extract the .zip or .tar.gz installation file. In the resulting directory structure, locate the file called mysql-connector-java-<version>-bin.jar. This is the jbdc driver itself, that you will copy to the Management Console directory structure.

Driver for MMC run as Mule app

Copy the MySQL jdbc driver, mysql-connector-java-<version>-bin.jar, to the following directory: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/lib.

Alternatively, make the driver generally available by copying it to: <Mule install path>/lib/user.

Driver for MMC run as a Web app

Copy the MySQL jdbc driver, mysql-connector-java-<version>-bin.jar, to the following directory: <MMC_HOME>/ WEB-INF/lib.

MMC Configuration

Configuring MMC to store data on a MySQL database involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use MySQL instead of its default database

  • Modifying the file mmc-mysql.properties to set the parameters for connecting to the MySQL database

Modifying web.xml

  1. In the directory <MMC_HOME>/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  1. Delete the string env-derby, then replace it with env-mysql, as shown below.

<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-mysql</param-value>
</context-param>

  1. If you are also planning to Persisting Transaction Data to MySQL, delete the string tracking-h2 and replace it with tracking-mysql.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data.
Modifying mmc-mysql.properties

  1. In the directory <MMC_HOME>/WEB-INF/classes/META-INF/databases, locate the file mmc-mysql.properties, then open it for editing.

  2. The table below lists the settings contained in the file. Modify the values as needed. In general, the only values that you should need to modify are env.username, env.password, env.host, env.port and env.dbschema.

Parameter Description Default

env.driver

Driver to use for connecting to the database

com.mysql.jdbc.Driver

env.script

Script to use for creating the tables in the target database

mysql

env.username

Database user

MMC_STATUS

env.password

Password for the database user

mmc123

env.host

Hostname or IP address where the database server is listening

localhost

env.port

Port where the database server is listening

3306

env.url

URL for connecting to the database

jdbc:mysql://${env.host}:${env.port}/${env.dbschema}

env.dbschema

Database to connect to

mmc_persistency_status

  1. Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store environment data on the external MySQL database that you specified.

Connecting to a Remote MySQL Server

If the MySQL server resides on a remote host, you are most likely unable to connect, unless you perform the following two actions:

  • configure the MySQL server to allow remote database connections

  • grant privileges to your database user allowing it to connect remotely

Both actions are described in the following two sections.

Remote Database Connections

  1. To check that the MySQL server allows remote database connections, locate the MySQL server configuration file, for example /etc/mysql/my.cnf.

  2. Search for the following line.

bind-address        = 127.0.0.1

  1. If the line exists, perform the following steps:

    1. Stop the MySQL server.

    2. Open the configuration file for editing, then comment out the line with a number sign (#), as shown below.

# bind-address      = 127.0.0.1

  1. Close the file, then restart the MySQL server.

Remote Access Privileges

  1. To grant remote database access to your database user, login to MySQL as the MySQL server root user, using the following command.

mysql -u root -D mysql -p

  1. Enter the password for root.

  2. Obtain the 41-digit hexadecimal representation of the database user’s password (in this case, the password for user MMC_STATUS) by running the following command.

SELECT * FROM user WHERE User = '<user>';

  1. In the output from the previous command, find, then copy the 41-digit hex number, which is preceded by an asterisk. Ensure that you save this number to your clipboard or a text file, since you will use it in the next step. A snippet of example output is shown below.

mysql> SELECT * FROM user WHERE User = 'MMC_STATUS';
+--------------+------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host         | User       | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+--------------+------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost    | MMC_STATUS | *14695FC49478AC013A63030250DD44DE579D54E1 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            |

  1. Grant the privileges with the following command. For the <password> parameter, paste the 41-digit hex number you copied in the previous step.

GRANT ALL PRIVILEGES ON <database>.* TO '<user>'@'<host|net>' IDENTIFIED BY PASSWORD '<password>' WITH GRANT OPTION;

  1. Tell the MySQL server to reload the grant tables, with the following command.

FLUSH PRIVILEGES;

Details and usage example for the GRANT ALL PRIVILEGES command

The value of <host|net> can either be a host or network specification. Wildcards are allowed, such as 'MMC_STATUS'@'172.16.0.%'.

The value of <password> is the 41-digit hexadecimal number representing the password. To obtain your user’s password, run the following command from the mysql prompt.

A complete command example follows.

Testing the remote connection with the telnet command

If you are unsure of the MySQL server configuration, but are certain that you have connectivity to the MySQL server host and port (i.e. you are sure there are no routing issues, firewalls, etc. blocking connectivity), then you can use the telnet command to do a remote test of the MySQL server.

If the MySQL server is not accepting remote connections, trying to telnet to the MySQL host and port results in a "Connection refused error," even if the server is indeed listening on the specified host and port.

If the MySQL server is accepting remote connections, but there is no user allowed to connect remotely, the output appears similar to the following. In this example, the connection succeeds but is closed by the remote host.

If connectivity to the MySQL server is fully working, and the server is granting user access, the output appears similar to the following.

If you obtain this output, but cannot successfully connect the Management Console to the remote MySQL server, then something is likely wrong with the Management Console configuration. Check the logs in Mule or your Web app server for details.

Persisting Transaction Data

To set up MySQL to persist your MMC transaction data, you need to complete these steps:

  1. Create the database.

  2. Create the database user.

  3. Set up MMC.

  4. Modifying the Cleanup Script for MySQL.

Creating the Database

Create a new database with the following parameters:

  • Database name: event_tracker

  • Database owner: MMC_EVENTS

Creating the Database User

Create a new database user with default permissions and the following parameters:

  • Username: MMC_EVENTS

  • Password: mmc123

Setting Up MMC to use MySQL for Persisting Transaction Data

Obtaining MySQL Drivers

  1. Download the latest MySQL driver. (You need a free Oracle account to download.) The driver is called mysql-connector-java-<version>, such as mysql-connector-java-5.1.26. You can download the driver as a zip or tar.gz file.

  2. Extract the zip or tar.gz installation file. In the resulting directory structure, locate the file called mysql-connector-java-<version>-bin.jar. This is the jbdc driver itself, that you will copy to the Management Console directory structure.

Installing the Driver

Copy the MySQL jdbc driver, mysql-connector-java-<version>-bin.jar, to the following directory: <MMC_HOME>/WEB-INF/lib.

MMC Configuration

Configuring MMC to store Business Events data on a MySQL database involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use MySQL instead of its default database

  • Modifying the file tracking-persistence-mysql.properties to set the parameters for connecting to the MySQL database

Modifying web.xml

  1. In the directory <MMC_HOME>/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

    <context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  3. Delete the string tracking-h2, then replace it with tracking-mysql, as shown below.

    <context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-mysql,env-mysql</param-value>
</context-param>

  4. If you are also planning to Persisting Environment Data to MySQL, delete the string env-derby and replace it with env-mysql.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data.

Modifying tracking-persistence-mysql.properties

  1. In the directory <MMC_HOME>/WEB-INF/classes/META-INF/databases, locate the file tracking-persistence-mysql.properties, then open it for editing.

  2. Modify the included settings as needed, according to the table below. In general, the only values that you should need to modify are mmc.tracking.db.username, mmc.tracking.db.password, mmc.tracking.db.host, mmc.tracking.db.port and mmc.tracking.db.dbname.

Parameter Description Default

mmc.tracking.db.events.query.propKeys

Do not change this value. This is a DB-engine-specific query to retrieve event keys.

SELECT DISTINCT t1.KEY0 FROM EVENT_PROPERTIES t1

mmc.tracking.db.platform

Type of database server to connect to

mysql

mmc.tracking.db.driver

Driver to use for connecting to the database

com.mysql.jdbc.Driver

mmc.tracking.db.host

Hostname or IP address where the database server is listening

localhost

mmc.tracking.db.port

Port where the database server is listening

3306

mmc.tracking.db.url

URL for connecting to the database

jdbc:mysql://${mmc.tracking.db.host}:${mmc.tracking.db.port}/${mmc.tracking.db.dbname}

mmc.tracking.db.username

Database user

MMC_EVENTS

mmc.tracking.db.password

Password for the database user

mmc123

mmc.tracking.db.dbname

Database to connect to

event_tracker

mmc.max.events.exception.details.length

Number of characters from a Business Events exception that will be stored in the tracking database. The maximum allowed is 261120.

8000

  1. Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store environment data on the external MySQL database that you specified.

Modifying the Cleanup Script for MySQL

MMC includes a script to perform a routine cleanup of the MMC database, which by default takes place every day at midnight. The current script for MySQL contains a bug which causes MySQL to throw an SQL exception. To avoid this issue, replace the original script with the one provided below, by completing the following steps.

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import com.mulesoft.mmc.tracking.repository.jpa.entities.*

import java.util.Calendar;

def logger = log

def oneWeekAgo = Calendar.getInstance()
oneWeekAgo.add(Calendar.DAY_OF_MONTH, -7);

log.info("About to delete all tracking details older than "+oneWeekAgo.getTime());

DriverManagerDataSource ds = applicationContext.getBean("dataSource");

Properties properties = new Properties();
properties.put("openjpa.jdbc.DBDictionary", "mysql");
properties.put("javax.persistence.jdbc.url", ds.getUrl());
properties.put("javax.persistence.jdbc.user", ds.getUsername());
properties.put("javax.persistence.jdbc.password", ds.getPassword());
properties.put("openjpa.ConnectionDriverName", "com.mysql.jdbc.Driver");

EntityManagerFactory emf = null;
EntityManager em = null;

try {

    emf = Persistence.createEntityManagerFactory("tracking-persistence-unit", properties);

    em = emf.createEntityManager();

    em.getTransaction().begin();

    em.createNativeQuery("DELETE p FROM EVENT_PROPERTIES p where p.id in (select e.id from EVENTS e where e.transaction_id in (select t.id from TRANSACTION_SUMMARIES t where t.timestamp < ?1))").setParameter(1, oneWeekAgo).executeUpdate();

    em.createNamedQuery(EventEntity.DELETE_OLDER_THAN_QUERY_NAME).setParameter("timestamp", oneWeekAgo).executeUpdate();
            em.createNamedQuery(TransactionSummaryEntity.DELETE_OLDER_THAN_QUERY_NAME).setParameter("timestamp", oneWeekAgo).executeUpdate();

    em.getTransaction().commit();

} catch (RuntimeException re)
    {
        if (em?.getTransaction()?.isActive()) {
            em.getTransaction().rollback();
        }
        throw re;
    }

finally {
    em?.close();
    emf?.close();
}

Replacing the Old Script

  1. Log in to MMC.

  2. Go to the Administration tab, then Admin Shell. You should see the following screen.

    admin_shell

  3. Click Cleanup Tracking DB in the column to the right. This displays the contents of the script in the editor box, as shown below.

    admin_shell2

  4. Now you can edit the script as desired. Delete all of the contents of the script, leaving the editor blank.

  5. Copy-paste the new script into the editor.

  6. Click Save As under the editor.

  7. In the input field next to Save As, type Cleanup Tracking DB (case sensitive), then click Save. This causes the new script to overwrite the old one.

See Also