Ivan  Zoratti  

GTIDs  ExplainedMySQL 5.6, MariaDB 10, Galera 3

V1403.01

MySQL  5.6  Replication

�2

M1

S1 S2 S3

GTID  in  MySQL  5.6

�3

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

Transaction  ID  Sequence  number  determined  by  the  order  in  which  

the  transaction  was  committed  on  this  server  !

Transaction  ID  =  =  SequenceSource  ID

Identifies  the  originating  server!

Source  ID  =  =  Server  UUID

GTID  in  MySQL  5.6

•Used  to  identify  a  transaction  in  a  MySQL  5.6  Cluster  •Sets  are  also  allowed  to  identify  an  interval  of  transactions  •Preserved  between  masters  and  slaves,  i.e.  they  identify  the  source  (origin)  of  a  transaction  •Transactions  with  the  same  GTID  cannot  be  applied  more  than  once  

•Benefits  •Simplified  failover  (with  CHANGE  MASTER  TO  and  MASTER_AUTO_POSITION=1)  •Guarantees  slave  consistency  by  checking  Executed_Gtid_Set

�4

Galera  Cluster

• Read  &  Write  access  to  any  node  

• Client  can  connect  to  any  node  

• There  can  be  several  nodes  

• Automatic  node  provisioning  

• Replication  is  synchronous

�5

GTID  in  Galera  3

•Used  to  identify  a  state  change  •i.e.  to  identify  write  operations  to  the  database  

•Used  to  identify  the  state  itself  •i.e.  the  last  state  change

�6

GTID  in  Galera  3

�7

!45eec521-2f34-11e0-0800-2a36050b826b:94530586304

An  ordinal  sequence  number  (seqno,  64-­‐bit  signed  integer)  to  denote  the  position  of  the  change  in  the  

sequence  !

Sequence  =  =  Change  in  the  Cluster

State  UUIDI  uniquely  identifies  the  state  and  the  sequence  of  changes  it  

undergoes  !

UUID  =  =  Cluster  ID

GTID  in  Galera  3

•A  Galera  Cluster  acts  as  a  single  node  

•No  holes  in  the  sequence  

•Stored  in  grastate.dat  •5ee99582-­‐bb8d-­‐11e2-­‐b8e3-­‐23de375c1d30:8204503945773  <<<  Typical  sequence  •5ee99582-­‐bb8d-­‐11e2-­‐b8e3-­‐23de375c1d30:-­‐1  <<<  The  node  has  crashed  •00000000-­‐0000-­‐0000-­‐0000-­‐000000000000:-­‐1  <<<  The  node  has  crashed  during  the  execution  of  a  non  transactional  operations  (e.g.  a  DDL  op)  or  the  op  has  been  aborted

�8

MariaDB  10  Replication

�9

GTID  in  MariaDB  10

�10

!#131109 14:59:59 server id 1 end_log_pos 4151 GTID XXX-YYY-ZZZ

/*!100001 SET @@session.gtid_seq_no=ZZZ*//*!*/;

Sequence  Number  64bit  unsigned  

It  increases  monotonically  at  each  commit  It  is  applied  for  each  Event  Group,  i.e.,  for  each  BEGIN/COMMIT  or  

for  groups  that  have  no  BEGIN/COMMIT(for  example  DDL  commands,  TRUNCATE  and  others)

Server  ID  32bit  unsigned  

The  server_id  value  for  the  MariaDB  instance

Domain  ID  32bit  unsigned  

The  domain_id  value  for  the  Replication  stream

GTID  in  MariaDB  10

•1  GTID  for  each  event  group  •Groups  can  be  executed  in  parallel  

•Transaction  safe  (stored  in  an  InnoDB  table,not  in  a  file)  

•MASTER_USE_GTID=gtid_slave_pos  -­‐  great  to  position/reposition  slave  nodes  •Domain  ID  is  used  to  guarantee  that  this  works  with  multi-­‐source  replication

�11