🤖

【技術覚書】MySQL LOADコマンド LOCALオプションの有無による使い方の違いについて

に公開
MySQL
SQL
DB
データインポート
tech

はじめに

データベースに大量のデータをインポートする際、手動で一つ一つのレコードを追加するのは非常に手間がかかります。MySQLでは大量のデータのインポート操作にLOAD DATAコマンドを用います。LOAD DATAコマンドはLOCALオプションの有無によりインポート操作の手順が異なります。どういう使い分けがあるのかが気になったので、調べた結果を自分用の覚書として記しておきます。

シナリオ

  • MySQLのサンプルDBを利用する
  • employeesDBの操作は専用ユーザemployees_userを利用する
  • employeesDBのdepartmentsテーブルにdepartments_202412061658.csvファイルのデータをLOAD DATAコマンドを用いてインポートする
  • departments_202412061658.csvファイルはあらかじめエキスポートして作成しておく

LOAD DATAコマンドのLOCALオプションの有無によるメリットとデメリット

LOCALオプション 特徴 運用、セキュリティ上の注意事項
有り 実行ユーザーにFILE権限は不要
接続した実行ユーザーのクライアントのローカルのデータをアップロードできる		 実行ユーザーemployees_userのMySQL接続時にオプション--load-infile=1指定が必要
アップロード前後でrootユーザーによるアップロード許可、制限のサーバー側の設定が必要
恒常的にアップロードを許可するのはセキュリティ的によくない
無し 実行ユーザーにFILE権限が必要 変数secure_file_privが示すディレクトリにファイルコピー操作が必要

実際の操作 / LOCALオプションが有る場合

# 管理者ユーザ root 実行ユーザ employees_user
インポートステップ1 local_infileシステム変数をONにする
インポートステップ2 local_infile=1でMySQL DBに接続
インポートステップ3 LOAD DATA LOCALでクライアントのdepartments_202412061658.csvファイルをインポートする
インポートステップ4 MySQL DBを切断
インポートステップ5 local_infileシステム変数をOFFにする

実際の操作 / LOCALオプションが無い場合

# 管理者ユーザ root 実行ユーザ employees_user
設定ステップ FILE権限を実行ユーザemployees_userに与える
インポートステップ1 departments_202412061658.csvファイルを変数secure_file_privが示すディレクトリにコピーする
インポートステップ2 MySQL DBに接続
インポートステップ3 LOAD DATAで変数secure_file_privが示すディレクトリに格納したCSVをインポートする
インポートステップ4 MySQL DBを切断

まとめ

LOAD DATAコマンドを使うことで、大量のデータを効率的にインポートすることができます。LOCALオプションを指定するかどうかはそれぞれの運用方法に応じてに決まります。それぞれのシステムの運用に合わせたインポートを行いましょう。

参考にした記事

Discussion