liquibase 命令行

配置信息

liquibase.properties

1
2
3
4
5
6
7
driver: com.mysql.cj.jdbc.Driver
classpath: ./mysql-connector-java-8.0.11.jar
url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC&autoReconnect=true&allowPublicKeyRetrieval=true&useOldAliasMetadataBehavior=true
username: root
password: abc123
changeLogFile: myChangeLog.xml
liquibase.hub.mode=off

  1. mysql-connector-java-8.0.11.jar 提前下载好放到同级目录下面.

  2. 如果使用 liquibase diff 需要增加 reference 配置节点

    1
    2
    3
    referenceUrl: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC&autoReconnect=true&allowPublicKeyRetrieval=true&useOldAliasMetadataBehavior=true
    referenceUsername: root
    referencePassword: abc123

myChangeLog.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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.1.xsd">
  
  <!-- 
  1.file：表示将此路径下 /sql/001_create_person_table.sql 目录中的 changelog 文件引入。
  假设 db 目录下还有其他模块（目录），继续通过 <include> 或 <includeAll> 元素引入即可。 
  2.relativeToChangelogFile：为 true 时表示使用的是相对路径 而不是classpath
  -->
  <include file="./sql/create_table.xml" relativeToChangelogFile="true"></include>
  <include file="./sql/init_data.sql" relativeToChangelogFile="true"></include>
  
</databaseChangeLog>
  1. include 可以设置多个, 现在这个配置create_table.xml 是表结构, init_data.sql是数据.

create_table.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?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.1.xsd">

  <changeSet id="1" author="zhaopeisheng">
        <createTable tableName="department">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="varchar(1)" defaultValue="Y"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

  1. id 和 author 必填

init_data.sql

1
2
3
4
5
6
-- liquibase formatted sql
-- changeset jack:2022_04_2
insert into department values(1, '研发部', 'Y');
insert into department values(2, '销售部', 'Y');
-- changeset tom:2022_04_22
insert into department values(3, '后勤部', 'Y');
  1. 文件开始必须以 -- liquibase formatted sql 开头
  2. 每一个changeset 格式 -- changeset author:id

整体项目结构图

设置数据库

更新

1
liquibase update

执行日志

查看数据库

前两张表databasechangelog databasechangeloglock 是liquibase的业务表.

给当前版本数据库打标签

1
2
3
4
5
6
7
8
9
10
11
liquibase tag v1.0
liquibase history
+---------------+----------------+----------------------+------------------+--------------+------+
| Deployment ID | Update Date    | Changelog Path       | Changeset Author | Changeset ID | Tag  |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/create_table.xml | zhaopeisheng     | 1            |      |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/init_data.sql    | jack             | 2022_04_2    |      |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/init_data.sql    | tom              | 2022_04_22   | v1.0 |
+---------------+----------------+----------------------+------------------+--------------+------+

新增changeset记录

这种场景是项目在开发过程中新增表了.

在create_table.xml 记录增加新表

1
2
3
4
5
6
7
8
9
10
<changeSet id="2" author="zhaopeisheng">
     <createTable tableName="org">
         <column name="id" type="int">
             <constraints primaryKey="true" nullable="false"/>
         </column>
         <column name="name" type="varchar(50)">
             <constraints nullable="false"/>
         </column>
      </createTable>
 </changeSet>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
liquibase update
liquibase tag v2.0
liquibase history

+---------------+----------------+----------------------+------------------+--------------+------+
| Deployment ID | Update Date    | Changelog Path       | Changeset Author | Changeset ID | Tag  |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/create_table.xml | zhaopeisheng     | 1            |      |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/init_data.sql    | jack             | 2022_04_2    |      |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/init_data.sql    | tom              | 2022_04_22   | v1.0 |
+---------------+----------------+----------------------+------------------+--------------+------+


+---------------+----------------+----------------------+------------------+--------------+------+
| Deployment ID | Update Date    | Changelog Path       | Changeset Author | Changeset ID | Tag  |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4358382582    | 24-4-29 下午6:39 | sql/create_table.xml | zhaopeisheng     | 2            | v2.0 |

回滚版本

回滚版本必须有 tag 标签的.

1
2
3
4
5
6
7
8
9
10
11
liquibase rollback v1.0
liquibase history
+---------------+----------------+----------------------+------------------+--------------+------+
| Deployment ID | Update Date    | Changelog Path       | Changeset Author | Changeset ID | Tag  |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/create_table.xml | zhaopeisheng     | 1            |      |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/init_data.sql    | jack             | 2022_04_2    |      |
+---------------+----------------+----------------------+------------------+--------------+------+
| 4357274202    | 24-4-29 下午6:21 | sql/init_data.sql    | tom              | 2022_04_22   | v1.0 |
+---------------+----------------+----------------------+------------------+--------------+------+

v2.0 新增的表被删除掉了

根据数据库逆向生成changelog.xml

把原来的myChangeLog.xml 改个名字. liquibase 是根据liquibase.properties 里面的changeLogFile 来生成的文件, 还原的数据库是liquibase.properties 配置的数据库.

1
liquibase generate-changelog

新生成的表结构xml, 这个文件可以直接复制到 create_table.xml,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
    <changeSet author="zhaopeisheng (generated)" id="1714362443795-1">
        <createTable tableName="department">
            <column name="id" type="INT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(50)">
                <constraints nullable="false"/>
            </column>
            <column defaultValue="Y" name="active" type="VARCHAR(1)"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

用生成的xml 还原数据库

1
2
3
4
5
6
7
8
9
10
11
12
liquibase update
liquibase history

+---------------+----------------+----------------------+--------------------------+-----------------+-----+
| Deployment ID | Update Date    | Changelog Path       | Changeset Author         | Changeset ID    | Tag |
+---------------+----------------+----------------------+--------------------------+-----------------+-----+
| 4362687615    | 24-4-29 下午7:51 | sql/create_table.xml | zhaopeisheng (generated) | 1714362443795-1 |     |
+---------------+----------------+----------------------+--------------------------+-----------------+-----+
| 4362687615    | 24-4-29 下午7:51 | sql/init_data.sql    | jack                     | 2022_04_2       |     |
+---------------+----------------+----------------------+--------------------------+-----------------+-----+
| 4362687615    | 24-4-29 下午7:51 | sql/init_data.sql    | tom                      | 2022_04_22      |     |
+---------------+----------------+----------------------+--------------------------+-----------------+-----+

代码地址

https://gitee.com/zhaops_learning/spring-cloud-learning/tree/master/mysql/liquibaseCmd

liquibase
#mysql #liquibase
liquibase 命令行
https://zhaops-hub.github.io/2024/04/29/mysql/liquibase/liquibase 命令行/
作者
赵培胜
发布于
2024年4月29日
许可协议