MySQL5.6でGTIDãƒ¬ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã®å°Žå…¥æ‰‹é †ã¨è§£é™¤æ‰‹é †

MySQL5.6GAが出てから大分経っちゃいましたが、
皆様プロダクト環境でガンガン使っておりますでしょうか。

今更ながら手順を整理したので記述しておきます。

■GTIDとは
Global Transaction Identifiers
今までのMySQLのレプリケーションで行なっていた、「Masterのbinlogファイル名 + ポジション番号」による情報ではなく、
トランザクションごとにGTIDを持たせる事で管理できるようになるという機能です。
mysqlfailoverを利用すると、マスター障害時に各スレーブで最も進んでいるGTIDを持つスレーブがマスターに自動昇格します。

・メリット
　　　・CHANGE MASTER文を打つ際にポジション番号を指定しなくてよくなる
　　　・MySQL-MHAを利用しなくてもGTID+mysqlfailoverでSPOFをなくせる

・デメリット
　　・慣れるのが大変（何年もbinlog+positionでやってきてるので）
　　・GTIDモードと従来のモードは混在できない（マスターGTID,スレーブ非GTID等）
　　・全スレーブでbinlog出力が必要
　　・非トランザクション対応のストレージエンジンは使用不能（MyISAM）
　　・CREATE TABLE ・・・SELECTは使用不能

などなど
MySQL-5.6.5の新機能「GTID」を使う際に抑えておきたい落とし穴

デメリット推しになってしまいましたが、とりあえず導入していきましょう。

■MySQL5.6導入とGTIDレプリケーション作成

【マスター、スレーブで作業】
MySQL5.6インストール

yum install http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-client-5.6.10-1.el6.x86_64.rpm \
            http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-shared-compat-5.6.10-1.el6.x86_64.rpm \
            http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-server-5.6.10-1.el6.x86_64.rpm \
            http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-devel-5.6.10-1.el6.x86_64.rpm \
            http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-shared-5.6.10-1.el6.x86_64.rpm

my.cnf編集

vim /etc/my.cnf

report_host=ホスト名
server-id = 一意の番号
をそれぞれのサーバで変更してください。

#-----------------------------------------------------------
# MySQL clients
#-----------------------------------------------------------
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

#-----------------------------------------------------------
# MySQL server
#-----------------------------------------------------------
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
character-set-server = utf8

skip-name-resolve
max_connections = 1000
binlog_cache_size = 1M
sort_buffer_size = 8M
join_buffer_size = 128K
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 0
query_cache_limit = 2M
ft_min_word_len = 4
default_storage_engine = InnoDB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log_output = FILE
general_log = OFF

##### Binary Log Configuration
binlog_format = MIXED
expire_logs_days = 2

##### GTIDに必要な設定 #####
log-bin= mysql-bin
log-slave-update
gtid-mode = ON
enforce-gtid-consistency
master_info_repository = TABLE
relay_log_info_repository = TABLE
report_host=ホスト名

sync_binlog = 1
server-id = 10

key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M

innodb_buffer_pool_size = 256M

innodb_log_buffer_size = 16M
innodb_log_file_size = 256M

innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
innodb_support_xa = ON

innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 200

[mysqldump]
quick
max_allowed_packet = 128M
default-character-set = utf8

[mysql]
no-auto-rehash
default-character-set = utf8
#safe-updates

[mysqld_safe]
open-files-limit = 8192

MySQLの初期パスワード確認
(コピーしておく）

cat /root/.mysql_secret
# The random password set for the root user at Thu Jun  6 19:11:26 2013 (local time):*******

MySQLèµ·å‹•

/etc/init.d/mysql start

確認

mysql -uroot -p*******

パスワード変更

SET PASSWORD FOR root@localhost=PASSWORD('*****');

GTIDモード確認

mysql> show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.05 sec)

【マスターで作業】
マスターのデータをdumpする

mysqldump -uroot --all-databases --single-transaction > fulldump.sql

と、今までのMySQLの用にdumpしてみたらエラーが出る。

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 --single-transaction --triggers --routines --events. 

5.6でGTIDつかってるならこのオプションつけろと怒られる
--triggers --routines --events

なので

mysqldump -uroot --all-databases --single-transaction --triggers --routines --events > fulldump.sql

もしくは、mysqlのデータディレクトリごとまるごとコピー。

スレーブに転送する

scp fulldump.sql スレーブIP:/tmp

レプリケーションユーザ作成

CREATE USER 'repl'@'スレーブIP' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'スレーブIP';
FLUSH PRIVILEGES;

ユーザ確認

mysql> select user,host from mysql.user;
+------+-----------------+
| user | host            |
+------+-----------------+
| root | 127.0.0.1       |
| repl | スレーブIP      |
|      |                 |
〜〜〜〜〜〜

【スレーブで作業】
dumpデータをリストア
MySQL起動済みの場合、
gtid_executedをresetする必要があるため、slave側でreset masterを行う

mysql> reset master;

mysql -uroot < /tmp/fulldump.sql

※データディレクトリをコピーしてきた場合は、auto.cnfは削除して起動する

マスター情報

change master to
  master_host = 'マスターIP',
  master_port=3306,
  master_user='repl',
  master_password='repl',
  master_auto_position=1;

確認する

start slave;
show slave status\G;

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set: e82f9904-ce91-11e2-8089-fa163e6d5fe4:17
Executed_Gtid_Set: e82f9904-ce91-11e2-8089-fa163e6d5fe4:1-17
...

あとはマスターで更新したものがスレーブに反映されるか確認。

■レプリケーションのエラーSKIP方法
運用しているとオペミスなどで、スレーブ側にエラーが発生してしまうことがあるかと思います。
今までは、SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;でSKIPしまくれましたが、
GTIDモードがONだとこの方法は使えません。

エラー例

Last_Error: Error 'Unknown database 'ttt4'' on query. Default database: 'ttt4'. Query: 'create table test_table(id int, name varchar(20))'

【スレーブで作業】
show slave statusでMaster_UUID、Retrieved_Gtid_Set、Executed_Gtid_Set
の値を確認します。

mysql> show slave status \G;
...
Master_UUID: e82f9904-ce91-11e2-8089-fa163e6d5fe4
Retrieved_Gtid_Set: e82f9904-ce91-11e2-8089-fa163e6d5fe4:17-18
Executed_Gtid_Set: 98193a23-cf34-11e2-84ae-fa163e1944de:1,e82f9904-ce91-11e2-8089-fa163e6d5fe4:1-17
...

Executed_Gtid_Setが実行済みのGTIDなので、:1-17となっているのがわかります。
Retrieved_Gtid_Setを見ると:17-18となっていて、:18が実行されていないので
この:18がエラーがでているIDだと特定できます。
この一文をSKIPするためには、以下のように指定します。

SET GTID_NEXT = "e82f9904-ce91-11e2-8089-fa163e6d5fe4:18";
BEGIN;
COMMIT;
SET GTID_NEXT = AUTOMATIC;
START SLAVE;

めんどい！！！！！
SKIP大量にするとかになったらすごく大変そう。

ここまで、構築とレプリケーションの作成、エラー対処などをやりましたが、
別にmysqlfailover使う予定ないし…っていう人は、以下の手順で従来のレプリケーション方法に
戻すことが可能です。

■GTIDを使わない今まで通りのレプリケーションに戻す

【マスターで作業】
my.cnf設定変更

vim /etc/my.cnf
gtid-mode = OFF

MySQL再起動

/etc/init.d/mysql restart

ポジション確認

mysql> show master status;   
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000003 |     120  |              |                  | e82f9904-ce91-11e2-8089-fa163e6d5fe4:1-18 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

【スレーブで作業】
my.cnf設定変更

vim /etc/my.cnf
gtid-mode = OFF

MySQL再起動

/etc/init.d/mysql restart

チェンジマスター

CHANGE MASTER
CHANGE MASTER TO
MASTER_HOST = 'マスターIP',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'repl',
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 120,
MASTER_AUTO_POSITION = 0;

レプリ再開

start slave;

確認

show slave status \G;

これでエラーSKIPし放題です！！！お帰りなさい！

他にもいろいろMySQL5.6の勘所が纏められてるので、
こんまめさんの記事を参考にしましょう。

MySQL5.6のちょっとした話

素敵なMySQL5.6ライフを！