資料庫版本管理工具Liquibase

Bohan Zhang發表於2019-12-04
資料庫UI

資料庫版本管理工具Liquibase

研發過程中經常涉及到資料庫變更，對錶結構的修復及對資料的修改，為了保證各環境都能正確的進行變更我們可能需要維護一個資料庫升級文件來儲存這些記錄，有需要升級的環境按文件進行升級。

這樣手工維護有幾個缺點：

  1. 無法保證每個環境都按要求執行
  2. 遇到問題不一定有相對的回滾語句
  3. 無法自動化

為了解決這些問題，我們進行了一些調研，主要調研物件是Liquibase和Flyway，我們希望通過資料庫版本管理工具實現以下幾個目標：

  1. 資料庫升級
  2. 資料庫回滾
  3. 版本標記

調研過程中發現Flyway據庫回滾功能是增值功能且實現邏輯是通過我們的升級指令碼來進行“智慧”降級，不符合我們目前的使用場景，Flyway相關的介紹可以看我早期的另一篇介紹：segmentfault.com/a/119000000…

Liquibase

Liquibase幫助團隊跟蹤、版本化及部署資料庫架構和邏輯修改

安裝

檢查JRE

$ java -version
java version "1.8.0_231"
Java(TM) SE Runtime Environment (build 1.8.0_231-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.231-b11, mixed mode)
複製程式碼

如果沒有安裝Java請自行安裝

安裝Liquibase

下載Liquibase-Version#-bin.tar.gz 檔案， 解壓壓縮包，將目錄新增到環境變數中

$ export PATH="/opt/liquibase-3.8.2:$PATH"
複製程式碼

這個命令重新啟動命令列就不會生效了，如果要保證一直可用需要將這個領先設定到.bashrc或者.zshrc中

通過執行幫助命令驗證安裝

$ liquibase --help
17:12:10.389 [main] DEBUG liquibase.resource.ClassLoaderResourceAccessor - Opening jar:file:/opt/liquibase-3.8.2/liquibase.jar!/liquibase.build.properties as liquibase.build.properties
Starting Liquibase at 星期三, 04 十二月 2019 17:12:10 CST (version 3.8.2 #26 built at Tue Nov 26 04:53:39 UTC 2019)


Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
...
複製程式碼

配置檔案

下面是以mysql為例的配置檔案

$ cat liquibase.properties
driver: com.mysql.cj.jdbc.Driver
classpath: ./mysql-connector-java-8.0.18.jar
url: jdbc:mysql://127.0.0.1/test
username: root
password: 123456
changeLogFile: myChangeLog.xml
複製程式碼

資料庫升級

建立myChangeLog.xml檔案，這個檔案用來記錄升級記錄升級資訊，初始化的內容

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

</databaseChangeLog>
複製程式碼

Liquibase支援通過SQL描述的方式來建立資料庫

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1.0" author="bohan">
        <sql>
        CREATE TABLE `deparment` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        </sql>
    </changeSet>
</databaseChangeLog>
$ liquibase update
Liquibase Community 3.8.2 by Datical
Liquibase: Update has been successful.
複製程式碼

通過執行liquibase update進行升級，升級後的資料庫如下，已經為我們建立了資料庫，同時Liquibase生成了兩個表用來管理資料庫升級記錄

$ mysql -h 127.0.0.1 -uroot -p123456 test -e "show tables;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+
| Tables_in_test        |
+-----------------------+
| DATABASECHANGELOG     |
| DATABASECHANGELOGLOCK |
| deparment             |
+-----------------------+
複製程式碼

繼續執行升級

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1.0" author="bohan">
        <sql>
        CREATE TABLE `deparment` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        </sql>
    </changeSet>
    <changeSet id="1.1" author="bohan">
        <sql>
        insert into deparment values(1, "test");
        </sql>
    </changeSet>
</databaseChangeLog>
$ liquibase update
Liquibase Community 3.8.2 by Datical
Liquibase: Update has been successful.
$ mysql -h 127.0.0.1 -uroot -p test -e "select * from deparment;"
Enter password:
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+
複製程式碼

資料如預期被新增

通過SQL檔案

資料庫變更也可以通過sql檔案形式引用，避免myChangeLog.xml檔案過大

<changeSet id="1.1" author="bohan">
    <sqlFile path="./update_deparment_name.sql"></sqlFile>
</changeSet>
複製程式碼

資料庫回滾

liquibase --help
Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
 rollbackCount <value>          Rolls back the last <value> change sets
                                applied to the database
複製程式碼

我們來執行rollbackCount進行回滾

$ liquibase rollbackCount 1
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.0::bohan
Unexpected error running Liquibase: No inverse to liquibase.change.core.RawSQLChange created
For more information, please use the --logLevel flag
複製程式碼

提示沒有回滾SQL，修改我們的myChangeLog.xml

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1.0" author="bohan">
        <sql>
        CREATE TABLE `deparment` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        </sql>
        <rollback>
        DROP TABLE deparment;
        </rollback>
    </changeSet>
    <changeSet id="1.1" author="bohan">
        <sql>
        insert into deparment values(1, "test");
        </sql>
        <rollback>
        DELETE FROM deparment WHERE id = 1;
        </rollback>
    </changeSet>
</databaseChangeLog>
複製程式碼

執行回滾，發現已經沒有新增的記錄了

liquibase rollbackCount 1
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.1::bohan
Liquibase: Rollback has been successful.
$ mysql -h 127.0.0.1 -uroot -p test -e "select * from deparment;"
Enter password:
複製程式碼

再次執行，資料庫也如預期被刪除

$ liquibase rollbackCount 1
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.0::bohan
Liquibase: Rollback has been successful.
$ mysql -h 127.0.0.1 -uroot -p test -e "show tables;"
Enter password:
+-----------------------+
| Tables_in_test        |
+-----------------------+
| DATABASECHANGELOG     |
| DATABASECHANGELOGLOCK |
+-----------------------+
複製程式碼

版本標記

Liquibase提供了完善的標籤功能，經過剛剛的回滾到上一次操作後我們目前只執行了ID為1.0的變更

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | NULL | 3.8.2     | NULL     | NULL   | 5515718387    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 row in set (0.00 sec)
複製程式碼

在實際開發中，我們升級版本時通常會需要同時執行多個變更，如果變數存在問題需要回滾時按數量回滾就比較麻煩了，我們需要對我們的變更進行標籤標記，下面可能用到的命令如下：

liquibase --help
11:21:31.994 [main] DEBUG liquibase.resource.ClassLoaderResourceAccessor - Opening jar:file:/opt/liquibase-3.8.2/liquibase.jar!/liquibase.build.properties as liquibase.build.properties
Starting Liquibase at 星期四, 05 十二月 2019 11:21:31 CST (version 3.8.2 #26 built at Tue Nov 26 04:53:39 UTC 2019)


Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
 rollback <tag>                 Rolls back the database to the the state is was
Maintenance Commands
 tag <tag string>          'Tags' the current database state for future rollback
 tagExists <tag string>    Checks whether the given tag is already existing
複製程式碼

針對當前資料庫，我們通過liquibase tag進行打標籤操作

$ liquibase tag v1.0
Liquibase Community 3.8.2 by Datical
Successfully tagged 'root@172.17.0.1@jdbc:mysql://127.0.0.1/test'
Liquibase command 'tag' was executed successfully.
複製程式碼

檢視記錄發現ID為1.0的記錄TAG中已設定為v1.0，符合我們的預期

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | v1.0 | 3.8.2     | NULL     | NULL   | 5515718387    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 row in set (0.00 sec)
複製程式碼

執行更新後如果需要回滾通過liquibase rollback v1.0即可

$ liquibase update
Liquibase Community 3.8.2 by Datical
Liquibase: Update has been successful.

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | v1.0 | 3.8.2     | NULL     | NULL   | 5515718387    |
| 1.1 | bohan  | myChangeLog.xml | 2019-12-05 03:28:06 |             2 | EXECUTED | 8:695a5ec0b2b3ddc4a9beeeca530adebc | sql         |          | NULL | 3.8.2     | NULL     | NULL   | 5516486105    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
2 rows in set (0.00 sec)


$ liquibase rollback v1.0
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.1::bohan
Liquibase: Rollback has been successful.

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | v1.0 | 3.8.2     | NULL     | NULL   | 5515718387    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 row in set (0.00 sec)
複製程式碼

相關文章