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