MySQL SSL接続(SSL必須)

サーバ側

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10281
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> grant all on test.* to test@’client.example.com’ identified by ‘XXXXXX’ require ssl;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

クライアント側

% mysql -u test -h master.example.com -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘test’@’master.example.com’ (using password: YES)
%mysql -u test -h master.example.com -p –ssl-ca=/var/lib/mysql/example_root.crt –ssl-verify-server-cert
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10323
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> \q
Bye

MySQL SSL接続

サーバ側

# cat /var/db/mysql/my.cnf | grep ^ssl
ssl = 1
ssl-ca = /var/db/mysql/example_root.crt
ssl-cert = /var/db/mysql/master.example.com.crt
ssl-key = /var/db/mysql/master.example.com.key
# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10281
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> grant all on test.* to test@’client.example.com’ identified by ‘XXXXXX’ ;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

クライアント側

% mysql -u test -p –ssl-ca=example_root.crt -h master.example.com –ssl-verify-server-cert
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6838
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> status
————–
mysql  Ver 14.14 Distrib 5.5.16, for FreeBSD8.2 (amd64) using  5.2

Connection id:          6838
Current database:
Current user:           test@client.example.com
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          more
Using outfile:          ”
Using delimiter:        ;
Server version:         5.5.15-log Source distribution
Protocol version:       10
Connection:             master.example.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 1 day 23 hours 3 min 21 sec

Threads: 2  Questions: 196428  Slow queries: 0  Opens: 11328  Flush tables: 1  Open tables: 4  Queries per second avg: 1.159
————–
mysql> \q
Bye

OpenSSLで証明書に署名する

# openssl genrsa 2048 > master.example.com.key
Generating RSA private key, 2048 bit long modulus
…………………………………………………………..+++
……………………………………………………………………………………………………………………+++
e is 65537 (0x10001)
# openssl req -new -key master.example.com.key > master.example.com.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [AU]:JP
State or Province Name (full name) [Some-State]:Tokyo
Locality Name (eg, city) []:Chiyoda
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Example Corp.
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:master.example.com
Email Address []:

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# openssl x509 -req -days 730 -CA example_root.crt -CAkey example_root.key -set_serial 1 < master.example.com.csr > master.example.com.crt
Signature ok
subject=/C=JP/ST=Tokyo/L=Chiyoda/O=Example Corp./CN=master.example.com
Getting CA Private Key
# openssl verify -CAfile example_root.crt master.example.com.crt
master.example.com.crt: OK

OpenSSLでオレオレ証明書をつくる

# openssl genrsa 2048 > example_root.key
Generating RSA private key, 2048 bit long modulus
…………+++
……………………….+++
e is 65537 (0x10001)
# openssl req -new -key example_root.key > example_root.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [AU]:JP
State or Province Name (full name) [Some-State]:Tokyo
Locality Name (eg, city) []:Chiyoda
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Example Corp.
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:Example Corp Root CA
Email Address []:

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# openssl x509 -req -days 730 -signkey example_root.key < example_root.csr > example_root.crt
Signature ok
subject=/C=JP/ST=Tokyo/L=Chiyoda/O=Example Corp./CN=Example Corp Root CA
Getting Private key
# openssl verify -CAfile example_root.crt example_root.crt
example_root.crt: OK

MySQL レプリケート

自宅サーバのMySQLから実験用のAWS環境にレプリケートする設定をしてみる。

1ヶ月ぐらい運用してみて、AWSの利用状況(=課金)を見る、と。

WordPress Amazon Link ver.2.0.0 beta4 その3

書誌データのキャッシュをDBに入れるタイミングで壊れているみたいだったので、とりあえずBase64エンコードする

diff AmazonLink/AmazonLink.php public_html/wordpress/wp-content/plugins/AmazonLink/AmazonLink.php

% diff AmazonLink/AmazonLink.php public_html/wordpress/wp-content/plugins/AmazonLink/AmazonLink.php
389c389
< $item_info[$asin] = unserialize($result[‘item_info’]);

> $item_info[$asin] = unserialize(base64_decode($result[‘item_info’]));
432c432
< $ary[‘[SALES_RANK]’] = number_format($item->SalesRank);

> $ary[‘[SALES_RANK]’] = number_format(doubleval($item->SalesRank));
509c509
< $sql = sprintf($sql_insert, $asin, mysql_real_escape_string(serialize($item_info[$asin])));

> $sql = sprintf($sql_insert, $asin, mysql_real_escape_string(base64_encode(serialize($item_info[$asin]))));
512c512
< $sql = sprintf($sql_update, mysql_real_escape_string(serialize($item_info[$asin])), $asin);

> $sql = sprintf($sql_update, mysql_real_escape_string(base64_encode(serialize($item_info[$asin]))), $asin);

number_format()のためにdoubleval()する修正もしたんだった。

WordPress Amazon Link ver.2.0.0 beta4 その2

Amazon APIを使った機能が動いてなかったのを修正

環境

  • FreeBSD-8.2R
  • PHP-5.3.8

問題点

  • php5のPortsのみではhash_hmac関数がないので、Amazon APIへの問い合わせにSignatureがつけられない
  • 5.3でdeprecatedになった関数でのエラーを処理できずにループする

修正点

  • php5-hashを導入
  • php.iniで「error_reporting = E_ALL & ~E_DEPRECATED」