MYSQL RANGE COLUMNS partitioning

データ量の多いテーブルでWHERE句で効率的にインデックスを利用出来ないテーブルや、
テーブルを分ける事が出来ないログなどを蓄積しておくテーブルにはパーティショニングが向いている可能性あります。
特に大きなテーブルでTruncateが出来ないテーブルでのデータ削除は非常に時間がかかるので、
Partitionを組んでPartition単位でデータ削除が出来るのは非常に助かります。
但し常にパフォーマンスが上がるわけでは無いので、どのような用途にテーブルが利用されているかまずは確認して下さい。

今回の検証では、既存テーブルを変更するので先ずはDBダンプしました。

aws$ mysqldump --all-databases --single-transaction --flush-logs > /home/ec2-user/mysql_dump20120929.sql  -u root -p
Enter password:
aws$ ls -l /home/ec2-user/mysql_dump20120929.sql
-rw-rw-r-- 1 ec2-user ec2-user 1119781 Sep 29 00:59 /home/ec2-user/mysql_dump20120929.sql
aws$

テーブルのデータサイズを予め確認しておいても良いです。

mysql> select
    -> table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
    -> floor((data_length+index_length)/1024/1024) as allMB,
    -> floor((data_length)/1024/1024) as dMB,
    -> floor((index_length)/1024/1024) as iMB
    -> from information_schema.tables
    -> where table_schema=database()
    -> order by (data_length+index_length) desc;

テーブルを変更するので先ずは事前にデータ数の確認。
現在はあまりデータが無いですが、将来的に増える可能性があるテーブルを選択。

mysql> select count(*) from wp01_posts;
+----------+
| count(*) |
+----------+
|       77 |
+----------+
1 row in set (0.00 sec)

失敗した時の為にデータを簡単にLOADする為にテーブルのデータだけバックアップしておく。
ファイルに落とすと同時に、ディスクに余裕があるので既存テーブルをRENAMEしていた。

mysql> SELECT * INTO OUTFILE '/tmp/wp01_posts.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM WP01.wp01_posts;
Query OK, 77 rows affected (0.03 sec)

mysql>

mysql> ALTER TABLE wp01_posts RENAME TO wp01_posts_0929;
Query OK, 0 rows affected (0.05 sec)

事前確認とバックアップ

パーティションを適用したテーブルを作成。

mysql> CREATE TABLE `wp01_posts` (
    ->   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_content` longtext NOT NULL,
    ->   `post_title` text NOT NULL,
    ->   `post_excerpt` text NOT NULL,
    ->   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
    ->   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
    ->   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
    ->   `post_password` varchar(20) NOT NULL DEFAULT '',
    ->   `post_name` varchar(200) NOT NULL DEFAULT '',
    ->   `to_ping` text NOT NULL,
    ->   `pinged` text NOT NULL,
    ->   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `post_content_filtered` longtext NOT NULL,
    ->   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   `guid` varchar(255) NOT NULL DEFAULT '',
    ->   `menu_order` int(11) NOT NULL DEFAULT '0',
    ->   `post_type` varchar(20) NOT NULL DEFAULT 'post',
    ->   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
    ->   `comment_count` bigint(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`ID`,`post_date`),
    ->   KEY `post_name` (`post_name`),
    ->   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
    ->   KEY `post_parent` (`post_parent`),
    ->   KEY `post_author` (`post_author`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> /*!50500 PARTITION BY RANGE COLUMNS(`post_date`)
    -> (
    ->  PARTITION  p001  VALUES LESS THAN ('2012-12-31') ENGINE = InnoDB,
    ->  PARTITION  p002  VALUES LESS THAN ('2013-12-31') ENGINE = InnoDB,
    ->  PARTITION  p003  VALUES LESS THAN ('2014-12-31') ENGINE = InnoDB,
    ->  PARTITION  p004  VALUES LESS THAN ('2015-12-31') ENGINE = InnoDB,
    ->  PARTITION  p005  VALUES LESS THAN ('2016-12-31') ENGINE = InnoDB,
    ->  PARTITION  pmax   VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
    -> ) */
    -> ;
Query OK, 0 rows affected (0.16 sec)

mysql>

create and insert

テーブルを作成し終わったのでデータをINSERTしてデータ量を確認。
サイトの表示も合わせて確認しておく。

mysql> insert into wp01_posts select * from wp01_posts_0929;
Query OK, 77 rows affected (0.04 sec)
Records: 77  Duplicates: 0  Warnings: 0

mysql>


mysql> select count(*) from WP01.wp01_posts;
+----------+
| count(*) |
+----------+
|       77 |
+----------+
1 row in set (0.00 sec)

mysql>

create table

RENAMEしたテーブルはサイトの動作確認とパフォーマンス等に問題が無い事が確認出来るまで残しておく。

参考サイト:
18.2.3.1. RANGE COLUMNS partitioning
15.2.1. RANGE パーティショニング
15.3.1. RANGE と LIST パーティションの管理
パーティショニングの使用例 – カーディナリティが低いカラムを使って検索する場合
パーティショニングの使用例 – http session情報
MYSQLにてTABLE名の変更
MySQL 5.5.4が与えるインパクト。
ソーシャルゲームのためのMySQL入門


先日、MYSQLを5.5.27に更新したのでinformation_schemaを確認すると同時に
information_schema.tablesを改めて確認してみる。

MYSQL Version 5.5.27のinformation_schema

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_CMP_RESET                      |
| INNODB_TRX                            |
| INNODB_CMPMEM_RESET                   |
| INNODB_LOCK_WAITS                     |
| INNODB_CMPMEM                         |
| INNODB_CMP                            |
| INNODB_LOCKS                          |
+---------------------------------------+
37 rows in set (0.00 sec)

mysql>

information_schema

information_schema.tablesの基本構造

mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

information_schema_tables

DBインスタンスで利用されているENGINEの種類

mysql> select distinct engine from information_schema.tables;
+--------------------+
| engine             |
+--------------------+
| MEMORY             |
| MyISAM             |
| InnoDB             |
| CSV                |
| PERFORMANCE_SCHEMA |
+--------------------+
5 rows in set (0.00 sec)

mysql>

InnoDBは自分で作成したテーブルがメインなので今回はあえて、
MYSQL、MEMORYエンジンなどを確認してみる。

MyISAMを利用しているTABLE

mysql> select table_schema, table_name
    -> from information_schema.tables
    -> where engine='MyISAM';
+--------------------+---------------------------+
| table_schema       | table_name                |
+--------------------+---------------------------+
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | columns_priv              |
| mysql              | db                        |
| mysql              | event                     |
| mysql              | func                      |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | host                      |
| mysql              | ndb_binlog_index          |
| mysql              | plugin                    |
| mysql              | proc                      |
| mysql              | procs_priv                |
| mysql              | proxies_priv              |
| mysql              | servers                   |
| mysql              | tables_priv               |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| mysql              | user                      |
+--------------------+---------------------------+
31 rows in set (0.01 sec)

CSVを利用しているテーブル

mysql> select table_schema, table_name
    -> from information_schema.tables
    -> where engine='CSV';
+--------------+-------------+
| table_schema | table_name  |
+--------------+-------------+
| mysql        | general_log |
| mysql        | slow_log    |
+--------------+-------------+
2 rows in set (0.01 sec)

mysql>

information_schema_tables_csv

MEMORYを利用しているテーブル

mysql> select table_schema, table_name
    -> from information_schema.tables
    -> where engine='MEMORY';
+--------------------+---------------------------------------+
| table_schema       | table_name                            |
+--------------------+---------------------------------------+
| information_schema | CHARACTER_SETS                        |
| information_schema | COLLATIONS                            |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMN_PRIVILEGES                     |
| information_schema | ENGINES                               |
| information_schema | FILES                                 |
| information_schema | GLOBAL_STATUS                         |
| information_schema | GLOBAL_VARIABLES                      |
| information_schema | KEY_COLUMN_USAGE                      |
| information_schema | PROFILING                             |
| information_schema | REFERENTIAL_CONSTRAINTS               |
| information_schema | SCHEMATA                              |
| information_schema | SCHEMA_PRIVILEGES                     |
| information_schema | SESSION_STATUS                        |
| information_schema | SESSION_VARIABLES                     |
| information_schema | STATISTICS                            |
| information_schema | TABLES                                |
| information_schema | TABLESPACES                           |
| information_schema | TABLE_CONSTRAINTS                     |
| information_schema | TABLE_PRIVILEGES                      |
| information_schema | USER_PRIVILEGES                       |
| information_schema | INNODB_CMP_RESET                      |
| information_schema | INNODB_TRX                            |
| information_schema | INNODB_CMPMEM_RESET                   |
| information_schema | INNODB_LOCK_WAITS                     |
| information_schema | INNODB_CMPMEM                         |
| information_schema | INNODB_CMP                            |
| information_schema | INNODB_LOCKS                          |
+--------------------+---------------------------------------+
28 rows in set (0.01 sec)

mysql>

詳細はまた次回再確認確認


SanDisk Extreme USB3.0 高速フラッシュメモリ SDCZ80 32GBのパフォーマンスが良いと聞いたので、
普段使いのUSBとして使えると思い購入してみました。

実際にパフォーマンスを確認してみたら、以前パフォーマンスを計測したときと同じマシーンで
ローカルドライブやSDカードと比較してもかなり良い数値が計測出来ました。

OLD PC
crystaldisk

NEW PC
latest-pc
昨年購入したノートPCのローカルドライブはやはり5年前に購入したノートPCと比較してもローカルドライブの処理スピードが大幅に違う様です。

前回の検証
CrystalDiskMarkによるi/o性能確認

製品
SanDisk Extreme USB3.0 高速フラッシュメモリ SDCZ80 32GB サンディスク 海外リテール[並行輸入品]


ngrep
パケットに含まれるテキストデータの内容を基にキャプチャしたい
場合などに利用出来るngrepについて。

ngrepのインストール

[root@colinux ~]# yum install ngrep
fedora 100% |=========================| 2.1 kB 00:00
updates 100% |=========================| 2.3 kB 00:00
Setting up Install Process
Parsing package install arguments
Resolving Dependencies
–> Running transaction check
—> Package ngrep.i386 0:1.45-1.fc7 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
ngrep i386 1.45-1.fc7 fedora 28 k

Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total download size: 28 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): ngrep-1.45-1.fc7.i 100% |=========================| 28 kB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: ngrep ######################### [1/1]

Installed: ngrep.i386 0:1.45-1.fc7
Complete!

ngrep

パスが通ってないようなので、必要であればパスを通しておく。

[root@colinux ~]# ngrep
bash: ngrep: command not found
[root@colinux ~]#

[root@colinux etc]# whereis ngrep
ngrep: /usr/sbin/ngrep /usr/share/man/man8/ngrep.8.gz
[root@colinux etc]#

[root@colinux etc]# vi /etc/profile.d/sbin.sh
if ! echo ${PATH} | /bin/grep -q /usr/sbin ; then
        PATH=/usr/sbin:${PATH}
fi

[root@colinux etc]# chmod 644 /etc/profile.d/sbin.sh

パスが通ったのでヘルプを見てコマンド確認。
ngrep help

先ずは、以下のコマンドでMYSQLのPORT:3306へのアクセスを確認してみる。
ngrep -W byline -q port 3306
ngrep session check

暗号化されていなければ、実行されたSQLの詳細やデータなどが良く分かる。
show tables details

tcpdumpなどでも分かるが、こちらはDefaultでGrepなので使いやすいかもしれません。

TAR BALLの場合
ダウンロード

[aws_user@192.168.11.36 tmp]$ wget 'http://sourceforge.jp/frs/g_redir.php?m=jaist&f=%2Fngrep%2Fngrep%2F1.45%2Fngrep-1.45.tar.bz2'
[aws_user@192.168.11.36 tmp]$ ls -l
total 13992
-rw-rw-r-- 1 aws_user aws_user 10363484 Nov  1 09:03 clustrix-common-v4.0-526.x86_64.rpm
-rw-rw-r-- 1 aws_user aws_user  3402668 Nov  1 09:03 clustrix-devnode-v4.0-8097.x86_64.rpm
-rw-rw-r-- 1 aws_user aws_user      234 Aug  8 18:55 fizzbuzz.c
-rwxrwxr-x 1 aws_user aws_user     5229 Aug  8 18:55 fizzbuzz.out
-rwxr-xr-x 1 aws_user aws_user      214 Aug  8 18:29 fizzbuzz.pl
-rw-rw-r-- 1 aws_user aws_user    18645 Aug 17 11:55 index.html
-rw-rw-r-- 1 aws_user aws_user    19142 Nov 12 15:23 index.html.1
-rw-rw-r-- 1 aws_user aws_user   463361 Nov 28  2006 ngrep-1.45.tar.bz2
-rw-rw-r-- 1 aws_user aws_user     3354 Nov  1 09:08 README.txt
drwxrwxr-x 2 aws_user aws_user     4096 Aug 10 08:25 wget
[aws_user@192.168.11.36 tmp]$ tar xvf ngrep-1.45.tar.bz2
ngrep-1.45/
ngrep-1.45/win32/
ngrep-1.45/win32/ngrep.sln
ngrep-1.45/win32/ngrep.vcproj
ngrep-1.45/win32/support/
ngrep-1.45/win32/support/getopt.c
ngrep-1.45/win32/support/getopt.h
ngrep-1.45/win32/support/inet_ntop.c
ngrep-1.45/win32/support/inet_ntop.h

... 省略

ngrep-1.45/pcre-5.0/libpcre.def
ngrep-1.45/pcre-5.0/libpcreposix.def
ngrep-1.45/pcre-5.0/RunTest.in
ngrep-1.45/pcre-5.0/configure
ngrep-1.45/pcre-5.0/install-sh
ngrep-1.45/pcre-5.0/mkinstalldirs
ngrep-1.45/pcre-5.0/config.guess
ngrep-1.45/pcre-5.0/config.sub
ngrep-1.45/pcre-5.0/ltmain.sh
[aws_user@192.168.11.36 tmp]$ ls -l
total 13996
-rw-rw-r-- 1 aws_user aws_user 10363484 Nov  1 09:03 clustrix-common-v4.0-526.x86_64.rpm
-rw-rw-r-- 1 aws_user aws_user  3402668 Nov  1 09:03 clustrix-devnode-v4.0-8097.x86_64.rpm
-rw-rw-r-- 1 aws_user aws_user      234 Aug  8 18:55 fizzbuzz.c
-rwxrwxr-x 1 aws_user aws_user     5229 Aug  8 18:55 fizzbuzz.out
-rwxr-xr-x 1 aws_user aws_user      214 Aug  8 18:29 fizzbuzz.pl
-rw-rw-r-- 1 aws_user aws_user    18645 Aug 17 11:55 index.html
-rw-rw-r-- 1 aws_user aws_user    19142 Nov 12 15:23 index.html.1
drwxr-xr-x 7 aws_user aws_user     4096 Nov 28  2006 ngrep-1.45
-rw-rw-r-- 1 aws_user aws_user   463361 Nov 28  2006 ngrep-1.45.tar.bz2
-rw-rw-r-- 1 aws_user aws_user     3354 Nov  1 09:08 README.txt
drwxrwxr-x 2 aws_user aws_user     4096 Aug 10 08:25 wget
[aws_user@192.168.11.36 tmp]$ cd ngrep-1.45
[aws_user@192.168.11.36 ngrep-1.45]$ ls -l
total 340
-rw-r--r-- 1 aws_user aws_user  44208 Oct 18  2006 config.guess
-rw-r--r-- 1 aws_user aws_user    854 Nov 28  2006 config.h.in
-rw-r--r-- 1 aws_user aws_user  32560 Oct 18  2006 config.sub
-rwxr-xr-x 1 aws_user aws_user 155425 Nov 15  2006 configure
-rw-r--r-- 1 aws_user aws_user   9916 Nov 15  2006 configure.in
drwxr-xr-x 2 aws_user aws_user   4096 Nov 28  2006 doc
-rwxr-xr-x 1 aws_user aws_user   5598 Sep 26  2004 install-sh
-rw-r--r-- 1 aws_user aws_user   1840 Nov 16  2006 LICENSE.txt
-rw-r--r-- 1 aws_user aws_user   2986 Nov 28  2006 Makefile.in
-rw-r--r-- 1 aws_user aws_user  15480 Nov 28  2006 ngrep.8
-rw-r--r-- 1 aws_user aws_user  36033 Nov 28  2006 ngrep.c
-rw-r--r-- 1 aws_user aws_user   2700 Nov 28  2006 ngrep.h
drwxr-xr-x 2 aws_user aws_user   4096 Nov 15  2006 pcre-5.0
drwxr-xr-x 4 aws_user aws_user   4096 Nov 28  2006 regex-0.12
drwxr-xr-x 2 aws_user aws_user   4096 Sep 26  2004 scripts
drwxr-xr-x 3 aws_user aws_user   4096 Nov 16  2006 win32

CONFIGURE~INSTALLまで

[aws_user@192.168.11.36 ngrep-1.45]$ ./configure

Configuring System ...

checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking target system type... i686-pc-linux-gnu
checking for gcc... gcc
checking for gcc option to accept ISO C89... none needed

...省略

configure: creating ./config.status
config.status: creating Makefile
config.status: creating config.h
[aws_user@192.168.11.36 ngrep-1.45]$ make
make  -C regex-0.12 regex.o
make[1]: Entering directory `/home/aws_user/tmp/ngrep-1.45/regex-0.12'
gcc -g  -DSTDC_HEADERS=1 -DHAVE_STRING_H=1 -DHAVE_ALLOCA_H=1 -DHAVE_ALLOCA=1  -I. -I. -c regex.c
make[1]: Leaving directory `/home/aws_user/tmp/ngrep-1.45/regex-0.12'
gcc -g -O2 -DLINUX -DHAVE_CONFIG_H  -D_BSD_SOURCE=1 -D__FAVOR_BSD=1  -I. -I/usr/include  -g -c ngrep.c
gcc -g -O2 -DLINUX -DHAVE_CONFIG_H  -D_BSD_SOURCE=1 -D__FAVOR_BSD=1  -L/usr/lib -s -o ngrep ngrep.o  regex-0.12/regex.o -lpcap
[aws_user@192.168.11.36 ngrep-1.45]$ sudo -s
[sudo] password for aws_user:
[root@192.168.11.36 ngrep-1.45]# make install
./install-sh -c -m 0755 ngrep  //usr/local/bin/ngrep
./install-sh -c -m 0644 ngrep.8 //usr/local/share/man/man8/ngrep.8
[root@192.168.11.36 ngrep-1.45]# 

実行してみる。

[root@192.168.11.36 ngrep-1.45]# ngrep -W byline -q port 80
interface: eth0 (192.168.11.0/255.255.255.0)
filter: (ip) and ( port 80 )

T 192.168.11.101:12814 -> 192.168.11.36:80 [AP]
GET /nagios/ HTTP/1.1.
Via: 1.1 TMG03.
Accept-Encoding:gzip.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; ja; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3.
Host: 192.168.11.36.
Keep-Alive: 115.
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8.
Accept-Language: ja,en-us;q=0.7,en;q=0.3.
Accept-Charset: Shift_JIS,utf-8;q=0.7,*;q=0.7.
Connection: Keep-Alive.
.


T 192.168.11.36:80 -> 192.168.11.101:12814 [AP]
HTTP/1.1 401 Authorization Required.
Date: Mon, 12 Nov 2012 06:59:18 GMT.
Server: Apache.
WWW-Authenticate: Basic realm="Nagios Access".
Content-Length: 401.
Connection: close.
Content-Type: text/html; charset=iso-8859-1.
.
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>401 Authorization Required</title>
</head><body>
<h1>Authorization Required</h1>
<p>This server could not verify that you
are authorized to access the document
requested.  Either you supplied the wrong
credentials (e.g., bad password), or your
browser doesn't understand how to supply
the credentials required.</p>
</body></html>


T 192.168.11.101:13132 -> 192.168.11.36:80 [AP]
GET /favicon.ico HTTP/1.1.
Via: 1.1 TMG03.
Accept-Encoding:gzip.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; ja; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3.
Host: 192.168.11.36.
Keep-Alive: 115.
Accept: image/png,image/*;q=0.8,*/*;q=0.5.
Accept-Language: ja,en-us;q=0.7,en;q=0.3.
Accept-Charset: Shift_JIS,utf-8;q=0.7,*;q=0.7.
Connection: Keep-Alive.
.


T 192.168.11.36:80 -> 192.168.11.101:13132 [AP]
HTTP/1.1 404 Not Found.
Date: Mon, 12 Nov 2012 06:59:22 GMT.
Server: Apache.
Content-Length: 209.
Connection: close.
Content-Type: text/html; charset=iso-8859-1.
.
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>404 Not Found</title>
</head><body>
<h1>Not Found</h1>
<p>The requested URL /favicon.ico was not found on this server.</p>
</body></html>

[root@192.168.11.36 ngrep-1.45]#

参考サイト

ngrepでパケットをキャプチャしてgrep
通信内容をわかりやすくキャプチャできるngrep
Network Grepで手軽なパケットキャプチャ
bash の環境設定


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]#

egrep,awk,cut,sedコマンドによる文字列操作の見直し。
仕事でよく使うコマンドなので、改めてレビューしてみる。

セキュリティログを確認
■AND検索

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | head
Aug 19 12:56:05 aws sshd[31397]: Invalid user db2inst1 from 123.15.36.218
Aug 19 12:56:08 aws sshd[31401]: Invalid user prueba from 123.15.36.218
Aug 19 12:56:12 aws sshd[31405]: Invalid user postgres from 123.15.36.218
Aug 19 12:56:19 aws sshd[31413]: Invalid user mythtv from 123.15.36.218
Aug 19 12:56:26 aws sshd[31421]: Invalid user mmroot from 123.15.36.218
Aug 19 12:56:33 aws sshd[31429]: Invalid user x from 123.15.36.218
Aug 19 12:56:38 aws sshd[31433]: Invalid user rob from 123.15.36.218
Aug 19 12:56:47 aws sshd[31443]: Invalid user tommy from 123.15.36.218
Aug 19 12:57:12 aws sshd[31467]: Invalid user www from 123.15.36.218
Aug 19 12:57:19 aws sshd[31475]: Invalid user nagios from 123.15.36.218
AWS$

and

■OR検索

AWS$cat secure-20120826 | egrep -i "postgres|nagios" | head
Aug 19 12:56:12 aws sshd[31405]: Invalid user postgres from 123.15.36.218
Aug 19 12:56:12 aws sshd[31406]: input_userauth_request: invalid user postgres
Aug 19 12:57:19 aws sshd[31475]: Invalid user nagios from 123.15.36.218
Aug 19 12:57:19 aws sshd[31476]: input_userauth_request: invalid user nagios
Aug 19 13:01:23 aws sshd[31714]: Invalid user nagios from 123.15.36.218
Aug 19 13:01:23 aws sshd[31715]: input_userauth_request: invalid user nagios
Aug 19 13:10:16 aws sshd[32250]: Invalid user nagios from 123.15.36.218
Aug 19 13:10:16 aws sshd[32251]: input_userauth_request: invalid user nagios
Aug 25 06:11:34 aws sshd[4726]: Invalid user nagios from 221.13.104.162
Aug 25 06:11:34 aws sshd[4727]: input_userauth_request: invalid user nagios
AWS$

or

■awkにて特定列抽出

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $8}' | head
db2inst1
prueba
postgres
mythtv
mmroot
x
rob
tommy
www
nagios
AWS$ 

■awkで抽出したデータから同じ値を纏めて合計を表示して多い順に表示する。

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $8}' | sort | uniq -c | sort -nr
     19 oracle
     12 nagios
     10 mythtv
      7 test
      6 user0
      6 info
      5 test1
      5 backup
      4 testuser
      4 jack
      4 i-heart
[省略...]

AWS$ 

awk

■awkにてIPのみ抽出

AWS$cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $10}' | head
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218
123.15.36.218

■awkにてIPのみ抽出して合計が多い順に並べる

AWS$ cat secure-20120826 | egrep -i "Invalid user.*from" | awk '{print $10}' | sort | uniq -c | sort -nr
    174 123.15.36.218
     51 141.89.97.171
     49 221.13.104.162
      7 101.44.1.134
      4 140.120.90.196
      1 187.16.247.187
      1 183.59.9.150
AWS$

cat

※何処からログインを試みているか念の為確認。
access

■awkにてwebログから日付を抽出

AWS$cat access_log-20120826 |  awk '{print $4,$5}' | head
[19/Aug/2012:03:41:49 +0900]
[19/Aug/2012:03:41:49 +0900]
[19/Aug/2012:03:41:50 +0900]
[19/Aug/2012:06:23:22 +0900]
[19/Aug/2012:06:23:22 +0900]
[19/Aug/2012:06:56:52 +0900]
[19/Aug/2012:06:56:52 +0900]
[19/Aug/2012:08:07:44 +0900]
[19/Aug/2012:08:07:44 +0900]
[19/Aug/2012:12:52:29 +0900]
AWS$

■awkにてwebログから特定の列を抽出してから、cutコマンドで特定文字のみを抜き出す。

AWS$cat access_log-20120826 |  awk '{print $4}' | head
[19/Aug/2012:03:41:49
[19/Aug/2012:03:41:49
[19/Aug/2012:03:41:50
[19/Aug/2012:06:23:22
[19/Aug/2012:06:23:22
[19/Aug/2012:06:56:52
[19/Aug/2012:06:56:52
[19/Aug/2012:08:07:44
[19/Aug/2012:08:07:44
[19/Aug/2012:12:52:29
AWS$
AWS$cat access_log-20120826 |  awk '{print $4}' | cut -c 2-12 | head
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
AWS$ 

cut

■awkにてwebログから特定の列を抽出してから、cutコマンドで時間のみを抜き出す。

AWS$cat access_log-20120826 |  awk '{print $4,$5}' | cut -d: -f 2-3 | head
03:41
03:41
03:41
06:23
06:23
06:56
06:56
08:07
08:07
12:52
AWS$

■awkにて特定列の抽出後にcutにて特定範囲のみを抜き出しsed特定文字を入れ替える。

AWS$cat access_log-20120826 |  awk '{print $4}' | cut -c 2-12 | head
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
19/Aug/2012
AWS$

■sedにて/をtabにて入れ替え

AWS$cat access_log-20120826 |  awk '{print $4}' | cut -c 2-12 | sed -e 's/\//\t/g' | head
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
19      Aug     2012
AWS$

sed

■awkその他集計等

[root@HOME001 log]# cat number_cout | awk '{ print $1}'
1
2
3
4
5
6
7
8
9
10
[root@HOME001 log]# cat number_cout | awk '{ sum += $1 } END { print sum }'
55
[root@HOME001 log]# cat number_cout | awk '{ sum += $1; num++ } END { print "sum = " sum; print "average = " sum/num }'         
sum = 55
average = 5.5
[root@HOME001 log]#

参考サイト:
【 sed 】 文字列の置換,行の削除を行う