自鯖(自由度高)を使うことが多いが、さくらインターネット
のユーザーから質問を受けたのでメモ。
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の内容が格納されている
さて、上のmysqlimport～にて、DB名の指定はあるが、テーブル名の指定はないのか？
と疑問に思うが、test.csvの拡張子を取ったものが、テーブル名なのだ。
つまり、ファイルがtest.txtの場合も、testがテーブル名。
ファイル名とテーブル名が異なる場合は？
import.phpをリロードすると、データが「追記」されてしまう。
2回格納されている
これを防ぐには、
–delete インポートする前にテーブルを空にする
のオプションを追加する。
これで、格納前に空にしてくれるので、追記は回避できる。
なお、原因は不明だが -D ではエラーとなる(エラー表示はないが格納されない)。
–delete なら問題なし。
–オプションに対し、-省略形、つまり、
–delete と -D は同意という理解は間違っているのか？
関連：mysqlimport –helpの内容 csvファイルの内容をデータベースにインポートする
レコードの区切り
項目の区切りは「,(カンマ)」で指定しているが、レコードの区切りは「–lines-terminated-by=”●”」で行う。
例えば、行頭に「^」が入っている(行末でもよい)、以下のようなデータの場合、
^1,aaa,xxx
^2,bbb,yyy
^3,ccc,ddd
「–lines-terminated-by=”^”」とすることで、改行風に扱える。
つまり、以下のように改行がない場合でも、
^1,aaa,xxx^2,bbb,yyy^3,ccc,ddd
「–lines-terminated-by=”^”」で切ることが可能だ。
1件のレコードが短い場合は、改行によってレコードの終端が見えるが、1件のレコードが非常に長い場合、どこまでがレコードの区切りか分からなくなるので、区切り文字を入れておくと、動作確認の際にも役立つ。
オプションに関しては、以下を参照のコト。
関連：mysqlimport –helpの内容 csvファイルの内容をデータベースにインポートする
cronに登録、自動実行
このphpファイルを定期的に実行するには、cronに登録すればよい。
cd /home/aaa/www ; /usr/local/bin/php test.php 1> /dev/null
test.phpのあるディレクトリ ; PHPのコマンドパス プログラムを記したphpファイル メール設定
最後の1> /dev/null は postmaster@aaa.sakura.ne.jp へメールを送らない場合に書く。
そして、cronが実行できるように、test.phpのパーミッションを755に設定。
設定後は、指定した間隔や時間に、問題なくDBに格納されるかを確認する。
メールを送る設定であれば Permission denied 等のエラーが postmaster@aaa.sakura.ne.jp に送られるので、確実に動くことを確認するまでは ＞/dev/null を付けない方がいい。
cronは5個までしか設定できないようだが、コントロールパネルからではなく、サーバーに接続しコマンドでやれば、5個以上可能？
insecure(安全ではない)と言われる場合
上記をcronで実行した時、動作は成功するが、
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
というメールが、管理者宛に届くことがある。
cronに「1> /dev/null」を付けていたとしてもだ。
コマンドラインにパスワードを含んでいるので、安全ではないというコトだ。
これを回避するには、パスワードを別ファイルに記して置き、コマンドラインでソレを読み取ればよい。
パスワードだけを別ファイルにすれば「安全ではない」とは言われなくなるが、どうせなら、ユーザー名とホストも別ファイルにした方が、より安全だろう。
まず、以下のログイン情報を記したファイルを、login.conf(名称は任意)として保存する。
－－－－－
[client]
user = ユーザー名
password = パスワード
host = データベースのホスト
－－－－－
当然、login.confは安全な場所に設置する必要がある。
さくらインターネットなら、
/home/ユーザー名/www
のwwwの中が公開ディレクトリだが、その上(wwwの中ではなく同階層)に置けば安全だろう。
さらに、ファイルのパーミッションを400(所有者の読み取りのみ)にシテオク。
そして、上述のコマンドラインから「-h(ホスト) -u(ユーザー) -p(パスワード)」を削除し、代わりに
–defaults-extra-file=/home/ユーザー名/login.conf
を追加する。
そうすれば、コマンドラインにログイン情報を含まないので、安全ではないとはイワレナくなる。
関連：さくらインターネットのCRONの設定画面(1.CRONの設定)
関連：さくらインターネットのCRONの設定画面(2.CRONスケジュール設定)
技術評論社
売り上げランキング: 170,416
