カテゴリ：データベース

2014年06月08日

[解決]ローカル内の他の端末からMySQLに接続できない件[my.cnf,bind-address]

DebianにMySQLを入れてDBサーバーとし、ローカル環境に置く。

試験サーバーをローカルに立てて、作業用端末からアクセスすることはよくあることだ。

が、デフォルトでは、MySQLの入った端末からは当然アクセスできるが(自己内)、ローカル内の他の端末からはアクセスできない。

これを可能にするには、MySQLの入った端末(DBサーバー)の

/etc/mysql/my.cnf (MySQLの設定ファイル)

を編集する。

なお、Debianに限らずLinuxでは、/etcは設定ファイルを格納するディレクトリである。

/etc/mysql/my.cnf

に

bind-address = 127.0.0.1

という記述があるので、これを削除するか、先頭に#(コメント行)を付けて、

# bind-address = 127.0.0.1

とし、

/etc/init.d/mysql restart

で、MySQLの再起動。

再起動後、DBサーバーに接続できるかを確認する。

これで解決はするのだが、このbind-addressは、接続したいMySQLが動いている端末のIPアドレスであり、接続を許可するIPアドレスではないのだ。

従って、接続できる端末を増やそうとして

bind-address = 127.0.0.1

bind-address = 192.168.0.10

bind-address = 192.168.0.20

と併記しても意味がない、というか、そもそも無効。

繰り返すが、bind-addressは接続を許可するIPアドレスではなく、これで接続してくる端末のIPを制限するものではない。

なお、MySQLの入った端末(DBサーバー)にApache等のWEBサーバーが入っており、そのWEBサーバーからMySQLにアクセスする場合は、自己内なので何もする必要はない。

MySQLの入った端末(DBサーバー)が複数台あり、各々他方へアクセスするような場合も、上記の設定変更が必要。

MySQL全機能バイブル ~現場で役立つAtoZ~

posted with amazlet at 14.06.01

鈴木啓修
技術評論社
売り上げランキング: 199,175

Amazon.co.jpで詳細を見る

タグ：: #MySQL; #my.cnf; #bind-address; #ローカル; #他の端末

[PR] au PAY / au WALLET カード情報

2014年05月24日

csvファイルのMySQLへのインポート (LOAD DATA LOCAL INFILE,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の内容が格納されている

さて、上の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 > /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個以上可能？

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スケジュール設定)

MySQL全機能バイブル ~現場で役立つAtoZ~

posted with amazlet at 14.05.23

鈴木啓修
技術評論社
売り上げランキング: 170,416

Amazon.co.jpで詳細を見る

タグ：: #csv; #MySQL; #インポート; #mysqlimport; #さくらインターネット

[PR] au PAY / au WALLET カード情報

2014年05月23日

mysqlimport --helpの内容 csvファイルの内容をデータベースにインポートする

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

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

関連：続き

タグ：: #mysqlimport; #オプション; #--help; #csv; #インポート

[PR] au PAY / au WALLET カード情報

2014年04月05日

DISTINCTの逆(重複を抽出するSQL文)

以下のようなテーブルがある。

id,item_code,reg_date
1,00A,2014-04-01
2,00B,2014-04-02
3,00C,2014-04-03
4,00A,2014-04-04
5,00B,2014-04-05
6,00A,2014-04-06

ここから、重複を省いて結果を得たい時は DISTINCT を使う。

SELECT DISTINCT `item_code` FROM `テーブル名` ;

00A
00B
00C

しかし「重複しているのは何か」を知りたい時がある。

その場合、以下で対応可能。

SELECT * FORM `テーブル名` GROUP BY `item_code` HAVING COUNT(*) > 1 ;

`item_code`でGROUP化し、その上で件数が1より多い、とすればよい。

1,00A,2014-04-01
2,00B,2014-04-02

但し、GROUP BYを使用しているため、重複している1件は取り出せるが、他は取り出せない。

00Aは3件重複、00Bは2件重複しているが、表示されるのは1件しかなく、その1件はそのうちどれかは未確定である。

関連：MySQLに於けるGROUP BYでのミス(集約キー以外を書いてしまう)

重複しているレコード全てを取り出すには、

SELECT * FORM `テーブル名` WHERE `item_code` IN (SELECT `item_code` FROM `テーブル名` GROUP BY `item_code` HAVING COUNT(*) > 1) ;

とする。

サブクエリは上のSQL文とほぼ同じで、全体からitem_codeが「重複した`item_code`」と同じものを取り出すことで、実現している。

INはORの省略形、との理解で止まりがちだが、条件に合うものを取り出すには、INは非常に適している。

では、ここで例題。

`sp_id`は上司のidを示す。

＜部下のいる人のid,name,wardを抽出セヨ＞

↓

回答：

SELECT `id`,`name`,`ward` FROM `members` WHERE `id` IN (SELECT `sp_id` FROM `members`) ;

SQLアンチパターン

posted with amazlet at 14.03.26

Bill Karwin
オライリージャパン
売り上げランキング: 12,496

Amazon.co.jpで詳細を見る

タグ：: #MySQL; #重複; #DISTINCT; #GROUP; #BY

[PR] au PAY / au WALLET カード情報

2014年04月04日

MySQLに於けるGROUP BYでのミス(集約キー以外を書いてしまう)

テーブル名：商品一覧
商品名,販売単価,仕入単価
品目A,1000,700
品目B,2000,1400
品目C,3000,2100
品目D,1000,700
品目E,2000,1400
品目F,1000,700
...

というテーブルがあるとして、ここから、販売価格でGROUP BYする。

SELECT `販売価格`,COUNT(*)
FROM `商品一覧`
GROUP BY `販売価格`

結果は

販売単価,数
1000,3
2000,2
3000,1

となる。

ここで、

SELECT `商品名`,`販売価格`,COUNT(*)
FROM `商品一覧`
GROUP BY `販売価格`

としてしまうとエラー。

MySQLではエラーとはならずに通ってしまう(※)が、このSQL文に問題があることはすぐに分かる。

商品名,販売単価,数
ア,1000,3
イ,2000,2
ウ,3000,1

アは販売単価が1,000円の集合だが、販売単価が1,000円の商品は、品目A、品目D、品目Fの3商品がある。
で、アには、その内のどれを表示すればいいのか？

イは販売単価が2,000円の集合だが、販売単価が2,000円の商品は、品目B、品目Eの2商品がある。
で、イには、その内のどれを表示すればいいのか？

ウは販売単価が3,000円の集合だが、販売単価が3,000円の商品は、品目Cの1商品だけ。
ウには品目Cを表示すれば済むが、その後品目Gが追加され、その販売単価が3,000円だったら？

GROUP化クエリは、GROUP化したそのGROUP毎に1つにまとめてしまうため、その中身を全て表示することはできない。

GROUP BYを使う時、SELECT句に書ける列名は、GROUP BY句で指定した列名(集約キーという)だけである。

※ MySQLとSQLiteでは、エラーとはならず通ってしまう。
では、上記のアには何を表示するか、だが、実は、これといった決まりがない。

GROUP BY 部から省略したカラムがグループ内で一定していない場合は、この機能を使用しないでください。
サーバはいかなる値もグループから自由に戻すことができ、すべての値が同じでない限り、結果は不確定です。

11.11.3. 非常時フィールドとの GROUP BY および HAVING

つまり、

商品名,販売単価,数
品目A,1000,3

となることもあれば、

商品名,販売単価,数
品目D,1000,3

となることもあれば、

商品名,販売単価,数
品目F,1000,3

となることもあるということ。

参考：SQLアンチパターン 155ページ第14章アンビギュアスグループ(曖昧なグループ)

SQLアンチパターン

posted with amazlet at 14.03.26

Bill Karwin
オライリージャパン
売り上げランキング: 12,496

Amazon.co.jpで詳細を見る

これは非常に危険であるので、エラーではじいてくれた方がいいかもしれない。

また、問題のないSQL文、

SELECT `販売価格`,COUNT(*)
FROM `商品一覧`
GROUP BY `販売価格`

で得られた結果

販売単価,数
1000,3
2000,2
3000,1

だが、この並び順はランダム。

販売単価,数
3000,1
2000,2
1000,3

となるかもしれないし、

販売単価,数
2000,2
1000,3
3000,1

となるかもしれない。

SQLアンチパターン

posted with amazlet at 14.03.26

Bill Karwin
オライリージャパン
売り上げランキング: 12,496

Amazon.co.jpで詳細を見る