development of fault-tolerant failover tools with mysql utilities - mysql connect 2013 [con4276]


Development of Fault-Tolerant Failover Tools with MySQL Utilities

Dr. Paulo Jesus

Software Developer – MySQL Utilities

Dr. Lars Thalmann

MySQL Development Director


Program Agenda

Introduction: Faults, Fault-Tolerance and Failover

MySQL Failover

– How to Handle Master Crash, Slave Crash and Connection Failures

Automatically!

Introducing the mysqlfailover utility

– Fault-Tolerance and Taking Advantage of its Features

Final Remarks


Introduction: Faults, Fault-tolerance and Failover


Faults

Crash

– Server crash and stop due to a cosmic ray (more likely software/hardware

malfunction)

Message loss

– Communication channel fails

Byzantine

– Data corruption or malicious attacks

Different types of faults can lead to system failures


Fault-Tolerance

Provide high-availability

Improve reliability

Reduce system downtime and revenue loss

Typically obtained through redundancy

– Example: replication

Fault-tolerance is a property that enables the system to continue

operating properly when faults occur.


Failover

Fault-tolerant strategy:

– Promote a slave as the new master

– Reduce system downtime

* Switchover is the transfer of the master role in an otherwise healthy topology

and is performed manually. In many cases, the original master can be returned

to the topology as a slave.

Failover can be achieved by automatically switching to another server

(redundant or standby) upon failure. *


Replication topology:

Context: MySQL ReplicationFaults, Fault-Tolerance and Failover

Master: A

Slaves: B, C and D

Asynchronous

replication

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3




Master: A

Slaves: B, C and D

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Here comes the cosmic ray!!!


Not critical...


Partial impact:

– Reduce reads

performance

Replace the slave

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Slave crashed




Master: A

Slaves: B, C and D

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3




Master: A

Slaves: B, C and D

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Connection failure


Not critical…


Partial impact:

– Outdated data

read from slave

Restore connection

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Master-Slave Connection lost




Master: A

Slaves: B, C and D

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3




Master: A

Slaves: B, C and D

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Here comes another cosmic ray!!!


Now, it gets serious!


Replication stops

– Reads only

– No more writes

Failover is needed

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Master crashed


Failover process:


Promote the

“best”* slave as

master

Connect remaining

slaves to the new

master

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

* Here it is the most up-to-date (for simplicity).


Why ?

Context: MySQL ReplicationFailover Is Not So Easy…

New master might

not be the most up-

to-date *

– But must be at the

end of failover

Ensure data

consistency

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx2

trx3

trx1

trx2

* The “best” candidate might be the one with the best hardware.


MySQL Failover


Relay log details:

Relay LogsMySQL Failover

Slave B: trx2

Slave C: <none>

Slave D: trx2, trx3

Slave B and C did

not receive trx3

Atrx1

trx2

trx3

B

C

Dtrx1

trx1

trx1

trx2

trx3

trx3trx2

trx2

trx3

binlog

relay

relay

relay


Master crashed!


Slave D is the most

up-do-date

– SHOW SLAVE STATUS

– Master log file and

position or

retrieved GTIDs

Atrx1

trx2

trx3

B

C

Dtrx1

trx1

trx1

trx2

trx3

trx3trx2

trx2

trx3

binlog

relay

relay

relay

Most up-to-date:

Check slave

status


Failover:


Promote D as the

new master

– STOP SLAVE,

CHANGE MASTER

TO*, START SLAVE

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx1

trx2

trx2

trx2

trx3

binlog

relay

relay

relay

Hum... Something is not right...

binlog

* CHANGE MASTER TO deletes all relay log files and starts a

new one, unless relay_log_file or relay_log_pos are specified.


(1) What can happen:


“D” consume trxs on

relay log

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx2

trx3

trx1

trx2

trx2

binlog

relay

relay

relay binlog





relay log

Slaves read trxs

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx2

trx3

trx2

trx3

binlog

relay

relay

relay binlog

trx2, trx3





relay log

Slaves read trxs

Slaves apply trxs

SQL thread stops on “C”

Atrx1

trx2

trx3

B

C

D

trx1

trx2

trx3

trx1

trx2

trx3

trx1

trx2

trx2

trx3

binlog

relay

relay

relay

Conflicting transactions!

binlog

SQL thread error!




Delete relay log on

the new master

– RESET SLAVE

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx1

trx2

trx2

trx3

binlog

relay

relay

relay binlog




Delete relay log on

the new master

– RESET SLAVE

trx2 only on slave B and

trx3 is lost.

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx1

trx2

trx2

trx3

binlog

relay

relay

relay binlog

trx3 is lost!

Transactions lost!


Solution:


Consume all

transactions on the

relay logs before

promoting the new

master

Atrx1

trx2

trx3

B

C

D

trx1

trx2

trx1

trx2

trx3

trx1

trx2

binlog

relay

relay

relay binlog


New master is not the most up-to-date:

New Master SynchronizationMySQL Failover

“Best” candidate is

the one with better:

– Hardware

– Location

It is not the most up-

to-date

Atrx1

trx2

trx3

B

C

Dtrx1

trx1

trx1

trx2

trx3

trx3trx2

trx2

trx3

binlog

relay

relay

relay


Failover:


Slave B is the “best”

candidate

Consume relay logs

Promote “B” to

master

Atrx1

trx2

trx3B

C

D

trx1

trx2

trx1

trx2

trx3

trx1

trx2

binlog

relay

relay

relay


Failover:


Slave B is the “best”

candidate

Consume relay logs

Promote “B” to

master

Atrx1

trx2

trx3B

C

D

trx1

trx2

trx1

trx2

trx3

trx1

trx2

binlog

relay

relay

relay

Data consistency issue!

trx3 is only on “D”!


Solution:


Synchronize candidate

before promoting it:

Get and apply

differential changes

from the most up-to-

date slave or loop

throw all slaves

Atrx1

trx2

trx3B

C

D

trx1

trx2

trx3

trx1

trx2

trx3

trx1

trx2

binlog

relay

relay

relay

trx3


Solution:


New master must

become one of the

most up-to-date

before being

promoted

Atrx1

trx2

trx3B

C

D

trx1

trx2

trx3

trx1

trx2

trx3

trx1

trx2

binlog

relay

relay

relay

Note: If candidate is too delayed it might take too much

time to synchronize... Better to choose another one?


Failover With or Without GTIDs

Without GTIDs:

– Handle files and positions

Headache to determine file and position to synchronize candidates

Headache to determine file and position for CHANGE MASTER TO

With GTIDs (from MySQL 5.6.5):

– Handle GTID sets

Simple sets manipulation to determine missing transactions

Automatic: CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1

No headache to determine new master’s file and position

MySQL Failover


Failover With or Without GTIDs

Use of GTIDs simplifies the failover task:

– Removes complexity of handling files and positions

– Opens the door to support more complex multi-tier replication topologies

Still a difficult task to achieve (manually):

– Detecting master failure

– Requires profound knowledge of MySQL replication

– Is time consuming and error-prone

Wouldn’t it be great if we had a tool to do it automatically?

MySQL Failover


Introducing the mysqlfailover Utility



Features

– Configurable timeouts to detect master failures (interval and ping)

– Configurable failover modes (auto, elect, fail)

– Optimized failover algorithm

http://svenmysql.blogspot.pt/2013/03/flexible-fail-over-policies-using-mysql.html

– Automatic slave discovery

– Logging to file

Utility to report the health of a replication topology (master and its slaves)

and perform automatic failover.

New



Features

– Two operation modes:

Console (default)

Daemon (only on POSIX platforms)

– Support for new authentication mechanism (login-path)

– Detect execution of multiple instances (for the same master)

– Detect errant transactions

– Pedantic execution mode

– Extension points (allow execution of external scripts)

New

New

New

New



Requirements

– All servers with version >= MySQL 5.6.9 and GTID_MODE=ON:

--report-host, --report-port, --log-slave-updated, --enforce-gtid-consistency, and

--master-info-repository=TABLE

More information

– http://dev.mysql.com/doc/workbench/en/mysqlfailover.html

Example of use

mysqlfailover --master=m1 --slaves=s2,s1,s3 --daemon=start --log=failover.txt

Note: m1, s1, s2 and s3 are login-path in the mylogin.cnf file used for security and

usability purposes, avoiding the use of plain text connection strings, such as

rpluser:mypass@master_host:3306.


mysqlfailover in action:


Master fault detection

– Are you alive?

Get health data from

master and slaves

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3

Simple, open source, no impact on system

performance!


Fault-Tolerance of mysqlfailover and Taking Advantage of its Features


Fault-Tolerance of mysqlfailover

How faults affect mysqlfailover?

– Server crash

– Connection failure

How to improve fault-tolerance?

– Tips and tricks

– Extension points

Errant transactions

– Good practice to avoid them

What will be discussed


Server crash:


Utility stopped

Replication topology

not affected

Easy to start a new

instance of the utility

– Use --force option,

overwrite instance

registration

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3


Connection failure (1):


Fault detection

– Suspect master

has failed

Failover

– Slave promoted to

new master

– Old master still

alive, abandoned

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3


Connection failure (2):


Fault detection

– Suspect master

has failed

Failover

– Split replication

topology

Atrx1

trx2

trx3

B

C

Dtrx1

trx2

trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3


Improve fault-tolerance:


Run mysqlfailover

close to the master,

but not on it

Carefully set “interval”

and “ping” values

Monitor mysqlfailover

Use extension points

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx3

trx1

trx2

trx2, trx3

trx3


Extension Points of mysqlfailover

--exec-fail-check: script executed on each interval to replace default

failure detection

– E.g., customize failure detector according to replication topology (test

master connection through different network paths)

--exec-before: script to execute before starting failover

– E.g., shutdown master’s system (to ensure only one master will be online)

Allow the execution of external scripts to override the behavior of the

utility at specific points using the following options:


Extension Points of mysqlfailover

--exec-after: script to execute at the end of failover

– E.g., change network setting to direct client writes to the new master

--exec-post-failover: script to execute after completing the failover

process (successfully or not)

– E.g., send a notification to the administrator with the failover result

Access to variables with master’s information from scripts

– $1: old master host, $2: old master port, $3: new master host, $4: new

master port


Slave transaction that is not on all slaves

Errant Transactions

Examples: b-1, c-1

– Different GTID

– Can be the same

SQL command

CREATE TABLE

table1

– Each one only on

one slave

Aa-1

a-2

a-3

B

C

Da-1

b-1

a-1

a-2

a-3

a-1

a-2

c-1

trx2, trx3

trx3


Can lead to errors during/after failover:

Errant Transactions

Both b-1 and c-1

– CREATE TABLE table1

New master B cannot

apply c-1, and slave C

cannot apply b-1: Table

“table1” already exists.

Aa-1

a-2

a-3

B

C

D

a-1

b-1

a-2

a-3

a-1

a-2

a-3

a-1

a-2

c-1SQL Error!

b-1, a-3

b-1

Conflicting transactions!


Errant Transactions

mysqlfailover identifies errant transactions

– Error when starting the utility

– Warning while executing unless the --pedantic option is used

Can only be fixed manually

– Commit empty transaction on slaves with the GTID of the errant one

SET GTID_NEXT=‘…’; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC‘;

Good practice

– Disable binary log (SET sql_log_bin = 0) when executing transactions

locally on slaves. So that they are not replicated!

How to handle this problem? Avoid it!mysqlfailover

is clever!


Final Remarks


Final Remarks

Faults can greatly impact your system (replication topology)

– System down

– Revenue lost

– Negative reputation

Failover

– Fault-tolerant strategy for high availability

– Works in most situations

Fault detectors make mistakes, failover process can fail, etc.


Final Remarks

Simple and easy to use

Advanced features

– Perform several configuration checks (to prevent failures)

– Optimized failover algorithm

– Configurable monitoring timeouts and operation modes

– Extension points

Building block for more sophisticated and fault-tolerant tools

– Taking advantage of extension points or MySQL Utilities Library (Open

Source)

The mysqlfailover utility


By The Way…

There are many other useful MySQL Utilities

mysqldbcompare

mysqldbcopy

mysqldbexport

mysqldbimport

mysqldiff

Database Ops mysqlauditadmin

mysqlauditgrep

Specific – Audit log

mysqlserverclone

mysqlserverinfo

Server Ops

mysqluc

Usability

msqldiskusage

mysqlindexcheck

mysqlmetagrep

mysqlprocgrep

mysqluserclone

mysqlfrm

Generic/Support

New

New

New

New

mysqlfailover

mysqlreplicate

mysqlrpladmin

mysqlrplcheck

mysqlrplshow

High Availability


Do You Want More?

MySQL Utilities (1.3.5 GA)

– Download: http://dev.mysql.com/downloads/tools/utilities/

Launchpad: https://launchpad.net/mysql-utilities

– Docs: http://dev.mysql.com/doc/workbench/en/mysql-utilities.html .

Contributing Ideas:

– Community users can use: http://bugs.mysql.com (MySQL Workbench: Utilities).

– Oracle customers can use: bug.oraclecorp.com (Product = MySQL Workbench,

Component = WBUTILS).

Send us an e-mail (please use sparingly):

– Charles A. Bell, PhD (Team Lead): [email protected]

– Paulo Jesus, PhD (Developer): [email protected]

http://dev.mysql.com/downloads/tools/utilities/

https://launchpad.net/mysql-utilities

http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

http://bugs.mysql.com/

mailto:[email protected]

mailto:[email protected]


Questions ?Paulo [email protected]


Graphic Section Divider



Another Fault Scenario

Master: A

Slaves: B, C and D

Asynchronous

replication

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx1

trx2

trx2, trx3

trx3

trx3

trx3 did not reach any slave (yet)


Master crashed:


trx3 didn’t reach

any slave

Atrx1

trx2

trx3

B

C

Dtrx1 trx1

trx2

trx1

trx2

trx2, trx3

trx3

trx3

Is failover enough to solve this?


What can we do?


With failover

– Try to get missing

transaction from

master (if possible)

Semi-synchronous

replication helps

Atrx1

trx2

trx3

B

C

D

trx1

trx1

trx2

trx1

trx2

trx2

development of fault-tolerant failover tools with mysql utilities - mysql connect 2013 [con4276]

Technology