月別アーカイブ: 2月 2011

MySQLトリガとか

テーブルstockでレコードが削除されるとき自動でバックアップテーブルに挿入するトリガ

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
END..
DELIMITER ;

MySQLのunix_timestamp()関数により削除時間(now)を追加して記録することが出来る。
全てのUPDATE操作をログしたいとき、DELETEをUPDATEに書き換えれば、トリガでログしておけるので、操作側の修正の手間が省ける。

更に、一定以上古いバックアップを削除する機能もつける場合、END..の前に

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
DELETE FROM stock_backup WHERE unix_timestamp(deleted_on)<(unix_timestamp()-60*60*24*7); END.. DELIMITER ;

の様にすれば、トリガ実行タイミングで7日より前のレコードをバックアップテーブルから自動削除できる。

なお、MySQLでは同じテーブルの同じ条件に複数のトリガを設定できないので、同じ条件に機能追加する場合は

DROP TRIGGER stock_auto_backup

のようにしてトリガを一度削除する。

とあるテーブルのAUTO_INCREMENT値を変更する。

ALTER TABLE stock AUTO_INCREMENT = 1;

この場合はstockテーブルを1にしている。

(111)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

MySQLアップデート - epel/remiリポジトリ

 開発用にMySQLの全ログを取りたいのでmy.cnfに

log-slow-queries = /var/log/mysql-all-sql.log
long_query_time = 0

を追記して

#touch /var/log/mysql-all-sql.log
#chown mysql:mysql /var/log/mysql-all-sql.log

として、0秒以上かかるクエリをスロークエリログに入れる(=全て記録する)設定をしたらMySQL起動エラーが出た。 古いMySQLでは、ロングクエリタイムは1秒以上を設定しなければならず、0秒指定は5.1系にしないとダメらしい。
 早速、yumでMySQLを更新しようとするが、デフォルトのリポジトリだとMySQLは5.0.77が最新。 ソースを取ってきてビルドするのもありだが、今回は手軽にyumから入れたいので、リポジトリの追加で対応する。
 新しいバージョンが登録されているリポジトリとしては、epelやremiがメジャーだ。

#wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm
#wget http://rpms.famillecollet.com/enterprise/5/remi/i386/remi-release-5-8.el5.remi.noarch.rpm
#rpm -Uvh remi-release-5-8.el5.remi.noarch.rpm epel-release-5-4.noarch.rpm

※release-x-yの部分はバージョンなので、新しいのが出ると変わるので、エラーが出るときはブラウザでディレクトリを覗いてファイル名を確認する。
 これで各リポジトリの情報が登録される。
 各リポジトリの情報が追加されただけで、yumコマンドを打っただけでは有効にはならないので

yum –enablerepo=epel,remi list

等の様に、–enablerepoで使用するリポジトリを指定する(標準で有効にすることも出来るが、自動アップデートで更新されると都合の悪いときもあるので、コマンドで指定した方が安心)

yum –enablerepo=remi install MySQL

 
 で導入すると5.1.55に更新出来るが、依存関係でPHPも更新しなければならないようだ。 PHPを更新しても特に不都合はないので、まとめて更新した。
MySQLをバージョンアップしたので、おまじない

#mysql_upgrade –password=パスワード

を実行して、データベースをアップグレードしておく。
 PHPの更新で、date系関数でタイムゾーンを設定していないとWarnが出るようになっているので/etc/php.iniの修正が必要になる。

date.timezone=’Tokyo/Asia’

 設定を済ませてapache/mysqlを再起動

#/etc/init.d/httpd restart
#/etc/init.d/mysqld restart

 今回の更新ではセッション関連のエラーが出た。
初期設定でroot:apacheだったphpの実行がdaemon:daemonになっていたので、session保管場所のパーミッション不足でエラーが出ていた。
実行権限をrootに戻しても良いが、daemon:daemonで実行した方がセキュリティ的によさげなので

chown daemon:daemon /var/lib/php/session
rm /var/lib/php/session/*

して、対応した。
 なお、phpMyAdminを使っている場合、2.11系になっていると思うが、PHP5.3では推奨されない機能を使用しているためDeprecatedメッセージが出るので、こちらも対応のバージョンに更新する(remiリポジトリに3.3系が入っている; php.ini設定でDeprecatedメッセージを単純に消すことも出来るが、新しいバージョンを使用しない理由もないので更新した)
phpMyAdminを3系にするとblowfishの設定を求められるので、/usr/share/phpMyAdmin/config.inc.phpで

$cfg[‘blowfish_secret’] = ‘適当な文字列’;

を設定する。
 なお、現在のMySQLではlog-slow-queriesが非推奨になっている。
現在は

slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log

の様に、スロークエリログの有効をslow_query_logに1を与え(0なら無効)、slow_query_log_fileでスロークエリログファイルを指定する。

(234)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

vimでカーソルキーを押すとA,B,C,Dが入力される

vimのinsertモードでカーソルキーを押すとA,B,C,Dが入力される場合

vimのコマンドモードで

:set nocompatible

すると直る。

vimrcで

set nocompatible

を設定しておいてもOK。

(394)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP] | コメントをどうぞ

どーでもいい話

色々
ドメイン管理 Value-domain/DTI
VPS1 ServersMan@VPS プロプラン
VPS2 さくらVPS
共有サーバ Xrea
ローカルサーバ HP ML150G6 Win2K8 Server
回線 フレッツ光ネクスト ハイスピードタイプ
e-mobile
プロバ livedoorプロバイダ 固定IP
bb.excite
証明書 ジオトラスト

どーでもいい話だけど、うちのネットワーク関連設定。
ドメイン管理は昔からのお付き合いと言うことでバリュードメインメインで6ドメイン使ってます。

VPSは2本体制。 最初、DTIのスタンダード契約だったんだけど、プロにするとグローバルIPが4個ついて、仮想CPU4個、占有メモリ1GB(フリー時3GB)で1980円だったので移行した。
グローバルIPが複数あると、IPベースバーチャルホストが使えたり、リンクでSEO効果が多少出て便利。
しかし、DTIのVPSで逆引き(PTR)するにはDTIで登録したドメイン1個しか使えないので、ドメインの逆引き認証したいため、さくらのVPSを追加している(さくらは自由にとれる) 別にDTIの契約を追加しても良いのだけど、IPアドレスが離れているサイトからリンクを張るとSEO効果があると言う風聞に流されてみた。

共有サーバは、これも昔からのお付き合いのXreaで、上物の移動が面倒だったので放置継続している。

ローカルサーバはXeonデュアルとしてはお安いHPサーバ。 省電力Xeon2個とメモリ12GB乗っけてWindowsServer2K8を走らせて、更にVMware上でCentOSを走らせている。 ASP.NETとかいろいろ実験用だ。

回線は東日本のNTT回線では最速200Mタイプ。 プロバイダは超低価格固定IPのライブドアプロバイダとワンコインのbb.exciteで、サーバ用とクライアント用を振り分けている(ルータはサーバ用にCISCO1812Jを入れて会社とのVPN等色々設定、クライアント用はヤマハRTX1000だ)
サブでe-mobileのSIMを光ポータブルに突っ込んであって、出先でも家でもノートはこいつ経由で接続している(光ポータブルはクレードルにイーサネット端子があって、クレードルに載せるとWiFiAPかWiFiルータとして動作する=有線LANのモードがローカルネットとWAN切り替え式)

証明書は激安のジオトラストでさくらとDTIに1本ずつ取っている。 中間証明書の登録が必要なタイプだがかなり安い。

(228)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP] | コメントをどうぞ

SSL設定-中間証明書

 先日入れた証明書はルート証明機関からの証明書を利用していた。
 ルート証明機関から発行された証明書は高額で、最近はルート証明機関から認証された証明機関(中間証明機関;電子証明書には目的という項目があり、普通のSSL証明書はそのWebサーバを証明するのみだが、認証機関には証明書の発行が目的に含まれている)が安価に証明書を発行していたりする。
 この安価な証明書をサーバに導入した場合は、併せて中間証明機関の証明書も導入しないとオレオレ証明書のような警告が発生する。
 中間証明機関の証明書は、ssl.confのバーチャルサーバディレクティブ内

SSLCertificateChainFile /etc/pki/tls/certs/piyo.crt

のように記述して、証明書発行機関が示す証明書を/etc/pki/tls/certs/piyo.crtの中に書いておけば良い。

(573)


カテゴリー: サーバ設定 | コメントをどうぞ

MySQLストアド&トリガ

MySQL5シリーズからサポートされているストアド(ストアドプロシージャ)&トリガ。

ストアドはSQL文をDBに保管(ストア)しておいて呼び出す機能で、解析などが完了した状態で保存されるので、複雑な構文のクエリで応答が高速になったり、簡単な分岐などを用いてまとまった処理を行ったりすることが出来る便利機能(CREATE PROCEDURE)
トリガはINSERTやUPDATE、DELETEを引き金(トリガ)にして処理を行うようにする機能(CREATE TRIGGER)

トリガの使いどころは、在庫表と発注表を用意しておいて、在庫表がUPDATEされてアイテムの数量が0になったら発注表に発注を追加すると言った自動処理や、NEWエイリアスを組み合わせることでINSERTされたレコードに定型処理を加えるというような場合だろう。
また、MySQLのトリガ処理ではタイミングにBEFOREとAFTERを指定することが出来て、BEFOREの場合には実行されるSQL文に先行して実行されるので、条件に満たない値が与えられた場合に引き金となったSQL文の処理を止めたりすることができる。

例:Yahooショップのリンクリストで、#ItemInfoアンカーがつくのを自動で取り除くトリガ

DELIMITER ..
CREATE TRIGGER remove_ii
BEFORE INSERT ON yahoo_link FOR EACH ROW
BEGIN
SET NEW.address = REPLACE(NEW.address, ‘%23ItemInfo’, ”);
END..
DELIMITER ;

yahoo_linkテーブルにINSERT文が発行された場合、address中の#ItemInfoを消している。 NEWは追加される行、あるいは更新後の行を指すエイリアスで、UPDATE文なら更新前の行を指すOLDエイリアスもある。

プログラム側に依存せず、DBシステム上の動作も高速になるため、ストアドやトリガを上手く使うのがDB連携システムのキモ。

(496)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

SSL設定

物販サイトとか、最近の商用サイトではSSL(HTTPS)の設置はほぼ常識だろう。
未だに、一般ユーザにはHTTPSの効果がわからない人もいるようだけど、少しでも知識があれば秘密情報を送るのにHTTPSが必須なのがわかるだろう。
単純に言えば、HTTPSは電子証明書を使って、接続先のサーバ名が証明書発行先の名前と一致することを保証し、かつ、ブラウザからサーバまでの通信が暗号化される。
電子証明書は、最初から端末にいくらかの物が保存されている。 これらの証明書の発行元を信頼された証明書発行機関として、その機関が発行した証明書を信頼できると判断する。
新たに接続したサイトの証明書が、信頼された証明書発行機関から発行された証明書であれば、接続したサイトも信頼できる(電子証明書にはサーバのアドレスが記述されている)
従って、携帯電話等では端末によって初期の証明書が異なり、あまり有名ではない発行機関が発行した証明書が信頼できない証明書と判断されることがある。
また、外部の証明書発行機関に依存せず、最初から自分で証明書を発行することも出来る(いわゆるオレオレ証明書) この場合、サイトの確認に利用するためには、信頼された方法で証明書を端末に登録しておく必要がある(例えば、USBメモリなどに保存してPCに登録する等だが、一度目に接続したサイトを信頼するなら、初回接続時に証明書を登録しても良い)
しかしながら、特定のクライアントに向けたサイト(契約業者向けサイトであるとか)でもない限り、信頼された機関から発行されていない証明書の使用は、逆に信頼度を下げる結果になるだろう(最近のIEやFirefox等のブラウザは、証明書が信頼できませんとでかでかと表示してクライアントに警告する)
それでもオレオレ証明書を使用するのは、通信経路の暗号化が可能であると言うポイントにある。 個人情報をHTTPで送信すれば、ネットワーク上にパケット盗聴器を配置したり、無線LANの電波を盗聴する等することで簡単に情報を盗まれてしまう。 しかし、HTTPSでは、ブラウザの内部からサイト内までの区間は暗号化されているので、無線LAN電波を盗聴されても、情報を入手することは困難である。
そんなわけで、個人サイト等で出先からのアクセスを保護する目的などでオレオレ証明書が使われることがある。
まぁ、有料の信頼された証明書を取得するのがベストであるのは確かだが。

実際にSSLを使用する設定。
基本的に、ServersManに入っているApacheは最初からSSLが使えるので、証明書の準備と設定ファイルを記述するだけで良い。

証明書の準備
A,オレオレ証明書を使う
B,信頼された証明書を使う
どちらのパターンでも、秘密鍵ファイルの作成は共通で

openssl genrsa -des3 2048 -out /etc/pki/tls/private/hoge.pem

DES3(従来のDES暗号を3回かける方式)で2048ビット長の鍵を作る設定 ※古いサイトでは1024ビットを示しているところもあるが、現在は1024ビットでは強度不足とされている。
このとき、秘密鍵を保護するパスフレーズの入力を促されるので、適当なものを設定する。

Aパターンの場合、この秘密鍵を元にサイト証明書(CRT)を作成する。

openssl req -new -x509 -days 365 -key /etc/pki/tls/private/hoge.pem -out /etc/pki/tls/certs/hoge.crt

この場合は、365日間有効なX509証明書を先ほどの秘密鍵を使って作成する。 有効期間は長くても良いが、パスワードのように定期的に更新するのが一般的。
(後記共通項へ)

Bパターンの場合、この秘密鍵を使って証明書要求(CSR)を作成する。

openssl req -new -key /etc/pki/tls/private/hoge.pem -out hoge.csr

先ほどの秘密鍵を使って証明書要求を作成する。 A/Bともコマンドは似ているが、Aは証明書自体を作り、Bは証明書を要求するための中間情報を作成している。

<共通項>
先ほどのパスフレーズや、サイト情報などの入力を促されるので入力していく。 項目名はビルドにより多少異なるので環境毎に読み替え

Enter pass phrase for /etc/pki/tls/private/hoge.pem:先ほどのパスフレーズ
Country Name (2 letter code)[GB]:JP
#2文字の国コードで、日本ならJP
State or Province Name (full name) [Berkshire]:Kanagawa
#都道府県
Locality Name (eg, city) [Newbury]:Yokohama
#市区町村
Organization Name (eg, company) [My Company Ltd]:hogehoge Corp
#団体名(個人の場合はフルネーム等)
Organizational Unit Name (eg, section) []:online sales div
#部署名だが、団体名までが同じ複数の証明書を取得する場合に、この項目を変更して取得する。
Common Name (eg, your name or your server’s hostname) []:ssl.hoge.net
#サイトの完全名を入力する。 サイト確認する為に使われるアドレスになる(異なるアドレスのサイトで証明書を使うと警告が出るので、運用アドレスにしっかりあわせる)
これ以降のEmail Address等の項目は任意でOK

これで証明書ファイルか、証明書要求ファイルが出来る。
Aならこれで証明書準備完了。 Bの場合は、作成されたhoge.csrの中身をSSL証明書発行機関の発行要求Webフォームやメールに添付したりして証明書を要求すると、証明書ファイルが送られてくるので、Aの/etc/pki/tls/certs/hoge.crt等のように配置する。
証明書と間違えて証明書要求ファイルを配置するとApacheがエラーで起動できなくなるし、証明機関に証明書を送っても証明書が作成できないので注意。

Apacheの設定を行う。
ApacheのSSL設定ファイルは↓がデフォルト
/etc/httpd/conf.d/ssl.conf

#HTTPSを受け入れるためにバーチャルホスト設定を使う。
<VirtualHost 210.170.xxx.yyy:443>
#ServerNameディレクティブで完全なホスト名を設定して、証明書のCommon Nameと一致させる。
ServerName ssl.hoge.net
#サイト証明書ファイルを設定する
SSLCertificateFile /etc/pki/tls/certs/hoge.crt
#秘密鍵ファイルを設定する
SSLCertificateKeyFile /etc/pki/tls/private/hoge.pem

この状態でApacheを起動しようとすると、秘密鍵のパスフレーズを要求されるようになるので、自動起動したい場合には
ssl.conf

SSLPassPhraseDialog exec:/root/sslkey.sh

コマンドの標準出力をパスフレーズとして利用しようとするので、echo ‘passphrase’等とシェルに記述して実行権限をつけておけば自動起動できるようになる。

(364)


カテゴリー: サーバ設定 | コメントをどうぞ