【Docker×Mysql5.7】はじめてのLOAD DATA INFILE

プログラミング
2021.04.17

Mysqlに大量データをInsertしたい時、マルチプルインサートをしてたけども、LOAD DATA INFILEならさらに高速にInsertを行えるらしい。気になったのでやってみたときのメモ。

環境
mysql:5.7
docker-compose.ymlはversion 3

以上のおそらく初歩的エラー４本立てで書き残していく。

はじめに

target_table(データをimportしたいテーブル)

id    int(11) 連番  
user_id    varchar(255)    
target_date    datetime
count    int(11) [0]

import_data.csv(Importするデータ)

abcd,2021-04-16 01:00:00,1
bcda,2021-04-16 05:00:00,2
cdab,2021-04-16 21:00:00,1

ディレクトリの大体の構成

product/
  ├ mysql/
  │   ├ Dockerfile
  │   └ my.conf...etc
  │
  ├ app/
  │   ├ php.ini
  │   └ Dockerfile...etc
  ......(関係なさそうなのは端折る)
  ├.docker-compose.yml
  │
  └ www/
      ├ index.php..etc
      └ data/
           └import_data.csv

docker-compose.ymlの一部

version: "3"
services:
  php:
    container_name: php
    build: ./app
    env_file:
      - ./web/web.env
    volumes:
      - ./www:/var/www
      - ./app/php.ini:/usr/local/etc/php/php.ini
    links:
      - mysql

  mysql:
    container_name: mysql-db
    build: ./mysql
    volumes:
      - ./www/mysql_app_share:/tmp/mysql_app_share
      ...etc

入力コマンド

LOAD DATA INFILE 'var/www/data/import_data.csv' INTO TABLE target_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  (user_id,target_date,count);

エラー1.php.iniの設定

まずはこのエラーに当たる

LOAD DATA LOCAL INFILE forbidden

解消

phpinfo()で確認してみるとmysqli.allow_local_infile=”Off”…
docker-composeでphp.iniにマウントしているファイルをmysqli.allow_local_infile = “On”
に書き換えてdockerを再起動。

エラー2.Mysqlセキュリティの設定

次はこのエラー

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

secure-file-priv optionが動作して怒られてるみたい。

書き込みが安全に実行できるディレクトリに –secure-file-priv を設定したりできます。
-MySQL 5.6 リファレンスマニュアル

読み書きしたいものはここで指定したディレクトリに置かないといけないみたい。

設定を確認すると

@@global.secure_file_priv
-------------------------
/var/lib/mysql-files/

なんだか全然違うとこが参照になってた。

解消

他のphpファイルとかのあるwww/data/配下におきたいので新たにmy.cnfに記述を追加

[mysqld]
secure-file-priv = "www/data/"

マウントを追加して再起動。
volumes
./mysql/my.cnf:/etc/mysql/conf.d/my.cnf

エラー3.DockerとMysqlでファイルを参照できない

また怒られた

Can't get stat of '/var/lib/mysql/var/www/data/import_data.csv' (Errcode: 2 - No such file or directory)

当然だったんだけど、mysqlコンテナ内だけを参照する..

そこで
Dockerボリューム機能を使用して、外部のストレージとかを別コンテナにマウントできる->これを使っていこう

解決

docker上のmysqlコンテナとphp用コンテナでファイルを共有できるディレクトリが必要。
お得意のvolumeマウントでmysql部分に追記

    volumes:
      - ./mysql/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./www/data:/tmp/data<-ここ

でdocker-compose up -dとrestartをかけるとcsvファイルは見つからないよと怒られなくなる。

ちなみにこれでimport/Export用ディレクトが/tmp/dataに決まったので、エラー2でsecure-file-priv = “”にしちゃってたのをsecure-file-priv = “/tmp/data/”に書き換えてちょっと安全になった気でいる(どこのディレクトリでもImport/Exportオッケーにしておくのはよろしくないなということで)

おまけ.datetime型が入らない

Incorrect datetime value: 2021-04-16 01:00:00 for column 'target_date' at row 1

解決

SET  sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

or

SET sql_mode='';

…紆余曲折を経て

LOAD DATA INFILE '/tmp/data/import_data.csv' INTO TABLE target_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  (user_id,target_date,count);

クエリーを実行しました。3 行を変更しました (0.009 秒)

Yeah!

結局マルチプルインサートになるかもしれないけれど、ダミーデータ挿入とかでも使えそうなのでちゃんと覚えておこう。


本日もおつかれさま👏👏

タイトルとURLをコピーしました