Migration of the database can be said to be a common thing in daily work, but if you accidentally make the database exploded, it is a big trouble, with the gradual increase in the amount of data, changes in the structure of the table, database all kinds of migration, upgrade is imperative, so, have a good migration tool can greatly improve work efficiency, It can also largely avoid the risk of errors or data damage or loss caused by manual operation.

Today, the migrant worker elder brother will introduce a such database migration artifact to everyone.

Flyway profile

Flyway is an agile tool for database migration. Adopts the Java development, supports all the compatible JDBC database. It is mainly used to upgrade the structure of your database and the data in it as your version of the application is upgraded.

Flyway characteristics
  • Simple: Easy to use and learn, database migration through different versions of SQL scripts.
  • Professional: Focus on database migration functionality, you don’t have to worry about any problems.
  • Powerful: support a variety of databases, with a large number of third party tools, support 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 the Flyway works

The first time you point to an empty database on Flyway:

It will look up the schema history table, and if the database is empty at this point, Flyway will create a history table of its own, and now you have a database that contains only the empty table, Flyway_Schema_History (the default).

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

Database migration is performed in order of version number:

After each migration is performed, the schema_history table updates the records accordingly

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

The incremental migration

Again proceed by version number:

The schema_history table updates the record accordingly

That’s the whole process! Each time a database needs to be modified, either in terms of structure (DDL) or reference data (DML), you simply create a new migration with a version number higher than the current one. The next time Flyway starts up, it will find it and update the database accordingly.

Refer to the address: https://flywaydb.org/document…

download

Download address: https://flywaydb.org/download

There are free and paid versions to choose from, so of course we chose the free version that works best, hahaha…

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
$*" > /usr/local/bin/flyway && chmod +x /usr/local/bin/flyway'
The directory structure
/flyway-7.11.0./flyway-7.11.0./flyway-7.11.0./flyway-7.11.0 - Flyway. CMD #Windows Executable File Exercises - JARS # Java-Based Migration (as JARS) Exercises - JRE Exercises - Lib Exercises - Licences Exercises - Readme.txt Exercises - SQL SQL migration 7 directories, 3 files

use

Use some of the concepts you need to know earlier
  • Version: Each change to the database is called a version
  • Migrating: Flyway updates a database structure from one version to another called migrating
  • Available migration: The version of the migration identified by Flyway’s file system
  • Migrations that have been applied: Migrations that Flyway has performed on the database
Command line
> flyway [options] command
Flyway is based on six basic commands
Validate migration availability create baseline version of existing database The migration action REPAIR # modifies the schema history table

Modifying configuration files

[root@centos7 ~]# cd. /flyway-7.11.0/conf/ [key 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 an SQL script for testing

There is also the issue of command specification, otherwise subsequent SQL scripts will not be executed.

  • Prefix: V version number for database migration, U version number for database rollback, R for repeatable database migration
  • Version: Flyway executes the database migration scripts in order of size
  • Separator: USES a double underscore Separator
  • Description: The descriptive text used to describe the migration script
  • Suffix:.sql file

Add a test SQL script under the SQL directory, which is a simple table creation statement.

[root@centos7 SQL]# PWD /root/flyway-7.11.0/ 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 =' test table ';
Starting the migration
[root@centos7 ~]# Flyway Migrate Flyway Teams Edition 7.11.0 by Redgate Database: JDBC: mysql: / / localhost: 3306 / mingongge (mysql 5.7) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Flyway Teams the 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, 028 s) 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 final tip is clear. You need to create a schema history table, so let’s 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 the 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

Performed 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 the 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, 225 s) 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)

Add data for migration testing. Again, 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, 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 the migration action

[root@centos7 ~]# Flyway Migrate Flyway Teams Edition 7.11.0 by Redgate Database: JDBC: mysql: / / localhost: 3306 / mingongge (mysql 5.7) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Flyway Teams the 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, 042 s) the Current version  of schema `mingongge`: Migrating schema 'mingongge' to version "1.0.1 - add data" Successfully applied 1 migration to schema 'mingongge', Migrating schema 'mingongge' to version "1.0.1 - add data" Successfully applied 1 migration to schema 'mingongge', Migrating schema 'mingongge' to version "1.0.1 - migrate Now at Version v1.0.1 (Execution Time 00:00.170s)

View status information

The rollback

Create a rollback script first, pay attention to the naming convention, this will make a lot of people cramp… Remember ！！！！

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

Note: The rollback must be to the existing version number, so pay attention to the version number. If you write 1.0 here, you will get an error:

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

rollback

[root@centos7 ~]# Flyway Undo Flyway Teams Edition 7.11.0 by Redgate Database: JDBC: mysql: / / localhost: 3306 / mingongge (mysql 5.7) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Flyway Teams the 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 the status

You can also see that the script for V1.0.1 is now Pending.

Other integration

Flyway also provides a Maven plug-in, which you can use in Maven, add Maven dependencies, and configure the information to connect data in the pom.xml file.

<! > < grouppid >org.flywaydb</ grouppid > <artifactId flyway-maven-plugin</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> mysqd-connector-java </artifactId> <version>8.0.15</version> </dependency> </dependencies> </plugin>

Write the script in the same way as above and place it in the resources/db/migration directory. Open the IDEA console and type MVN flyway:migrate. If BUILD SUCCESS appears, it will succeed.

Detailed configuration and usage, please refer to the official manual: https://flywaydb.org/document…

The use of more detailed guide everyone interested can refer to the official documentation: https://flywaydb.org/document…