テキストファイル(SQL文) から MySQL にインポートする (Docker)

大量のデータ(数十万レコード程度)を phpMyAdmin 経由で MySQL にインポートしようとしたらメモリ不足でコケたので、 CLI から直接インポートする方法を模索しました。

今回試したのは次の2つの方法

  1. use DB_NAME; を SQL文 のテキストファイルの先頭に記述した上で、 mysql -u root -p < FILE_PATH
  2. MySQL にログインして use DB_NAME; した後に source FILE_PATH

方法1 (mysql -u root -p < FILE_PATH)

use DB_NAME; を SQL文 のテキストファイルの先頭に記述した上で、 mysql -u root -p < FILE_PATH する方法です。

まず、 SQL文 のテキストファイル (phpMyAdmin 等でエクスポートした、テーブル定義 CREATA TABLE やデータの INSERT を含むもの) に少し手を加えます。

-- MySQL dump XX.XX
--
-- Host: localhost    Database: DATABASE_NAME
-- ------------------------------------------------------
-- Server version    XX.XX.XX

USE DATABASE_NAME; /* 追記 */


/* 以下略 */

先頭に USE DATABASE_NAME; を記述して、どのデータベースにインポートするのか明記します。

この加工を施した上で、シェルに入り込み

> docker-compose exec db /bin/bash

※今回は MySQLサーバ が Docker Compose 使用の Dockerコンテナ なのでこれで入ります

# ls -al /home/MYSQL_DUMP.sql
-rw-r--r-- 1 root root XXXXX WWW dd hh:ii /home/MYSQL_DUMP.sql
# mysql -u root -p <  /home/MYSQL_DUMP.sql
Enter password: 
# exit

mysql -u root -p < /home/MYSQL_DUMP.sql でインポート。これで無事に入ったことを確認。

方法2 (use DB_NAME; && source FILE_PATH)

方法1だとお手軽ではあるのですが、実行する際に内容を確認せずに実行してしまうので若干怖い部分があります。

しかも今回は対象のファイルがかなり大きいのでエディタで開くのも一苦労なシロモノ。

そういった事情から、なるべくファイルの中身を編集することは避けたいと考えました。そこで2つ目の方法。

> docker-compose exec db /bin/bash

同じく入り込みます。

# mysql -u root -p
Enter password:
mysql>

今回は素直にログインします。

mysql> use DATABASE_NAME;
Database changed

違うのはまずここ。先程はファイル内で USE していましたが、今回はコマンドを直接叩きます。

mysql> source /home/MYSQL_DUMP.sql

# 略

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

今回は source /home/MYSQL_DUMP.sql の形でインポートを実行します。クエリごとにログが出力される点も先程と異なりますね。

どちらの方法でも大量のデータを正常にインポートできることを確認しました。

余談: エクスポート

インポートするのが大変ならば、エクスポートするのも大変。同様に phpMyAdmin の画面からエクスポートさせようとすると固まってしまうので、エクスポートも CLI から行うことにします。

# mysqldump -u root -p -h localhost DATABASE_NAME > /home/MYSQL_DUMP.sql

mysqldumpコマンド でエクスポート。

参考

方法1, 方法2

方法2

mysqldump によるエクスポート

この記事を書いた人

アルム=バンド

フロントエンド・バックエンド・サーバエンジニア。LAMPやNodeからWP、Gulpを使ってejs,Scss,JSのコーディングまで一通り。たまにRasPiで遊んだり、趣味で開発したり。