author：skate
time:2014/08/18
database(mysql)发版控制
the liquibase tool related database
一.installation & configration
二.advanced usage of the liquibase
三.frequently questions
二.advanced usage of the liquibase
1.实际场景模拟
实际需求：需要把不同环境的不同分支的数据库变更合并，并按需求应用或回滚到不同的db环境中，实现对db发版的管理
db的环境：dev,qa,sandbox,prod
规划db发版目录，如：
database
release-2014-05
db.changelog-master.xml
000_createtable.sql
001_init.sql
....
032_update.sql
release-2014-06
db.changelog-master.xml
000_createtable.sql
001_init.sql
....
192_update.sql
release-2014-07
db.changelog-master.xml
000_createtable.sql
001_init.sql
....
132_update.sql
db.changelog-master.xml是数据库变更的主文件，其包括每一个数据库的changeset文件，这样可以控制每个changeset的执行顺序和大小，如下是一个例子：
# more db.changelog-master.xml
# more 000_createtable.sql
--liquibase formatted sql
--changeset skate:release-2014-05_000_createtable.sql context:test
create table table3 (
id int(11) not null,
name varchar(255) not null,
primary key (id)
) engine=innodb;
alter table table3 change id id int( 11 ) auto_increment;
alter table table3 change name firstname varchar( 255 );
insert into table3 (id, firstname) values (null, 'name1'),(null, 'name2'), (null, 'name3');
--rollback drop table table3;
或者用xml文件
# more 000_createtable.xml
每个developer可以通过自己本机的liquibase把数据库的changset同步到dev环境
首先检查changelog是否有错误
[root@skatedb55 liquibase30]# sh liquibase --contexts=dev --defaultschemaname=test5 --loglevel=debug --changelogfile=/tmp/db.changelog-master.xml --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8 --username=skate --password=skate validate
liquibase home: /mysql/software/liquibase30
debug 8/19/14 2:45 pm:liquibase: connected to skate@127.0.0.1@jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8
debug 8/19/14 2:45 pm:liquibase: setting auto commit to false from true
info 8/19/14 2:46 pm:liquibase: reading from test5.databasechangelog
debug 8/19/14 2:46 pm:liquibase: executing query database command: select filename,author,id,md5sum,dateexecuted,orderexecuted,tag,exectype,description,comments from test5.databasechangelog order by dateexecuted asc, orderexecuted asc
debug 8/19/14 2:46 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for inputstream as 00ba919fb68564b7f5f8dab227e4f6a8
debug 8/19/14 2:46 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for 7:00ba919fb68564b7f5f8dab227e4f6a8: as 283384b19a5c2734d153462833903290
no validation errors found
liquibase 'validate' successful
[root@skatedb55 liquibase30]#
为本次更新创建tag（方便以后按需要回滚）
[root@skatedb55 liquibase30]# sh liquibase --contexts=dev --defaultschemaname=test5 --loglevel=debug --changelogfile=/tmp/db.changelog-master.xml --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8 --username=skate --password=skate tag release-2014-05_000_createtable
liquibase home: /mysql/software/liquibase30
debug 8/19/14 2:54 pm:liquibase: connected to skate@127.0.0.1@jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8
debug 8/19/14 2:54 pm:liquibase: setting auto commit to false from true
debug 8/19/14 2:54 pm:liquibase: executing query database command: select count(*) from test5.databasechangeloglock
debug 8/19/14 2:54 pm:liquibase: executing query database command: select locked from test5.databasechangeloglock where id=1
debug 8/19/14 2:54 pm:liquibase: lock database
debug 8/19/14 2:54 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 1, lockedby = 'skatedb55 (fe80:0:0:0:218:51ff:fe85:562a%3)', lockgranted = '2014-08-19 14:54:02.036' where id = 1 and locked = 0
info 8/19/14 2:54 pm:liquibase: successfully acquired change log lock
debug 8/19/14 2:54 pm:liquibase: executing query database command: select md5sum from test5.databasechangelog where md5sum is not null
debug 8/19/14 2:54 pm:liquibase: executing query database command: select count(*) from test5.databasechangelog
debug 8/19/14 2:54 pm:liquibase: executing execute database command: update test5.databasechangelog set tag = 'release-2014-05_000_createtable' where dateexecuted = (select max(dateexecuted) from (select dateexecuted from test5.databasechangelog) as x)
info 8/19/14 2:54 pm:liquibase: reading from test5.databasechangelog
debug 8/19/14 2:54 pm:liquibase: executing query database command: select filename,author,id,md5sum,dateexecuted,orderexecuted,tag,exectype,description,comments from test5.databasechangelog order by dateexecuted asc, orderexecuted asc
debug 8/19/14 2:54 pm:liquibase: release database lock
debug 8/19/14 2:54 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 0, lockedby = null, lockgranted = null where id = 1
info 8/19/14 2:54 pm:liquibase: successfully released change log lock
successfully tagged skate@127.0.0.1@jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8
liquibase 'tag' successful
you have new mail in /var/spool/mail/root
[root@skatedb55 liquibase30]#
执行数据库变更到dev数据库
[root@skatedb55 liquibase30]# sh liquibase --contexts=test --defaultschemaname=test5 --loglevel=debug --changelogfile=/tmp/db.changelog-master.xml --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8 --username=skate --password=skate update
liquibase home: /mysql/software/liquibase30
debug 8/19/14 2:39 pm:liquibase: connected to skate@127.0.0.1@jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8
debug 8/19/14 2:39 pm:liquibase: setting auto commit to false from true
debug 8/19/14 2:39 pm:liquibase: executing query database command: select count(*) from test5.databasechangeloglock
debug 8/19/14 2:39 pm:liquibase: executing query database command: select locked from test5.databasechangeloglock where id=1
debug 8/19/14 2:39 pm:liquibase: lock database
debug 8/19/14 2:39 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 1, lockedby = 'skatedb55 (fe80:0:0:0:218:51ff:fe85:562a%3)', lockgranted = '2014-08-19 14:39:26.745' where id = 1 and locked = 0
info 8/19/14 2:39 pm:liquibase: successfully acquired change log lock
debug 8/19/14 2:39 pm:liquibase: executing query database command: select md5sum from test5.databasechangelog where md5sum is not null
info 8/19/14 2:39 pm:liquibase: reading from test5.databasechangelog
debug 8/19/14 2:39 pm:liquibase: executing query database command: select filename,author,id,md5sum,dateexecuted,orderexecuted,tag,exectype,description,comments from test5.databasechangelog order by dateexecuted asc, orderexecuted asc
info 8/19/14 2:39 pm:liquibase: reading from test5.databasechangelog
debug 8/19/14 2:39 pm:liquibase: executing query database command: select filename,author,id,md5sum,dateexecuted,orderexecuted,tag,exectype,description,comments from test5.databasechangelog order by dateexecuted asc, orderexecuted asc
debug 8/19/14 2:39 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for inputstream as 00ba919fb68564b7f5f8dab227e4f6a8
debug 8/19/14 2:39 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for 7:00ba919fb68564b7f5f8dab227e4f6a8: as 283384b19a5c2734d153462833903290
debug 8/19/14 2:39 pm:liquibase: release database lock
debug 8/19/14 2:39 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 0, lockedby = null, lockgranted = null where id = 1
info 8/19/14 2:39 pm:liquibase: successfully released change log lock
liquibase update successful
you have new mail in /var/spool/mail/root
2.liquibase的一些高级用法
按指定tag rollback
[root@skatedb55 liquibase30]# sh liquibase --contexts=dev --defaultschemaname=test5 --loglevel=debug --changelogfile=/tmp/db.changelog-master.xml --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8 --username=skate --password=skate rollback release-2014-05_000_createtable
liquibase home: /mysql/software/liquibase30
debug 8/19/14 3:06 pm:liquibase: connected to skate@127.0.0.1@jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8
debug 8/19/14 3:06 pm:liquibase: setting auto commit to false from true
debug 8/19/14 3:06 pm:liquibase: executing query database command: select count(*) from test5.databasechangeloglock
debug 8/19/14 3:06 pm:liquibase: executing query database command: select locked from test5.databasechangeloglock where id=1
debug 8/19/14 3:06 pm:liquibase: lock database
debug 8/19/14 3:06 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 1, lockedby = 'skatedb55 (fe80:0:0:0:218:51ff:fe85:562a%3)', lockgranted = '2014-08-19 15:06:55.866' where id = 1 and locked = 0
info 8/19/14 3:06 pm:liquibase: successfully acquired change log lock
debug 8/19/14 3:07 pm:liquibase: executing query database command: select md5sum from test5.databasechangelog where md5sum is not null
info 8/19/14 3:07 pm:liquibase: reading from test5.databasechangelog
debug 8/19/14 3:07 pm:liquibase: executing query database command: select filename,author,id,md5sum,dateexecuted,orderexecuted,tag,exectype,description,comments from test5.databasechangelog order by dateexecuted asc, orderexecuted asc
debug 8/19/14 3:07 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for inputstream as 00ba919fb68564b7f5f8dab227e4f6a8
debug 8/19/14 3:07 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for 7:00ba919fb68564b7f5f8dab227e4f6a8: as 283384b19a5c2734d153462833903290
debug 8/19/14 3:07 pm:liquibase: release database lock
debug 8/19/14 3:07 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 0, lockedby = null, lockgranted = null where id = 1
info 8/19/14 3:07 pm:liquibase: successfully released change log lock
liquibase rollback successful
you have new mail in /var/spool/mail/root
按给定时间rollback
[root@skatedb55 liquibase30]# sh liquibase --contexts=dev --defaultschemaname=test5 --loglevel=debug --changelogfile=/tmp/db.changelog-master.xml --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8 --username=skate --password=skate rollbacktodate 2014-08-19 14:01:28
liquibase home: /mysql/software/liquibase30
debug 8/19/14 3:14 pm:liquibase: connected to skate@127.0.0.1@jdbc:mysql://localhost/test2?useunicode=true&characterencoding=utf-8
debug 8/19/14 3:14 pm:liquibase: setting auto commit to false from true
debug 8/19/14 3:14 pm:liquibase: executing query database command: select count(*) from test5.databasechangeloglock
debug 8/19/14 3:14 pm:liquibase: executing query database command: select locked from test5.databasechangeloglock where id=1
debug 8/19/14 3:14 pm:liquibase: lock database
debug 8/19/14 3:14 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 1, lockedby = 'skatedb55 (fe80:0:0:0:218:51ff:fe85:562a%3)', lockgranted = '2014-08-19 15:14:46.132' where id = 1 and locked = 0
info 8/19/14 3:14 pm:liquibase: successfully acquired change log lock
debug 8/19/14 3:14 pm:liquibase: executing query database command: select md5sum from test5.databasechangelog where md5sum is not null
info 8/19/14 3:14 pm:liquibase: reading from test5.databasechangelog
debug 8/19/14 3:14 pm:liquibase: executing query database command: select filename,author,id,md5sum,dateexecuted,orderexecuted,tag,exectype,description,comments from test5.databasechangelog order by dateexecuted asc, orderexecuted asc
debug 8/19/14 3:14 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for inputstream as 00ba919fb68564b7f5f8dab227e4f6a8
debug 8/19/14 3:14 pm:liquibase: /tmp/db.changelog-master.xml: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for 7:00ba919fb68564b7f5f8dab227e4f6a8: as 283384b19a5c2734d153462833903290
info 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: rolling back changeset:/tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate
debug 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: rolling back changeset: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate
debug 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: executing execute database command: drop table table3
debug 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: changeset /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate has been successfully rolled back.
debug 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: executing execute database command: delete from test5.databasechangelog where id='release-2014-05_000_createtable.sql' and author='skate' and filename='/tmp/database/release-2014-05/000_createtable.sql'
debug 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for inputstream as 00ba919fb68564b7f5f8dab227e4f6a8
debug 8/19/14 3:14 pm:liquibase: /tmp/database/release-2014-05/000_createtable.sql::release-2014-05_000_createtable.sql::skate: computed checksum for 7:00ba919fb68564b7f5f8dab227e4f6a8: as 283384b19a5c2734d153462833903290
debug 8/19/14 3:14 pm:liquibase: release database lock
debug 8/19/14 3:14 pm:liquibase: executing update database command: update test5.databasechangeloglock set locked = 0, lockedby = null, lockgranted = null where id = 1
info 8/19/14 3:14 pm:liquibase: successfully released change log lock
liquibase rollback successful
you have new mail in /var/spool/mail/root
[root@skatedb55 liquibase30]#
生成当前数据库状态的文档
[root@skatedb55 liquibase30]# sh liquibase --driver=com.mysql.jdbc.driver --changelogfile=/tmp/mysql_liquibase_changelog.xml --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test5?useunicode=true&characterencoding=utf-8 --username=skate --password=skate dbdoc /tmp/dbdoc/
liquibase home: /mysql/software/liquibase30
liquibase 'dbdoc' successful
you have new mail in /var/spool/mail/root
产生数据库的changelog
sh liquibase --driver=com.mysql.jdbc.driver --changelogfile=/tmp/mysql_liquibase_changelog.xml --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test5?useunicode=true&characterencoding=utf-8 --username=skate --password=skate generatechangelog
对比两个数据库
[root@skatedb55 liquibase30]# sh liquibase --loglevel=debug --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://10.10.0.117/test1?useunicode=true&characterencoding=utf-8 --username=test --password=test@df diff --referenceurl=jdbc:mysql://10.20.0.55/test5?useunicode=true&characterencoding=utf-8 --referenceusername=skate --referencepassword=skate
liquibase home: /mysql/software/liquibase30
debug 8/21/14 9:24 am:liquibase: connected to test@10.20.0.55@jdbc:mysql://10.10.0.117/test1?useunicode=true&characterencoding=utf-8
debug 8/21/14 9:24 am:liquibase: setting auto commit to false from true
debug 8/21/14 9:24 am:liquibase: connected to skate@10.20.0.55@jdbc:mysql://10.20.0.55/test5?useunicode=true&characterencoding=utf-8
debug 8/21/14 9:24 am:liquibase: setting auto commit to false from true
diff results:
reference database: skate@10.20.0.55 @ jdbc:mysql://10.20.0.55/test5?useunicode=true&characterencoding=utf-8 (default schema: test5)
comparison database: test@10.20.0.55 @ jdbc:mysql://10.10.0.117/test1?useunicode=true&characterencoding=utf-8 (default schema: test1)
product name: equal
product version: equal
missing catalog(s):
test5
unexpected catalog(s):
test1
changed catalog(s): none
missing column(s):
t2.a
t2.b
unexpected column(s): none
changed column(s): none
missing foreign key(s): none
unexpected foreign key(s): none
changed foreign key(s): none
missing index(s): none
unexpected index(s): none
changed index(s): none
missing primary key(s): none
unexpected primary key(s): none
changed primary key(s): none
missing schema(s): none
unexpected schema(s): none
changed schema(s): none
missing sequence(s): none
unexpected sequence(s): none
changed sequence(s): none
missing table(s):
t2
unexpected table(s): none
changed table(s): none
missing unique constraint(s): none
unexpected unique constraint(s): none
changed unique constraint(s): none
missing view(s): none
unexpected view(s): none
changed view(s): none
liquibase 'diff' successful
you have new mail in /var/spool/mail/root
[root@skatedb55 liquibase30]#
列出当前数据库的lock
[root@skatedb55 liquibase30]# sh liquibase --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test5?useunicode=true&characterencoding=utf-8 --username=skate --password=skate listlocks
liquibase home: /mysql/software/liquibase30
database change log locks for skate@127.0.0.1@jdbc:mysql://localhost/test5?useunicode=true&characterencoding=utf-8
- no locks
liquibase 'listlocks' successful
you have new mail in /var/spool/mail/root
[root@skatedb55 liquibase30]#
release当前数据库的所有的lock
[root@skatedb55 liquibase30]# sh liquibase --driver=com.mysql.jdbc.driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url=jdbc:mysql://localhost/test5?useunicode=true&characterencoding=utf-8 --username=skate --password=skate releaselocks
liquibase home: /mysql/software/liquibase30
successfully released all database change log locks for skate@127.0.0.1@jdbc:mysql://localhost/test5?useunicode=true&characterencoding=utf-8
liquibase 'releaselocks' successful
[root@skatedb55 liquibase30]#
--------end---------