MYSQL5.5.27がリリースされていたのでバージョンアップ

D.1.2. Changes in MySQL 5.5.27 (2012-08-02)
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-27.html

InnoDB、Partitioning、Replicationなどに関するBug Fixなど。
その他以下のように、YEAR(2)対応が含まれているようです。
Important Change: The YEAR(2) data type is now deprecated because it is problematic.
Support for YEAR(2) will be removed in a future release of MySQL.

1) MYSQLバックアップ

[root@aws ec2-user]# mysqldump --all-databases --single-transaction --flush-logs > /home/ec2-user/mysql_dump20120826.sql  -u root -p
Enter password:
[root@aws ec2-user]# ls -l mysql_dump20120826.sql
-rw-r--r-- 1 root root 1100643 Aug 26 12:00 mysql_dump20120826.sql

2)ダウンロードと展開
[root@aws src]# tar zxvf mysql-5.5.27-linux2.6-x86_64.tar.gz

[省略…]

[root@aws src]# mv mysql-5.5.27-linux2.6-x86_64 /usr/local/
[root@aws src]# 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 30 Jul 7 00:30 mysql -> mysql-5.5.25a-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 13 root root 4096 Aug 26 12:03 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 2 root root 4096 Aug 26 12:04 src
[root@aws src]#

3)サービス停止→データコピー→シンボリックリンク張り直し

[root@aws local]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@aws local]#

[root@aws local]# ls -l
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 30 Jul 7 00:30 mysql -> mysql-5.5.25a-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 13 root root 4096 Aug 26 12:07 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 2 root root 4096 Aug 26 12:04 src
[root@aws local]#

[root@aws local]# cp -rp /usr/local/mysql/data/ /usr/local/mysql-5.5.27-linux2.6-x86_64
[root@aws local]# rm mysql
rm: remove symbolic link `mysql’? y
[root@aws local]# ln -s /usr/local/mysql-5.5.27-linux2.6-x86_64 mysql
[root@aws local]#

[root@aws local]# ls -l
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 root root 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 2 root root 4096 Aug 26 12:04 src
[root@aws local]#

Symbolic Link

4)権限設定
[root@aws local]# chown -R mysql:mysql mysql-5.5.27-linux2.6-x86_64
[root@aws local]#

5)新しいバイナリーでサービスの起動
[root@aws data]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@aws data]#

6)MYSQL_UPGRADEの実行とシステムテーブルの更新
[root@aws bin]# /usr/local/mysql/bin/mysql_upgrade -u root -p
Enter password:
Looking for ‘mysql’ as: /usr/local/mysql/bin/mysql
Looking for ‘mysqlcheck’ as: /usr/local/mysql/bin/mysqlcheck
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/usr/local/mysql/data/mysql.sock’
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/usr/local/mysql/data/mysql.sock’
WP01.wp01_commentmeta OK
WP01.wp01_comments OK
WP01.wp01_geo_mashup_administrative_names OK
WP01.wp01_geo_mashup_location_relationships OK
WP01.wp01_geo_mashup_locations OK
WP01.wp01_links OK
WP01.wp01_mappress_maps OK
WP01.wp01_mappress_posts OK
WP01.wp01_options OK
WP01.wp01_postmeta OK
WP01.wp01_posts OK
WP01.wp01_searchmeter OK
WP01.wp01_searchmeter_recent OK
WP01.wp01_term_relationships OK
WP01.wp01_term_taxonomy OK
WP01.wp01_terms OK
WP01.wp01_usermeta OK
WP01.wp01_users OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running ‘mysql_fix_privilege_tables’…
OK
[root@aws bin]#

7)バージョンの確認
[root@aws data]# cat mysql_upgrade_info
5.5.27
[root@aws data]#

5.5.27

参考サイト:
D.1.2. Changes in MySQL 5.5.27 (2012-08-02)


以前にもバックアップのレビューを不定期で行ってきましたが、
MYSQLのバージョンも上がっているので、改めてバックアップのレビューをしておく。

Defaultオプションの確認

[root@CentOS64VM data]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/var/lib/mysql/mysql.sock --quick --max_allowed_packet=16M --default-character-set=utf8
[root@CentOS64VM data]#

mysqldump default option

–master-data=2
If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

ここら辺は、”--dump-slave” スレーブにてダンプをとった場合、
CHANGE MASTER情報をダンプに含むに入れ替えられるか要検証。
合わせて、”--apply-slave-statements”: STOP SLAVEおよびSTART SLAVEコマンドを、
CHANGE MASTERの前後に追加してくれるオプションも検証。

ログのFlush
--flush-logs
Flush the MySQL server log files before starting the dump.
This option requires the RELOAD privilege. If you use this option in combination
with the –all-databases option, the logs are flushed for each database dumped.
The exception is when using –lock-all-tables or –master-data: In this case, the logs are flushed only once,

ロック関連確認
--lock-all-tables, -x
--single-transaction

Lock all tables across all databases. This is achieved by acquiring a global read lock
for the duration of the whole dump. This option automatically turns off --single-transaction
and --lock-tables. --single-transaction is a much better option than --lock-tables
because it does not need to lock the tables at all.

Because –lock-tables locks tables for each database separately,
this option does not guarantee that the tables in the dump file are
logically consistent between databases. Tables in different databases
may be dumped in completely different states.

詳細は沢山あるので、4.5.4. mysqldump — A Database Backup Programを確認。

関連リンク
MYSQLバックアップ見直し

参考サイト
4.5.4. mysqldump — A Database Backup Program
MySQL: mysqldumpのコマンドオプションメモ
MySQLのmysqldump で –skip-opt を使うときに気をつける
mysqldump –dump-slave についてAdd Star
MySQLコミュニティ騒然!MySQL 5.5.4が与えるインパクト。


VisualStudioは無いけれど、簡単なWindowsプログラムを作成したい場合に
MinGWを使って簡易的な確認を行ってみました。

#include <windows.h>

int MingW_CALL()
{
        MessageBox (NULL, "Compile test on Mingw", "Windows Compile", MB_OK);
        return 0;
}

anywhere@any-place ~
$ vim windows-test.c

anywhere@any-place ~
$ gcc -c windows-test.c

anywhere@any-place ~
$ gcc -shared -o windows-test.dll windows-test.o

anywhere@any-place ~
$ rundll32.exe windows-test.dll MingW_CALL

anywhere@any-place ~
$

mingw

関連リンク
minttyインストール

参考サイト
MSYSとMingWでwin32のDLLをつくる


WiresharkはGUIベースですが、コマンド版でtsharkが準備されています。
LinuxのCUIで利用する場合や、バッチ連携したりGUIだと重いと言った場合に便利かもしれません。

[root@CentOS64VM tools]# yum install wireshark
Loaded plugins: fastestmirror, presto
Determining fastest mirrors
 * base: ftp.jaist.ac.jp
 * extras: ftp.jaist.ac.jp
 * updates: ftp.jaist.ac.jp
base
extras
updates
updates/primary_db
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package wireshark.x86_64 0:1.2.15-2.el6_2.1 will be installed
--> Processing Dependency: libgnutls.so.26(GNUTLS_1_4)(64bit) for package: wireshark-1.2.15-2.el6_2.1.x86_64
--> Processing Dependency: libsmi.so.2()(64bit) for package: wireshark-1.2.15-2.el6_2.1.x86_64
--> Processing Dependency: libpcap.so.1()(64bit) for package: wireshark-1.2.15-2.el6_2.1.x86_64
--> Processing Dependency: libgnutls.so.26()(64bit) for package: wireshark-1.2.15-2.el6_2.1.x86_64
--> Running transaction check
---> Package gnutls.x86_64 0:2.8.5-4.el6_2.2 will be installed
--> Processing Dependency: libtasn1.so.3(LIBTASN1_0_3)(64bit) for package: gnutls-2.8.5-4.el6_2.2.x86_64
--> Processing Dependency: libtasn1.so.3()(64bit) for package: gnutls-2.8.5-4.el6_2.2.x86_64
---> Package libpcap.x86_64 14:1.0.0-6.20091201git117cb5.el6 will be installed
---> Package libsmi.x86_64 0:0.4.8-4.el6 will be installed
--> Running transaction check
---> Package libtasn1.x86_64 0:2.3-3.el6_2.1 will be installed
--> Finished Dependency Resolution

wireshark

tsharkとtcpdumpでのパケットキャプチャー

[root@CentOS64VM tools]# /usr/sbin/tshark -n -i eth0 tcp port 80
Running as user "root" and group "root". This could be dangerous.
Capturing on eth0
  0.000000 192.168.137.1 -> 192.168.137.128 TCP 50018 > 80 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=2
  0.114130 192.168.137.1 -> 192.168.137.128 TCP 50019 > 80 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=2
  0.250229 192.168.137.1 -> 192.168.137.128 TCP 50020 > 80 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=2
  2.999452 192.168.137.1 -> 192.168.137.128 TCP 50018 > 80 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=2
  3.111388 192.168.137.1 -> 192.168.137.128 TCP 50019 > 80 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=2
  3.251262 192.168.137.1 -> 192.168.137.128 TCP 50020 > 80 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=2
^C6 packets captured
[root@CentOS64VM tools]# /usr/sbin/tcpdump -n -i eth0 port 80
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
19:24:35.773723 IP 192.168.137.1.50022 > 192.168.137.128.http: Flags [S], seq 3779509450, win 8192, options [mss 1460,nop,wscale 2,nop
19:24:36.784314 IP 192.168.137.1.50023 > 192.168.137.128.http: Flags [S], seq 1774179078, win 8192, options [mss 1460,nop,wscale 2,nop
19:24:37.055527 IP 192.168.137.1.50024 > 192.168.137.128.http: Flags [S], seq 1893589663, win 8192, options [mss 1460,nop,wscale 2,nop
19:24:39.783694 IP 192.168.137.1.50023 > 192.168.137.128.http: Flags [S], seq 1774179078, win 8192, options [mss 1460,nop,wscale 2,nop
19:24:40.063879 IP 192.168.137.1.50024 > 192.168.137.128.http: Flags [S], seq 1893589663, win 8192, options [mss 1460,nop,wscale 2,nop
19:24:41.795255 IP 192.168.137.1.50022 > 192.168.137.128.http: Flags [S], seq 3779509450, win 8192, options [mss 1460,nop,nop,sackOK],
^C
6 packets captured
6 packets received by filter
0 packets dropped by kernel
[root@CentOS64VM tools]#

tshark

[root@CentOS64VM tools]# /usr/sbin/tshark -help
TShark 1.2.15
Dump and analyze network traffic.
See http://www.wireshark.org for more information.

Copyright 1998-2011 Gerald Combs and contributors.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Usage: tshark [options] …

Capture interface:
-i name or idx of interface (def: first non-loopback)
-f packet filter in libpcap filter syntax
-s packet snapshot length (def: 65535)
-p don’t capture in promiscuous mode
-y link layer type (def: first appropriate)
-D print list of interfaces and exit
-L print list of link-layer types of iface and exit

Capture stop conditions:
-c stop after n packets (def: infinite)
-a … duration:NUM – stop after NUM seconds
filesize:NUM – stop this file after NUM KB
files:NUM – stop after NUM files
Capture output:
-b … duration:NUM – switch to next file after NUM secs
filesize:NUM – switch to next file after NUM KB
files:NUM – ringbuffer: replace after NUM files
Input file:
-r set the filename to read from (no pipes or stdin!)

Processing:
-R packet filter in Wireshark display filter syntax
-n disable all name resolutions (def: all enabled)
-N enable specific name resolution(s): “mntC”
-d ==,
“Decode As”, see the man page for details
Example: tcp.port==8888,http
Output:
-w set the output filename (or ‘-‘ for stdout)
-C start with specified configuration profile
-F set the output file type, default is libpcap
an empty “-F” option will list the file types
-V add output of packet tree (Packet Details)
-S display packets even when writing to a file
-x add output of hex and ASCII dump (Packet Bytes)
-T pdml|ps|psml|text|fields
format of text output (def: text)
-e field to print if -Tfields selected (e.g. tcp.port);
this option can be repeated to print multiple fields
-E= set options for output when -Tfields selected:
header=y|n switch headers on and off
separator=/t|/s| select tab, space, printable character as separator
quote=d|s|n select double, single, no quotes for values
-t ad|a|r|d|dd|e output format of time stamps (def: r: rel. to first)
-l flush standard output after each packet
-q be more quiet on stdout (e.g. when using statistics)
-X : eXtension options, see the man page for details
-z various statistics, see the man page for details

Miscellaneous:
-h display this help and exit
-v display version info and exit
-o : … override preference setting
-K keytab file to use for kerberos decryption
[root@CentOS64VM tools]#

関連ページ
Wireshark (旧 Ethereal)によるネットワーク解析

参考ページ
tshark (WiresharkのCLI版) の使い方
WireSharkの裏技 〜 コマンドラインで実行できる「TShark」
tcpdump ネットワーク上を流れるパケットを監視する


PerlからMemcachedを使ってみる
前回は、memcached基本設定でmemcachedのインストールのみ
しておいたのでPerlから簡易処理して動作確認してみる。

ファイルに書かれた設定やDBのデータをメモリ上にのせておくことで高速にデータの
取得を行うことが出来るようになります。

Memcachedのサービスを落とすとデータは消えます。何らかの理由でmemcachedが落ちてしまうと
データが全部消えて無くなってしまうので、もし永続的に利用したい場合は、
IO Driveなどを利用すると良いかと思います。

CPANでCache::Memcached::Fastをインストール

Cache::Memcahced::Fast is a Perl client for memcached,
a memory cache daemon (http://www.danga.com/memcached/).
Module core is implemented in C and tries hard to minimize number of system calls
and to avoid any key/value copying for speed. As a result, it has very low CPU consumption.

API is largely compatible with Cache::Memcached, original pure Perl client,
most users of the original module may start using this module by installing it
and adding “::Fast” to the old name in their scripts
(see “Compatibility with Cache::Memcached” below for full details).


[root@colinux mem]$ perl -MCPAN -e 'install Cache::Memcached::Fast'
CPAN: Storable loaded ok
Going to read /home/user/.cpan/Metadata
Database was generated on Fri, 10 Aug 2012 08:03:04 GMT
Cache::Memcached::Fast is up to date.
[root@colinux mem]$

[root@colinux mem]# cat memcached_fast.pl
#!/usr/bin/perl

use strict;
#use warnings;
use Cache::Memcached::Fast;

my $key = "key";
my $cache = Cache::Memcached::Fast->new({servers=>["localhost:11211"]});

$cache->set($key,"This is Memcache test by Perl");
if( my $val = $cache->get($key) ) { print "[ GET DATA FROM CACHE ] ".$val."\n";}

$cache->replace($key,"This is 2nd  Memcache test by Perl");
if( my $val = $cache->get($key) ) { print "[ GET DATA FROM CACHE ] ".$val."\n";}

$cache->delete($key);
if( my $val = $cache->get($key) ) { print "[ GET DATA FROM CACHE ] ".$val."\n";}
print "[ GET DATA FROM CACHE ] Data is deleted!! Please use flush_all to delete all data.\n";
[root@colinux mem]#


[root@colinux mem]$ ./memcached_fast.pl
[ GET DATA FROM CACHE ] This is Memcache test by Perl
[ GET DATA FROM CACHE ] This is 2nd Memcache test by Perl
[ GET DATA FROM CACHE ] Data is deleted!! Please use flush_all to delete all data.
[root@colinux mem]$

memcached

関連リンク
memcached基本設定

参考サイト
Cache::Memcached::Fast
Memcachedのインストール・利用方法
Perl Hackers Hub
memcachedを知り尽くす


fizzbuzz
知人がfizzbuzzの話をしていたので、
最近手をつけてないプログラムの頭を少し使ってみました。
通常は2~5分で解かなければいけないのですが、
普段開発してないので時間がかかってしまいました。

最初のプレイヤーは「1」と数字を発言する。
次のプレイヤーは直前のプレイヤーの次の数字を発言していく。
ただし、3で割り切れる場合は 「Fizz」(Bizz Buzz の場合は 「Bizz」)、
5で割り切れる場合は 「Buzz」、両者で割り切れる場合は 「Fizz Buzz」
を数の代わりに発言しなければならない。


#include

int main(void)
{
int i;

for (i = 1; i <= 100; i++) { printf("%d\r", i); if (i % 3 == 0) printf("Fizz"); if (i % 5 == 0) printf("Buzz"); printf("\n"); } return 0; } [/C] C

#!/usr/bin/perl
for (1..100) {
    if ($_ % 3 == 0 && $_ % 5 == 0) { print 'FizzBuzz'; }
    elsif ($_ % 3 == 0) { print 'Fizz'; }
    elsif ($_ % 5 == 0) { print 'Buzz'; }
    else { print $_; }
    print "\n";
}

perl

 perl -e'for(1..100){my$s;$s=Fizz if$_%3==0;$s.=Buzz if$_%5==0;print$s?"$s\n":"$_\n"}'

oneliner

参考サイト
FizzBuzz
Fizz Buzz Wiki
FizzBuzz を試してみた
変数の種類を理解する


サイト運営でドメイン有効期限、SSL有効期限は忘れないようにしなければいけない。
業者に自動更新を頼んでおくと自動更新してもらえるが、個人で運営している場合は自分で工夫して運用する必要あり。


$ echo "" | openssl s_client -connect ssl.kakaku.com:443 | openssl x509 -enddate -noout | sed 's/notAfter\=//'

$ echo "" | openssl s_client -connect ssl.kakaku.com:443 | openssl x509 -enddate -noout
depth=3 C = US, O = "VeriSign, Inc.", OU = Class 3 Public Primary Certification Authority
verify return:1
depth=2 C = US, O = "VeriSign, Inc.", OU = VeriSign Trust Network, OU = "(c) 2006 VeriSign, Inc. - For authorized use only", CN = VeriSign Class 3 Public Primary Certification Authority - G5
verify return:1
depth=1 C = US, O = "VeriSign, Inc.", OU = VeriSign Trust Network, OU = Terms of use at https://www.verisign.com/rpa (c)10, CN = VeriSign Class 3 International Server CA - G3
verify return:1
depth=0 C = JP, ST = Tokyo, L = Shibuya-ku, O = kakaku.com Inc, OU = Technology Platform Dept, OU = Terms of use at www.verisign.com/rpa (c)05, CN = ssl.kakaku.com
verify return:1
DONE
notAfter=Apr  9 23:59:59 2013 GMT
$
$ echo "" | openssl s_client -connect google.co.jp:443 | openssl x509 -enddate -noout
depth=2 C = US, O = Equifax, OU = Equifax Secure Certificate Authority
verify return:1
depth=1 C = US, O = Google Inc, CN = Google Internet Authority
verify return:1
depth=0 C = US, ST = California, O = Google Inc, CN = google.com
verify return:1
DONE
notAfter=Jan 17 12:55:04 2013 GMT
$

ssl有効期限

参考:
[Nagios] : Check SSL Certificate Expiration Date

Zabbix Fun – Tracking SSL Certificate Expiration Times