mysql user camp: gtids

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

1

MySQL Replication:Global Transaction Identifiers(GTIDs)

Shivji Kumar JhaSoftware Developer, MySQL Replication Team


2

Safe Harbour Statement

The following is intended to outline our general product direction. It is intended

for information purposes only, and may not be incorporated into any contract.

It is not a commitment to deliver any material, code, or functionality, and should

not be relied upon in making purchasing decisions. The development, release,

and timing of any features or functionality described for Oracle’s products

remains at the sole discretion of Oracle.


3

Program Agenda

Introduction to MySQL Replication

Fail-over Base– Global Transaction Identifiers – Automatic Positioning – Hands-On

Under the Hood: Become a GTID Expert– How Slave Preserves GTIDs – GTIDs Must Be Unique

Integration With Other Features– Seeking & Skipping – Purging Binary Logs – Restoring from Backup


4

Introduction to MySQL Replication Fail-over Base Under the Hood Integration With Other Features

What isReplication?


5

Replication: Copy Changes Master → Slave

MySQL Master Server– Changes data

– Sends changes to slave

MySQL Slave Server– Receives changes from master

– Applies received changes to database


M S


6

Replication: Copy Changes Master → SlaveIntroduction to MySQL Replication Fail-over Base Under the Hood Integration With Other Features

M M/S S

S

S

S

M

Server can be master, slave or both

Master can have multiple slaves


7


SM

M

● Slave can only have one master

SM

MSlave can have multiple masters!labs.mysql.com

labsYippee!


8


M/S

● Circular replication is also possible

M/S

M/S

M/S

M/S

M/S


9


Why UseReplication?


10

Why Replication? – Performance


Read scale-out

M S

write clients read clients


11



Read scale-out

M S


Morereads?More

slaves!


12



Read scale-out

M S


Morereads?More

slaves!

S

S

S

M

read clients

write clients


13

Why Replication? – Redundancy


If master crashes, promote slave to master

C

B

A


14




C

B

ACrash


15




C

B

A

B is thenew master


16

Why Replication? – Online backup/reporting


Expensive queries on slave(s)

M S

Regular clients

Reports Big queries Business intelligence


17

CB

BAAC

Image fromwww.ginkgomaps.com

Why Replication? – Long-distance Data Distribution



18


How DoesReplication

Work?


19

All Changes Written to Binary Log


A

binary log

Client


20



A

binary log

Clientcreate table t (a int);


21



create...

A

binary log

Client

Table t

create table t (a int);


22



create...

A

binary log

Client

Table t

create table t (a int);insert into t values (1);


23



create...insert...A

binary log

Client

Table t1

create table t (a int);insert into t values (1);


24

Slave Initiates Replication


B

binary log

A

binary log

Client1. Slave sends request

to start replicationto master

2. Master sends streamof replication data

to slave


25

Binary Log Sent to Slave, Re-executed


B

binary log

A

binary log

Client


26



create...

B

binary log

A

binary log

Client


27



create...

Table t

B

binary log

create...

A

binary log

Client


28



create...

Table t Table t

create...

B

binary log

create...

A

binary log

Client


29



create...insert...

Table t Table t

create...

B

binary log

create...

A

binary log

Client


30



create...insert...

Table t1

Table t

create...

B

binary log

create...insert...A

binary log

Client


31



create...insert...

Table t1

Table t1

create...insert...B

binary log

create...insert...A

binary log

Client


32

Makes sense?


33

Program Agenda






34

Fail-over Crash can happen to anyone:

– Hardware failure

– Human mistake

– Bug

– Natural disaster

Automatic fail-over with Global Transaction Identifiers:– Reduces admin overhead

– Prevents planned downtime

– Prevents unplanned downtime



36

Fail-over

Handle server crash with minimal disruption Example 1: tree


C

B

A


37

Fail-over



C

B

ACrash


38

Fail-over



C

B

A


39

Fail-over



C

B

A

Make B the new master


43

Fail-over

Handle server crash with minimal disruption Example 1: tree Example 2: line



44

Fail-over



CA B


45

Fail-over



CA BCrash


46

Fail-over



CA B


47

Fail-over



CA B

Make A direct master of C


53

Fail-over


Handle server crash with minimal disruption Example 1: tree Example 2: line Example 3: circle

C

A

B

D


54

Fail-over



C

A

B

DCrash


55

Fail-over



C

A

B

D


56

Fail-over



C

A

B

D

Make a shortcutin the circle


61

Fail-over


Handle server crash with minimal disruption Example 1: tree Example 2: line Example 3: circle Example 4: scheduled maintenance

C

A

B

D

Takeout D!


62

Fail-over



C

A

B

D

MaintainD offline,without

disruptingservice


63

Fail-over



C

A

B

D


64

Fail-over


Handle server crash with minimal disruption Example 1: tree Example 2: line Example 3: circle Example 4: scheduled maintenance Example 5: arbitrary topology

A

B

C

D

E

F


65

Wow!Lets

get started withfail-over.


66

Program Agenda






67

Global Transaction Identifiers


Server assigns Global Transaction Identifier (GTID) to every transaction:

– server_uuid:numbera61678ba-4889-4279-9e58-45ba840af334:1

– server_uuid identifies the server; globally unique

– number is incremented by 1 for each transaction on this server

Writes GTID to binary log Slave ensures transaction gets the same GTID when re-executed


68



master> CREATE TABLE t1 (a INT);

master> SELECT @@global.gtid_executed;a61678ba488942799e5845ba840af334:1

master> INSERT INTO t1 VALUES (1);

master> INSERT INTO t1 VALUES (2);


Note: interval

New variable:gtid_executed


69




slave> SELECT @@global.gtid_executed;a61678ba488942799e5845ba840af334:19999

Slave is missingone transactionSlave is missingone transaction


70

But how are GTIDs

used infail-over?


71

Program Agenda






72

Automatic Positioning


Master–slave protocol:– Slave sends @@gtid_executed to master

– Master sends all other transactions to slave

id1,trx1,id2,trx2

id1,trx1,id2,trx2,id3,trx3

A 2. id3, trx3, …

1. id1…id2

B


73



Example 1: tree

(master)

Aid1,trx1,id2,trx2,id3,trx3

(slave)

Cid1,trx1

(slave)

id1,trx1,id2,trx2 B


74



Example 1: tree

(master)


(slave)

Cid1,trx1

(slave)

id1,trx1,id2,trx2 B

Crash!


75



Example 1: tree

(master)


(slave)

Cid1,trx1

(slave)

id1,trx1,id2,trx2 B


76



Example 1: tree

(master)


(slave)

Cid1,trx1

(slave)

id1,trx1,id2,trx2 B id1

id2, trx2,...

What theslave has

What theslave doesnot have


80



Example 1: tree Example 2: circle

BA C

client client


81




id1,trx1

BA C

client client trx1


82




id1,trx1

Bid2,trx2

A C

client clienttrx2 trx1


83




id1,trx1,id2,trx2 B

id2,trx2

A C

client clienttrx2 trx1


84





Bid2,trx2

A C

client clienttrx2 trx1, trx3


85





Bid2,trx2

A C


Crash!


86





Bid2,trx2

A C



87





Bid2,trx2

A C


id2

id1,trx1,id3,trx3,...What theslave has

What theslave doesnot have


91

Awesome!How do I set

it up?


92

Program Agenda






93

Hands-On


Prerequisites:– Use transactional storage engine for all tables (InnoDB)

– Don't use CREATE TABLE … SELECT

– Don't execute CREATE TEMPORARY TABLE or

– DROP TEMPORARY TABLE inside a transaction


94

Hands-On


Prepare the server for fail-over– Sync and stop all servers

– Add to every my.cnf:gtidmode=onenforcegtidconsistency=onlogbinlogslaveupdates

– Start all servers

– Execute:> CHANGE MASTER TO MASTER_AUTO_POSITION = 1


95

Hands-On


Perform fail-over

On the slave, simply point to the new master:> CHANGE MASTER TO MASTER_HOST = '<host>', MASTER_PORT = <port number>, MASTER_USER = '<user name>' MASTER_PASSWORD = 'secret';

No positions needed!


96

Hands-On


Perform fail-over

C

B

ACHANGE MASTER TOMASTER_HOST = 'B',MASTER_PORT = <B's port>,MASTER_USER = '<user name>'MASTER_PASSWORD = 'secret';


97

Interesting!I want to learn

more.


98

Program Agenda






99

Under the Hood: How Slave Preserves GTIDs


Bid1,trx1,id2,trx2 A

Slave= client


100




Slave= client

SET GTID_NEXT = 'id1';trx1


101



id1,trx1


Slave= client

SET GTID_NEXT = 'id1';trx1


102



id1,trx1


Slave= client

SET GTID_NEXT = 'id1';trx1SET GTID_NEXT = 'id2';trx2


103



id1,trx1,id2,trx2 B

id1,trx1,id2,trx2 A

Slave= client

SET GTID_NEXT = 'id1';trx1SET GTID_NEXT = 'id2';trx2


104

Under the Hood: GTID_NEXT

GTID_NEXT – session system variable

Default: “AUTOMATIC”

– → Server generates new GTID for next transaction

Slave thread sets to “UUID:NUMBER”– → Server uses specified GTID for next transaction



105

Under the Hood: GTID_NEXT

Clients can set GTID_NEXT too:

Mysqlbinlog outputs SET GTID_NEXT statements:

id1,trx1id2,trx2

set gtid_next = “id1”;trx1set gtid_next = “id2”;trx2

mysqlbinlog

binary log

id,insert

A

Client

set gtid_next=”id”;insert



106

Program Agenda






107

Under the Hood: GTIDs Must Be Unique

Impossible to execute a GTID twice:


id1,trx1id1,trx2A


108



Try to execute a GTID second time → transaction skipped:


id1,trx1id1,trx2A

id1,trx1

A

Client


109





id1,trx1id1,trx2A

id1,trx1

A

Client

set gtid_next=”id1”;trx2


110





id1,trx1id1,trx2A

id1,trx1

A

Client

set gtid_next=”id1”;trx2

trx2 not executed!


111

I loveGTIDs!

Is there anythingthat I should pay

attention to?


112

Program Agenda






113

Skipping a Transaction

Common use case: skip a bad transaction Transaction should be skipped forever Not enough to move replication position



id1,trx1

B

Badtransaction


114





id1,trx1,id3,trx3 B

Badtransaction


115





id1,trx1,id3,trx3 B

Badtransaction trx2 comes back

after reconnect

id2, trx2, …

id1,id3


116





id1,trx1,id3,trx3 B

Badtransaction trx2 comes back

after reconnect

So we don'tallow seeking!

id2, trx2, …

id1,id3


117


Recall: Try to execute a GTID second time → transaction skipped Interesting feature:

Client executes GTID before slave→ slave skips transaction

To force slave thread to skip a transaction:

slave> SET GTID_NEXT=“GTID of transaction to skip”;slave> COMMIT;

Transaction is skipped forever



118


How to skip atransaction forever



id1,trx1

B

Badtransaction

set gtid_next=”id2”;commit;Client


119





id1,trx1,id2, – B

Badtransaction



120





id1,trx1,id2, – ,id3,trx3

B

Badtransaction



121





id1,trx1,id2, – ,id3,trx3

B

Badtransaction


id4, trx4, …

id1,id2,id3


122

Filters

--replicate-ignore-table etc Transactions should be filtered-out forever Slave commits empty transaction


A

(crashed)(master)


(slave)

id1,trx1,id2,-

id3,trx3B

Filters outtrx2

Logs id2 with anempty transaction


123

Skipping Stuff: Summary & Recipe

To seek forward from position A to B:– Commit empty transaction for each GTID between A and B

To seek backward:– Not possible, does not make sense

To skip N transactions:– Get the GTIDs, commit empty transaction for each GTID

Replication filters automatically commit empty transactions



124

So the ruleis easy:

skip = emptytransaction


125

Program Agenda






126

Purging Binary Logs

Binary logs are rotated and can be purged



master-bin.02 master-bin.03

id7,trx7,id8,trx8

master-bin.01



127

Purging Binary Logs





id7,trx7,id8,trx8

Client PURGE BINARY LOGS TO 'masterbin.02'

master-bin.01



128

Purging Binary Logs





id7,trx7,id8,trx8 XPurged!

Client PURGE BINARY LOGS TO 'masterbin.02'

master-bin.01


129

Purging Binary Logs

On Master: cannot send purged GTIDs to slave

– @@gtid_purged = all purged GTIDs



130

Purging Binary Logs




B Purged!Aid4,trx4,id5,trx5,id6,trx6

master-bin.02

XPurged!id1,trx1,id2,trx2

slave-bin.01

gtid_purged=id1,id2,id3 gtid_executed=id1,id2

master-bin.01


131

Purging Binary Logs




B Purged!Aid4,trx4,id5,trx5,id6,trx6

master-bin.02

XPurged!id1,trx1,id2,trx2

slave-bin.01

Error!

id1,id2

Nothing sensible to do:B needs trx3

which is purgedgtid_purged=id1,id2,id3 gtid_executed=id1,id2

master-bin.01


132

Purging Binary Logs

On Slave: must send all GTIDs (purged or not) to master

– @@gtid_executed = all GTIDs (purged or not)


A

slave-bin.02

XPurged!A

master-bin.02


id4,trx4,id5,trx5

id4,trx4

slave-bin.01master-bin.01

id5,trx5

id1…id4

Note: slave must send id1...id3 despite purgedor else master would send trx1…trx3


133

Purging Binary Logs



On Slave: must send all GTIDs (purged or not) to master

– @@gtid_executed = all GTIDs (purged or not)




id7,trx7,id8,trx8 XPurged!

@@gtid_purged = id1 … id3 @@gtid_executed = id1 … id8

master-bin.01


134

Purged and Executed are Stored in Binary Log

Binary logs begin with:

[ list of all GTIDs in all previous binary logs ]


A

[id1…id3]id4,trx4,id5,trx5,id6,trx6


[id1…id6]id7,trx7,id8,trx8 XPurged!

master-bin.01

GTIDs inmaster-bin.01 +master-bin.02

GTIDs inmaster-bin.01


135

Purged and Executed are Stored in Binary Log

Binary logs begin with:

[ list of all GTIDs in all previous binary logs ]


A

[id1…id3]id4,trx4,id5,trx5,id6,trx6


[id1…id6]id7,trx7,id8,trx8 XPurged!

@@gtid_executed=

head of newest log +GTIDs in newest logid1…id3

id1…id6 + id7 + id8

master-bin.01

@@gtid_purged=

head of oldest log


136

Purging Binary Logs: Summary

Works automatically, behind the scenes

Purged GTIDs are stored in @@gtid_purged @@gtid_purged is a subset of @@gtid_executed



137

Automaticis the word!Does purging haveanything to do with

backups?


138

Program Agenda






139

Provision a Slave From a Backup


id1,create,id2,insert A

Table t1

B


140




Table t1

B

Backup

Table t1

Backup


141




Table t1

B

Table t1 Backup

Table t1

Backup Restore


142




Table t1

B

Table t1 Backup

Table t1

Backup Restore

Binary lognot

restored!


143




Table t1

B

Table t1 Backup

Table t1

Backup Restore

Binary lognot

restored!

Set gtid_executed andgtid_purged to id1,id2

(or else A would sendid1,id2 when B connects)


144




Table t1

B

Table t1 Backup

Table t1

Backup Restore

Binary lognot

restored!

Set gtid_executed andgtid_purged to id1,id2

(or else A would sendid1,id2 when B connects)

XPurged!

set @@gtid_purged = “id1,id2”


145

Provision a Slave From a Backup @@gtid_executed must agree with database state

– @@gtid_executed after restore = @@gtid_executed at backup

@@gtid_purged = GTIDs in @@gtid_executed that are not in log– Binary logs not (normally) restored

– @@gtid_purged after restore = @@gtid_executed after restore

@@gtid_purged is settable:

– SET @@GLOBAL.GTID_PURGED = "GTID_EXECUTED at backup"– Also sets @@GTID_EXECUTED to the same value



146

Restore a Backup

Backup tools must store @@gtid_executed Restore tools must execute:

– SET @@GLOBAL.GTID_PURGED = "GTID_EXECUTED at backup"



147

You hadme at “GTID”

What shall Ido now?


148

Next Steps

More on GTIDs– svenmysql.blogspot.co.uk

More on MySQL 5.6 Replication– http://dev.mysql.com/tech-resources/articles/

mysql-5.6-replication.html

Try Out MySQL 5.6– http://dev.mysql.com/downloads/mysql/

mysql user camp: gtids

Technology