Jump to: navigation, search

Difference between revisions of "Reddwarf-MySQL-Replication-and-Clustering"

Line 41: Line 41:
 
| Works with all storage engines (see notes on MyISAM) || Single threaded, high concurrency on the master can cause issues where slaves can't keep up
 
| Works with all storage engines (see notes on MyISAM) || Single threaded, high concurrency on the master can cause issues where slaves can't keep up
 
|-
 
|-
| Offers simple and complex configurations for read scaling || Example
+
| Offers simple and complex configurations for read scaling ||  
 
|-
 
|-
| Opensource tools available for monitoring/troubleshooting || Example
+
| Opensource tools available for monitoring/troubleshooting ||  
 
|-
 
|-
| Great for making backup or reporting slaves || Example
+
| Great for making backup or reporting slaves ||  
 
|-
 
|-
| Good general purpose replication ||
+
| Good general purpose replication ||  
 
|}
 
|}

Revision as of 14:33, 2 May 2013

MySQL Master/Slave Replication

How master-slave replication works:

  • Replication in mysql works basically via log shipping.
  • The mysql master logs all data manipulation events in the mysql binary log.
  • While this is happening the slave instances are connected to the master via TCP and receive the same events and write them to their relay log.
  • Binary logging should be turned *on* on the master and *off* on the slaves.
  • If binary logging is turned on on the slave you will suffer massive IO hits as each log entry writes to the relay log, then the table, then the binary log.
  • there are 2 internal mysql threads that exist on the slave, the SQL thread and the IO thread
  • the IO thread is responsible for getting binary events from the master and the SQL thread is responsible for applying them locally.
  • slaves should be read-only, allowing data manipulation statements on slaves will result in corrupt or out of sync slaves


How to know that replication broke:

  • parse the output of "SHOW SLAVE STATUS" on the replicated slave.
  • the IO thread is set to No
  • the SQL thread is set to No
  • the seconds_behind_master is > a set number and isn't reducing over time (this is a conditional case that we need to define).
  • This case is a failure because it means that for whatever reason the slave will never be in sync with the master.
  • This can be due to the slave not having enough resources to keep up or that the master has too many slaves.
  • If ever any of these 3 things are true it is assumed that replication is broken and the slave is to be destroyed and recreated.


Steps to setup replicaiton:


Pros/Cons

Pros Cons
Standard MySQL Prior to MySQL 5.6 Replication is brittle (See notes on MyISAM)
Very well understood Repairing can be time consuming
No special architecture needed Creating new slaves can require downtime or read locking on the master
Easy to troubleshoot Only works well for scaling reads
Works with all storage engines (see notes on MyISAM) Single threaded, high concurrency on the master can cause issues where slaves can't keep up
Offers simple and complex configurations for read scaling
Opensource tools available for monitoring/troubleshooting
Great for making backup or reporting slaves
Good general purpose replication