MySQL データのエクスポート

MySQL では、次を使用できますSELECT...INTO OUTFILE文を使用すると、データを簡単にテキストファイルにエクスポートできます。


SELECT ... INTO OUTFILE 文を使用したデータのエクスポート

SELECT...INTO OUTFILEこれは、MySQL でクエリ結果をファイルにエクスポートするための構文です。

SELECT...INTO OUTFILEクエリの結果をテキストファイルに書き込むことができます。基本的な使用方法は次のとおりです:

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;

パラメータの説明:

  • column1, column2, ...: 選択する列。
  • 'file_path': 出力ファイルのパスと名前を指定します。
  • your_table: クエリを実行するテーブル。
  • your_conditions: クエリ条件。

以下は簡単な例です:

例

SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;

上記の SQL 文では、users テーブルから id、name、email 列を選択し、結果を /tmp/user_data.csv ファイルに書き込みました。FIELDS TERMINATED BY ',' は列間の区切り文字(カンマ)を指定し、LINES TERMINATED BY '\n' は行間の区切り文字(改行)を指定します。

注意すべき点として、実行にはSELECT...INTO OUTFILE対応する権限が必要であり、出力ファイルのディレクトリは MySQL サーバーが書き込み可能な場所である必要があります。

次の例では、データテーブル サンプル_tbl のデータを /tmp/サンプル.txt ファイルにエクスポートします:

mysql> SELECT * FROM サンプル_tbl 
    -> INTO OUTFILE '/tmp/サンプル.txt';

コマンドオプションを使用してデータ出力の指定形式を設定できます。次の例は CSV 形式でのエクスポートです:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/サンプル.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

次の例では、各値をカンマで区切ったファイルを生成します。この形式は多くのプログラムで使用できます。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT ... INTO OUTFILE 文には以下の属性があります:

  • LOAD DATA INFILE は SELECT ... INTO OUTFILE の逆操作であり、SELECT 構文です。データベースのデータをファイルに書き込むには SELECT ... INTO OUTFILE を使用し、ファイルをデータベースに読み戻すには LOAD DATA INFILE を使用します。
  • SELECT...INTO OUTFILE 'file_name' 形式の SELECT は、選択された行をファイルに書き込むことができます。このファイルはサーバーホスト上に作成されるため、この構文を使用するには FILE 権限を持っている必要があります。
  • 出力は既存のファイルであってはなりません。ファイルデータの改ざんを防ぐためです。
  • ファイルを取得するには、サーバーにログインするためのアカウントが必要です。そうしないと、SELECT ... INTO OUTFILE は何の効果もありません。
  • UNIX では、このファイルは作成されると読み取り可能になり、権限は MySQL サーバーが所有します。つまり、ファイルを読み取ることはできても、削除できない可能性があります。

mysqldump でテーブルを生データとしてエクスポート

mysqldump は、MySQL が提供するデータベースのバックアップおよびエクスポート用のコマンドラインツールです。

mysqldumpこれは、mysql がデータベースをダンプするためのユーティリティです。主に、データベースをゼロから再作成するために必要なコマンド(CREATE TABLE、INSERT など)を含む SQL スクリプトを生成します。

使用mysqldumpデータのエクスポートには、以下のものを使用する必要があります--tabオプションを使用して、エクスポートファイルの保存先ディレクトリを指定します。この保存先は書き込み可能である必要があります。

mysqldump の基本的な使用方法:

mysqldump -u username -p password -h hostname database_name > output_file.sql

パラメータの説明:

  • -u: MySQL ユーザー名を指定します。
  • -p: パスワードの入力を求めます。
  • -h: MySQL ホスト名を指定します。
  • database_name: エクスポートするデータベース名。
  • output_file.sql: エクスポートデータが保存されるファイル。

次の例では、データテーブル サンプル_tbl を /tmp ディレクトリにエクスポートします:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp サンプル サンプル_tbl
password ******

mysqldump の例

1. データベース全体をエクスポート

mydatabase データベースを mydatabase_backup.sql ファイルにエクスポートします:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. 特定のテーブルをエクスポート

データベース内の特定のテーブルだけをエクスポートしたい場合は、次のコマンドを使用できます:

mysqldump -u username -p password -h hostname database_name table_name > output_file.sql

または:

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. データベース構造をエクスポート

データを含めずにデータベース構造だけをエクスポートしたい場合は、次のオプションを使用できます--no-data オプション:

mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. 圧縮ファイルとしてエクスポート

エクスポートしたデータを圧縮して、ファイルサイズを小さくすることができます。例えば、gzip を使用します:

mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

SQL 形式のデータをエクスポート

SQL 形式のデータを指定したファイルにエクスポートするには、次のようにします:

$ mysqldump -u root -p サンプル サンプル_tbl > dump.txt
password ******

上記のコマンドで作成されるファイルの内容は次のとおりです:

-- MySQL dump 8.23
--
-- Host: localhost    Database: サンプル
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `サンプル_tbl`
--

CREATE TABLE サンプル_tbl (
  サンプル_id int(11) NOT NULL auto_increment,
  サンプル_title varchar(100) NOT NULL default '',
  サンプル_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (サンプル_id),
  UNIQUE KEY AUTHOR_INDEX (サンプル_author)
) TYPE=MyISAM;

--
-- Dumping data for table `サンプル_tbl`
--

INSERT INTO サンプル_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO サンプル_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO サンプル_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

データベース全体のデータをエクスポートする必要がある場合は、次のコマンドを使用できます:

$ mysqldump -u root -p サンプル > database_dump.txt
password ******

すべてのデータベースをバックアップする必要がある場合は、次のコマンドを使用できます:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

--all-databases オプションは MySQL 3.23.12 以降のバージョンで追加されました。

この方法は、データベースのバックアップ戦略を実装するために使用できます。


データテーブルとデータベースを他のホストにコピー

他の MySQL サーバーにデータをコピーする必要がある場合は、mysqldump コマンドでデータベース名とデータテーブルを指定できます。

ソースホストで次のコマンドを実行して、データを dump.txt ファイルにバックアップします:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

データベースを完全にバックアップする場合は、特定のテーブル名を指定する必要はありません。

バックアップしたデータベースを MySQL サーバーにインポートする場合は、次のコマンドを使用できます。このコマンドを使用するには、データベースが作成済みであることを確認する必要があります:

$ mysql -u root -p database_name < dump.txt
password *****

次のコマンドを使用して、エクスポートしたデータをリモートサーバーに直接インポートすることもできます。ただし、2 台のサーバーが相互に通信でき、アクセス可能であることを確認してください:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

上記のコマンドでは、パイプを使用してエクスポートしたデータを指定したリモートホストにインポートしています。

その他の拡張