Go to Qiita Advent Calendar 2024 Top
LoginSignup
8
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@kazzool(kazzool)

GradleでFlywayでMySQLをマイグレーション

Posted at

#概要
タイトル的なことをやろうと思ったら、新しくてちょうど良い感じの「こと始め的」な記事が見つけられなかったため、やってみたついでにまとめてみた。
→ MySQLDBをFlywayでマイグレーションする専用プロジェクト(by Gradle)を作ってみた。

#経緯

  • 趣味開発にFlywayのDBマイグレーションを導入したかった
  • SpringBootのアプリに組み込みじゃなくて専用プロジェクトにしたかった
  • コマンドラインからやる気はなかった
  • そういえば「./gradlew flywayMigrate」ってなんか見たことあるぞ

#環境

  • OS : macOS Mojave(10.14.6)
  • Gradle : 5.2.1
  • Flyway : 6.2.2
  • MySQL : 5.7.24

#前程知識

#やってみた💪
###MySQLの準備
MySQL側で最低限の準備をする。
schema、userを作成し、userに権限を付与。

mysql
mysql> CREATE DATABASE localdb DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| localdb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE USER 'flywayuser'@'localhost' IDENTIFIED BY 'flywaypass';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host FROM mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| flywayuser    | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

mysql> GRANT ALL ON localdb.* TO 'flywayuser'@'localhost' IDENTIFIED BY 'flywaypass';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'flywayuser'@'localhost';
+-----------------------------------------------------------------+
| Grants for flywayuser@localhost                                 |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'flywayuser'@'localhost'                  |
| GRANT ALL PRIVILEGES ON `localdb`.* TO 'flywayuser'@'localhost' |
+-----------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

###Flyway by Gradleのプロジェクト作成
プロジェクト用ディレクトリ(今回はflyway-skeleton)配下でgradle initする。
色々聞かれたが全部デフォルトで突破した。

console
$ gradle init
Starting a Gradle Daemon (subsequent builds will be faster)

Select type of project to generate:
  1: basic
  2: cpp-application
  3: cpp-library
  4: groovy-application
  5: groovy-library
  6: java-application
  7: java-library
  8: kotlin-application
  9: kotlin-library
  10: scala-library
Enter selection (default: basic) [1..10] 

Select build script DSL:
  1: groovy
  2: kotlin
Enter selection (default: groovy) [1..2] 

Project name (default: flyway-skeleton): 

BUILD SUCCESSFUL in 20s
2 actionable tasks: 2 executed
$

直下に生成されたbuild.gradleの中身を書く。

build.gradle
buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        /*flywayのバージョンが古いとgradleのバージョンに対応してない(？)みたいで動かず、ひとまず最新に*/
        classpath 'org.flywaydb:flyway-gradle-plugin:6.2.2'
        /*MySQL5.7に対応してそうなバージョンをググッて適当に設定*/
        classpath 'mysql:mysql-connector-java:5.1.45'
    }
}

plugins {
    id "java"
    id "org.flywaydb.flyway" version "6.2.2"
}

repositories {
    mavenCentral()
}

flyway {
    /*さっきMySQLで準備したやつ*/
    url = 'jdbc:mysql://localhost:3306/localdb?&allowPublicKeyRetrieval=true&useSSL=false'
    user = 'flywayuser'
    password = 'flywaypass'
}

###SQLの準備
マイグレーション用SQLの置き場所を作る。
デフォルトで./src/main/resources/db/migrationらしい。

console
$ mkdir -p src/main/resources/db/migration
$
$ du
120     ./gradle/wrapper
120     ./gradle
0       ./.gradle/vcs-1
56      ./.gradle/buildOutputCleanup
48      ./.gradle/5.2.1/executionHistory
0       ./.gradle/5.2.1/vcsMetadata-1
8       ./.gradle/5.2.1/fileChanges
48      ./.gradle/5.2.1/fileHashes
104     ./.gradle/5.2.1
160     ./.gradle
0       ./src/main/resources/db/migration
0       ./src/main/resources/db
0       ./src/main/resources
0       ./src/main
0       ./src
456     .
$

上記で作成したディレクトリに命名規則に沿ったSQLを配置する。

V00_000_001__Create_InitialTable.sql
-- ユーザテーブル
CREATE TABLE `t_user` (
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

###Flyway tasksを実行
まずはgradle flywayInfoで状態確認。
上記で作ったSQLファイルがPendingになっている。（まだDBには何も実行されない）

console
$ gradle flywayInfo

> Task :flywayInfo
Schema version: << Empty Schema >>
+-----------+------------+---------------------+------+--------------+---------+
| Category  | Version    | Description         | Type | Installed On | State   |
+-----------+------------+---------------------+------+--------------+---------+
| Versioned | 00.000.001 | Create InitialTable | SQL  |              | Pending |
+-----------+------------+---------------------+------+--------------+---------+


BUILD SUCCESSFUL in 0s
1 actionable task: 1 executed
$
mysql
mysql> show tables from localdb;
Empty set (0.00 sec)

mysql>

gradle flywayMigrateでマイグレーションを実行する。

console
$ gradle flywayMigrate

BUILD SUCCESSFUL in 0s
1 actionable task: 1 executed
$ 
$ gradle flywayInfo

> Task :flywayInfo
Schema version: 00.000.001
+-----------+------------+---------------------+------+---------------------+---------+
| Category  | Version    | Description         | Type | Installed On        | State   |
+-----------+------------+---------------------+------+---------------------+---------+
| Versioned | 00.000.001 | Create InitialTable | SQL  | 2020-02-12 22:50:51 | Success |
+-----------+------------+---------------------+------+---------------------+---------+


BUILD SUCCESSFUL in 0s
1 actionable task: 1 executed
<-------------> 0% WAITING
> IDLE
$
mysql
mysql> show tables from localdb;
+-----------------------+
| Tables_in_localdb     |
+-----------------------+
| flyway_schema_history |
| t_user                |
+-----------------------+
2 rows in set (0.00 sec)

mysql> select * from localdb.flyway_schema_history;
+----------------+------------+---------------------+------+--------------------------------------+------------+--------------+---------------------+----------------+---------+
| installed_rank | version    | description         | type | script                               | checksum   | installed_by | installed_on        | execution_time | success |
+----------------+------------+---------------------+------+--------------------------------------+------------+--------------+---------------------+----------------+---------+
|              1 | 00.000.001 | Create InitialTable | SQL  | V00_000_001__Create_InitialTable.sql | -615596340 | flywayuser   | 2020-02-12 22:50:51 |             16 |       1 |
+----------------+------------+---------------------+------+--------------------------------------+------------+--------------+---------------------+----------------+---------+
1 row in set (0.00 sec)

mysql>

上手いことできたっぽい。終わり。

###その他

  • flywayRepairとかflywayCleanとかFlywayの基本的な使い方については割愛。
  • gradleが入ってない環境にプロジェクトを配布したら./gradlew flywayInfoみたいな感じ（のはず）。
  • 記事では触れてないが、環境毎のMySQLにマイグレーションできるよう拡張（Repository参照）。

#Repository
https://github.com/kazzool/flyway-skeleton

#参考文献

#おわりに
Qiita初投稿の記事がFlywayって。。。

8
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?