スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

MySQL5.6のSQLモード

ついに出ましたね、MySQL5.6!

my.cnfの位置が/usr/my.cnfに変わったとか標準動作がinnodb_per_tableになったとか色々ありますが、とりあえずデフォルトのmy.cnf内に

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


という記述があって、詳細はこちらを見ていただくとして、今まではsql_mode=TRADITIONALだったのですが、このまま起動するとSTRICT_TRANS_TABLESモードで起動してしまいます。

TRADITIONALはカラムレングスよりも長い文字列のレコードがインサートされた場合、カラムに入らない部分を切り捨てて格納し、エラーメッセージを返すんですが、STRICT_TRANS_TABLESだとインサートエラーを返します。

WEBアプリでレングスチェックをしているのでMySQL側でチェックする必要ないよ、という人はTRADITIONALモードに変更しましょう。

TRADITIONALモードで起動した場合、余計チェックが厳しくなりました><
5.6はTRADITIONALモードも変更が入ってるらしい。。。

mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+



5.5時代と同じように使う場合はsql_mode自体をコメントアウトして起動しましょう

スポンサーサイト

MySQL-MHAを試してみたよ。

新しく購入したDELLのサーバがネットワークエラーで不通になっちゃう事象が半年に1回くらい起きてて、それが運悪くマスタDBだったので、そろそろ本気で対策をしないといけないかしらんと思ってMySQL-MHAについて調べてみました。

MySQL-MHAについてはpublic keyの記事を参考に。

作業に入る前に本家のプロジェクトと、試した方々のブログを拝見しました。
・本家
http://code.google.com/p/mysql-master-ha/
・試した方々のブログ
http://myhome.munetika.mydns.jp/ossdbwiki/index.php/%E3%83%A1%E3%82%A4%E3%83%B3%E3%83%9A%E3%83%BC%E3%82%B8
http://6pongi.wordpress.com/2011/11/14/mysql-mha/
http://d.hatena.ne.jp/ke-16/20110912/1315824419
http://d.hatena.ne.jp/kaze-kaoru/20110830/1314677120

先ず最初に疑問に思ったのが、通常WEBアプリケーションというのはアプリケーションサーバとDBサーバが物理的に分かれていると思うんですが、スレーブがマスターに昇格した場合、アプリケーション側からどう見えるのか?ということでしたが、それについては本家にも書いてありますが、こちらがわかりやすくQAにまとめてくれていました。
簡単に言うと、MHAはマスタ昇格のタイミングで任意のスクリプトを走らせることができるので、IP付け替え(仮想NICだったりコールドスタンバイのNIC起動だったり)はそのタイミングでできるそうです。

次に疑問だったのが、フェイルオーバー後に旧マスタを復旧させた場合、旧マスタの扱いはどうなるのかということでした。

これについてあまり言及してるブログがなかったので、せっかくの機会なので自分で試してみることにしました。
ローカルのPC2台にCentOSを入れ、1台がマスタノード兼マネージャ、2台めをスレーブとしました。
MySQLのバージョンはマスタが若干古くて5.5.11、スレーブが5.5.25aでした。MySQLは1つ上のメジャーバージョンまでレプリケーションの互換性をみてくれるので問題無いと判断しました。

インストール方法などはこちらを参考に。特につまづくところもなくセットアップまで完了しました。

Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to localhost-relay-bin.000002
Temporary relay log file is /var/lib/mysql/localhost-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Nov 29 17:05:22 2012 - [info] Slaves settings check done.
Thu Nov 29 17:05:22 2012 - [info]


マスタ側のMySQLを落とす。

# /etc/rc.d/init.d/mysql stop


マネージャーがマスタの異常を検出

Thu Nov 29 17:07:47 2012 - [info] HealthCheck: SSH to 192.168.0.187 is reachable.
Thu Nov 29 17:07:49 2012 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.0.187' (111))
Thu Nov 29 17:07:49 2012 - [warning] Connection failed 1 time(s)..
Thu Nov 29 17:07:52 2012 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.0.187' (111))
Thu Nov 29 17:07:52 2012 - [warning] Connection failed 2 time(s)..
Thu Nov 29 17:07:55 2012 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.0.187' (111))
Thu Nov 29 17:07:55 2012 - [warning] Connection failed 3 time(s)..
Thu Nov 29 17:07:55 2012 - [warning] Master is not reachable from health checker!

落ちたかどうかの判定は設定ファイルで変更できるようです。

ヘルスチェックを行った後、設定ファイルにしたがってフェイルオーバーするよ!というログが出てフェイルオーバーが始まります。

Thu Nov 29 17:07:55 2012 - [warning] Master is not reachable from health checker!
Thu Nov 29 17:07:55 2012 - [warning] Master 192.168.0.187(192.168.0.187:3306) is not reachable!
Thu Nov 29 17:07:55 2012 - [warning] SSH is reachable.
Thu Nov 29 17:07:55 2012 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/app1.cnf again, and trying to connect to all servers to check server status..
Thu Nov 29 17:07:55 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Nov 29 17:07:55 2012 - [info] Reading application default configurations from /etc/app1.cnf..
Thu Nov 29 17:07:55 2012 - [info] Reading server configurations from /etc/app1.cnf..
Thu Nov 29 17:07:56 2012 - [info] Dead Servers:
Thu Nov 29 17:07:56 2012 - [info] 192.168.0.187(192.168.0.187:3306)
Thu Nov 29 17:07:56 2012 - [info] Alive Servers:
Thu Nov 29 17:07:56 2012 - [info] 192.168.0.232(192.168.0.232:3306)
Thu Nov 29 17:07:56 2012 - [info] Alive Slaves:
Thu Nov 29 17:07:56 2012 - [info] 192.168.0.232(192.168.0.232:3306) Version=5.5.25a-log (oldest major version between slaves) log-bin:enabled
Thu Nov 29 17:07:56 2012 - [info] Replicating from 192.168.0.187(192.168.0.187:3306)
Thu Nov 29 17:07:56 2012 - [info] Checking slave configurations..
Thu Nov 29 17:07:56 2012 - [info] read_only=1 is not set on slave 192.168.0.232(192.168.0.232:3306).
Thu Nov 29 17:07:56 2012 - [warning] relay_log_purge=0 is not set on slave 192.168.0.232(192.168.0.232:3306).
Thu Nov 29 17:07:56 2012 - [info] Checking replication filtering settings..
Thu Nov 29 17:07:56 2012 - [info] Replication filtering check ok.
Thu Nov 29 17:07:56 2012 - [info] Master is down!
Thu Nov 29 17:07:56 2012 - [info] Terminating monitoring script.
Thu Nov 29 17:07:56 2012 - [info] Got exit code 20 (Master dead).
Thu Nov 29 17:07:56 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Nov 29 17:07:56 2012 - [info] Reading application default configurations from /etc/app1.cnf..
Thu Nov 29 17:07:56 2012 - [info] Reading server configurations from /etc/app1.cnf..
Thu Nov 29 17:07:56 2012 - [info] MHA::MasterFailover version 0.53.
Thu Nov 29 17:07:56 2012 - [info] Starting master failover.


ぐりぐりと進んでいきます。

Thu Nov 29 17:07:56 2012 - [info] * Phase 1: Configuration Check Phase..
Thu Nov 29 17:07:56 2012 - [info]
[中略]
Thu Nov 29 17:07:56 2012 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Nov 29 17:07:56 2012 - [info]
[中略]
Thu Nov 29 17:07:56 2012 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu Nov 29 17:07:56 2012 - [info]
[中略]
Thu Nov 29 17:07:56 2012 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Nov 29 17:07:56 2012 - [info]
[中略]
Thu Nov 29 17:07:56 2012 - [info] * Phase 3: Master Recovery Phase..
Thu Nov 29 17:07:56 2012 - [info]
[中略]
Thu Nov 29 17:07:56 2012 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu Nov 29 17:07:56 2012 - [info]
[中略]
Thu Nov 29 17:07:56 2012 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Thu Nov 29 17:07:56 2012 - [info]
[後略]


終わった!

----- Failover Report -----

app1: MySQL Master failover 192.168.0.187 to 192.168.0.232 succeeded

Master 192.168.0.187 is down!

Check MHA Manager logs at hogehoge for details.

Started automated(non-interactive) failover.
The latest slave 192.168.0.232(192.168.0.232:3306) has all relay logs for recovery.
Selected 192.168.0.232 as a new master.
192.168.0.232: OK: Applying all logs succeeded.
Generating relay diff files from the latest slave succeeded.
192.168.0.232: Resetting slave info succeeded.


全ての処理をやり終えるとマネージャーは停止します。。

スレーブが昇格したのを確認して、マスタのMySQLを再度起動します。

/etc/rc.d/init.d/mysql start



起動しましたが自動的にスレーブになったりするわけではありませんでした。
やってみて思ったんですが、異常で死んだスレーブが勝手にフェイルオーバーしてマスタに戻るとデータの欠損などのトラブルの元になりますからね。。。

とはいえ、調べてみて特に異常ないことがわかった旧マスタをまたスレーブとして登録するためにはデータのリストアが必要で、データ量が多ければ多いほどこの作業は時間がかかってしまいます。
そういうところも見越してか、マネージャーの実行結果には昇格時のバイナリログのポジション情報が出力されています。

Thu Nov 29 17:07:58 2012 - [info] All relay logs were successfully applied.
Thu Nov 29 17:07:58 2012 - [info] Getting new master's binlog name and position..
Thu Nov 29 17:07:58 2012 - [info] localhost-bin.000003:787
Thu Nov 29 17:07:58 2012 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.0.232', MASTER_PORT=3306, MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=787, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Thu Nov 29 17:07:58 2012 - [warning] master_ip_failover_script is not set. Skipping taking over new master ip address.


データを確認して問題無いと判断したら、旧マスタにchange masterしてスレーブとして加えることもできます。すごいなあ。

ちなみに起動中にマネージャーを強制停止し、再実行しても問題ありませんでした。どうやらマネージャーは起動した時点からのログの整合性を取っているようです。
なので仮にマネージャーが落ちても他のサーバを代用できそうです(あくまで推測なので実際に作業される方は十分にテストしてくだい)。

ざっと使ってみた感じですが、MySQLサーバ側にほとんど影響を与えないよう作られているように感じました。
マネージャーは、どのタイミングで登録してもサーバに影響しないので、稼働中のサービスにも導入できそうでした。
HA製品というと導入や運用が難しく、専用のサポートが必要なイメージがありますが、MySQL-MHAはシンプルに極力ダウンタイムを発生させずフェイルオーバーを実現させることに重点を置いている感じがし、とても魅力的なOSSですので、本番環境にも導入を進めたいと思います。

テーブルの内容をテキストに書き出す方法

MySQLでテーブルのデータをテキストにエクスポートする場合、ターミナルからコマンドラインでやる方法とCUIから出力するのと2種類方法があります。

ターミナルからやる

# echo "select * from fuga" | mysql hogehoge > abc.csv



MySQL CUIからやる

select * from `fuga` into outfile "/tmp/abc.csv" fields terminated by ',';


ターミナルからやるとTSV形式になるけどカラムヘッダが出力されるのでデータを渡したりエクセルとかに取り込んで何か加工する時に何かと都合がいいけど、テーブルサイズによっては落ちちゃう。
CUIだと区切り文字が選べるけどヘッダが吐き出せないので、後でどのカラムが何のデータだったかわかんなくなる可能性がある、でもサイズが大きくてもちゃんと書きだしてくれる。

どっちも一長一短があるけど、とりあえずCUIでヘッダを吐き出す方法を考えた。

select id1,id2,id3,... from (select "id1","id2","id3",... union select id1,id2,id3 from fuga) as a into outfile "/tmp/abc.csv" fields terminated by ',';


何か他にいい方法ないですかねぇ。

追記
最後のやつだと一回一時領域にデータをコピーするのですごく遅い

MySQLでrootなのにgrantコマンドが実行できない場合の対処法

rootでログインしてユーザーを増やそうと思ったら

mysql> grant all on *.* to "myuser"@"192.168.0.0/255.255.255.0";
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


と怒られた。

調べてみたら色々対処法があるみたいだが、とりあえずエラーログを見てみると

120801 9:43:15 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it



言われた通りmysql_upgradeを実行すると

[root@localhost ~]# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
mysql.columns_priv OK
mysql.db OK
mysql.func OK
mysql.general_log OK
mysql.help_category
error : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
mysql.help_relation OK
mysql.help_topic
error : Table upgrade required. Please do "REPAIR TABLE `help_topic`" or dump/reload to fix it!
mysql.host OK
mysql.proc
error : Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it!
mysql.procs_priv OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name
error : Table upgrade required. Please do "REPAIR TABLE `time_zone_name`" or dump/reload to fix it!
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

Repairing tables
mysql.help_category OK
mysql.help_keyword OK
mysql.help_topic OK
mysql.proc OK
mysql.time_zone_name OK
Running 'mysql_fix_privilege_tables'...
OK


それ以外にも幾つかテーブルが足りなかったようで、作ってくれた。

再度grantしてみる

mysql> grant all on *.* to "myuser"@"192.168.0.0/255.255.255.0";
Query OK, 0 rows affected (0.00 sec)


今度はOK

入れたrpmが足りなかったのか、mysql_install_dbがおかしかったのか管理テーブルが足りなかったのが原因のようだ。

MySQLでDate関数同士を比較するときの注意

MySQLには日付の計算を行う関数がいくつかあって、例えば当日は


mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2012-04-23 16:26:11 |
+---------------------+
1 row in set (0.00 sec)



前日は

mysql> SELECT NOW() - interval 1 day;
+------------------------+
| NOW() - interval 1 day |
+------------------------+
| 2012-04-22 16:28:17 |
+------------------------+
1 row in set (0.00 sec)



で、これを日付だけ取り出したい場合、Date()を使う

mysql> SELECT date(NOW() - interval 1 day);
+------------------------------+
| date(NOW() - interval 1 day) |
+------------------------------+
| 2012-04-22 |
+------------------------------+
1 row in set (0.00 sec)



単体で動かしているならこれで問題ないんだけど、Date()同士の引き算の結果がおかしくて軽くハマったので備忘録
まず同一月の引き算

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2012-04-23 |
+-------------+
1 row in set (0.00 sec);

mysql> SELECT DATE(NOW() - INTERVAL 1 DAY);
+------------------------------+
| DATE(NOW() - INTERVAL 1 DAY) |
+------------------------------+
| 2012-04-22 |
+------------------------------+
1 row in set (0.00 sec);

mysql> SELECT DATE(NOW()) - DATE(NOW() - INTERVAL 1 DAY);
+--------------------------------------------+
| DATE(NOW()) - DATE(NOW() - INTERVAL 1 DAY) |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
1 row in set (0.00 sec)



同月同士なら全く問題なく計算できる。これが月をまたぐと全くおかしくなる。

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2012-04-23 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(NOW() - INTERVAL 23 DAY);
+-------------------------------+
| DATE(NOW() - INTERVAL 23 DAY) |
+-------------------------------+
| 2012-03-31 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(NOW()) - DATE(NOW() - INTERVAL 23 DAY);
+---------------------------------------------+
| DATE(NOW()) - DATE(NOW() - INTERVAL 23 DAY) |
+---------------------------------------------+
| 92 |
+---------------------------------------------+
1 row in set (0.01 sec)


!?

どうも日付同士の演算をする場合、Date()だと年を365日、月を30 or 31 or 29として計算し直すのではなく、単純に年、月を10進数で+1桁としているようだ。
だからひと月前は30日ではなく、100、年は1000が足されたような格好になる。

なので日付どうしの計算を行う場合は、ちゃんとTO_DAYSを使いましょう。


mysql> SELECT TO_DAYS(NOW()) - TO_DAYS(NOW() - INTERVAL 23 DAY);
+---------------------------------------------------+
| TO_DAYS(NOW()) - TO_DAYS(NOW() - INTERVAL 23 DAY) |
+---------------------------------------------------+
| 23 |
+---------------------------------------------------+
1 row in set (0.00 sec)



もしくはDATEDIFF

mysql> SELECT DATEDIFF(DATE(NOW()), DATE(NOW() - INTERVAL 23 DAY));
+------------------------------------------------------+
| DATEDIFF(DATE(NOW()), DATE(NOW() - INTERVAL 23 DAY)) |
+------------------------------------------------------+
| 23 |
+------------------------------------------------------+
1 row in set (0.00 sec)



使用頻度はどっちが高いんだろう。

このカテゴリーに該当する記事はありません。
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。