Gheek.net

February 28, 2011

Liquibase…A RCS for SQL

Filed under: database, java, Liquibase, mysql — lancevermilion @ 6:14 pm

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
Advertisements

Create a free website or blog at WordPress.com.