この記事を書いた背景

Windows版のMySQLで、大量のテストデータを用意する作業が発生しました。

テストデータを作れるWebサービスは色々とあるものの出力形式がCSVなので、そのファイルをMySQLでインポートする操作と、
複数のPCにインストールされたMySQLに横展開したいので、インポートしたDBをSQLとしてエクスポートする操作があるので、それを記事としてまとめました。

ちょっとずつハマったので・・

手順

作業全体の流れ

操作自体は次の3ステップです。

1. テストデータを作る
2. MySQLにテストデータをインポートする
3. MySQLのデータベースをエクスポートする

注意点などを含めた詳細な手順を次に載せます。

1. テストデータを作る

テストデータは次のWebサービスを利用して作りました。
CSVファイルで出力しています。

SmartData

2. MySQLにテストデータをインポートする

MySQLにはLOAD DATA LOCAL INFILEという外部ファイルからのデータ取り込み機能があります。

しかし、セキュリティの観点からローカルファイルの利用がデフォルトでは無効になっているため、次のどちらかの方法でローカルファイルの利用を許可する必要があります。

mysqlコマンドにオプションを付ける方法

mysqlコマンドの起動オプションに「–local-infile=1」を付ける方法です。

MySQL Command Line Client – Unicodeのショートカットに直接追記するか、コマンド プロンプトで起動する際にオプションで指定します。

ショートカットに追記する際は、オプションを「”–local-infile=1″」のように「”」で書くようにしてください。

my.iniで設定する方法

my.iniで有効にする場合には、次の記述を書き加えてmysqlを再起動してください。

[client]
loose-local-infile=1

インポートの記述

インポートを実行するコマンドは次の構文で記述します。

mysql> LOAD DATA LOCAL INFILE 'C:/path/to/file.csv' INTO TABLE tablename CHARACTER SET cp932 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

注意点

CSVファイルのパスは、「\」ではなく「/」で区切ります。
「tablename」には、インポートしたいテーブル名を指定します。

オプションについて

オプションの意味は次の通りです。

CHARACTER SET cp932

インポートするファイルの文字コードを指定（cp932はWindowsが利用しているShift-JIS）

TERMINATED BY ‘,’

「,」をカラムを区切りに指定（デフォルトは「タブ（\t）」）

OPTIONALLY ENCLOSED BY ‘”‘

「,」を含むデータを囲む文字の指定

LINES TERMINATED BY ‘\r\n’

改行コードにCR/LFを指定（Excelなど多くのWindowsアプリケーションのデフォルト値）

LOAD DATA LNFILEの設定のデフォルトでは改行コードをLFとして扱うので、最終カラムの末尾にCRが付いた状態でインポートされてしまうのを防ぐために指定します。

3. MySQLのデータベースをエクスポートする

作成したデータは、データベースの単位でダンプすることでエクスポートします。

コマンドラインやPowerShellで次のコマンド実行します。

>path\to\mysql\bin\mysqldump -u -root -p databasename > export.sql

export.sqlの出力先によっては（Program Filesの下とか）コマンドプロンプトを管理者権限で立ち上げないと、ファイルの書込権限がなくてエラーします。

新規データベースにインポートする

先の手順で作成したエクスポートファイル（export.sql）を利用する際は、直接SQLを実行する方法と、コマンドプロンプトを利用する方法があります。

直接SQLを実行する方法

直接SQLを実行する場合、MySQL Command Line Client – Unicodeなどを起動して、SQLの中身をコピペします。

SQLが大きい場合、画面がスクロールして時間がかかるかも知れません。

コマンドを利用する方法

コマンド プロンプトを利用する場合、SQLを実行するには事前に受け入れ先のデータベースを用意しておく必要があります。

MySQL Command Line Client – Unicodeなどを起動して、次のコマンドでデータベースを用意します。

mysql> CREATE DATABASE targetdb

続いてコマンド プロンプトを起動して、次のコマンドでエクスポートファイルを読み込ませます。

>path\to\mysql\bin\mysql -u root -p targetdb < export.sql

まとめ

けっして難しい作業ではないのですが、細々とミスりそうなポイントがあるのに注意しないとですね！