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: クエリ条件。
以下は簡単な例です:
例
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
上記のコマンドでは、パイプを使用してエクスポートしたデータを指定したリモートホストにインポートしています。
その他の拡張