During the PerconaLive conference in Amsterdam, I attended a session where I heard a good piece of advice about using GTID. It amounts to: look at SHOW SLAVE STATUS output, and if you see more than one line in the Executed_Gtid_Set field, this tells you immediately if someone has written on a slave database.
This is good advice. Let's dissect it. Here is what a regular slave looks like, when nobody has messed up with it:
Notice that this advice holds true even when the slave being considered is an intermediate one, i.e. a relay slave which is master of one or more slaves. Due to the nature of GTIDs, even though the intermediate slave is recording the transactions to its own binary log, the transaction identifier does not change. Thus you should see a clean set of transactions throughout the chain. For example, if you have another slave that is replicating from slave #2, you would see something like this:
This is good advice. Let's dissect it. Here is what a regular slave looks like, when nobody has messed up with it:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
[...]
Master_Server_Id: 1
Master_UUID: 00013454-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
Executed_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
1 row in set (0.00 sec)
Notice that this advice holds true even when the slave being considered is an intermediate one, i.e. a relay slave which is master of one or more slaves. Due to the nature of GTIDs, even though the intermediate slave is recording the transactions to its own binary log, the transaction identifier does not change. Thus you should see a clean set of transactions throughout the chain. For example, if you have another slave that is replicating from slave #2, you would see something like this:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
[...]
Master_Server_Id: 102
Master_UUID: 00013456-3333-3333-3333-333333333333
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
Executed_Gtid_Set: 00013454-1111-1111-1111-111111111111:1-12
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Figure 1 - Hierarchical replication depth of processing
