Monday, February 20, 2012

ActiveMQ: JDBC Master Slave with MySQL

In this post I'll document the simple configuration needed by ActiveMQ to configure the JDBC persistence adapter and setting up MySQL as the persistent storage.  With this type of configuration you can also configure a Master/Slave broker setup by having more than one broker connect to the same database instance.

List of Binaries used for this Example


Configuring MySQL

Download and install MySQL.

Note for OS X users:  The dmg provides a simple install that contains a Startup Item package which will configure MySQL to start automatically after each reboot as well as a Preference Pane plugin which will get added to the Settings panel to allow you to start/stop and configure autostart of MySQL.

Once you have you have MySQL installed and properly configured, you will need to start the MySQL Monitor to create a user and database.
 macbookpro-251a:bin jsherman$ ./mysql -u root  
 Welcome to the MySQL monitor. Commands end with ; or \g.  
 Your MySQL connection id is 29  
 Server version: 5.5.20 MySQL Community Server (GPL)  
 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.  
 Oracle is a registered trademark of Oracle Corporation and/or its  
 affiliates. Other names may be trademarks of their respective  
 owners.  
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
 mysql>  

Then create the database for ActiveMQ
 mysql> CREATE DATABASE activemq;  

Then create a user and grant them privileges for the database
 mysql> CREATE USER 'activemq'@'%'localhost' IDENTIFIED BY 'activemq';  
 mysql> GRANT ALL ON activemq.* TO 'activemq'@'localhost';  
 mysql> exit  

Now log back into the MySQL Monitor and access the activemq database with the activemq user to make sure everything is okay
 macbookpro-251a:bin jsherman$ ./mysql -u activemq -p activemq  
 Enter password:   
 Reading table information for completion of table and column names  
 You can turn off this feature to get a quicker startup with -A  
 Welcome to the MySQL monitor. Commands end with ; or \g.  
 Your MySQL connection id is 28  
 Server version: 5.5.20 MySQL Community Server (GPL)  
 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.  
 Oracle is a registered trademark of Oracle Corporation and/or its  
 affiliates. Other names may be trademarks of their respective  
 owners.  
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
 mysql>exit  

ActiveMQ Broker Configuration
In the broker's configuration file, activemq.xml, add the following persistence adapter to configure a JDBC connection to MySQL.
 <persistenceAdapter>  
     <jdbcPersistenceAdapter dataDirectory="${activemq.base}/data" dataSource="#mysql-ds"/>  
  </persistenceAdapter>  

Then, just after the ending broker element (</broker>) add the following bean
 <bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
   <property name="driverClassName" value="com.mysql.jdbc.Driver"/>  
   <property name="url" value="jdbc:mysql://localhost/activemq?relaxAutoCommit=true"/>  
   <property name="username" value="activemq"/>  
   <property name="password" value="activemq"/>  
   <property name="maxActive" value="200"/>  
   <property name="poolPreparedStatements" value="true"/>  
 </bean>  

Copy the MySQL diver to the ActiveMQ lib directory, mysql-connector-java-5.1.18-bin.jar was used in this example.

Now start your broker, you should see the following output if running from the console
  INFO | Using Persistence Adapter: JDBCPersistenceAdapter(org.apache.commons.dbcp.BasicDataSource@303bc1a1)  
  INFO | Database adapter driver override recognized for : [mysql-ab_jdbc_driver] - adapter: class org.apache.activemq.store.jdbc.adapter.MySqlJDBCAdapter  
  INFO | Database lock driver override not found for : [mysql-ab_jdbc_driver]. Will use default implementation.  
  INFO | Attempting to acquire the exclusive lock to become the Master broker  
  INFO | Becoming the master on dataSource: org.apache.commons.dbcp.BasicDataSource@303bc1a1  
  INFO | ActiveMQ 5.5.1-fuse-01-13 JMS Message Broker (jdbcBroker1) is starting  

Now you can check your database in MySQL and see that ActiveMQ has created the required tables
 mysql> USE activemq; SHOW TABLES;  
 +--------------------+  
 | Tables_in_activemq |  
 +--------------------+  
 | ACTIVEMQ_ACKS   |  
 | ACTIVEMQ_LOCK   |  
 | activemq_msgs   |  
 +--------------------+  
 3 rows in set (0.00 sec)  
 mysql>  

If you configure multiple brokers to use this same database instance in the jdbcPersistenceAdapter element
then these brokers will attempt to acquire a lock, if they are unable to get a database lock the will wait until the lock becomes available.  This can be seen by starting a second broker using the above JDBC persistence configuration.
  INFO | PListStore:activemq-data/jdbcBroker/tmp_storage started  
  INFO | Using Persistence Adapter: JDBCPersistenceAdapter(org.apache.commons.dbcp.BasicDataSource@78979f67)  
  INFO | Database adapter driver override recognized for : [mysql-ab_jdbc_driver] - adapter: class org.apache.activemq.store.jdbc.adapter.MySqlJDBCAdapter  

As you can see the second broker did not fully initialize as it is waiting to acquire the database lock.  If the master broker is killed, then you see the slave will acquire the database lock and becomes the new master.
  INFO | Database lock driver override not found for : [mysql-ab_jdbc_driver]. Will use default implementation.  
  INFO | Attempting to acquire the exclusive lock to become the Master broker  
  INFO | Becoming the master on dataSource: org.apache.commons.dbcp.BasicDataSource@2e19fc25  
  INFO | ActiveMQ 5.5.1-fuse-01-13 JMS Message Broker (jdbcBroker2) is starting  
  INFO | For help or more information please see: http://activemq.apache.org/  
  INFO | Listening for connections at: tcp://macbookpro-251a.home:61617  
  INFO | Connector openwire Started  
  INFO | ActiveMQ JMS Message Broker (jdbcBroker2, ID:macbookpro-251a.home-53193-1328656157052-0:1) started  

Summary

As you can see, it is fairly simple and straight forward to configure a robust highly-available messaging system using ActiveMQ with database persistence.

2 comments:

  1. Hello,

    I am using activemq, stompe and mysql for creating a job queuing app. i referred above tutorial for mysql connection with active mq, but when i am adding a queue suing stomp and PHP it is not showing in the database (using phpmyadmin) browsing the database.

    Please help.

    -- Vinod

    ReplyDelete
  2. Hi,

    Can I configure multiple mysql ds for different queues in activemq?

    Thanks,
    Karthik

    ReplyDelete