Persisting MMC Data to MySQL

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:

Persisting Environment Data
Persisting Transaction Data

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:

Mule Enterprise 3.4.1. Download it from the customer portal if you need to upgrade to this version.
MMC Version 3.4.1. 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 using the trial version, this is <Mule install path>/mmc/webapps/mmc, for example /opt/mule/mule-enterprise-3.4.1/mmc/webapps/mmc. If using an application server such as Tomcat, this should be similar to <Tomcat root>/webapps/mmc-console-3.4.1, for example /var/lib/tomcat6/webapps/mmc-console-3.4.1.

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:

Create the database.
Create the database user.
Create the tables.
Set up MMC.
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 earlier than 4.1
tables_mysql_version_4_1_and_above.sql for MySQL server versions 4.1 and above

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

Open a terminal and run the mysql utility with the following command.
```
mysql -u root -D mysql -p
```
mysql prompts you for root’s user password. Enter the password, and you should see the following mysql prompt.
```
mysql>
```
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.
```
CREATE DATABASE mmc_persistency_status;
CREATE USER 'MMC_STATUS'@'localhost' IDENTIFIED BY 'mmc123';
GRANT ALL PRIVILEGES ON mmc_persistency_status.* TO 'MMC_STATUS'@'localhost';
exit
```
After you exit mysql , locate the relevant script for MySQL provided with the Management Console (by default at $MMC_HOME/webapps/mmc/WEB-INF/classes/quartz ). In your terminal, change to the directory containing the script by running the cd command. An example command follows.
```
cd /opt/mule/mule-ee-3.4.1/apps/mmc/webapps/mmc/WEB-INF/classes/quartz
```
From this directory, run mysql again, this time with the parameters shown below.
```
mysql -u MMC_STATUS -D mmc_persistency_status -p
```
After you enter the password, you are logged in to database mmc_persistency_status as user MMC_STATUS .
Run the MySQLscript by issuing the following command.
```
source <script name>
```
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.
```
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
...
```

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).

SHOW TABLES IN mmc_persistency_status;

+----------------------------------+
| Tables_in_mmc_persistency_status |
+----------------------------------+
| QRTZ_BLOB_TRIGGERS               |
| QRTZ_CALENDARS                   |
| QRTZ_CRON_TRIGGERS               |
| QRTZ_FIRED_TRIGGERS              |
| QRTZ_JOB_DETAILS                 |
| QRTZ_JOB_LISTENERS               |
| QRTZ_LOCKS                       |
| QRTZ_PAUSED_TRIGGER_GRPS         |
| QRTZ_SCHEDULER_STATE             |
| QRTZ_SIMPLE_TRIGGERS             |
| QRTZ_TRIGGERS                    |
| QRTZ_TRIGGER_LISTENERS           |
+----------------------------------+
12 rows in set (0.00 sec)

To see the contents of a particular table, run the following command.
```
DESCRIBE <table name>;
```

The example below includes the command output for table QRTZ_BLOB_TRIGGERS:

mysql> DESCRIBE QRTZ_BLOB_TRIGGERS;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| TRIGGER_NAME  | varchar(80) | NO   | PRI | NULL    |       |
| TRIGGER_GROUP | varchar(80) | NO   | PRI | NULL    |       |
| BLOB_DATA     | blob        | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

First, install the appropriate Obtaining MySQL Drivers.
Then, follow the instructions below for editing the following configuration files:

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

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.
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.

Configuration file: `mmc.properties`

(Directory: <MMC_HOME>/WEB-INF/classes/META-INF.)

In the directory listed above, locate the file mmc.properties.
Open the file for editing, then edit it as per the following. ++

datasource.driver=com.mysql.jdbc.Driver
datasource.username=MMC_STATUS
datasource.password=mmc123
datasource.url=jdbc:mysql://localhost/mmc_persistency_status
script=mysql
mmc.data=./mmc-data
plugin.directory=WEB-INF/plugins
console.inactivityTimeout=60

In the above example and in all configuration files in this section, the database URL assumes the default port for MySQL server, 3306. If your MySQL server is listening on a different port, include the port number in the URL, with the following format: jdbc:mysql://<host>:<port>/<database name>.

Make sure that the following line does not appear:

datasource.dbName=${galaxy.data}/db from mmc.properties

This line would conflict with the parameters for accessing the correct database, resulting in a connection error. If the line is present in the mmc.properties file, delete it.

Configuration file: `applicationContext.xml`

(Directory: Same as previous file (<MMC_HOME>/WEB-INF/classes/META-INF).)

In the directory listed above, locate the file named applicationContext.xml and open it for editing.
Look for the bean named internalDataSource, then edit it as per the following.

<bean name="internalDataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
   <!-- TODO: change this via the configurator -->
   <property name="driverClass" value="com.mysql.jdbc.Driver"/>
   <property name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
   <property name="username" value="MMC_STATUS"/>
   <property name="password" value="mmc123"/>
</bean>

Configuration file: `jackrabbit-repo.xml`

Before editing this file, delete the following files (if listed):

jackrabbit-repo-default.xml
jackrabbit-repo-test.xml

(Directory: Same as previous file (<MMC_HOME>/WEB-INF/classes/META-INF).)

In the directory listed above, locate the file jackrabbit-repo.xml and open it for editing.
Replace the appropriate parameters to use MySQL instead of the default Derby database. Use the following parameters for the database connection:
- User: MMC_STATUS
- Password: mmc123
- URL: jdbc:mysql://localhost/mmc_persistency_status
- Schema: mysql
- Driver: com.mysql.jdbc.Driver
- IP: localhost
- Port: 3306 (default at installation)
Replace the parameters in the appropriate sections, which are displayed in the code snippets below.

The schema should be always mysql. It defines the database, not the actual schema to be used for storing persistent data.

...
<Repository>
    <!--
        virtual file system where the repository stores global state
        (e.g. registered namespaces, custom node types, etc.)
    -->
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
        <param name="driver" value="com.mysql.jdbc.Driver"/>
        <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
        <param name="schema" value="mysql"/>
        <param name="schemaObjectPrefix" value="rep_"/>
        <param name="user" value="MMC_STATUS"/>
        <param name="password" value="mmc123"/>
    </FileSystem>
...

...
<!--
        workspace configuration template:
        used to create the initial workspace if there's no workspace yet
    -->
    <Workspace name="Jackrabbit Core">
        <!--
            virtual file system of the workspace:
            class: FQN of class implementing the FileSystem interface
        -->
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="com.mysql.jdbc.Driver"/>
            <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
            <param name="schema" value="mysql"/>
            <param name="schemaObjectPrefix" value="rep_"/>
            <param name="user" value="MMC_STATUS"/>
            <param name="password" value="mmc123"/>
        </FileSystem>
...

When configuring the default workspace and the workspace name itself, ensure that you comply with one of the following conditions:

assign the same new name to both workspaces (for example mySQL workspace)

OR
delete all subfolders in mmc-data/workspaces, specifically the default subfolder. You will find the mmc-data folder on the root folder of your Web application server installation (for example, /var/lib/tomcat6/mmc-data).

If neither of the above conditions are met, all binary data persists locally instead of on the external database.

...
<!--
            persistence manager of the workspace:
            class: FQN of class implementing the PersistenceManager interface
        -->
        <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
          <param name="schemaObjectPrefix" value="Jackrabbit Core_"/>
          <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
          <param name="externalBLOBs" value="true"/>
          <param name="schemaCheckEnabled" value="true"/>
          <param name="user" value="MMC_STATUS"/>
          <param name="password" value="mmc123"/>
        </PersistenceManager>
...

PersistenceManager parameters

The externalBLOBs boolean parameter defines whether to store binary data (the apps deployed via the Management Console) on the database, or on the filesystem. The default value, true, indicates that this data is stored on the filesystem. Set the value to false to store the data on the database.

The schemaCheckEnabled parameter specifies whether to create additional necessary database tables on Management Console launch. It must always be set to true.

...
<!--
            Search index and the file system it uses.
            class: FQN of class implementing the QueryHandler interface
        -->
        <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
            <param name="path" value="${wsp.home}/index"/>
            <param name="textFilterClasses"
                value="org.apache.jackrabbit.extractor.PlainTextExtractor,
                       org.apache.jackrabbit.extractor.MsExcelTextExtractor,
                       org.apache.jackrabbit.extractor.MsPowerPointTextExtractor,
                       org.apache.jackrabbit.extractor.MsWordTextExtractor,
                       org.apache.jackrabbit.extractor.PdfTextExtractor,
                       org.apache.jackrabbit.extractor.HTMLTextExtractor,
                       org.apache.jackrabbit.extractor.XMLTextExtractor,
                       org.apache.jackrabbit.extractor.RTFTextExtractor,
                       org.apache.jackrabbit.extractor.OpenOfficeTextExtractor" />
            <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
                <param name="driver" value="com.mysql.jdbc.Driver"/>
                <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
                <param name="schema" value="mysql"/>
                <param name="schemaObjectPrefix" value="rep_"/>
                <param name="user" value="MMC_STATUS"/>
                <param name="password" value="mmc123"/>
            </FileSystem>
        </SearchIndex>
...

...
<Versioning rootPath="${rep.home}/version">
        <!--
            Configures the filesystem to use for versioning for the respective
            persistence manager
        -->
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="com.mysql.jdbc.Driver"/>
            <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
            <param name="schema" value="mysql"/>
            <param name="schemaObjectPrefix" value="rep_"/>
            <param name="user" value="MMC_STATUS"/>
            <param name="password" value="mmc123"/>
        </FileSystem>
...

...
<!--
            Configures the persistence manager to be used for persisting version state.
            Please note that the current versioning implementation is based on
            a 'normal' persistence manager, but this could change in future
            implementations.
        -->
        <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
        <param name="schemaCheckEnabled" value="true"/>
          <param name="schemaObjectPrefix" value="version_"/>
          <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
          <param name="externalBLOBs" value="true"/>
          <param name="user" value="MMC_STATUS"/>
          <param name="password" value="mmc123"/>
        </PersistenceManager>
    </Versioning>
...

...
<!--
        Search index for content that is shared repository wide
        (/jcr:system tree, contains mainly versions)

        -->
    <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
        <param name="path" value="${rep.home}/repository/index"/>
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="com.mysql.jdbc.Driver"/>
            <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
            <param name="schema" value="mysql"/>
            <param name="schemaObjectPrefix" value="rep_"/>
            <param name="user" value="MMC_STATUS"/>
            <param name="password" value="mmc123"/>
        </FileSystem>
    </SearchIndex>
</Repository>

Example of jackrabbit-repo.xml

<?xml version="1.0"?>
<!--
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
-->
<!DOCTYPE Repository PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 1.2//EN"
                            "http://jackrabbit.apache.org/dtd/repository-1.2.dtd">
<Repository>
    <!--
        virtual file system where the repository stores global state
        (e.g. registered namespaces, custom node types, etc.)
    -->
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
        <param name="driver" value="com.mysql.jdbc.Driver"/>
        <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
        <param name="schema" value="mysql"/>
        <param name="schemaObjectPrefix" value="rep_"/>
        <param name="user" value="MMC_STATUS"/>
        <param name="password" value="mmc123"/>
    </FileSystem>

    <!--
        security configuration
    -->
    <Security appName="Jackrabbit">
        <!--
            access manager:
            class: FQN of class implementing the AccessManager interface
        -->
        <AccessManager class="org.apache.jackrabbit.core.security.SimpleAccessManager">
            <!-- <param name="config" value="${rep.home}/access.xml"/> -->
        </AccessManager>

        <LoginModule class="org.apache.jackrabbit.core.security.SimpleLoginModule">
           <!-- anonymous user name ('anonymous' is the default value) -->
           <param name="anonymousId" value="anonymous"/>
           <!--
              default user name to be used instead of the anonymous user
              when no login credentials are provided (unset by default)
           -->
           <!-- <param name="defaultUserId" value="superuser"/> -->
        </LoginModule>
    </Security>

    <!--
        location of workspaces root directory and name of default workspace
    -->
    <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
    <!--
        workspace configuration template:
        used to create the initial workspace if there's no workspace yet
    -->
    <Workspace name="Jackrabbit Core">
        <!--
            virtual file system of the workspace:
            class: FQN of class implementing the FileSystem interface
        -->
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="com.mysql.jdbc.Driver"/>
            <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
            <param name="schema" value="mysql"/>
            <param name="schemaObjectPrefix" value="rep_"/>
            <param name="user" value="MMC_STATUS"/>
            <param name="password" value="mmc123"/>
        </FileSystem>
        <!--
            persistence manager of the workspace:
            class: FQN of class implementing the PersistenceManager interface
        -->
        <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
          <param name="schemaObjectPrefix" value="Jackrabbit Core_"/>
          <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
          <param name="externalBLOBs" value="true"/>
          <param name="schemaCheckEnabled" value="true"/>
          <param name="user" value="MMC_STATUS"/>
          <param name="password" value="mmc123"/>
        </PersistenceManager>
        <!--
            Search index and the file system it uses.
            class: FQN of class implementing the QueryHandler interface
        -->
        <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
            <param name="path" value="${wsp.home}/index"/>
            <param name="textFilterClasses"
                value="org.apache.jackrabbit.extractor.PlainTextExtractor,
                       org.apache.jackrabbit.extractor.MsExcelTextExtractor,
                       org.apache.jackrabbit.extractor.MsPowerPointTextExtractor,
                       org.apache.jackrabbit.extractor.MsWordTextExtractor,
                       org.apache.jackrabbit.extractor.PdfTextExtractor,
                       org.apache.jackrabbit.extractor.HTMLTextExtractor,
                       org.apache.jackrabbit.extractor.XMLTextExtractor,
                       org.apache.jackrabbit.extractor.RTFTextExtractor,
                       org.apache.jackrabbit.extractor.OpenOfficeTextExtractor" />
            <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
                <param name="driver" value="com.mysql.jdbc.Driver"/>
                <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
                <param name="schema" value="mysql"/>
                <param name="schemaObjectPrefix" value="rep_"/>
                <param name="user" value="MMC_STATUS"/>
                <param name="password" value="mmc123"/>
            </FileSystem>
        </SearchIndex>
    </Workspace>

    <!--
        Configures the versioning
    -->
    <Versioning rootPath="${rep.home}/version">
        <!--
            Configures the filesystem to use for versioning for the respective
            persistence manager
        -->
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="com.mysql.jdbc.Driver"/>
            <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
            <param name="schema" value="mysql"/>
            <param name="schemaObjectPrefix" value="rep_"/>
            <param name="user" value="MMC_STATUS"/>
            <param name="password" value="mmc123"/>
        </FileSystem>

        <!--
            Configures the persistence manager to be used for persisting version state.
            Please note that the current versioning implementation is based on
            a 'normal' persistence manager, but this could change in future
            implementations.
        -->
        <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
        <param name="schemaCheckEnabled" value="true"/>
          <param name="schemaObjectPrefix" value="version_"/>
          <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
          <param name="externalBLOBs" value="true"/>
          <param name="user" value="MMC_STATUS"/>
          <param name="password" value="mmc123"/>
        </PersistenceManager>
    </Versioning>

    <!--
        Search index for content that is shared repository wide
        (/jcr:system tree, contains mainly versions)

        -->
    <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
        <param name="path" value="${rep.home}/repository/index"/>
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="com.mysql.jdbc.Driver"/>
            <param name="url" value="jdbc:mysql://localhost/mmc_persistency_status"/>
            <param name="schema" value="mysql"/>
            <param name="schemaObjectPrefix" value="rep_"/>
            <param name="user" value="MMC_STATUS"/>
            <param name="password" value="mmc123"/>
        </FileSystem>
    </SearchIndex>
</Repository>

Configuration file: `scheduler.properties`

(Directory: Same as previous files (`<MMC_HOME>/`WEB-INF/classes/META-INF).)

In the directory listed above, locate the file called scheduler.properties, then open it for editing.

Edit the file as per the following.

org.quartz.jobStore.isClustered=false
org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.useProperties=true
org.quartz.scheduler.instanceId=AUTO

The Management Console is now configured to store its persistent data in the specified MySQL database. Start the Management Console and login normally.

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

To check that the MySQL server allows remote database connections, locate the MySQL server configuration file, for example /etc/mysql/my.cnf.
Search for the following line.
```
bind-address        = 127.0.0.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
3. Close the file, then restart the MySQL server.

Remote Access Privileges

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
```
Enter the password for root.
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>';
```

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            |

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;
```
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.

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

A complete command example follows.

mysql> GRANT ALL PRIVILEGES ON mmc_persistency_status.* TO 'MMC_STATUS'@'192.168.56.1' IDENTIFIED BY PASSWORD '*14695FC49478AC013A63030250DD44DE579D54E1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

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.

~> telnet xubuntu 3306
Trying 192.168.56.2...
telnet: connect to address 192.168.56.2: Connection refused
telnet: Unable to connect to remote host
~>

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.

~> telnet xubuntu 3306
Trying 192.168.56.2...
Connected to xubuntu    .
Escape character is '^]'.
<Host 'mac' is not allowed to connect to this MySQL serverConnection closed by foreign host.
~>

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

~>
Trying 192.168.56.2...
Connected to xubuntu.
Escape character is '^]'.
[
5.5.32-0ubuntu0.12.04.1.1asB.^p�h"kx9Pf1]ecBmysql_native_password

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:

Create the database.
Create the database user.
Set up MMC.
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

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.
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.

Database-Specific Configuration

Configuration File: `persistence.xml`

(Directory: <MMC_HOME>/`WEB-INF/classes/META-INF.)

In the directory listed above, locate the file persistence.xml.

Open the file for editing, then find the line listed below.

<property name="openjpa.jdbc.DBDictionary" value="org.apache.openjpa.jdbc.sql.H2Dictionary(batchLimit=-1)" />

Edit the line as per below.

<property name="openjpa.jdbc.DBDictionary" value="mysql" />

Configuration File: `applicationContext-tracking.xml`

(Directory: Same as previous file (<MMC_HOME>/`WEB-INF/classes/META-INF).)

In the directory listed above, locate the file applicationContext-tracking.xml.
Open the file for editing.
Find the beans dataSource and jpaAdapter as per the listing below.

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://<db_host>:3306/event_tracker" />
        <property name="username" value="MMC_EVENTS" />
        <property name="password" value="mmc123" />
    </bean>

[...]

    <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter">
        <property name="databasePlatform" value="mysql" />
    </bean>

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.

New Cleanup Script

the new cleanup script

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

Log in to MMC.
Go to the Administration tab, then Admin Shell. You should see the following screen.
Click Cleanup Tracking DB in the column to the right. This displays the contents of the script in the editor box, as shown below.
Now you can edit the script as desired. Delete all of the contents of the script, leaving the editor blank.
Copy-paste the New Cleanup Script into the editor.
Click Save. This causes the new script to overwrite the old one.

Gartner names MuleSoft a Leader

Unleash the power of Salesforce Customer 360 through integration

Anypoint Platform Fundamentals

MuleSoft at World Tour

Gartner names MuleSoft a Leader

Unleash the power of Salesforce Customer 360 through integration

Anypoint Platform Fundamentals

MuleSoft at World Tour

Persisting MMC Data to MySQL

Assumptions

MySQL Installation

Tools

Persisting Environment Data

Creating the Database

Creating the Database User

Creating the Tables

Setting Up MMC to use MySQL for Persisting Environment Data

Obtaining MySQL Drivers

Driver for MMC run as Mule app

Driver for MMC run as a Web app

Configuration file: `mmc.properties`

Configuration file: `applicationContext.xml`

Configuration file: `jackrabbit-repo.xml`

Configuration file: `scheduler.properties`

Connecting to a Remote MySQL Server

Remote Database Connections

Remote Access Privileges

Persisting Transaction Data

Creating the Database

Creating the Database User

Setting Up MMC to use MySQL for Persisting Transaction Data

Obtaining MySQL Drivers

Installing the Driver

Database-Specific Configuration

Configuration File: `persistence.xml`

Configuration File: `applicationContext-tracking.xml`

Modifying the Cleanup Script For MySQL

New Cleanup Script

Replacing the Old Script

See Also

Persisting MMC Data to MySQL

Assumptions

MySQL Installation

Tools

Persisting Environment Data

Creating the Database

Creating the Database User

Creating the Tables

Setting Up MMC to use MySQL for Persisting Environment Data

Obtaining MySQL Drivers

Driver for MMC run as Mule app

Driver for MMC run as a Web app

Configuration file: mmc.properties

Configuration file: applicationContext.xml

Configuration file: jackrabbit-repo.xml

Configuration file: scheduler.properties

Connecting to a Remote MySQL Server

Remote Database Connections

Remote Access Privileges

Persisting Transaction Data

Creating the Database

Creating the Database User

Setting Up MMC to use MySQL for Persisting Transaction Data

Obtaining MySQL Drivers

Installing the Driver

Database-Specific Configuration

Configuration File: persistence.xml

Configuration File: applicationContext-tracking.xml

Modifying the Cleanup Script For MySQL

New Cleanup Script

Replacing the Old Script

See Also

Configuration file: `mmc.properties`

Configuration file: `applicationContext.xml`

Configuration file: `jackrabbit-repo.xml`

Configuration file: `scheduler.properties`

Configuration File: `persistence.xml`

Configuration File: `applicationContext-tracking.xml`