Flyway使用(插件)_flyway plugin

Flyway简介以及依赖

简介

Flyway是一个开源的数据库迁移工具，可以实现数据库脚本的版本管理，开发人员能够轻松将数据库迁移管理集成到应用程序中。
Flyway提供了七个命令：Migrate, Clean, Info, Validate, Undo（社区版不支持）, Baseline 和Repair。
Flyway目前支持的数据库：Oracle, SQL Server (including Amazon RDS and Azure SQL Database), Azure Synapse (Formerly Data Warehouse), DB2, MySQL (including Amazon RDS, Azure Database & Google Cloud SQL), Aurora MySQL, MariaDB, Percona XtraDB Cluster, Testcontainers, PostgreSQL (including Amazon RDS, Azure Database, Google Cloud SQL, TimescaleDB, YugabyteDB & Heroku), Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, Snowflake, SQLite and Firebird。

依赖

pom依赖

       <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>5.2.4</version>
        </dependency>
1
2
3
4
5

插件

	<build>
        <plugins>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>5.2.4</version>
                <configuration>
                    <driver>com.mysql.cj.jdbc.Driver</driver>
                    <url>jdbc:mysql://ip:port/db_name?characterEncoding=utf-8&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai
                    </url>
                    <user>用户名</user>
                    <password>密码</password>
                </configuration>
            </plugin>

        </plugins>
    </build>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Flyway工作原理

1、首次发布脚本，会创建flyway_schema_history表，然后执行发布脚本，记录发布脚本的版本以及发布脚本的checksum值。
2、再次发布脚本时，首先会根据版本号校验已发布的脚本的checksum值是否与flyway_schema_history表中记录的checksum值是否一致，一致则校验通过；若不一致则报错。

Flyway插件命令

baseline：创建基线，适用于非空数据库首次使用Flyway
migrants：迁移，执行发布脚本
cleanup：清除
repair：修复，修复metadata表checksum值
undo：撤销，社区版不支持
validate：检查，根据版本号校验已发布脚本的checksum和flyway_schema_history表中记录的checksum值是否一致
info：输出flyway元数据信息

Flyway 工作场景

空数据库首次发布

1、场景

发布初始化脚本V1.0.0__init.sql（创建user表）

2、执行流程

3、执行结果
执行信息：

[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:5.2.4:migrate (default-cli) @ order-service ---
[INFO] Flyway Community Edition 5.2.4 by Boxfuse
[INFO] Database: jdbc:mysql://127.0.0.1:3306/local (MySQL 5.7)
[INFO] Successfully validated 1 migration (execution time 00:00.040s)
[INFO] Creating Schema History table: `local`.`flyway_schema_history`
[INFO] Current version of schema `local`: << Empty Schema >>
[INFO] Migrating schema `local` to version 1.0.0 - init
[WARNING] DB: Unknown table 'local.user' (SQL State: 42S02 - Error Code: 1051)
[INFO] Successfully applied 1 migration to schema `local` (execution time 00:00.193s)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.554 s
[INFO] Finished at: 2023-11-24T11:02:27+08:00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

flyway_schema_history表信息：

数据库：

修改历史sql脚本后发布

1、场景

修改已执行的SQL文件V1.0.0__init.sql，然后发布新SQL文件V1.0.1__upgrade.sql（user表中新增两条数据）

2、执行流程

3、执行结果
执行信息：

Validate failed: Migration checksum mismatch for migration version 1.0.0
-> Applied to database : -448731188
-> Resolved locally    : 1606388741
1
2
3

4、如何处理
执行repair命令

[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:5.2.4:repair (default-cli) @ order-service ---
[INFO] Flyway Community Edition 5.2.4 by Boxfuse
[INFO] Database: jdbc:mysql://127.0.0.1:3306/local (MySQL 5.7)
[INFO] Repair of failed migration in Schema History table `local`.`flyway_schema_history` not necessary. No failed migration detected.
[INFO] Repairing Schema History table for version 1.0.0 (Description: init, Type: SQL, Checksum: 1606388741)  ...
[INFO] Successfully repaired schema history table `local`.`flyway_schema_history` (execution time 00:00.022s).
[INFO] Manual cleanup of the remaining effects the failed migration may still be required.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.755 s
[INFO] Finished at: 2023-11-24T14:21:05+08:00
[INFO] ------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

再执行migrate命令，进行SQL文件发布

5、修复后执行结果

[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:5.2.4:migrate (default-cli) @ order-service ---
[INFO] Flyway Community Edition 5.2.4 by Boxfuse
[INFO] Database: jdbc:mysql://127.0.0.1:3306/local (MySQL 5.7)
[INFO] Successfully validated 2 migrations (execution time 00:00.017s)
[INFO] Current version of schema `local`: 1.0.0
[INFO] Migrating schema `local` to version 1.0.1 - upgrade
[INFO] Successfully applied 1 migration to schema `local` (execution time 00:00.019s)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.796 s
[INFO] Finished at: 2023-11-24T14:22:58+08:00
[INFO] ------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

数据库表信息

非空数据库首次发布

1、场景

发布脚本V1.0.1__upgrade.sql

这种情况如果直接发布SQL脚本，会发生报错，如下

Found non-empty schema(s) `local` without schema history table! Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

1
2

2、执行流程
需要先执行baseline命令，然后再执行migrate命令

3、执行结果
(1)执行baseline命令后



(2)执行migrate命令后

注意事项

1、发布脚本默认位置

2、脚本命名
例如V1.0.0__init.sql，“__”双下滑线为Flyway的特殊标识，“V”作为统一前缀，双下滑线前为版本号，双下滑线前为描述。
脚本发布时，脚本版本号要求递增，发布多个版本时，根据版本号从小到大的顺序执行。
3、info命令可以查看元数据
（1）

[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:5.2.4:info (default-cli) @ order-service ---
[INFO] Flyway Community Edition 5.2.4 by Boxfuse
[INFO] Database: jdbc:mysql://127.0.0.1:3306/local (MySQL 5.7)
[INFO] Schema version: 1.0.1
[INFO] 
[INFO] +-----------+---------+-------------+------+---------------------+---------+
| Category  | Version | Description | Type | Installed On        | State   |
+-----------+---------+-------------+------+---------------------+---------+
| Versioned | 1.0.0   | init        | SQL  | 2023-11-24 11:02:27 | Success |
| Versioned | 1.0.1   | upgrade     | SQL  | 2023-11-24 14:22:58 | Success |
+-----------+---------+-------------+------+---------------------+---------+

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.863 s
[INFO] Finished at: 2023-11-24T14:37:07+08:00
[INFO] ------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（2）

4、clean操作是删除数据库的所有内容，包括baseline之前的内容
5、插件配置中的特殊字符转义

6、尽量不要修改已发布的脚本文件

参考：
https://blog.csdn.net/weixin_44727769/article/details/131188398

https://www.jianshu.com/p/476f1189f647

https://www.jianshu.com/p/48f056326941