Liquibase…A RCS for SQL

OS: CentOS 5.4

This is a short tutorial on how to get Liquibase to diff 2 database schemas.

Download Liquibase and Extract it

HOME="/home/testuser"
cd $HOME
mkdir liquibase
cd liquibase
wget http://liquibase.org/liquibase-2.0.1-bin.tar.gz
tar zxvf liquibase-2.0.1-bin.tar.gz
chmod +x liquibase

Download a MySQL connector jar file and place it in the Liquibase lib directory.

wget http://www.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.15.tar.gz/from/http://mysql.he.net/
tar zxvf mysql-connector-java-5.1.15.tar.gz
cp mysql-connector-java-5.1.15/mysql-connector-java-5.1.15-bin.jar /home/testuser/liquibase/lib/

How to diff 2 databases schemas.
Let Liquibase diff the two database schemas for you. It will tell you if you have 2 different tables/columns.
Both databases (testdb1 and testdb2) are located on a mysql-server running on my localhost.
I access them with the username of “root” and the password of “mypassword”.

{testuser@localhost /home/testuser/liquibase/}#  ./liquibase --driver=com.mysql.jdbc.Driver \ 
--url=jdbc:mysql://localhost/testdb1 \ 
--username=root \ 
--password=mypassword \ 
diff \ 
--referenceUrl=jdbc:mysql://localhost/testdb2 \ 
--referenceusername=root \ 
--referencepassword=mypassword

Here is a sample output with differences.
testdb2 I changed the Column user_id to user1_id.

INFO 2/28/11 10:58 AM:liquibase: Reading tables for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading views for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading foreign keys for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading primary keys for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading columns for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading unique constraints for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading indexes for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Sequences not supported for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading tables for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading views for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading foreign keys for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading primary keys for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading columns for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading unique constraints for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading indexes for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Sequences not supported for root@localhost @ jdbc:mysql://localhost/testdb1 ...

Diff Results:
Reference Database: root@localhost @ jdbc:mysql://localhost/testdb1
Target Database: root@localhost @ jdbc:mysql://localhost/testdb2
Product Name: EQUAL
Product Version: EQUAL
Missing Tables: NONE
Unexpected Tables: NONE
Missing Views: NONE
Unexpected Views: NONE
Changed Views: NONE
Missing Columns: 
     accounting.user_id
Unexpected Columns: 
     accounting.user1_id
Changed Columns: NONE
Missing Foreign Keys: NONE
Unexpected Foreign Keys: NONE
Missing Primary Keys: NONE
Unexpected Primary Keys: NONE
Unexpected Unique Constraints: NONE
Missing Unique Constraints: NONE
Missing Indexes: NONE
Unexpected Indexes: NONE
Missing Sequences: NONE
Unexpected Sequences: NONE
Liquibase 'diff' Successful

Advertisement

Tomcat 5.5 – JMX managmenet

I have had the need to monitor Java webapps. There are two ways of doing this. One is via SNMP into the JVM and the other is via JMX. Once you make the configuration changes you will need to restart tomcat. Once tomcat is restarted you will want to make sure those ports were opened and are currently being listened on by java. Run “sudo /usr/sbin/lsof | grep :” then look for your port numbers you chose to bind SNMP/JMX to. If they are not listed then they did not bind and you are going to want to simplify your JAVA_OPTS down to just the bare basics + the port commands. Keep in mind applying these configuration to JAVA_OPTS means it will try to do these are start/stop of the JVM which will result in an error in your logs saying something like “the port is already bound”

Add this to your “/etc/sysconfig/tomcat5”. Comment out any JAVA_OPTS (if they are redundant to what is below) you have uncommented and move them to this one if you have extra values. Exclude the all after the snmp line if you only want SNMP monitoring.

# WITH AUTHENTICATION

JAVA_OPTS="$JAVA_OPTS -Dcatalina.ext.dirs=$CATALINA_HOME/shared/lib:$CATALINA_HOME/common/lib Dcom.sun.management.snmp.port=9004 -Djava.awt.headless=true -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9003 -Dcom.sun.management.jmxremote.authenticate=true -Dcom.sun.management.jmxremote.password.file=$CATALINA_HOME/conf/jmxremote.password -Dcom.sun.management.jmxremote.access.file=$CATALINA_HOME/conf/jmxremote.access -Dcom.sun.management.jmxremote.ssl=false -Djava.rmi.server.hostname=adminsvr"

# NO AUTHENTICATION

JAVA_OPTS="$JAVA_OPTS -Dcatalina.ext.dirs=$CATALINA_HOME/shared/lib:$CATALINA_HOME/common/lib -Dcom.sun.management.snmp.port=9004 -Djava.awt.headless=true -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9003 -Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authenticate=false -Djava.rmi.server.hostname=adminsvr"

# Specify the RMI server name which is used when RMI connect comes inbound. This is sent to the client connecting to the RMI server.

-Djava.rmi.server.hostname=

# Disable use of authentication (options: true/false). True requires passwd file and access file.
-Dcom.sun.management.jmxremote.authenticate=false

# Specifies the location of the password file for JMX management access. (chmod 600, chown user/group )

-Dcom.sun.management.jmxremote.password.file=$CATALINA_HOME/conf/jmxremote.password

# Specifies the location of the access file for JMX management access. (chmod 600, chown user/group )

-Dcom.sun.management.jmxremote.access.file=$CATALINA_HOME/conf/jmxremote.access

# Disable SSL support (I have not played with getting it setup yet) (options: true/false)

-Dcom.sun.management.jmxremote.ssl=false

# Specify the JMX management port

-Dcom.sun.management.jmxremote.port=

# Enable local jmx management from the localhost

-Dcom.sun.management.jmxremote

# Suppose to enable headless connection where X is required…aka forward jconsole from the server to your X server.

-Djava.awt.headless=true

# Used to enable SNMP access to the JVM interface. If you use this you will need to enable the snmp.acl as well to set the community/access control

-Dcom.sun.management.snmp.port=

# /usr/java/jdk1.6.0_03/jre/lib/management/snmp.acl (you should already have a smp.acl.template installed as a part of your JDK) (chmod 600, chown user/group ). IF YOU HAVE A DIFFERENT JDK AND/OR LOCATION KEEP THAT IN MIND!!!

acl = {
    {
      communities = public
      access = read-only
      managers = localhost, 10.0.6.151
    }
  }

# $CATALINA_HOME/conf/jmxremote.access (chmod 600, chown user/group ).

opennms     readonly
monitorRole readonly
controlRole readwrite

# $CATALINA_HOME/conf/jmxremote.password (chmod 600, chown user/group ).

opennms     OpenNMS
monitorRole tomcat
controlRole tomcat

# Need to add a proxy line so snmp proxy will hit the JMX snmp port when we query the server for the SNMP info. Get system and JMX info in one query.
proxy -v 2c -c cotssnmp localhost:9004 .1.3.6.1.4.1.42

#
# Trouble shooting
#
A good set of tools to have are same JMX server/client java tools that you can run outside your tomcat JVM containers to make sure your system is accessable.

JMX Java Tutorial for MXBeans
jmx_examples.zip

Not sure what connect string to put in jconsole/etc. This is the default.

The default container name is jmxrmi
The JMX Service URL is:
service:jmx:rmi:///jndi/rmi://localhost:/jmxrmi