25.4. MySQL Connector/MXJ

MySQL 5.0

25.4. MySQL Connector/MXJ

25.4.1. Introduction

MySQL Connector/MXJ is a Java Utility package for deploying and managing a MySQL database. Connector/MXJ may be bundled in to an existing Java application or may be deployed as a JMX MBean. Deploying and using MySQL can be as easy as adding an additional parameter to the JDBC connection url, which will result in the database being started when the first connection is made. This makes it easy for Java developers to deploy applications which require a database by reducing installation barriers for their end-users.

MySQL Connector/MXJ makes the MySQL database appear to be a java-based component. It does this by determining what platform the system is running on, selecting the appropriate binary, and launching the executable. It will also optionally deploy an initial database, with any specified parameters.

As a JMX MBean, MySQL Connector/MXJ requires a JMX v1.2 compliant MBean container, such as JBoss version 4. The MBean will uses the standard JMX management APIs to present (and allow the setting of) parameters which are appropriate for that platform.

Included are instructions for use with a JDBC driver and deploying as a JMX MBean to JBoss.

You can download sources and binaries from: http://dev.mysql.com/downloads/connector/mxj/

This an beta release and feedback is welcome and encouraged.

Please send questions or comments to [email protected].

25.4.2. Support Platforms:

  • Linux, i386

  • Windows NT, x86

  • Windows 2000, x86

  • Windows XP, x86

  • Solaris 9, SPARC 32

25.4.3. JUnit Test Requirements

The best way to ensure that your platform is supported is to run the JUnit tests.

The first thing to do is make sure that the components will work on the platform. Since the ''MysqldResource'' class is really a wrapper for a native version of MySQL, not all platforms are supported. At the time of this writing, Linux on the i386 architecture has been tested and seems to work quite well, as does OS X v10.3. There has been limited testing on Windows and Solaris.

Requirements:

  1. JDK-1.4 or newer (or the JRE if you aren't going to be compiling the source or JSPs).

  2. MySQL Connector/J version 3.1 or newer (from http://dev.mysql.com/downloads/connector/j/ ) installed and available via your CLASSPATH.

  3. The classes for JMX version 1.2.1, these are present in the following application servers:

  4. Junit 3.8.1 (from http://www.junit.org/)

If building from source, All of the requirements from above, plus:

  1. Ant version 1.5 or newer (download from http://ant.apache.org/)

25.4.4. Running the JUnit Tests

  1. The tests attempt to launch MySQL on the port 3336. If you have a MySQL running, it may conflict, but this isn't very likely since thedefault port for MySQL is 3306. However, You may set the "c-mxj_test_port" Java property to a port of your choosing. Alternatively, you may wish to start by shutting down any instances of MySQL you have running on the target machine.

    The tests surpress output to the console by default. For verbose output, you may set the "c-mxj_test_silent" Java property to "false".

  2. In order to run the JUnit test suite, the $CLASSPATH must include the following:

    • JUnit

    • JMX

    • Connector/J

    • MySQL Connector/MXJ

  3. If is not present in your download, unzip MySQL Connector/MXJ source archive.

    cd mysqldjmx
    ant dist
         

    Then add to the CLASSPATH.

  4. if you have , execute the unit tests. From the command line, type:

    java junit.textui.TestRunner com.mysql.management.AllTestsSuite
        

    The output should look something like this:

    .........................................
    .........................................
    ..........
    Time: 259.438
    
    OK (101 tests)
      

    Note that the tests are a bit slow near the end, so please be patient.

25.4.5. Running as part of the JDBC Driver

A feature of the MySQL Connector/J JDBC driver is the ability to specify a ''SocketFactory'' as a parameter in the JDBC connection string. MySQL Connector/MXJ includes a custom SocketFactory. The SocketFactory will, upon the first connection, deploy and launch the MySQL database. The SocketFactory also exposes a ''shutdown'' method.

To try it specify the ''socketFactory'' parameter on the JDBC connection string with a value equal to ''com.mysql.management.driverlaunched.ServerLauncherSocketFactory''

In the following example, we have a program which creates a connection, executes a query, and prints the result to the System.out. The MySQL database will be deployed and started as part of the connection process, and shutdown as part of the finally block.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.mysql.management.driverlaunched.ServerLauncherSocketFactory;

public class ConnectorMXJTestExample {
    public static void main(String[] args) throws Exception {
        String hostColonPort = "localhost:3336";
        
        String driver = com.mysql.jdbc.Driver.class.getName();
        String url = "jdbc:mysql://" + hostColonPort + "/" + "?"
                + "socketFactory="
                + ServerLauncherSocketFactory.class.getName();
        String userName = "root";
        String password = "";

        Class.forName(driver);
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, userName, password);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT VERSION()");
            rs.next();
            String version = rs.getString(1);
            rs.close();
            stmt.close();

            System.out.println("------------------------");
            System.out.println(version);
            System.out.println("------------------------");
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            ServerLauncherSocketFactory.shutdown(hostColonPort);
        }
    }
}
  

To run the above program, be sure to have connector-mxj.jar and Connector/J in the CLASSPATH. Then type:

java ConnectorMXJTestExample
  

Of course there are many options we may wish to set for a MySQL database. These options may be specified as part of the JDBC connection string simply by prefixing each server option with ''server.''. In the following example we set three driver parameters and two server parameters:

        String url = "jdbc:mysql://" + hostColonPort + "/" 
                + "?"
                + "socketFactory="
                + ServerLauncherSocketFactory.class.getName();
                + "&"
                + "cacheServerConfiguration=true"
                + "&"
                + "useLocalSessionState=true"
                + "&"
                + "server.basedir=/opt/myapp/db"
                + "&"
                + "server.datadir=/mnt/bigdisk/myapp/data";
  

25.4.6. Running within a Java Object

Have a java application and wish to "embed" a MySQL database, make use of the com.mysql.management.MysqldResource class directly. This class may be instantiated with the default (no argument) constructor, or by passing in a java.io.File object representing the directory you wish the server to be "unzipped" into. It may also be instantiated with printstreams for "stdout" and "stderr" for logging.

Once instantiated, a java.util.Map, the object will be able to provide a java.util.Map of server options appropriate for the platform and version of MySQL which you will be using.

The MysqldResource will allow you to "start" MySQL with a java.util.Map of server options which you provide, as well as "shutdown" the database. The following example shows a simplistic way to embed MySQL in an applicaiton using plain java objects:

import com.mysql.management.MysqldResource;

 ...

    public void startMySQL() {
        File baseDir = new File(ourAppDir, "mysql");
        mysqldResource = new MysqldResource(baseDir);
        Map options = new HashMap();
        options.put("port", "3336");
        String threadName = "OurApp MySQL";
        mysqldResource.start(threadName, options);
    }
    
    public void stopMySQL() {
        if (mysqldResource != null) {
            mysqldResource.shutdown();
        }
        mysqldResource = null;
    }
    
    public java.sql.Connection getConnection() throws Exception {
        String db = "test";
        String url = "jdbc:mysql://localhost:3336/" + db;
        String userName = "root";
        String password = "";
        Class.forName(com.mysql.jdbc.Driver.class.getName());
        return DriverManager.getConnection(url, userName, password);
    }
  

25.4.7. The MysqldResource API

Constructors:

  • public MysqldResource(File baseDir, PrintStream out, PrintStream err);

    Allows the setting of the "basedir" to deploy the MySQL files to, as well as out put streams for standard out and standard err.

  • public MysqldResource(File baseDir);

    Allows the setting of the "basedir" to deploy the MySQL files to. Output for standard out and standard err are directed to System.out and System.err.

  • public MysqldResource();

    The basedir is defaulted to a subdirectory of the java.io.tempdir. Output for standard out and standard err are directed to System.out and System.err;

MysqldResource API includes the following methods:

  • void start(String threadName, Map mysqldArgs);

    Deploys and starts MySQL. The "threadName" string is used to name the thread which actually performs the execution of the MySQL command line. The map is the set of arguments and their values to be passed to the command line.

  • void shutdown();

    Shuts down the MySQL instance managed by the MysqldResource object.

  • Map getServerOptions();

    Returns a map of all the options and their current (or default, if not running) options available for the MySQL database.

  • boolean isRunning();

    Returns true if the MySQL database is running.

  • boolean isReadyForConnections();

    Returns true once the database reports that is is ready for connections.

  • void setKillDelay(int millis);

    The default "Kill Delay" is 30 seconds. This represents the amount of time to wait between the initial request to shutdown and issuing a "force kill" if the database has not shutdown by itself.

  • void addCompletionListenser(Runnable listener);

    Allows for applications to be notified when the server process completes. Each ''listener'' will be fired off in its own thread.

  • String getVersion();

    returns the version of MySQL.

  • void setVersion(int MajorVersion, int minorVersion, int patchLevel);

    The standard distribution comes with only one version of MySQL packaged. However, it is possible to package multiple versions, and specify which version to use.

25.4.8. Running within a JMX Agent (custom)

If you are not using the SUN Reference implementation of the JMX libraries, you should skip this section. Or, if you are deploying to JBoss, you also may wish to skip to the next section.

We want to see the MysqldDynamicMBean in action inside of a JMX agent. In the package is a custom JMX agent with two MBeans:

  1. the MysqldDynamicMBean, and

  2. a com.sun.jdmk.comm.HtmlAdaptorServer, which provides a web interface for manipulating the beans inside of a JMX agent.

When this very simple agent is started, it will allow a MySQL database to be started and stopped with a web browser.

  1. Complete the testing of the platform as above.

    • current JDK, JUnit, Connector/J, MySQL Connector/MXJ

    • this section requires the SUN reference implementation of JMX

    • PATH, JAVA_HOME, ANT_HOME, CLASSPATH

  2. If not building from source, skip to next step

    rebuild with the "sunri.present"

    ant -Dsunri.present=true dist 
    re-run tests:
    java junit.textui.TestRunner com.mysql.management.AllTestsSuite
    
  3. launch the test agent from the command line:

    java com.mysql.management.jmx.sunri.MysqldTestAgentSunHtmlAdaptor &
         
  4. from a browser:

    http://localhost:9092/
         
  5. under MysqldAgent,

    select "name=mysqld"
         
  6. Observe the MBean View

  7. scroll to the bottom of the screen press the startMysqld button

  8. click

  9. scroll to the bottom of the screen press stopMysqld button

  10. kill the java process running the Test Agent (jmx server)

25.4.9. Deployment in a standard JMX Agent environment (JBoss)

Once there is confidence that the MBean will function on the platform, deploying the MBean inside of a standard JMX Agent is the next step. Included are instructions for deploying to JBoss.

  1. Ensure a current version of java development kit (v1.4.x), see above.

    • Ensure is set (JBoss requires )

    • Ensure is in the (You will NOT need to set your CLASSPATH, nor will you need any of the jars used in the previous tests).

  2. Ensure a current version of JBoss (v4.0RC1 or better)

    http://www.jboss.org/index.html
    select "Downloads"
    select "jboss-4.0.zip"
    pick a mirror
    unzip ~/dload/jboss-4.0.zip
    create a JBOSS_HOME environment variable set to the unzipped directory
    unix only:
    cd $JBOSS_HOME/bin
    chmod +x *.sh
    
  3. Deploy (copy) the to .

  4. Deploy (copy) to .

  5. Create a directory in .

  6. Deploy (copy) to .

  7. Create a file in .

    <?xml version="1.0" encoding="UTF-8"?>
     <server>
      <mbean code="com.mysql.management.jmx.jboss.JBossMysqldDynamicMBean"
         name="mysql:type=service,name=mysqld">
      <attribute name="datadir">/tmp/xxx_data_xxx</attribute>
      <attribute name="autostart">true</attribute>
      </mbean>
     </server>
         
  8. Start jboss:

    • on unix: $JBOSS_HOME/bin/run.sh

    • on windows: %JBOSS_HOME%\bin\run.bat

    Be ready: JBoss sends a lot of output to the screen.

  9. When JBoss seems to have stopped sending output to the screen, open a web browser to:

  10. Scroll down to the bottom of the page in the section, select the bulleted link.

  11. Observe the JMX MBean View page. MySQL should already be running.

  12. (If "autostart=true" was set, you may skip this step.) Scroll to the bottom of the screen. You may press the Invoke button to stop (or start) MySQL observe Click

  13. To confirm MySQL is running, open a web browser to and you should see that

    SELECT 1

    returned with a result of

    1
  14. Guided by the you will be able to use MySQL in your Web Application. There is a database and a user (no password) ready to expirement with. Try creating a table, inserting some rows, and doing some selects.

  15. Shut down MySQL. MySQL will be stopped automatically when JBoss is stopped, or: from the browser, scroll down to the bottom of the MBean View press the stop service Invoke button to halt the service. Observe Using or see that MySQL is no longer running

As of 1.0.6-beta version is the ability to have the MBean start the MySQL database upon start up. Also, we've taken advantage of the JBoss life-cycle extension methods so that the database will gracefully shut down when JBoss is shutdown.

25.4.10. Installation

If you've worked through the above sections, you've arleady performed these steps. But we list them here for quick reference.

Driver Launched:

  1. Download and unzip Connector/MXJ, add connector-mxj.jar to the CLASSPATH

  2. To the JDBC connection string add the following parameter: "socketFactory=" + ServerLauncherSocketFactory.class.getName()

JBoss:

  1. Download Connector/MXJ copy the file to the diretory.

  2. Download Connector/J copy the file to the diretory.

  3. Create an MBean service xml file in the directory with any attributes set, for instance the and .

  4. Set the JDBC parameters of your web application to use:

You may wish to create a separate users and database table spaces for each application, rather than using "root and test".

We highly suggest having a routine backup procedure for backing up the database files in the .