Mysql/MariaDBとPHPでデータを暗号化、復号化する

Mysql/MariaDBの場合

-- AESで暗号化。BLOB型(バイナリ)で入れるならHEX()は不要
-- "E0827B40347D3227B65775B8226A1BF1"が返ってくる
select HEX( AES_ENCRYPT('hogehoge', 'cryptkey') );
-- AESで暗号化されたものを復号化。BLOB型(バイナリ)で入れてあるならUNHEX()は不要
select AES_DECRYPT(UNHEX('E0827B40347D3227B65775B8226A1BF1'), 'cryptkey');

PHPの場合

<?php
// 以下の User Contributed Notes を参照のこと
// see. http://php.net/manual/ja/ref.mcrypt.php#99263
// "E0827B40347D3227B65775B8226A1BF1"が返ってくる
echo bin2hex(mysql_aes_encrypt("hogehoge", "cryptkey")) ;
echo "\n";
echo mysql_aes_decrypt(hex2bin("E0827B40347D3227B65775B8226A1BF1"), "cryptkey") ;
echo "\n";
function mysql_aes_decrypt($val,$ky)
{
$key="\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0";
for($a=0;$a<strlen($ky);$a++)
$key[$a%16]=chr(ord($key[$a%16]) ^ ord($ky[$a]));
$mode = MCRYPT_MODE_ECB;
$enc = MCRYPT_RIJNDAEL_128;
$dec = @mcrypt_decrypt($enc, $key, $val, $mode, @mcrypt_create_iv( @mcrypt_get_iv_size($enc, $mode), MCRYPT_DEV_URANDOM ) );
return rtrim($dec,(( ord(substr($dec,strlen($dec)-1,1))>=0 and ord(substr($dec, strlen($dec)-1,1))<=16)? chr(ord( substr($dec,strlen($dec)-1,1))):null));
}
function mysql_aes_encrypt($val,$ky)
{
$key="\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0";
for($a=0;$a<strlen($ky);$a++)
$key[$a%16]=chr(ord($key[$a%16]) ^ ord($ky[$a]));
$mode=MCRYPT_MODE_ECB;
$enc=MCRYPT_RIJNDAEL_128;
$val=str_pad($val, (16*(floor(strlen($val) / 16)+(strlen($val) % 16==0?2:1))), chr(16-(strlen($val) % 16)));
return mcrypt_encrypt($enc, $key, $val, $mode, mcrypt_create_iv( mcrypt_get_iv_size($enc, $mode), MCRYPT_DEV_URANDOM));
}

なんかテーブルが壊れたので修復してみた is marked as crashed and should be repaired

Table ‘tablename’ is marked as crashed and should be repaired [

一先ずチェックしてみる
CHECK TABLE tablename;

Table Op Msg_type Msg_text
tablename check warning Table is marked as crashed
tablename check warning 6 clients are using or haven’t closed the table properly
tablename check error Checksum for key:  4 doesn’t match checksum for records
tablename check error Corrupt

修復してみるとなんか治った
repair table tablename;

再度チェックしてみると治った
CHECK TABLE tablename;

Table Op Msg_type Msg_text
tablename check status OK

Mysqlで作ったテーブルにUTF-8の文字を突っ込むとincorrect string value

MysqlUTF-8の文字を突っ込むと以下の様なエラーで弾かれる
incorrect string value

一応、テーブルもUTF-8、突っ込む文字もUTF-8のはずだけど、受け入れてくれない
どうも特定の文字がはじかれるらしいというので調べてみると、MysqlのUTF-8は3バイトで表される範囲歯科対応していない様子

MySQL’s utf8 permits only the unicode characters that can be represented with 3 bytes in UTF-8. Here you have a character that needs 4 bytes: \xF0\x90\x8D\x83 (U+10343 GOTHIC LETTER SAUIL).

If you have MySQL 5.5 or later you can change the column encoding from utf8 to utf8mb4. This encoding allows storage of characters that occupy 4 bytes in UTF-8.

http://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc

じゃぁ、どうすればよいのかというとい、以下の様な話しらしい。

「UTF-8で4バイト」の文字を扱おうと思ったら、MySQL 5.5.3以降を使い、さらに”utf8mb4″を指定せよ、という冗談のような本当の話。

http://d.hatena.nh.jp/hhelibex/20120110/1326179698

ちなみに、今回突っ込もうとして失敗した文字はこんなの。なんて読むんだ
http://www.charbash.com/28a1e-unicode-invalid-character

test_のプレフィックスのデータベースに出ている権限を外す

デフォルトの状態だと、「test」データベースに対してすべてのユーザが触れるような権限が出ているが、合わせて「test_」から始まるデータベースにも同様の権限が出ている。

時たまデータベースの命名規則によってヨロシクないことがあるので、権限を外したい。で、以下の様なSQLを発行。これで全体に出ている権限を解除できる

revoke all on `test\_%`.* from ''@'%';

mysqlで「server has gone away」というエラーが返ってくる

おそらく、Mysqlのセッションがタイムアウトしてしまっていると思われる。確かに若干sleepを長くしすぎていたかも

とりあえず、セッションの有効時間を調べてみる
SHOW VARIABLES LIKE ‘wait_timeout’

英語ですが、下記のページがまとまっていると思います。
http://blog.taragana.com/index.php/archive/mysql-tip-mysql-servh…
ざっくり概要を訳してみると(間違っていたらゴメンナサイ…)

  • 保持しているコネクションが、デフォルトでは8時間以上、やりとりが無かった時に切断される。PHP でコネクションプーリングしている場合が多い。
  • ロジックの誤りでコネクションを閉じちゃっている場合。例えば、マルチスレッドのアプリケーションで、別のスレッドが閉じている、とか。
  • クライアント側からタイムアウトが通知された場合。
  • クライアント側の自動再接続機能が無効にされている状態で、サーバ側でタイムアウトが発生した場合。
  • 巨大なクエリーや誤ったクエリーを投げた場合。
  • INSERT や REPLACE で、大量の行をソートしなければいけないような場合。
  • クライアントバージョンが 4.0.8 より古くて、サーババージョンが 4.0.8 以降の場合に、16MB 以上のパケットを送った場合。

稀なケースとして、

  • 管理者が MySQL のサーバを止めちゃった場合。
  • クライアントがサーバと別のホストで動いていて、接続するための権限を十分に持っていない場合。
  • Windows の場合、たぶん、タイムアウトが起きてコネクションが閉じられたんだけど、OS から十分なエラー情報が得られないので、結果、このエラーメッセージになる。
  • 5.0.19 よりで、自動再接続フラグが有効になっていても、うまく再接続出来ない場合がある。
  • 名前解決に失敗しているケース。
  • –skipe-network オプション付きでサーバが起動されている場合。
  • アプリケーションの全ての子プロセスが、同じコネクションを使おうとしている場合。
  • ファイヤーウォールで MySQL サーバが使うポートがブロックされた場合。
  • クエリーを処理中のサーバが死んじゃった場合。
  • というのが挙げられています。

関係ありそうなもの、無さそうなもの、玉石混交ですが、参考まで。

なんとなく、PHP のコネクションプーリングの問題のような気がするけど。

http://q.hatena.nh.jp/1267971507

また、PHPにはmysql_pingというmysqlとの接続を確認して、切断されていたら再接続する関数がある様子
MySQL server has gone away – 揮発性のメモ

ただし、MySQL 5.0.13 以降、自動再接続機能は使えなくなったようなのでバージョンに注意
PHP: mysql_ping – Manual

TEXT型のフィールドにインデックスを付加する

インデックスと入っても、値の有無でサクッとヒットさせたいだけ
floatingdays: MySQLの TEXT型の列に INDEXを付ける

alter table `TABLE_NAME` add index `INDEX_NAE`(`FIELD_NAME`(255));

※ インデックスの対象範囲が255までという話だけど、自分の環境だと333まで対応しているっぽい
※ インデックスがどういう場合に有効になるかは要チェック(一日目午後:MySQLの最適化 – Oliver の日記)

本当はFULLTEXTインデックスをつけたほうがいいのかもしれない
MySQLで全文検索 – FULLTEXTインデックスの基礎知識|blog|たたみラボ

mysqlのバイナリログ(mysql-bin.******)を削除する

データベースを眺めるとかなり肥大化している様子なのでどうにかする

参考

【MySQL】バイナリログを自動削除したい(mysql-bin.******) at softelメモ
あんじーのテクニカルブログ: mysql-bin.000001が肥大化する
» 【MySQL】バイナリログ圧迫の対処。 Pattern: Pattern Ref —自分用覚書
variablh.jp [Mainly Deals with RDBMS] » expire_logs_days

既に存在するバイナリログを削除する

きっと手動でrmしてもいいような気はするのだけど、セオリー的に

$ /usr/local/mysql/bin/mysql ~

-- バイナリログの一覧を確認する
SHOW BINARY LOGS;
-- 指定した日時より古いバイナリログを削除する
--  以下の例では、当日現在
PURGE MASTER LOGS before now();
--  以下の例は、一ヶ月前まで
PURGE MASTER LOGS before DATE_SUB( NOW( ), INTERVAL 31 DAY);

バイナリログを取らないように設定を変更する

ログなんかもういらないよ

設定ファイルの「log-bin=mysql-bin」の行をコメントアウトしてmysqldを再起動する

$ vi /etc/my.cnf

binary logging is required for replication

log-bin=mysql-bin

$ /etc/init.d/mysqld restart

バイナリログを一定期間ごとに削除する

流石にバイナリログを取らないのはちょっとなぁ

設定ファイルの「expire_logs_days=日数」の行を追加してmysqldを再起動する
※ デフォルトは0で削除しない

$ vi /etc/my.cnf

expire_logs_days = 5

$ /etc/init.d/mysqld restart

Mysqlのストアードファンクションでレーベンシュタイン距離を利用する

使用目的としては、通常通り検索した後mysql内で検索語とタイトルのレーベンシュタイン距離を基準に並び替えたい
レーベンシュタイン距離の計算はStored Function(ストアード・ファンクション)として実装

http://ja.wikipedia.org/wiki/%E3%83%AC%E3%83%BC%E3%83%99%E3%83%B3%E3%82%B7%E3%83%A5%E3%82%BF%E3%82%A4%E3%83%B3%E8%B7%9D%E9%9B%A2
http://php.benscom.com/manual/ja/function.levenshtein.php
http://eringi.com/weblog/archives/2009/05/mysql_levenshtein.html
http://www.assembla.com/wiki/show/winvictory/Levenshtein_SQL_code

設定

以下をmysqlで実行する

|sql|
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `levenshtein`(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN SET cost = 0; ELSE SET cost = 1; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN SET c = c_temp; END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END $$
CREATE DEFINER=`root`@`localhost` FUNCTION `levenshtein_ratio`(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, max_len INT;
SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
IF s1_len > s2_len THEN SET max_len = s1_len; ELSE SET max_len = s2_len; END IF;
RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
END $$
DELIMITER ;

||<

使い方

|sql|
ORDER LEVENSHTEIN_RATIO(FIELD_TITLE, ‘キーワード’) DESC
||<