Is the database migration exploded? ? Come try this super awesome open source tool

Database migration can be said to be a common occurrence in daily work, but if you accidentally blow up the database, it will be a big trouble. With the gradual increase in data volume and changes in table structure, various types of database migration and upgrades are It is imperative, so having a good migration tool can greatly improve work efficiency, and to a large extent avoid the risk of errors or data damage or loss caused by manual operations.

Today, Migrant Workers will introduce you to such a database migration artifact.

Introduction to Flyway

flyway is an agile tool for database migration. It is developed in Java and supports all JDBC-compatible databases. It is mainly used to upgrade your database structure and the data in it while your application version is constantly upgrading.

Flyway features

• Simple: easy to use and learn, database migration is achieved through different versions of SQL scripts.
• Professional: Focus on the database migration function, you don't need to worry about any problems.
• Powerful function: supports a variety of databases, has a large number of third-party tools, and supports CI/DI.

Work platform

Windows, macOS, Linux, Docker, Java and Android

Supported build tools

Maven and Gradle

Supported databases

Oracle, SQL Server, DB2, MySQL, Aurora MySQL, MariaDB, Percona XtraDB cluster, PostgreSQL, Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, SQLite, Firebird

How Flyway works

When you first point to an empty database to Flyway:

It will look for the schema history table. If the database is empty at this time, FlyWAY will create a history table by itself, and now there is a database containing only the empty table flyway_schema_history (default).

flyway_schema_history This table is used to track the state of the database.

The migration of the database is performed in sequence according to the version number:

The schema_history table will update records accordingly after each migration is executed

If you migrate again later, Flyway will scan the application file system, classpath, and history table again. If the version number is lower than or equal to the current version number, the migration operation will be ignored.

Incremental migration

Still proceed by version number:

The schema_history history table will update records accordingly

The whole process is like this! Every time you need to modify the database, whether it is structure (DDL) or reference data (DML), you only need create a new migration with a version number higher than the current version number The next time Flyway starts, it will find it and upgrade the database accordingly.

Reference address: https://flywaydb.org/documentation/getstarted/how

download

Download : 160ecfab397082 https://flywaydb.org/download

There are free and paid versions to choose from, of course we choose the best free version, hahahaha. . . .

Linux system

wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/7.11.0/flyway-commandline-7.11.0-linux-x64.tar.gz | tar xvz && ln -s `pwd`/flyway-7.11.0/flyway /usr/local/bin 

Docker

sh -c 'echo "docker run --rm flyway/flyway:7.11.0 $*" > /usr/local/bin/flyway && chmod +x /usr/local/bin/flyway'

Directory Structure

[root@centos7 ~]# tree -L 1 ./flyway-7.11.0
./flyway-7.11.0
├── conf       #配置文件
├── drivers    #JDBD驱动程序
├── flyway     #macOS/Linux 可执行文件
├── flyway.cmd #Windows 可执行文件
├── jars       #基于 Java 的迁移（作为 jars）
├── jre
├── lib
├── licenses
├── README.txt
└── sql       #SQL 迁移

7 directories, 3 files

use

Some concepts you need to understand before using

• Version: Every change to the database can be called a version
• Migration: Flyway updates the database structure from one version to another version called migration
• Available migration: the migration version identified by Flyway's file system
• Migration that has been applied: The migration that Flyway has performed on the database

Command line use

> flyway [options] command

flyway is mainly based on 6 basic commands

migrate  #迁移数据库
clean    #清除所有配置
info     #打印迁移相关的详细信息与状态信息
validate #验证迁移的可用性
baseline #为现有数据库创建基准版本，高于基准版本的才会被执行迁移动作
repair   #修改 schema history 表

Modify the configuration file

[root@centos7 ~]# cd ./flyway-7.11.0/conf/
[root@centos7 conf]# ll
total 24
-rw-r--r-- 1 root root 22943 Jul  1  2021 flyway.conf
[root@centos7 conf]# vim flyway.conf
flyway.url=jdbc:mysql://localhost:3306/mingongge?useUnicode=true
flyway.user=root
flyway.password=123456

Prepare a sql script for testing

Here we have to mention the issue of command specifications, otherwise the subsequent sql scripts cannot be executed.

• Prefix: V is used for the version number of database migration, U is used for the version number of database rollback, and R represents a repeatable database migration
• Version (version number): Flyway will execute the database migration script in the order of the size of the version number
• Separator (separator): use double underscore separator
• Description: Descriptive text used to describe the migration script
• Suffix (suffix): .sql file

Add a test sql script under the sql directory, here is a simple statement to create a table.

[root@centos7 sql]# pwd
/root/flyway-7.11.0/sql
[root@centos7 sql]# cat V1.0__Create_test_table.sql
CREATE TABLE `test_table`
(
  `id`          bigint(10)   NOT NULL,
  `username`    varchar(64)  DEFAULT NULL,
  `password`    varchar(64)  DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT ='测试表';

Start migration

[root@centos7 ~]# flyway migrate
Flyway Teams Edition 7.11.0 by Redgate
Database: jdbc:mysql://localhost:3306/mingongge (MySQL 5.7)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://flywaydb.org/?ref=v7.11.0_teams
----------------------------------------
Successfully validated 0 migrations (execution time 00:00.028s)
WARNING: No migrations found. Are your locations set up correctly?
ERROR: Found non-empty schema(s) `mingongge` but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

The last tip is very clear, you need to create a schema history table, so well, create one.

[root@centos7 ~]# flyway baseline
Flyway Teams Edition 7.11.0 by Redgate
Database: jdbc:mysql://localhost:3306/mingongge (MySQL 5.7)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://flywaydb.org/?ref=v7.11.0_teams
----------------------------------------
Creating Schema History table `mingongge`.`flyway_schema_history` with baseline ...
Successfully baselined schema with version: 1

Execute again

[root@centos7 sql]# flyway migrate
Flyway Teams Edition 7.11.0 by Redgate
Database: jdbc:mysql://localhost:3306/mingongge (MySQL 5.7)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://flywaydb.org/?ref=v7.11.0_teams
----------------------------------------
Successfully validated 1 migration (execution time 00:00.225s)
Migrating schema `mingongge` to version "1.0 - Create test table"
Successfully applied 1 migration to schema `mingongge`, now at version v1.0 (execution time 00:00.356s)

Increase data for migration testing. Still create the script V1.0.1__add_data.sql in the sql directory

[root@centos7 ~]# cat flyway-7.11.0/sql/V1.0.1__add_data.sql 
INSERT INTO test_table (id,username, PASSWORD)
VALUES ('001','test1', '123456');
INSERT INTO test_table (id,username, PASSWORD)
VALUES ('002','test2', '123456');

Check the status information at this time, you can find the following:

Perform migration actions

[root@centos7 ~]# flyway migrate
Flyway Teams Edition 7.11.0 by Redgate
Database: jdbc:mysql://localhost:3306/mingongge (MySQL 5.7)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://flywaydb.org/?ref=v7.11.0_teams
----------------------------------------
Successfully validated 2 migrations (execution time 00:00.042s)
Current version of schema `mingongge`: 1.0
Migrating schema `mingongge` to version "1.0.1 - add data"
Successfully applied 1 migration to schema `mingongge`, now at version v1.0.1 (execution time 00:00.170s)

View status information

Rollback

First create a rollback script, pay attention to the naming convention, this will make a lot of people step on the pit. . . . Remember! ! ! !

[root@centos7 sql]# cat U1.0.1__delete_data.sql 
DELETE FROM test_table;

Note : The rollback here must be rolled back to the existing version number, so pay attention to the version number when naming it. If it is written as 1.0 here, an error will appear:

ERROR: Unable to undo migration to version 1.0.1 as no corresponding undo migration has been found.

Perform rollback

[root@centos7 ~]# flyway undo
Flyway Teams Edition 7.11.0 by Redgate
Database: jdbc:mysql://localhost:3306/mingongge (MySQL 5.7)
----------------------------------------
Flyway Teams features are enabled by default for the next 27 days. Learn more at https://flywaydb.org/?ref=v7.11.0_teams
----------------------------------------
Current version of schema `mingongge`: 1.0.1
Undoing migration of schema `mingongge` to version 1.0.1 - delete data
Successfully undid 1 migration to schema `mingongge`, now at version v1.0 (execution time 00:00.256s)

Check status

It can also be seen from the figure above that the script of version V1.0.1 is currently in the Pending state.

Other integration

Flyway also provides a Maven plug-in. You can also use this tool in Maven to add maven dependencies and configure the connection data information in the pom.xml file.

<!--引入flyway插件-->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.11.0</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/mingongge</url>
        <user>root</user>
        <password>root</password>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>
</plugin>

Then write the script in the same way as the above method, and place it in the resources/db/migration directory. Open the idea console, enter mvn flyway:migrate, the execution is successful when BUILD SUCCESS appears.

For detailed configuration and usage, please refer to the official manual: https://flywaydb.org/documentation/usage/maven/

For more detailed usage guides, if you are interested, please refer to the official document: https://flywaydb.org/documentation/