ハードディスクメンテナンス

ハードディスクの診断、物理フォーマット、静音化、Linux、サーバー、MySQLなどがメインだったのですが、その後広がり、カメラやハードウェアの投稿も増えてきました。 モバイルデバイスは、MacBookAir(13型)、iPad Air2、ポメラ DM100(キングジム)OLYMPUS STYLUS XZ-2など。 これらを使いながら、ブログを更新しています。

タグ:mysqlimport



自鯖(自由度高)を使うことが多いが、さくらインターネットのユーザーから質問を受けたのでメモ。





WEBサーバー内にcsvファイルを置いて、それをDB(MySQL)にインポートする。

phpMyAdminのインポート機能を使えば可能だが、phpプログラムで実行したい。

となると、LOAD DATA LOCAL INFILEが考えられる。

LOAD DATA INFILE
インポート先のDBがあるサーバーにcsvファイルがある場合

LOAD DATA LOCAL INFILE
クライアントにあるcsvファイルをサーバーのDBへ

MySQL全機能バイブルの381ページ

この場合は,使用可能と思えるが、csvファイルのあるサーバー(WEBサーバー)とMySQLのあるサーバー(DBサーバー)が別の場合(さくらインターネット等)は、この方法では不可能では?

ネットで調べると、成功したとかしなかったとか、mysqlimportで成功した等の情報があるが、同じレンタルサーバー業者でも、DBのバージョンや設定が異なるので、実際は自分で試してみないとならない。

また、情報の日付に注意。

以前は有効でも今は無効ということがあるからだ。

phpMyAdminでのインポートでは、最大サイズの制限がある(最長:16MiB)。

自己管理のローカルサーバー等、自由度のある場合は上限値を変更できるが、レンタルサーバーの場合は困難だろう。

そのような場合、FTPでcsvファイルを上げておいて、以下の方法でDBにインポートするとよいだろう。

phpでプログラムを書いておくと、コマンド打たなくてもいいしね。

以下、手順。

■csvファイルの準備

WEBサーバー上の /home/aaa/www/ に test.csv を置く。

aaaは、さくらインターネットでのアカウント名。

test.csvの中身は

1,first
2,second
3,third
4,forth
5,fifth

という、カンマ区切りの簡単なもの。

■DBの準備

mysql***.db.sakura.ne.jp - aaa_xxx - test

に、以下のようなテーブル(test)を作成しておく。

テーブル

・id int
・name text

***は、さくらインターネットから割り当てられたDBの番号。

aaa_xxxはDB名だが、必ずaaa_が付加されるのは、さくらインターネットの仕様。

テーブル名は、

■コマンドを記したphpファイルを作成、実行

WEBサーバーのtest.csvと同じディレクトリに

<?PHP

$import = "mysqlimport -h mysql***.db.sakura.ne.jp -u aaa -p(パスワード) -L --fields-terminated-by=',' aaa_xxx /home/aaa/www/test.csv" ;
system($import) ;

?>


というphpファイル(import.phpとする)を置き、これを実行。

・-h mysql***.db.sakura.ne.jp ホスト
・-u aaa DBアクセス用のユーザ名
・-p(パスワード) DBアクセス用のパスワード、-pに続けて(スペースなしに)書くこと
・-L ローカルファイルから読み込むオプション
・--fields-terminated-by=',' csvファイルの区切文字、今回は「,(半角カンマ)」
・aaa_xxx DB名
・/home/aaa/www/test.csv csvファイルの場所

すると、

aaa_xxx.test: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

と表示され、以下のようにデータが格納される。

csvの内容が格納されている
csvの内容が格納されている

さて、上のmysqlimport〜にて、DB名の指定はあるが、テーブル名の指定はないのか?

と疑問に思うが、test.csvの拡張子を取ったものが、テーブル名なのだ。

つまり、ファイルがtest.txtの場合も、testがテーブル名。

ファイル名とテーブル名が異なる場合は?

import.phpをリロードすると、データが「追記」されてしまう。

2回格納されている
2回格納されている

これを防ぐには、

--delete インポートする前にテーブルを空にする

のオプションを追加する。

これで、格納前に空にしてくれるので、追記は回避できる。

なお、原因は不明だが -D ではエラーとなる(エラー表示はないが格納されない)。

--delete なら問題なし。

--オプションに対し、-省略形、つまり、

--delete と -D は同意という理解は間違っているのか?

関連:mysqlimport --helpの内容

このphpファイルを定期的に実行するには、cronに登録すればよい。

cd /home/aaa/www ; /usr/local/bin/php test.php > /dev/null

test.phpのあるディレクトリ ; PHPのコマンドパス プログラムを記したphpファイル メール設定

最後の >/dev/null は postmaster@aaa.sakura.ne.jp へメールを送らない場合に書く。

そして、cronが実行できるように、test.phpのパーミッションを755に設定。

設定後は、指定した間隔や時間に、問題なくDBに格納されるかを確認する。

メールを送る設定であれば Permission denied 等のエラーが postmaster@aaa.sakura.ne.jp に送られるので、確実に動くことを確認するまでは >/dev/null を付けない方がいい。

cronは5個までしか設定できないようだが、コントロールパネルからではなく、サーバーに接続しコマンドでやれば、5個以上可能?

関連:さくらインターネットのCRONの設定画面(1.CRONの設定)

関連:さくらインターネットのCRONの設定画面(2.CRONスケジュール設定)





MySQL全機能バイブル ~現場で役立つAtoZ~
鈴木 啓修
技術評論社
売り上げランキング: 170,416
[PR] au WALLETカードの情報 - auのプリペイドカードでショッピングをおトクに!

このエントリーをはてなブックマークに追加 mixiチェック Share on Tumblr Clip to Evernote



mysqlimport --help の内容を以下に付しておく。

試験サーバー(Debian)上で実行した内容である。

-----

mysqlimport Ver 3.7 Distrib 5.1.73, for debian-linux-gnu (x86_64)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Loads tables from text files in various formats.
The base name of the text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and read the text file directly.
In other cases the client will open the text file.
The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:

--print-defaults
Print the program argument list and exit.

--no-defaults
Don't read default options from any option file.

--defaults-file=#
Only read default options from the given file #.

--defaults-extra-file=#
Read this file after the global files are read.

--character-sets-dir=name
Directory for character set files.

--default-character-set=name
Set the default character set.

-c, --columns=name
Use only these columns to import the data to. Give the column names in a comma separated list.
This is same as giving columns to LOAD DATA INFILE.

-C, --compress
Use compression in server/client protocol.

-#, --debug[=name]
Output debug log. Often this is 'd:t:o,filename'.

--debug-check
Check memory and open file usage at exit.

--debug-info
Print some debug info at exit.

-d, --delete
First delete all rows from table.

--fields-terminated-by=name
Fields in the input file are terminated by the given string.

--fields-enclosed-by=name
Fields in the import file are enclosed by the given character.

--fields-optionally-enclosed-by=name
Fields in the input file are optionally enclosed by the given character.

--fields-escaped-by=name
Fields in the input file are escaped by the given character.

-f, --force
Continue even if we get an SQL error.

-?, --help
Displays this help and exits.

-h, --host=name
Connect to host.

-i, --ignore
If duplicate unique key was found, keep old row.

--ignore-lines=#
Ignore first n lines of data infile.

--lines-terminated-by=name
Lines in the input file are terminated by the given string.

-L, --local
Read all files through the client.

-l, --lock-tables
Lock all tables for write (this disables threads).

--low-priority
Use LOW_PRIORITY when updating the table.

-p, --password[=name]
Password to use when connecting to server.
If password is not given it's asked from the tty.

-P, --port=#
Port number to use for connection or 0 for default to, in order of preference,
my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).

--protocol=name
The protocol to use for connection (tcp, socket, pipe, memory).

-r, --replace
If duplicate unique key was found, replace old row.

-s, --silent
Be more silent.

-S, --socket=name
The socket file to use for connection.

--ssl
Enable SSL for connection (automatically enabled with other flags).Disable with --skip-ssl.

--ssl-ca=name
CA file in PEM format (check OpenSSL docs, implies --ssl).

--ssl-capath=name
CA directory (check OpenSSL docs, implies --ssl).

--ssl-cert=name
X509 cert in PEM format (implies --ssl).

--ssl-cipher=name
SSL cipher to use (implies --ssl).

--ssl-key=name
X509 key in PEM format (implies --ssl).

--ssl-verify-server-cert
Verify server's "Common Name" in its cert against hostname used when connecting.
This option is disabled by default.

--use-threads=#
Load files in parallel.
The argument is the number of threads to use for loading data.

-u, --user=name
User for login if not current user.

-v, --verbose
Print info about the various stages.

-V, --version
Output version information and exit.

注意すべきことは、パスワードの設定。

-pの後にスペースを開けずにパスワードを書くこと。

パスワードがpasswordの場合、

-ppassword

とする。

関連:csvファイルのMySQLへのインポート(LOAD DATA LOCAL INFILE,mysqlimport)さくらインターネット

基礎からのMySQL 改訂版 (プログラマの種シリーズ SE必修! )
西沢 夢路
ソフトバンククリエイティブ
売り上げランキング: 4,031
[PR] au WALLETカードの情報 - auのプリペイドカードでショッピングをおトクに!

このエントリーをはてなブックマークに追加 mixiチェック Share on Tumblr Clip to Evernote

このページのトップヘ