Replication for Scaling and High Availability - PowerPoint PPT Presentation

Loading...

PPT – Replication for Scaling and High Availability PowerPoint presentation | free to view - id: 14163f-YjMzZ



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Replication for Scaling and High Availability

Description:

Replication for Scaling. and High Availability. Jeremy Cole ... jcole_at_yahoo-inc.com. Basics. Master/Slave one-way asynchronous replication ' ... Being Picky ... – PowerPoint PPT presentation

Number of Views:615
Avg rating:3.0/5.0
Slides: 62
Provided by: Yah966
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Replication for Scaling and High Availability


1
Replication for Scalingand High Availability
Eric Bergen
Jeremy Cole
ebergen_at_yahoo-inc.com
jcole_at_yahoo-inc.com
2
Replication In-Depth
Jeremy Cole
jcole_at_yahoo-inc.com
3
Basics
  • Master/Slave one-way asynchronous replication
  • Master keeps logs of all changes called
    binary logs or binlogs
  • Slave connects to the master through normal
    MySQL protocol (TCP port 3306)
  • Slave requests binary logs from last position
  • Master sends binary logs up to current time
  • Master keeps sending binary logs in real-time

4
Basics Notes
  • Replication works with all tables types
  • Any critical reads must be done on the master
    replication is asynchronous, there may be a delay
  • Master will rotate binary logs automatically for
    every 1G of log records
  • You must purge any old, unused, logs yourself

5
Typical Setup
  • One Master (single source of truth)
  • Any number of slaves
  • Slaves are used for reads only
  • All writes go to the master
  • There are many other possibilities

6
Keeping Up
  • Master and slave must be similar hardware
  • Slave generally has to do at least as much,
    usually more, work than the master
  • Make sure disks can keep up with the writes
  • Dont skimp on slave configuration, especially
    memory, as its needed for caching

7
Architect for Growth
  • Keep a spare slave, that you can take down to
    clone and bring up new slaves
  • Make sure your application doesnt depend on a
    certain number of slaves
  • Use a load balancer for managing access to slaves

8
Topologies
9
Master with Slave
Master
Slave
10
Master with Many Slaves
Master
Slave
Slave
Slave
Slave
Slave
11
Master with Relay Slave
Master
Relay Slave
Slave
12
Master with Relay and Many Slaves
Master
Relay Slave
Slave
Slave
Slave
Slave
Slave
13
Master with Many Relays
Master
Relay Slave
Relay Slave
Relay Slave
Relay Slave
Relay Slave
Slave
Slave
Slave
Slave
Slave
14
Dual Masters
Master
Master
15
Dual Masters with Slaves
Slave
Master
Master
Relay Slave
Slave
Slave
Slave
16
Ring (Hmm, bad)
Master
Master
Master
17
How It Really Works
18
Master
UPDATE INSERT DELETE CREATE DROPALTER
Data
Master
Client
Client
binlog-do-db binlog-ignore-db
Client
Logging
Client
Control
Binary Log hostname-bin.001
19
Network
TCP/3306
20
Slave
I/O Thread
SQL Thread
replicate-do- replicate-ignore-
Relay Log hostname-relay-bin.001
Slave
master.info
CHANGE MASTER TO ...
Client
21
I/O Thread vs. SQL Thread
  • Reading queries from the master and executing
    them are decoupled in 4.0
  • Incoming queries are read from the master by the
    I/O thread and spooled locally in relay logs
  • The SQL thread follows the relay logs and
    executes the queries
  • Due to caching, most of the time, the SQL thread
    never really reads the files off disk

22
Dos and Donts
23
Do
  • Make sure server-id is set on all machines
  • Enable log-bin on your master
  • If a slave will also be a master, set
    log-slave-updates
  • Take backups on a slave
  • Architect your replication setup for growth!

24
Dont
  • Dont use master- lines in my.cnf use CHANGE
    MASTER instead
  • Dont take your backups on the master

25
Commands
26
SHOW MASTER STATUS
  • Run on the master
  • Shows you what log file the master is currently
    writing to, and at what position
  • mysqlgt show master status
  • ----------------------------------------------
    ------------
  • File Position Binlog_do_db
    Binlog_ignore_db
  • ----------------------------------------------
    ------------
  • mysql1-bin.005 79
  • ----------------------------------------------
    ------------
  • 1 row in set (0.00 sec)

27
SHOW MASTER LOGS
  • Run on the master
  • Lists binary logs available
  • Alias SHOW BINARY LOGS
  • mysqlgt show master logs
  • ------------------------------
  • Log_name File_size
  • ------------------------------
  • mysql2-bin.000001 359125074
  • mysql2-bin.000002 327
  • mysql2-bin.000003 4155
  • mysql2-bin.000004 4307
  • mysql2-bin.000005 2081
  • ------------------------------
  • 5 rows in set (0.00 sec)

28
SHOW BINLOG EVENTS
  • Run on the master
  • Shows events (queries) from the binary log
    specified
  • SHOW BINLOG EVENTS
  • IN 'log_name'
  • FROM pos
  • LIMIT offset, row_count

29
SHOW BINLOG EVENTS
  • mysqlgt show binlog events
  • -gt in 'mysql1-bin.002
  • -gt from 709 limit 1 \G
  • 1. row
  • Log_name mysql1-bin.002
  • Pos 709
  • Event_type Query
  • Server_id 1
  • Orig_log_pos 709
  • Info use movies insert into revo
    values (NULL,'Step by Step Wedding
    Dances(2004)',NULL,1,'1808585847')
  • 1 row in set (0.00 sec)

30
PURGE MASTER LOGS
  • Run on the master
  • Deletes binary logs up to (but not including) a
    certain log file, or date
  • Alias PURGE BINARY LOGS
  • In 4.1, BEFORE is new uses the files
    modification time
  • PURGE MASTER LOGS
  • TO mysql1-bin.003
  • PURGE MASTER LOGS
  • BEFORE 2005-01-05 101504

31
CHANGE MASTER TO
  • Run on slave
  • Allows you to configure, or reconfigure a slave
  • Used instead of master- options in the
    configuration
  • If all goes well, only needs to be run once in
    lifetime of server ?
  • CHANGE MASTER TO
  • master_hostfoo.corp,
  • master_usergeorge,
  • master_passwords3cr3t

32
SHOW SLAVE STATUS
  • Run on the slave
  • Shows you
  • Whether the SQL and I/O threads are currently
    running
  • What file and position the slave is currently
    reading from the master (Master_Log_File and
    Read_Master_Log_Pos)
  • What file and position the slave is currently
    executing (Relay_Log_File and Relay_Log_Pos)
  • What file and position the slave is currently
    executing, relative to the master
    (Relay_Master_Log_File and Exec_master_log_pos)

33
SHOW SLAVE STATUS Example
  • mysqlgt show slave status \G
  • 1. row
  • Master_Host mysql1.inf.dcn
  • Master_User repl
  • Master_Port 3306
  • Connect_retry 60
  • Master_Log_File mysql1-bin.005
  • Read_Master_Log_Pos 79
  • Relay_Log_File mysql4-relay-bin.006
  • Relay_Log_Pos 6646
  • Relay_Master_Log_File mysql1-bin.005
  • Slave_IO_Running Yes
  • Slave_SQL_Running Yes
  • Replicate_do_db
  • Replicate_ignore_db
  • Last_errno 0
  • Last_error
  • Skip_counter 0
  • Exec_master_log_pos 79

34
SLAVE STOP and SLAVE START
  • Run on the slave
  • Stops or starts the slave threads
  • Allows manual connection/disconnection of master
    and slave
  • Optionally specify which parts to stop or start
    SLAVE START IO_THREAD

35
LOAD DATA FROM MASTER
  • Run on the slave
  • Dont use it!
  • Only support MyISAM tables
  • Locks tables on master
  • Only sets up one slave
  • Use other methods (e.g. mysqlhotcopy, ibbackup)
    instead

36
Configuration Details
37
Master Configuration
  • server-id The unique server ID of this MySQL
  • log-bin Enable logging of changes to binary
    logs
  • log-slave-updates Log updates that arrive on
    the slave thread to the binary logs as well
    (required if this master is also a slave of
    another machine)
  • binlog-do-db Disables logging of any changes,
    except to the specified databases
  • binlog-ignore-db Log all changes, as usual,
    except for the specified databases

38
Slave Configuration
  • server-id Same thing
  • log-slave-updates Use this if the machine is
    both master and slave
  • master- Dont use these! They are read only
    once, at first startup. After that, master.info
    is used instead!
  • report-host, report-port The host and port to
    report to the master as connecting from This
    allows the master to connect back for SHOW SLAVE
    HOSTS.
  • read-only Disallow all local changes.

39
Slave Config. Being Picky
  • replicate-do-db, replicate-do-table Ignore all
    changes to any databases or tables except those
    listed. The changes are still received over the
    network, but ignored on the slave.
  • replicate-ignore-db, replicate-ignore-table
    Ignore any changes to the listed databases or
    tables. Again, all changes are still received.
  • replicate-wild-do-table, replicate-wild-ignore-tab
    le Same as -do-table and -ignore-table, but
    allows LIKE pattern matching.

40
Any questions?
41
Inter-DatacenterFailover
42
Disclaimer
  • The solution in these slides does not necessarily
    work as-is for anyone
  • You will have to do some work, some thinking, and
    some design on your own
  • If youre setting up failover within one
    datacenter, many other solutions exist

43
Goals
  • Avoid downtime due to failures
  • Extremely fast failover
  • No single point of failure
  • No dependency on DNS changes
  • Allow for painless, worry-free planned
    failovers to upgrade, change of hardware, etc.
  • Fail-back must be just as painless

44
Normal (Data Center A)
Data Center A
Data Center B
SSL Encrypted TCP/13306
Master
Master
stunnel
stunnel
Slave
Slave
(www)
(www)
Slave
Slave
Slave
Slave
End Users
45
Failover (Data Center B)
Data Center A
Data Center B
SSL Encrypted TCP/13306
Master
Master
stunnel
stunnel
Slave
Slave
(www)
(www)
Slave
Slave
Slave
Slave
End Users
46
Why not use DNS?
  • Waiting for DNS changes can introduce very long
    delays in the failover process
  • DNS does not ensure complete and immediate
    traffic movement downtime required to maintain
    consistency

47
Why use proxy?
  • Using proxy (or equivalent) allows
    instantaneous and complete switching of traffic
    between masters
  • DNS introduces large delays and uncertainty
  • Client-side configuration changes introduce
    uncertainty
  • For planned failovers, you cant have uncertainty
  • In emergency failovers, changes must happen
    quickly

48
Normal Operation
mysqld
mysqld
stunnel server
proxy
stunnel server
proxy
stunnel client
fw
stunnel client
fw
(www)
(www)
49
Failover Operation
mysqld
mysqld
stunnel server
proxy
stunnel server
proxy
stunnel client
fw
stunnel client
fw
(www)
(www)
50
Considerations
  • Writes go to only one master at a time
    ipfw/iptables configuration ensures that
  • stunnel/proxy needs a failover backup you
    should run at least two of them
  • Run on master and one slave?
  • Entire setup should come up read-only at boot
  • Both masters should use skip-replication, and
    read-only
  • SET GLOBAL read_only01

51
More Considerations
  • Extra logic needed in application
  • Handle Server is read only response on write
  • Handle Connection refused on connect

52
Failover Steps
53
Planned Failover Steps
  • Set current master read-only
  • Wait for writes to flush through
  • Remove forward rules with old settings
  • Add forwarding rules with new settings
  • Remove read-only from new master

54
Master Failure (Usable Data)
  • Same as for Planned Failover
  • Run STOP SLAVE on new master
  • If data is deemed OK, replication can catch up
    master when it reboots
  • If data is not OK, will require a rebuild

55
Master Failure (No Data)
  • Recover from a slave paired against the remote
    master
  • Set active master read-only
  • Wait for writes to flush through
  • Resume two-way replication
  • Turn off read-only on active master

56
Partial Datacenter Loss
  • Depending on what parts have failed, most of the
    same steps as Planned Failover are OK
  • If network trouble, many cases can be treated as
    complete datacenter loss

57
Complete Datacenter Loss
  • Cant be generalized
  • Case-by-case basis
  • You may be able to call the datacenter techs and
    have them reboot any machine use this to gain
    control over their (unknown) state

58
Further Information
59
Sessions
  • Tue 2pm MySQL Replication New Features and
    Enhancements
  • Wed 11am Use Cases for Replication and Cluster
    in MySQL
  • Wed 7pm BoF Replication

60
Books
  • MySQL - New Riders / DuBois
  • High Performance MySQL - OReilly / Zaw.,Balling
  • Pro MySQL - Apress / Kruckenberg,Pipes

61
General Discussion
  • Got any questions for me?
About PowerShow.com