MySQL5.6のちょっとした話

最近、とあるサービスの本番環境にMySQL5.6を導入していっています。社内だけの環境も含めて5システムに導入しました。
5.5からのアップデートや最初から5.6というものもあります。

今回、導入で変わった点いろいろありますが、メモ程度にまとめておきます。
間違いなどありましたら指摘していただけるとありがたいです。

Replicationエラー時

今までは、replicationのエラーが起こった場合は

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

とかでダメなクエリを確認しつつSKIP出来ればしていましたが、5.6でGTIDモードONの場合、これが使えなくなりました。
GTID便利なんですが、この点少し不便です。
以下のように直します。

まず、slaveでmaster server UUIDと最新のGTID、Retrieved_Gtid_Setを確認します。

エラー時、以下の状態だったとします。

master server UUID: 0c6a3ed4-8ad8-11e2-86e7-02556798c2a1
Retrieved_Gtid_Set: 0c6a3ed4-8ad8-11e2-86e7-02556798c2a1:1-2:1297-1483
最新GTID sets: 0c6a3ed4-8ad8-11e2-86e7-02556798c2a1:3-1482

この状態で、エラーが発生した場合、 1483のGTIDのトランザクションがエラーになっていることがわかります。
そこで、このトランザクションをなかった事にするべく、空のコミットを作成します。

STOP SLAVE;
-- ここで確認
SET GTID_NEXT = 0c6a3ed4-8ad8-11e2-86e7-02556798c2a1:1483;
BEGIN;
COMMIT;
SET GTID_NEXT = AUTOMATIC;
START SLAVE;

ただ、大量にスキップした場合はかなり手間なので結構不便です。

特定のクエリまでReplicationしたい

オペミスとかした場合などに

UNTIL {   {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set }

を使った復旧

まず、SnapShotなどから、masterを復旧します。

まずmasterで

SHOW MASTER LOGS;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000065 |      1894 |
| mysql-bin.000066 |       174 |
| mysql-bin.000067 |    184886 |
| mysql-bin.000068 |    577080 |
| mysql-bin.000069 |       335 |
| mysql-bin.000070 |     44915 |
| mysql-bin.000071 |   6381951 |
+------------------+-----------+

で最新のBINLOGを確認します。

ここで、オペミスをしたクエリが入っているBINLOGを指定してEVENTを確認します。

SHOW BINLOG EVENTS IN 'mysql-bin.000071';

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                            |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000071 | 231 | Gtid           |  39256868 |         279 | SET @@SESSION.GTID_NEXT= '0c6a3ed4-8ad8-11e2-86e7-02556798c2a1:1305'                                                                                            |
| mysql-bin.000071 | 279 | Query          |  39256868 |         362 | BEGIN                                                                                                                                                           |
| mysql-bin.000071 | 362 | Query          |  39256868 |         585 | use `xxx`; UPDATE `fuga` SET `token` = 'bad token', `updated_at` = '2013-03-19 01:32:19' WHERE `fuga`.`id` = 10 |
| mysql-bin.000071 | 585 | Xid            |  39256868 |         616 | COMMIT /* xid=94101 */                                                                                                                                          |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+

このPOS362のクエリがダメだった場合、この前のクエリまでをレプリさせます。
このクエリに付けられたGTIDは

0c6a3ed4-8ad8-11e2-86e7-02556798c2a1:1305

です。

SLAVEにて

---  念のため
STOP SLAVE;

CHANGE MASTER TO
MASTER_HOST = 'Master IPAddr or host name',
MASTER_AUTO_POSITION = 1,
UNTIL SQL_BEFORE_GTIDS = '0c6a3ed4-8ad8-11e2-86e7-02556798c2a1:1305';

START SLAVE;

これで、該当のクエリの前までレプリケーションされて止まりますので、あとは復旧などします。
今までもポジションでのUNTILはありましたが、格段にわかりやすくなったと思います。

GTIDなしのRplicationに戻したい

この場合は

CHANGE MASTER TO
MASTER_HOST = 'Master IPAddr or host name',
MASTER_USER = 'user',
MASTER_PASSWORD = 'PASSWORD',
MASTER_LOG_FILE = 'bin-log.00001',
MASTER_LOG_POS = 98,
MASTER_AUTO_POSITION = 0;

MASTER_AUTO_POSITION = 0;
をつけるのがポイントです。
ちなみに、CHANGE MASTERにuser/passwordをつけるとワーニングが出ます。
START SLAVEで指定すると安全だよという事です。

他のSlaveのDiskをコピーしたい

普段EBSのSnapshotからSlaveをどんどんつくっているのですが、5.6ではdata dirに存在する
auto.cnf
に注意です。
この中には

[auto]
server-uuid=0c6a3ed4-8ad8-11e2-86e7-02556798c2a

のように、サーバのUUIDが格納されています。
GTIDモードの環境で使われますが、かぶると困りものです。
このファイルは削除してしまって構いません。
ない場合はMySQL起動時に自動で作成されます。

mysqldump

5.6のサーバにmysqldumpで他のサーバからデータを持ってきたいとか、バックアップしておきたいとかあると思いますが、GTIDが有効の場合は

mysqldump -uUser -pPassword -DDBName --single-transaction --master-data=2 --triggers --routines --events > dump.sql
or
mysqldump -uUser -pPassword --all-databases --single-transaction --master-data=2 --triggers --routines --events > dump.sql

のような感じで、
「--triggers --routines --events」を指定しないと、

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

怒られます。

全部指定しない場合や、5.6以前のバージョンのMySQLにimportする場合、また、リモートの5.5サーバから5.6のmysqldumpを使用してダンプする場合は、

mysqldump -uUser -pPassword -DDBName --single-transaction --set-gtid-purged=OFF

のような感じで、
--set-gtid-purged=OFF
オプションをつけます。
他のオプションは適時つけてください。

実行したGTIDがgtid_purged変数に格納されたりするのですが、細かいところは今回省略します。

おまけ

InnoDBはinnodb_bufferpoolというものを持っていて、データファイルからフェッチしたデータをメモリ中にキャッシュしておき、Diskアクセスを減らす機構が存在しています。
今までは、mysqldを再起動するとこの領域は解放されてしまっていたのですが、MySQL5.6から動的もしくは、shutdown前にファイルにページ情報を書きだして、start時に読み込むことによって暖気が行われるようになりました。

my.cnfに

innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

を追加しておけば自動で行われます。

ダンプファイルサイズは大きくなく、ib_buffer_poolという名前でデータディレクトリに格納されます。
中身は

483
0,484
0,486
0,488
0,489
0,450
0,490
0,493
0,494
0,451
0,452
0,453
0,454
0,495
0,455
0,456
0,457
0,458
......

このような感じになっています。
ページの情報だったかな。

今回は簡単にちょっとした違いをまとめました。
検証は自体は1年近く行なっているのですが、実運用ははじめたばかりなので、また何かあったらまとめようと思います。
AWSのENIを使ったdual master構成でGTIDモードをONにした場合の運用などもかけたらなとおもいます。

まめ畑

ゆるゆると書いていきます