postfixでVirtualアカウントの管理をMySQLで

どうも、俺＠仕事中です。
今日はPostfix(2.5.5)のvirtual_mailbox_mapsについてです。
しかもそれをMySQLで管理しちゃおう！という話です。

そもそもvirtual_mailbox_mapsというのは、簡単に説明するとその名の通り
そのサーバのアカウントにないアカウント（仮想アカウント）のメールをメールボックスに保存するというものです。
今回はMySQLを利用しますので、postfix-2.5.5ソースをDLして、まず

# tar zxvf postfix-2.5.5.tar.gz
# cd postfix-2.5.5
# make makefiles AUXLIBS='-L/usr/local/lib -L/usr/local/mysql/lib/mysql -lssl -lcrypto -lmysqlclient -lz -lm' CCARGS='-DHAS_MYSQL -I/usr/local/mysql/include/mysql -DUSE_TLS -I/usr/local/include -DUSE_SASL_AUTH -DDEF_SERVER_SASL_TYPE=\"dovecot\"'

（パスは環境に合わせてね）のコマンドを打ちます。
ここでlibmysqlclient.soが見つからないぜ！的なエラーが出る場合は、
-L/usr/local/mysql/lib/mysqlのパスが正しいかどうか確認し、それも合っている場合は、

# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/mysql

を追加（パスは適宜合わせてね）。で、

# ldconfig -v
# make upgrade

でもう一度 make makefiles を打ちます。次に、

# make && make install（全てEnter)

でインストール完了です。
次に、main.cfの編集です。

# vim /etc/postfix/main.cf
fallback_transport = virtual # ローカルに存在しないアカウント宛の処理
virtual_mailbox_base = /usr/local/virtual # 適宜あわせてね
virtual_mailbox_maps = /etc/postfix/virtual_mailbox_mysql
virtual_uid_maps = static:10000
virtual_gid_maps = static:1000

を追加します。

続いてグループ、ユーザの作成。

# groupadd -g 10000 vgroup
# useradd -u 10000 -g vgroup -d /usr/local/virtual -s /sbin/nologin vuser

次は、virtual_mailbox_mysqlの設定。

# vim /etc/postfix/virtual_mailbox_mysql
hosts = localhost
dbname = mailsystem
user = mysql_user
query = SELECT concat(name,'/') FROM mail_account WHERE active=1 AND domain='%d' AND name='%u'

で、これをhash化します。

# postmap /etc/postfix/virtual_maibox_mysql

続いてMySQLの構築。今回はMySQLは自サーバで稼動しているものとします。

# mysql -h localhost
mysql> create database mailsystem;
mysql> create table mail_account (
id int not null auto_increment,
name varchar(128) not null default '', # virtual_accountの名前
password varchar(128) not null default '', # virtual_accountのPass
domain varchar(128) not null default '', # サーバドメイン
active tinyint(1) not null default 0, # 利用する/しないのフラグ
primary key (id)
);

INSERT INTO mail_account (name, password,domain,active) values ('username','password','mydomain.jp',1);

テーブル操作権限等は適宜指定してください。

では、postfix起動します。

# /usr/sbin/postfix start

これで、MySQLに入れたアカウント宛のメールであれば、/usr/local/virtual以下へ配送されます。

ちなみに、配送の確認をするには

# postmap -a "username@mydomain.jp" mysql:/etc/postfix/virtual_mailbox_mysql

とやって、対象の結果（今回で言うと username/）が返って来ればOKです。
何も表示されない場合は、何か間違えてます。

めでたし、めでたし！お(・∀・)め(・∀・)で(・∀・)と(・∀・)う！

PostgreSQL8.xで外部サーバから接続 めも

お久し、俺＠仕事中です。

postgreSQL8.4.1を使ってて少しハマったのでメモφ(｀д´)ﾒﾓﾒﾓ...

タイトルの通り、外部サーバ<192.168.0.11>から自サーバ<192.168.0.10>（ローカル接続）のpostgreSQLへ接続しようとしてできませんでした。

 $ telnet 192.168.0.10 5432
Trying 192.168.0.10...
telnet: connect to address 192.168.0.10: Connection refused

これはpostgreSQLのバージョン8以降からpostgresql.confに設定漏れがあったことが原因でした。

# vim $PGDATA/postgresql.conf
------------------------------------------
#listen_addresses = 'localhost'     # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all

これは、どのネットワークインターフェースアドレスで接続を許可するか、を設定する項目だそうです。
デフォルトでは「localhost」になっているので、自サーバからの接続のみ許可となっているので

listen_addresses = 'localhost,192.168.0.10'

とするとローカル接続の外部サーバから接続できます。
全て許可する場合は

listen_addresses = '*'

とします。

インターフェースのアドレスを確認するのは、おなじみの

# /sbin/ifconfig

でできますね。


以上でえええす。

iptablesの設定メモ

こんばんわ、俺＠家で音楽鑑賞中です。

今年も残り1ヶ月切ってしまいましたね。
今年の元旦に神社で心に誓った目標の何割が達成できたでしょうか？
ちなみに僕は10の目標のうち4しか達成できませんでしたよ｡･ﾟ･(ﾉε`)･ﾟ･｡ｳﾜｰﾝ


で、今日はiptablesの簡単な設定方法をメモします。
解説サイトは山のようにあるのですが、結構みなさん複雑な事書かれているので
チョチョイっと設定する人向けのメモです。

まずは設定の確認。

# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     all  --  localhost.localdomain  anywhere            
ACCEPT     all  --  192.168.0.0/24      anywhere                               
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:http 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:omirr 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:smtp 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:ftp-data 
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:ftp 
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED 
REJECT     all  --  anywhere             anywhere            reject-with icmp-port-unreachable 

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
ACCEPT     all  --  localhost.localdomain  anywhere            
ACCEPT     all  --  192.168.0.0/24      anywhere            
ACCEPT     all  --  xxx.xxx.xxx.xx/28     anywhere                      
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:tproxy 
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED 
REJECT     all  --  anywhere             anywhere            reject-with icmp-port-unreachable 

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination 

一部のIPは伏せてあります。
僕のテストサーバ、思ったより色々設定されてありましたｗ誰だこんなんしたやつｗ

まぁ、大きく分けて3つあります。
「Chain INPUT」・・・入ってくるパケットに関するもの。
「Chain FORWARD」・・・転送されていくパケットに関するもの。
「Chain OUTPUT」・・・出力されるパケットに関するもの。

設定する順番は
１）ポリシーを設定
２）ルールを一旦削除（クリア）
３）ルールを設定
４）設定を保存
５）iptables再起動
です。

ではまず１）ポリシーを設定。
簡単なiptablesという事なのでINPUTは許可（ACCEPT）、FORWARDは破棄（DROP）、OUTPUTは許可（ACCEPT）と言う事にします。
FORWARDは滅多な事じゃないと使わない（ルータにするとか）ですし、OUTPUTはサーバからの出力なので上記の方法で良いと思います。

// 書式は iptables -P [チェイン] [ターゲット]
// チェインのポリシーを指定のターゲットに設定するという意。
# iptables -P INPUT ACCEPT
# iptables -P FORWARD DROP
# iptables -P OUTPUT ACCEPT

これでOK。

次に２）ルールを一旦削除します。現在の設定を変更せずに追加とかした人はココやらなくても良いです。

# iptables -F

OK。
これで一旦設定を見てみると、

# iptables -L
Chain INPUT (Policy ACCEPT)
target　　prot opt source　　　　　destination

Chain FORWARD (Policy DROP)
target　　prot opt source　　　　　destination

Chain OUTPUT (Policy ACCEPT)
target　　prot opt source　　　　　destination

になるはずです。

次に３）ルールを設定。

// 書式は iptables -A [チェイン] -p [プロトコル] -s [送信元] -j [ターゲット] --dport [ポート範囲の指定]
// 他にオプションはたくさんあるけど、これくらい使えばとりあえずOK。
# iptables -A INPUT -p tcp --dport 80 -j ACCEPT

と書けば、80番ポートに対するtcpプロトコルは通すよ！という意味です。ちなみに、

# iptables -I INPUT 3 -p tcp --dport 80 -j ACCEPT

だと、INPUTチェインの上から3番目に、80番ポートに対するtcpプロトコルは通すルールを挿入！という意味です。
さらには、

# iptables -A INPUT -p tcp -s 192.168.0.0/28 --dport 80 -j ACCEPT

と書けば、INPUTチェインの80番ポートに対するtcpプロトコルの192.168.0.0/28からのパケットは通すぜ！という意味になります。
まぁ、こんな感じでどんどんと

# iptables -A INPUT -p tcp --dport 22 -j ACCEPT // ssh接続はACCEPT
# iptables -A INPUT -p tcp --dport 110 -j ACCEPT // POPはACCEPT
# iptables -A INPUT -p tcp --dport 25 -j ACCEPT // SMTPはACCEPT
# iptables -A INPUT -p icmp -j ACCEPT // PINGはACCEPT
# iptables -A INPUT -i lo -j ACCEPT // 自サーバからはACCEPT（-iはインターフェースの指定）
# iptables -P INPUT DROP // 他の接続は全てDROP

とこんな感じです。

次は４）設定を保存します。

# service iptables save

これはサーバ再起動されたら設定がリセットされてしまうので、保存しておくのです。

次に５）iptables再起動します。（念のために）

# service iptables restart

OK！こんなもんです(´ﾟ艸ﾟ)∴


ちなみにルールを設定する時に

# iptables -A 

と「-A」を多用しましたが、他にも

# iptables -I [チェイン] 番号

で挿入。

# iptables -R [チェイン] 番号

で書き換え（置換）

# iptables -D [チェイン] 番号

で削除ができますので、あしからず。

以上、おやすみー。

MySQLで最初に見るべきチューニング設定項目

どうも、俺＠仕事中です。
わが社ではMySQL、PostgreSQLを良く使っている（というかほとんど）のですが、よくアクセス過多によるサーバチューニングをやっています。
今回はMySQLサーバで負荷対策を行う際の「まず最初にココの設定だけは要Check It Now」というのをメモします。

# vim /etc/my.cnf
または
# mysqladmin variables
または
mysql> show variables;

で、現在の設定の確認ができます。

-- key_buffer_size

mysql> show variables like '%key_buffer%';

MyISAMエンジンテーブルのインデックスを保存するメモリバッファのサイズ。
Key_read/Key_read_requestsでキーバッファミス率を算出し、0.01以下になるように。

-- max_allowed_packet

mysql> show variables like '%max_allowed%';

通信時の1パケット最大サイズ。画像データなど大容量のデータをMySQLへ保存する際には大きくする。

-- table_cache

mysql> show variables like '%table_cache%';

一度開いたMySQLのデータファイルポインタを保存しておくためのキャッシュ。二度目以降のテーブルへのアクセスを高速にする。つまりはサーバのディスクI/Oを減らせる。

mysql> show global status like '%Open_tables%'

の値が大きい場合はテーブルキャッシュの値を増やした方が良い場合がある。
※バージョン5.1.xからはtable_open_sizeに変わってるかも！？

-- sort_buffer_size

mysql> show variables like '%sort_buffer%';

ソートに利用されるメモリサイズ。Order ByとかGroup Byとかで使われる。もしsort_buffer_sizeが1Mでmax_connectionsが1000だった場合、メモリは最低でも1GB必要という事ですよ。

-- read_buffer_size

mysql> show variables like '%read_buffer%';

テーブル読み込み時に利用されるメモリサイズ。INDEXを使わずフルスキャンされる場合に利用。そもそもINDEXを見直すべきでは？！

-- read_rnd_buffer_size

mysql> show variables like '%read_rnd_buffer%';

テーブルをランダムに読み込む場合に使われるメモリサイズ。使用可能メモリより大きなテーブルでGroup By/Order Byをする際は大きめにしておく。ソート後のレコード読み取り速度を上げるかも。要はディスクI/Oの軽減。

-- myisam_sort_buffer_size

mysql> show variables like '%myisam_sort_buffer%';

REPAIR TABLE / CREATE INDEX / ALTER INDEX 時に利用されるメモリサイズ。かなり頻度は低い。

-- thread_cache_size

mysql> show variables like '%thread_cache%';

MySQLコネクションスレッドをプールする数。スレッドを破棄＆生成するコストを下げる。

-- tmp_table_size

mysql> show variables like '%tmp_table_size%';

サブクエリなどで一時的に実行結果をテンポラリテーブルに保存されたときのメモリサイズ。このサイズを超えた場合、MySQLはディスクへ一時テーブルへ保存しディスクI/Oが発生してしまう。このサイズを指定した場合はmax_heap_table_sizeも同じ値に設定する。

-- query_cache_size

mysql> show variables like '%query_cache%';

一度発行したクエリ実行結果をキャッシュしておき、二回目以降はクエリを発行することなくキャッシュから結果を返す。もちろん更新クエリなどが走った後はキャッシュはクリアされる。ちなみに

mysql> FLUSH QUERY CACHE;

でキャッシュを強制的にクリアできる。

-- skip-name-resolve
これは有名すぎますがDNS逆引きをしない。この設定をした場合、GRANT構文などでユーザを登録する際にもIP指定にする必要がある。

my.cnfの設定は基本的に上記のものを見直します。
もちろん細かい設定をする場合は、もっと詳細な設定を見直す必要があるのであくまで参考程度に。

あとは、スローログを取得させる。

slow_query_log=mysql-slow　// スローログを取得
logn_query_time=1　// 1秒以上かかったクエリを取得

OPTIMIZE TABLE [table_name]を定期的にかける。

mysql> OPTIMIZE TABLE table_name;

更新や削除などが頻繁に行われているテーブルに対してやると効果絶大。デフラグを解消させる。postgreSQLで言うところのvacuumみたいなもの。


あとは、サーバのメモリを増やすだとかサーバそのもののスペックを上げるだとか、アプリケーション側でのチューニングは必ず必要です。
特にスローログに出ているクエリは見直す余地がかなりあると思うので、そちらのチューニングも大切に！


以上でぇぇす。

linuxのssコマンドで利用中ポート一覧

こんちわ、俺＠仕事中です。

ふと、ターミナル(zsh)で

[user]$ sl

と打ってしまって

correct:sl -> ss [n y a e]?

と表示されて知ったのですがLinuxには「ss」というコマンドがあるみたいです。

[user]$ whereis ss
ss: /usr/sbin/ss /usr/include/ss /usr/share/ss

見つからない人は、yumで一発インストールできます。

で、このコマンドなのですが、そのサーバが開いているポート一覧を表示してくれます。ちょうど

[user]$ netstat -tpln

したのと同じような感じです。

[user]$ ss -an
State　　　　Recv-Q Send-Q　　　　Local Address:Port　　　　Peer Address:Port
LISTEN　　　　0　　　0　　　　　　　　　　　　*:3333　　　　　　　　　　　*:*
LISTEN　　　　0　　　0　　　　　　　　　　　　*:6666　　　　　　　　　　　*:*
LISTEN　　　　0　　　0　　　　　　　　　　　　*:21　　　　　　　　　　　　*:*
LISTEN　　　　0　　　0　　　　　　　　　　　　*:22　　　　　　　　　　　　*:*
LISTEN　　　　0　　　0　　　　　　　　127.0.0.1:5431　　　　　　　　　　　*:*
LISTEN　　　　0　　　0　　　　　　　　　　　　*:5432　　　　　　　　　　　*:*
ESTAB 　　　　0　　　0　　　　　　192.168.60.215:22　　　　　192.168,.60.29:1515
ESTAB 　　　　0　　　0　　　　　　192.168.60.215:22　　　　　192.168,.60.29:1764

と、こんな感じです。
オプションもnetstatと似てます。

[user]$ ss -h
Usage: ss [ OPTIONS ]
　　　 ss [ OPTIONS] [ FILTER]
-n, --numeric don't resolve service names サービス名に変換せずにポート表示
-a, --all dispaly all sockets 全てのソケットを表示
-t, --tcp display only TCP sockets TCPプロトコルのソケットを表示
-u, --udp display only UDP sockets UDPプロトコルのソケットを表示
-4, --ipv4 display only IP version 4 sockets ipv4のIPでのみ表示
-6, --ipv6 display only IP version 6 sockets ipv6のIPでのみ表示
-r, --resolve resolve host names ホスト名を名前解決する

主なオプションはこんなもんでしょうか。

以上でーーす。