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?