Database(Mysql)发版控制一

author：skate
time:2014/08/06

 

Database(Mysql)发版控制

The Liquibase Tool related Database

一.Installation & Configration
二.Advanced Usage of the Liquibase
三.Frequently Questions

一.Installation & Configration

Introduction

The Liquibase is a open source that may trace、manage and apply database changes，and may control database vesion.Through using a distributed locking system,and allow one process to update the database at a time，The other processes will simply wait until the lock has been released.So many developers can work together in the mean time.

The liquibase has lots of characteritic:
A.Supported lots of databases
B.Keeping the datbase change history
C.Provide for the function rollbacked database change
D.Generate database change document

Test Environment
ip: 10.20.0.56
os ver: Centos6.5
liquibase ver: 3.0.8
work-directory: /u01/liquibase

一.Installation & Configration

1. Requirements
   Liquibase 2.x requires Java 1.5+. Liquibase 3.x requires Java 1.6+.

Java Installation & Configuration（Windows）

Download & Installation JDK
 URL:http://www.oracle.com/technetwork/java/javase/downloads/index.html
 
Configuration Environment Variables

select‘The property of My Computer'->'Grade'->'Environment Variables'

setup new a variables in system variables.
A) variable name:JAVA_HOME  variable value：C:\Program Files (x86)\Java\jre7，and append this value to variable PATH of system variable

if the installation is success ,so select start->run->cmd ,excute "java/javac" get output of helpful,eg:

C:\Users\hyd>java -version
java version "1.7.0_60"
Java(TM) SE Runtime Environment (build 1.7.0_60-b19)
Java HotSpot(TM) Client VM (build 24.60-b09, mixed mode, sharing)

 
Java Installation & Configuration（Linux)
 
# yum install java-1.7.0-openjdk
# java -version
java version "1.7.0_65"
OpenJDK Runtime Environment (rhel-2.5.1.2.el6_5-x86_64 u65-b17)
OpenJDK 64-Bit Server VM (build 24.65-b04, mixed mode)
#

2. Liquibase Installation

2.1 Download
    URL:http://www.liquibase.org/download/index.html

2.2 Installation

# mkdir -p /u01/liquibase
# cd /u01/liquibase/
# unzip liquibase-3.0.8-bin.zip
# ll
total 6328
drwxrwxrwx 4 root root    4096 Dec 12  2013 javadoc
drwxrwxrwx 2 root root    4096 Dec 12  2013 lib
-rw-r--r-- 1 root root   11358 Dec 12  2013 LICENSE.txt
-rw-r--r-- 1 root root    1106 Dec 12  2013 liquibase
-rw-r--r-- 1 root root 5238829 Aug  7 15:43 liquibase-3.0.8-bin.zip
-rw-r--r-- 1 root root     647 Dec 12  2013 liquibase.bat
-rw-r--r-- 1 root root 1196748 Dec 12  2013 liquibase.jar
-rw-r--r-- 1 root root    7031 Dec 12  2013 liquibase.spec

 

2.3 Configuration Environment Variables
Linux:(user root)

# vi /root/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
LIQUIBASE_HOME=/u01/liquibase
export LIQUIBASE_HOME
export PATH

# source /root/.bash_profile
# echo $LIQUIBASE_HOME
/u01/liquibase

Windows：

The value of environment variable LIQUIBASE_HOME append into system variable PATH.

 

2.4 download the java driver to the lib directory of the liquibase installation,eg:
# ll
total 1076
-rw-r--r-- 1 root root 819803 Aug  7 15:57 mysql-connector-java-5.1.17.jar
-rw-r--r-- 1 root root    188 Dec 12  2013 README.txt
-rw-r--r-- 1 root root 273449 Oct  4  2013 snakeyaml-1.13.jar
mysql-connector-java-5.1.17.jar

3. Simple Usage
If you want to use liquibase,and must do the belowing.The belowing linux environmental as an example.
 
1）Create database changelog file
2）Create changeset in the changelog file
3) Through the command line to run the database changeset

3.1 Create database changelog file "/tmp/mysql-liquibase.sql"

# more /tmp/mysql-liquibase.sql
--liquibase formatted sql
--changeset skate:D3R1520140706-1 

 CREATE TABLE skate-tab ( 
   id int(11) NOT NULL, 
   name varchar(255) NOT NULL, 
   PRIMARY KEY (id) 
 ) ENGINE=inndodb; 

 ALTER TABLE  skate-tab CHANGE  id  id INT( 11 ) AUTO_INCREMENT; 

 ALTER TABLE  skate-tab CHANGE  name  firstname VARCHAR( 255 ); 

 INSERT INTO skate-tab (id, firstname) VALUES (NULL, 'name1'),(NULL, 'name2'), (NULL, 'name3'); 

--rollback drop table skate-tab;

 

3.2 Checks the changelog for error
#sh liquibase --defaultSchemaName=test --logLevel=debug --changeLogFile=/tmp/mysql-liquibase.sql --driver=com.mysql.jdbc.Driver --classpath=/u01/liquibase/lib/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://10.20.0.55/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate validate

 

3.3 Update database to current version
#sh liquibase --defaultSchemaName=test --logLevel=debug --changeLogFile=/tmp/mysql-liquibase.sql --driver=com.mysql.jdbc.Driver --classpath=/u01/liquibase/lib/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://10.20.0.55/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate update

3.4 Rollback database based time
# sh liquibase --defaultSchemaName=test --logLevel=debug --changeLogFile=/tmp/mysql-liquibase.sql --driver=com.mysql.jdbc.Driver --classpath=/u01/liquibase/lib/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://10.20.0.55/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate rollbackToDate 2014-08-07 16:53:04

OR

Rollbak based tag

Set up a tag first
# sh liquibase --defaultSchemaName=test --logLevel=debug --changeLogFile=/tmp/mysql-liquibase.sql --driver=com.mysql.jdbc.Driver --classpath=/u01/liquibase/lib/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://10.20.0.55/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate  tag D3R1520140706-2

 

Run the database changeset
# sh liquibase --defaultSchemaName=test --logLevel=debug --changeLogFile=/tmp/mysql-liquibase.sql --driver=com.mysql.jdbc.Driver --classpath=/u01/liquibase/lib/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://10.20.0.55/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate  update

 

Rollback database to the tag
#sh liquibase --defaultSchemaName=test --logLevel=debug --changeLogFile=/tmp/mysql-liquibase.sql --driver=com.mysql.jdbc.Driver --classpath=/u01/liquibase/lib/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://10.20.0.55/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate rollback  D3R1520140706-2

--------end--------