業務でSQL ServerのデータをMySQLに移行する必要があったため、復習も兼ねて記事にしました。
実行環境はDockerで構築しています。
リポジトリはこちら。
動作確認
まず実際に動かしてみて流れをつかんでおきます。
↓やりたいことはこんな感じ
- docker-compose実行でSQL ServerとMySQLを初期化(SQLServerには移行対象のデータがinsertされており、MySQLにはデータは存在しません)
- docker build & docker runでembulkを実行し、MySQLにデータを移行できているか確認する
docker-compose実行
まずdocker-composeを実行して、テーブルがちゃんとセットアップできているか確認します。
# DBのセットアップ
$ docker-compose up -d
Creating network "migrate-network" with the default driver
Creating volume "test-embulk_sqlserver" with default driver
Creating volume "test-embulk_mysql" with default driver
Creating migrate_mysql ... done
Creating migrate_sqlserver ... done
# 立ち上げたコンテナを確認
$ docker-compose ps
Name Command State Ports
-------------------------------------------------------------------------------------------
migrate_mysql docker-entrypoint.sh mysqld Up 0.0.0.0:3306->3306/tcp, 33060/tcp
migrate_sqlserver /init/entrypoint.sh Up 0.0.0.0:1433->1433/tcp
exec
でテーブルを確認します。
まずはMySQLから見ていきましょう。
# コンテナに入る
$ docker exec -it migrate_mysql /bin/bash
# MySQL接続
bash-4.2# mysql -uuser_mysql -ppassword_mysql db_mysql
# テーブル確認
> show tables;
+--------------------+
| Tables_in_db_mysql |
+--------------------+
| users_mysql |
+--------------------+
1 row in set (0.00 sec)
# データ確認
> select * from users_mysql;
Empty set (0.01 sec)
まだ移行前なのでデータは入っていませんね。
次はSQL Serverを見てみます。
# コンテナに入る
$ d exec -it migrate_sqlserver /bin/bash
# SQL Server接続
root@351027:/# /opt/mssql-tools/bin/sqlcmd -U SA -P Passw0rd -S 127.0.0.1
# DB選択
1> use db_sqlserver;
2> go
Changed database context to 'db_sqlserver'.
# データ確認
1> select * from users_sqlserver;
2> go
id_sqlserver name_sqlserver gender_sqlserver
------------ ------------------------------ ----------------
1 hoge 1
2 fuga 3
3 piyo 2
(3 rows affected)
移行対象のデータが入っています。
ちなみにSQL Serverではgender
を数値で表現していますが、今回はこれをmale
、female
、others
という文字列に変換して移行してみます。
DBのセットアップが確認できたところで、Embulkでデータ移行を行っていきます。
Embulkでデータ移行
Embulkを実行した後、MySQLのデータを確認してみます。
# Embulk実行
$ docker build -t test-embulk ./migrate --no-cache
$ docker run --network migrate-network --env-file ./migrate/env.local -t test-embulk
# MySQLのデータを確認
$ mysql -uuser_mysql -ppassword_mysql db_mysql
> select * from users_mysql;
+----------+------------+--------------+
| id_mysql | name_mysql | gender_mysql |
+----------+------------+--------------+
| 1 | hoge | male |
| 2 | fuga | others |
| 3 | piyo | female |
+----------+------------+--------------+
3 rows in set (0.00 sec)
無事移行できました!
gender
もちゃんと文字列に変換されてますね。
では簡単にファイルの中身を見ていきましょう。
ディレクトリ構造
test-embulk
├── docker
│ ├── mysql
│ │ ├── init
│ │ │ └── init.sql
│ │ └── my.cnf
│ └── sqlserver
│ └── init
│ ├── entrypoint.sh
│ └── init.sql
├── docker-compose.yml
└── migrate
├── Dockerfile
├── entrypoint.sh
├── env.local
└── migrate-sqlserver-to-mysql.yml.liquid
DBのセットアップ
docker-compose.yml
SQL ServerとMySQLのボリュームや環境変数を定義しています。
SQL Serverのパスワードはpassword
とかだとパスワードポリシーに引っかかってしまうので注意が必要です。
version: "3.8"
services:
sqlserver:
image: mcr.microsoft.com/mssql/server:2017-latest
container_name: migrate_sqlserver
volumes:
- ./docker/sqlserver/init:/init
- sqlserver:/var/opt/mssql/data
environment:
- ACCEPT_EULA=Y
- MSSQL_SA_PASSWORD=Passw0rd
- TZ=Asia/Tokyo
networks:
- migrate-network
ports:
- 1433:1433
entrypoint: /init/entrypoint.sh
mysql:
image: mysql:5.7.41
container_name: migrate_mysql
volumes:
- ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
- ./docker/mysql/init:/docker-entrypoint-initdb.d
- mysql:/var/lib/mysql
tmpfs:
- /var/lib/mysql,mode=755
- /var/log/mysql,mode=755
environment:
- MYSQL_DATABASE=db_mysql
- MYSQL_USER=user_mysql
- MYSQL_PASSWORD=password_mysql
- MYSQL_ROOT_PASSWORD=root_password_mysql
- TZ=Asia/Tokyo
networks:
- migrate-network
ports:
- 3306:3306
networks:
migrate-network:
name: migrate-network
volumes:
sqlserver:
mysql:
MySQLセットアップ
簡単なテーブルをCREATE
しています。
INSERTはしていないので中身は空です。
CREATE TABLE `users_mysql` (
`id_mysql` INT PRIMARY KEY,
`name_mysql` VARCHAR(100) NOT NULL,
`gender_mysql` ENUM('male', 'female', 'others') NOT NULL
)
+--------------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------------+------+-----+---------+-------+
| id_mysql | int(11) | NO | PRI | NULL | |
| name_mysql | varchar(100) | NO | | NULL | |
| gender_mysql | enum('male','female','others') | NO | | NULL | |
+--------------+--------------------------------+------+-----+---------+-------+
SQL Serverセットアップ
MySQLと同様、簡単なテーブルをCREATEしています。
こちらは移行用のデータをいくつかINSRETしています。
USE master
GO
CREATE DATABASE db_sqlserver
GO
USE db_sqlserver
GO
CREATE TABLE users_sqlserver(
id_sqlserver INT NOT NULL,
name_sqlserver VARCHAR(30) NOT NULL,
gender_sqlserver INT NOT NULL
) ON [PRIMARY]
GO
INSERT INTO users_sqlserver(id_sqlserver, name_sqlserver, gender_sqlserver) VALUES(1, 'hoge', 1)
INSERT INTO users_sqlserver(id_sqlserver, name_sqlserver, gender_sqlserver) VALUES(2, 'fuga', 3)
INSERT INTO users_sqlserver(id_sqlserver, name_sqlserver, gender_sqlserver) VALUES(3, 'piyo', 2)
GO
また、MySQLとは異なりシェルスクリプトで初期化する必要があるため、以下のentrypoint.sh
をdocker-compose up
時に実行しています。
#!/bin/bash
/opt/mssql/bin/sqlservr &
pid=$!
echo "SQL Serverをセットアップします"
command="/opt/mssql-tools/bin/sqlcmd -U SA -P ${MSSQL_SA_PASSWORD} -S 127.0.0.1 -i <(cat /init/init.sql)"
until eval "$command"; do
echo "SQL Serverを起動しています..."
sleep 1
done
echo "SQL Serverを停止します $pid"
kill $pid
wait $pid
exec /opt/mssql/bin/sqlservr
Embulkの設定
まずはDockerfileから見ていきます。
embulk gem
でembulk-input-sqlserver
プラグインとembulk-output-mysql
プラグインをインストールしています。
こちらでもコンテナ起動時にentrypoint.shを実行していますね。
FROM openjdk:8
RUN curl -o /usr/local/bin/embulk --create-dirs -L "https://dl.embulk.org/embulk-latest.jar" && \
chmod +x /usr/local/bin/embulk
RUN embulk gem install embulk-input-sqlserver
RUN embulk gem install embulk-output-mysql
RUN apt update && apt install -y mariadb-client && rm -rf /var/lib/apt/lists/*
WORKDIR /root
COPY migrate-sqlserver-to-mysql.yml.liquid .
COPY entrypoint.sh .
CMD [ "./entrypoint.sh" ]
#!/bin/bash
/usr/local/bin/embulk run migrate-sqlserver-to-mysql.yml.liquid
そしてEmbulk実行時に渡しているファイルが本記事の主人公、migrate-sqlserver-to-mysql.yml.liquid
です。
in:
type: sqlserver
host: '{{env.SQLSERVER_HOST}}'
port: '{{env.SQLSERVER_PORT}}'
user: '{{env.SQLSERVER_USER}}'
password: '{{env.SQLSERVER_PASSWORD}}'
database: '{{env.SQLSERVER_DB}}'
query: |
SELECT
id_sqlserver AS id_mysql,
name_sqlserver AS name_mysql,
(SELECT CASE gender_sqlserver
WHEN 1 THEN 'male'
WHEN 2 THEN 'female'
WHEN 3 THEN 'others'
END) AS gender_mysql
FROM
users_sqlserver
out:
type: mysql
host: '{{env.MYSQL_HOST}}'
port: '{{env.MYSQL_PORT}}'
user: '{{env.MYSQL_USER}}'
password: '{{env.MYSQL_PASSWORD}}'
database: '{{env.MYSQL_DB}}'
table: users_mysql
column_options:
id_mysql:
type: int
name_mysql:
type: VARCHAR(100)
gender_mysql:
type: ENUM('male', 'female', 'others')
options: {connectTimeout: 20000}
mode: insert
sqlserverで抽出したカラムをAS句でmysqlのカラムに対応させることでEmbulkが移行データを判別します。
gender_sqlserver
カラムに関しては、CASE句を使ってINTからmale、female、othersに変換しています。
このように移行元とは異なるカラム名や型であっても、簡単に変換して移行先に格納することができます。