dockerでMySql環境作成

docker-composeを使用してMySql環境を作成

下記から必要なファイル一式を取得
https://github.com/manchan13/docker_compose_mysql

コンテナ起動

start.sh

docker-compose up -d

コンテナに接続

login.sh

docker exec -it docker_compose_mysql_db_1 bash

MySql接続

ユーザー：user
パスワード：password

ターミナル

# mysql -u user -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample_db          |
+--------------------+
2 rows in set (0.00 sec)

mysql> use sample_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_sample_db |
+---------------------+
| users               |
+---------------------+
1 row in set (0.00 sec)

Spring Initializrでプロジェクト作成

Spring Initializrからプロジェクト作成¹

作成したプロジェクトをインポート ※intelliJを使用

build.gradleの編集

必要なプラグイン等を追加

編集前

build.gradle

plugins {
	id 'org.springframework.boot' version '2.3.0.RELEASE'
	id 'io.spring.dependency-management' version '1.0.9.RELEASE'
	id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter'
	implementation 'org.flywaydb:flyway-core'
	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}
}

test {
	useJUnitPlatform()
}

編集後

build.gradle(編集後)

plugins {
	id 'org.springframework.boot' version '2.3.0.RELEASE'
	id 'io.spring.dependency-management' version '1.0.9.RELEASE'
	id 'java'
	id 'org.flywaydb.flyway' version '6.4.3'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter'
	implementation 'org.flywaydb:flyway-core'
	runtimeOnly 'mysql:mysql-connector-java'
	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}
}

flyway {
	url = 'jdbc:mysql://127.0.0.1:3314/sample_db'
	user = 'user'
	password = 'password'
	locations = ['classpath:db/migration']
}
test {
	useJUnitPlatform()
}

Flywayで使用するSQLファイル作成

resources.db.migration配下にSQLファイルを格納

resources
└── db
    └── migration
        └── V1.0.1__my_first_flyway.sql
        └── V1.1.0__Insert-InitialData.sql

V1.0.1__my_first_flyway.sql

CREATE TABLE message (
    id VARCHAR(36) NOT NULL PRIMARY KEY,
    title VARCHAR(255),
    message VARCHAR(255),
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

V1.1.0__Insert-InitialData.sql

INSERT INTO message(id, title, message) VALUES ('7b23257c-e9d9-4d1e-ba79-01f8b8715ba9', 'INIT', 'Inserted by FLYWAY');
INSERT INTO message(id, title, message) VALUES ('12345678-e9d9-4d1e-ba79-01f8b8715ba9', 'INIT', 'Inserted by FLYWAY');

gradle flyway実行

// SQLファイルが反映させるため、一旦ビルド
./gradlew build

./gradlew flywayInfo

> Task :flywayInfo
Schema version: << Empty Schema >>
+-----------+---------+--------------------+------+--------------+---------+
| Category  | Version | Description        | Type | Installed On | State   |
+-----------+---------+--------------------+------+--------------+---------+
| Versioned | 1.0.1   | my first flyway    | SQL  |              | Pending |
| Versioned | 1.1.0   | Insert-InitialData | SQL  |              | Pending |
+-----------+---------+--------------------+------+--------------+---------+

// flaway履歴テーブルを作成
./gradlew flywayBaseline

// SQLファイル適用
./gradlew flywayMigrate

// 結果確認
./gradlew flywayInfo

> Task :flywayInfo
Schema version: 1.1.0
+-----------+---------+-----------------------+----------+---------------------+----------+
| Category  | Version | Description           | Type     | Installed On        | State    |
+-----------+---------+-----------------------+----------+---------------------+----------+
|           | 1       | << Flyway Baseline >> | BASELINE | 2020-06-10 00:27:13 | Baseline |
| Versioned | 1.0.1   | my first flyway       | SQL      | 2020-06-10 00:31:13 | Success  |
| Versioned | 1.1.0   | Insert-InitialData    | SQL      | 2020-06-10 00:31:14 | Success  |
+-----------+---------+-----------------------+----------+---------------------+----------+

MySql確認

ターミナル

mysql> show tables;
+-----------------------+
| Tables_in_sample_db   |
+-----------------------+
| flyway_schema_history |
| message               |
| users                 |
+-----------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> select * from message;
+--------------------------------------+-------+--------------------+---------------------+---------------------+
| id                                   | title | message            | created             | updated             |
+--------------------------------------+-------+--------------------+---------------------+---------------------+
| 12345678-e9d9-4d1e-ba79-01f8b8715ba9 | INIT  | Inserted by FLYWAY | 2020-06-09 15:31:14 | 2020-06-09 15:31:14 |
| 7b23257c-e9d9-4d1e-ba79-01f8b8715ba9 | INIT  | Inserted by FLYWAY | 2020-06-09 15:31:14 | 2020-06-09 15:31:14 |
+--------------------------------------+-------+--------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql>

参考

Flyway で MySQL のバージョン管理をしてみる in docker-compose

構築済みDemo環境： https://github.com/manchan13/gradleFlywayDemo
DependenciesにFlyway Migrationを追加 ↩