翻訳に失敗しましたWindows版のMySQLで、CSVファイルをインポートしてからSQLファイルとしてエクスポートする手順

翻訳に失敗しました翻訳に失敗しました翻訳に失敗しました

この記事を書いた背景

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

まとめ

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

え?Webサイトのバックアップ、してないの!?

いざという時には復旧までしてくれる
『ワードプレス外部バックアップサービス』

■3つの特長
・自分でやらなくて良い
・いざという時の復旧付き
・外部サーバーにバックアップ

コメントを残す