【再確認】GTIDを利用したMySQL Replicationにおけるスレーブの追加
新規Slaveのoption fileに以下の設定を追加
# Binary logging and Replication gtid-mode = on enforce-gtid-consistency = on server_id = 3 log_bin = mysql-bin log-slave-updates # master_info_repository = TABLE # relay_log_info_repository = TABLE #disable-gtid-unsafe-statements #disable-gtid-unsafe-statementsは,GTIDと互換性のない一部のSQLの実行を無効にする。
GTIDを利用したSlave設定がONになっている事を確認@CentOS03
root@localhost [test]> select @@hostname; +------------+ | @@hostname | +------------+ | CentOS03 | +------------+ 1 row in set (0.00 sec) root@localhost [test]> show tables; Empty set (0.00 sec) root@localhost [test]> root@localhost [test]> show variables like '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 7 rows in set (0.00 sec)
Master側にてデータを取得してデータ作成(スレーブでもOK、また特定DBのみでも問題なければOK)
---------------- [admin@CentOS01 ~]$ /usr/local/mysql/bin/mysqldump -uroot -p --all-databases --single-transaction --triggers --routines --events > GTID_fulldump.sql Enter password: [admin@CentOS01 ~]$ scp GTID_fulldump.sql admin@192.168.56.112:/home/admin/ admin@192.168.56.112's password: GTID_fulldump.sql 100% 4456KB 4.4MB/s 00:00 [admin@CentOS01 ~]$
SlaveにてデータのリストアとReplicationの開始
[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql Enter password: [admin@CentOS03 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed root@localhost [mysql]> change master to -> master_host = '192.168.56.101', -> master_port=3306, -> master_user='GTID_USER', -> master_password='password', -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.18 sec) root@localhost [mysql]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.56.101 Master_User: GTID_USER Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: CentOS03-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 151 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08 Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 Auto_Position: 1 1 row in set (0.00 sec) root@localhost [mysql]> start slave; Query OK, 0 rows affected (0.04 sec) root@localhost [mysql]> SELECT @@global.gtid_executed; +--------------------------------------------+ | @@global.gtid_executed | +--------------------------------------------+ | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 | +--------------------------------------------+ 1 row in set (0.00 sec) root@localhost [mysql]>
Masterでの変更が反映される確認(Table作成)
root@localhost [test]>select @@hostname; +------------+ | @@hostname | +------------+ | CentOS01 | +------------+ 1 row in set (0.00 sec) root@localhost [test]>CREATE TABLE `Repli_Demo` ( -> `id` int(20) NOT NULL AUTO_INCREMENT, -> `event` varchar(256) NOT NULL DEFAULT '', -> `description` varchar(256) NOT NULL DEFAULT '', -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.10 sec) root@localhost [test]>show tables; +----------------+ | Tables_in_test | +----------------+ | Customer_Data | | MyISAM_InnoDB | | Personal_Info | | Personal_Info0 | | Personal_Info1 | | Personal_Info2 | | Repli_Demo | | employee_table | | ex_timestamp | | performance | +----------------+ 10 rows in set (0.00 sec) root@localhost [test]>
既存Slave
root@localhost [test]> select @@hostname; +------------+ | @@hostname | +------------+ | CentOS02 | +------------+ 1 row in set (0.00 sec) root@localhost [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | BR_TEST | | Customer_Data | | MyISAM_InnoDB | | Personal_Info | | Personal_Info0 | | Personal_Info1 | | Personal_Info2 | | Repli_Demo | | employee_table | | ex_timestamp | | performance | +----------------+ 11 rows in set (0.00 sec) root@localhost [test]>
新規追加Slave
root@localhost [test]> select @@hostname; +------------+ | @@hostname | +------------+ | CentOS03 | +------------+ 1 row in set (0.00 sec) root@localhost [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | Customer_Data | | MyISAM_InnoDB | | Personal_Info | | Personal_Info0 | | Personal_Info1 | | Personal_Info2 | | Repli_Demo | | employee_table | | ex_timestamp | | performance | +----------------+ 10 rows in set (0.00 sec) root@localhost [test]>
マスターにてデータ変更
root@localhost [test]>desc Repli_Demo; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(20) | NO | PRI | NULL | auto_increment | | event | varchar(256) | NO | | | | | description | varchar(256) | NO | | | | +-------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) root@localhost [test]>insert into Repli_Demo(event,description) values('Seminor 20150116','Repli Seminor for beginner'); Query OK, 1 row affected (0.16 sec) root@localhost [test]>show master status; +------------------+----------+--------------+------------------+--------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+--------------------------------------------+ | mysql-bin.000256 | 1536 | | | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209 | +------------------+----------+--------------+------------------+--------------------------------------------+ 1 row in set (0.00 sec) root@localhost [test]>
既存Slave
root@localhost [test]> select * from Repli_Demo; +----+------------------+----------------------------+ | id | event | description | +----+------------------+----------------------------+ | 1 | Seminor 20150116 | Repli Seminor for beginner | +----+------------------+----------------------------+ 1 row in set (0.00 sec) root@localhost [test]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.101 Master_User: GTID_SSL_USER Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000256 Read_Master_Log_Pos: 1536 Relay_Log_File: CentOS02-relay-bin.000111 Relay_Log_Pos: 1746 Relay_Master_Log_File: mysql-bin.000256 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1536 Relay_Log_Space: 2350 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /usr/local/mysql/ssl/sql-ssl-cert.pem Master_SSL_CA_Path: /usr/local/mysql/ssl/ Master_SSL_Cert: /usr/local/mysql/ssl/sql-gtid-cert.pem Master_SSL_Cipher: Master_SSL_Key: /usr/local/mysql/ssl/sql-ssl-repl-key.pem Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08 Master_Info_File: /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:122-209 Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209, cf80b01f-364f-11e4-aa59-0800270e2d1e:1-11 Auto_Position: 1 1 row in set (0.00 sec) root@localhost [test]>
新規追加Slave
root@localhost [test]> select * from Repli_Demo; +----+------------------+----------------------------+ | id | event | description | +----+------------------+----------------------------+ | 1 | Seminor 20150116 | Repli Seminor for beginner | +----+------------------+----------------------------+ 1 row in set (0.00 sec) root@localhost [test]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.101 Master_User: GTID_USER Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000256 Read_Master_Log_Pos: 1536 Relay_Log_File: CentOS03-relay-bin.000002 Relay_Log_Pos: 1706 Relay_Master_Log_File: mysql-bin.000256 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1536 Relay_Log_Space: 1913 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08 Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:205-209 Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209 Auto_Position: 1 1 row in set (0.00 sec) root@localhost [test]>
データ追加
GTIDの確認
Binary Logの確認
Binary Log確認時のGTID確認
ここら辺は、Enterprise版のMySQL Enterprise Monitorでレプリケーションを統合監視しても良いかもしれません。
その他:メモ
—————————————————————————————————-
以前のReplicationの設定が残っていてエラーになる場合の対応 ERROR 1840 (HY000)
—————————————————————————————————-
[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql
Enter password:
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
[admin@CentOS03 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)]> SELECT @@global.gtid_executed;
+——————————————–+
| @@global.gtid_executed |
+——————————————–+
| 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
+——————————————–+
1 row in set (0.00 sec)
root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%GTID%’;
+———————————+——————————————–+
| Variable_name | Value |
+———————————+——————————————–+
| enforce_gtid_consistency | ON |
| gtid_executed | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-203 |
| simplified_binlog_gtid_recovery | OFF |
+———————————+——————————————–+
6 rows in set (0.00 sec)
root@localhost [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.03 sec)
root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%GTID%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+———————————+——-+
6 rows in set (0.00 sec)
root@localhost [(none)]> exit
Bye
[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql
Enter password:
[/SQL]
GTIDを利用するにあたり、読んでおいた方が良さそうなページ
http://nippondanji.blogspot.jp/2014/12/mysqlgtid.html
http://yakst.com/ja/posts/896
http://www.percona.com/live/mysql-conference-2014/sites/default/files/slides/FacebookGTIDPerconaLive2014.pdf