mysqldumpは知っているけど、CSVとして出てくるわけではないし、SELECT INTO OUTFILEという方法もあるらしいけど設定が必要らしい・・・と調べて止まってしまう人は少なくありません。
この記事では、LinuxサーバーのMySQLで mysql dump csv を出力する2つの方法(SELECT INTO OUTFILEとmysqldump)の使い方と使い分けを解説します。
RHEL 9系/Ubuntu 22.04 LTSを想定環境として説明します。
この記事のポイント
・SELECT INTO OUTFILEはSQL1行でサーバー上に直接CSVを書き出せる
・mysqldump --tab で全テーブルをTSV化する方法もある
・mysql コマンドとリダイレクトで手軽なCSV出力も可能
・secure_file_privの設定次第でSELECT INTO OUTFILEが使えないケースに注意
でも安心してください。プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
MySQLでCSVを出力する2つのアプローチ
MySQLからCSVを取り出す方法は大きく2系統あります。1つ目はMySQLサーバー自身に書き出させる方法で、SQL文の
SELECT INTO OUTFILE を使います。サーバープロセスが直接ファイルを作成するので、大量データでも速く処理できます。
2つ目はクライアント側で整形する方法で、
mysql コマンドや mysqldump の出力をリダイレクトして作ります。追加の権限設定が不要な分、手軽に試せます。
どちらにも向き不向きがあるので、後ほど比較表でまとめます。
SELECT INTO OUTFILEでCSVを直接書き出す
1. 基本構文と実行例(出力例)
SELECT INTO OUTFILEはSQL文の中にファイル出力先を書く方法です。# MySQLにログインしてから実行する mysql -u root -p # データベースを選択する USE myapp; # ordersテーブルをCSVとして書き出す SELECT * INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM orders;
/var/lib/mysql-files/orders.csv にCSVが作成されます。確認してみると、このような出力になります(出力例。環境により異なります):
1,2026-01-15 09:00:00,"yamada@example.com",5800 2,2026-01-16 14:30:00,"tanaka@example.com",12000 3,2026-01-17 10:00:00,"suzuki@example.com",3200
FIELDS TERMINATED BY ',' がカンマ区切り、OPTIONALLY ENCLOSED BY '"' が文字列をダブルクォートで囲む指定です。文字列フィールドにカンマが含まれる場合に備えて、
OPTIONALLY ENCLOSED BY は入れておくのが安全です。2. 文字コードと日本語の扱い
日本語データを含む場合は文字コードを指定しないと文字化けすることがあります。# utf8mb4で書き出す場合 SELECT * INTO OUTFILE '/var/lib/mysql-files/orders_ja.csv' CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM orders;
その場合は書き出したファイルにBOMを付加する変換が別途必要です。
3. FILE権限とsecure_file_privの確認
SELECT INTO OUTFILEを実行するには2つの条件を満たす必要があります。まずMySQLユーザーに
FILE 権限が必要です。rootユーザーはデフォルトで持っていますが、一般ユーザーは確認が必要です。# 自分の権限を確認する SHOW GRANTS FOR CURRENT_USER(); # 出力例(環境により異なります) +------------------------------------------------------+ | Grants for root@localhost | +------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost`... | +------------------------------------------------------+
secure_file_priv の設定を確認します。この変数が設定されていると、指定ディレクトリ以外には書き込めません。
# secure_file_privを確認する SHOW VARIABLES LIKE 'secure_file_priv'; # 出力例(環境により異なります) +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+
/var/lib/mysql-files/ になっている場合は、そのディレクトリにのみ書き出せます。値が空文字なら制限なし、
NULL なら SELECT INTO OUTFILE は完全に無効です。NULL になっているMySQL 8.0以降の環境では、SELECT INTO OUTFILEは使えません。/etc/my.cnf の [mysqld] セクションに secure_file_priv=/var/lib/mysql-files/ を追加してサービスを再起動すると有効にできます。ただし本番環境では変更前に必ずバックアップを取ってから作業してください。
mysqldumpを使ってCSV形式のデータを出力する
1. mysql コマンドとリダイレクトで手軽なCSVを作る
最も手軽な方法はmysql コマンドの出力をリダイレクトする方法です。# タブ区切りのTSVとして出力する(-B で区切り文字がタブになる) mysql -u root -p -B -e "SELECT * FROM myapp.orders;" > orders.tsv # カンマ区切りに変換する(sedで置換) mysql -u root -p -B -e "SELECT * FROM myapp.orders;" | \ sed 's/\t/,/g' > orders.csv
-B(--batch)オプションを付けると、ヘッダー行を含むタブ区切りの出力になります。フィールド値にタブやカンマが含まれない場合はこの方法で十分です。
ヘッダー行を除きたい場合は
-N(--skip-column-names)を追加します。# ヘッダーなしでCSV出力する mysql -u root -p -B -N -e "SELECT * FROM myapp.orders;" | \ sed 's/\t/,/g' > orders_noheader.csv
2. mysqldump --tab でテーブルごとにファイルを作る
mysqldump --tab はテーブルごとに2つのファイルを作成します。構造定義の
.sql ファイルと、データのみの .txt(タブ区切り)ファイルです。# /var/lib/mysql-files/ にテーブルごとのファイルを作成する mysqldump --tab=/var/lib/mysql-files/ \ --fields-terminated-by=',' \ --fields-optionally-enclosed-by='"' \ --lines-terminated-by='\n' \ -u root -p myapp # 出力されるファイルを確認する(例) ls /var/lib/mysql-files/ # orders.sql orders.txt # users.sql users.txt
--tab を使うと内部的にSELECT INTO OUTFILEを実行するため、secure_file_priv の制限を受けます。また、出力先ディレクトリへのOSレベルの書き込み権限も必要です。
データベース全体を一度にCSVとして取り出したい場合はこの方法が便利です。
データを他のサーバーやシステムに取り込む際に使います。
Linuxサーバーのデータベース管理に興味がある方は、【初心者向けLinuxセミナー(2日間ハンズオン)】でハンズオンで学べます。
SELECT INTO OUTFILEとmysqldumpの使い分け
2つの方法をまとめると、以下のように使い分けられます。| 観点 | SELECT INTO OUTFILE | mysql / mysqldump |
|---|---|---|
| 出力先 | サーバー上のファイル | クライアント側の端末・ファイル |
| 必要な権限 | FILE権限 + secure_file_priv設定 | SELECT権限のみ(通常) |
| 大量データ | 高速(サーバー直書き) | データ転送が発生するため遅め |
| WHERE条件 | 自由に指定できる | mysql -e でSQL実行なら可能 |
| 向いている場面 | 大量データの高速エクスポート | 権限なし環境・リモート接続時 |
本番サーバーに直接SSHログインして作業できる場合は SELECT INTO OUTFILE が速くて確実です。
リモートから接続していて
secure_file_priv=NULL の環境では mysql -B によるリダイレクト出力を選んでください。「ERROR 1290」「Access denied」が出たときの対処
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv optionこの場合は
secure_file_priv の設定が原因です。# secure_file_privの現在値を確認する SHOW VARIABLES LIKE 'secure_file_priv'; # NULLが返った場合は /etc/my.cnf を編集する(要mysqldサービス再起動) # [mysqld] セクションに追加: # secure_file_priv=/var/lib/mysql-files/ # 設定後にディレクトリの権限を確認する ls -la /var/lib/mysql-files/ # mysql ユーザーが書き込めることを確認する
OSレベルの権限の問題です。MySQLサーバープロセス(通常は
mysql ユーザー)が出力先ディレクトリに書き込めない場合に発生します。# /var/lib/mysql-files/ の所有者を確認する ls -la /var/lib/ | grep mysql-files # 出力例(環境により異なります) drwxr-x--- 2 mysql mysql 4096 Nov 17 10:00 mysql-files # mysqlユーザーが書き込めるよう権限を調整する(必要な場合) chown mysql:mysql /var/lib/mysql-files/ chmod 750 /var/lib/mysql-files/
SELECT INTO OUTFILEは既存ファイルへの上書きができません。
同じ名前のファイルがある場合はエラーになります。
事前に古いファイルを削除するか、日付入りのファイル名を使ってください。
# シェルスクリプトで日付付きのファイル名を使う例 DATE=$(date +%Y%m%d) mysql -u root -p myapp -e " SELECT * INTO OUTFILE '/var/lib/mysql-files/orders_${DATE}.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM orders; "
本記事のまとめ
MySQLでCSVを出力する方法を2系統で説明しました。| やりたいこと | コマンド・方法 |
|---|---|
| SQLの結果をサーバー上にCSV保存 | SELECT * INTO OUTFILE '/パス/ファイル.csv' FIELDS TERMINATED BY ',' FROM テーブル; |
| クライアント側にTSVとして取り出す | mysql -u root -p -B -e "SELECT * FROM DB.テーブル;" > ファイル.tsv |
| クライアント側にCSVとして取り出す | mysql -B ... | sed 's/\t/,/g' > ファイル.csv |
| データベース全体をテーブル別CSVに | mysqldump --tab=/var/lib/mysql-files/ --fields-terminated-by=',' -u root -p DB名 |
| secure_file_privの確認 | SHOW VARIABLES LIKE 'secure_file_priv'; |
SELECT INTO OUTFILEはサーバー側で処理するため大量データに強い一方、
secure_file_priv の設定が必要です。mysql -B によるリダイレクトは権限の壁がなく手軽に使えます。目的の環境と権限に合わせて使い分けてみてください。
MySQL・MariaDBのインストールから権限設計・バックアップまで、2日間のハンズオンで実践的に学べます。
>>【初心者向けLinuxセミナー(2日間ハンズオン)】
3,100名以上が実践した「型」を無料で公開中
プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
その「型」を図解60Pにまとめた入門マニュアルを、完全無料でプレゼントしています。
姓・名・メールの3つだけ/30秒/解除は3秒 / 詳細はこちら
- 前のページへ:mysqldumpでテーブル指定してデータをエクスポートする方法|複数テーブルと条件絞り込み
- この記事の属するカテゴリ:データーベース管理へ戻る

無料メルマガで学習を続ける
Linuxの実践スキルをメールで毎週お届け。
登録は30秒、解除もいつでも可。
登録無料・いつでも解除できます