add slave – variable.jp [データベース,パフォーマンス,運用]

mysqlfabricにてslaveを追加した場合に、新しいSlaveのバックグラウンド処理の確認。

Slave追加前

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0

スレーブ追加
mysqlfabric group add global 127.0.0.1:63304

スレーブ追加後

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
8e43506d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

新しいSlaveでは~~リストア~~同期が行われ、アプリアカウント、テーブル、データの~~リストア~~同期が行われている。

-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 | egrep -v ^# | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
BINLOG '
jledVA8EAAAAdAAAAHgAAAABAAQANS42LjIyLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQbR
bZs=
'/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:1'/*!*/;
SET TIMESTAMP=1419597327/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:2'/*!*/;
SET TIMESTAMP=1419597331/*!*/;
GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:3'/*!*/;
use `test`/*!*/;
SET TIMESTAMP=1419597503/*!*/;
DROP TABLE IF EXISTS `employees` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:4'/*!*/;
SET TIMESTAMP=1419597503/*!*/;
CREATE TABLE employees (   emp_no INT,    first_name CHAR(40),    last_name CHAR(40))
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:5'/*!*/;
SET TIMESTAMP=1419597505/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597505/*!*/;
INSERT INTO employees VALUES (0, 'John:0', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:6'/*!*/;
SET TIMESTAMP=1419597506/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597506/*!*/;
INSERT INTO employees VALUES (1, 'John:1', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:7'/*!*/;
--続ける--

-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 |  egrep -i GTID | tail -n 10
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:417'/*!*/;
#141226 21:42:16 server id 1  end_log_pos 118585 CRC32 0x55ca8db9       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:418'/*!*/;
#141226 21:42:17 server id 1  end_log_pos 118870 CRC32 0x19ecc662       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:419'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119155 CRC32 0x63c53321       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:420'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119440 CRC32 0xee427af3       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:421'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
-bash-4.2$

スレーブ追加時のデータ同期については、GTIDモードのレプリケーションに依存するので此方を参照下さい。
http://variable.jp/2015/02/26/mysqlfabric-group-add%E5%AE%9F%E8%A1%8C%E6%99%82%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E5%90%8C%E6%9C%9F/

Tag Archives: Add Slave

mysqlfabricにてSlaveを追加