【再確認】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]> 

Initial

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]> 

データ追加
final
GTIDの確認
final-2
Binary Logの確認
GTID
Binary Log確認時のGTID確認
GTID-Confirm

ここら辺は、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

Comments are closed.

Post Navigation