Difference between revisions of "Reddwarf-MySQL-Replication-and-Clustering"
Imsplitbit (talk | contribs) (Created page with "'''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 l...") |
Imsplitbit (talk | contribs) |
||
Line 1: | Line 1: | ||
+ | == MySQL Master/Slave Replication == | ||
+ | |||
'''How master-slave replication works:''' | '''How master-slave replication works:''' | ||
* Replication in mysql works basically via log shipping. | * Replication in mysql works basically via log shipping. | ||
Line 8: | Line 10: | ||
* the IO thread is responsible for getting binary events from the master and the SQL thread is responsible for applying them locally. | * 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 | * 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:''' | '''How to know that replication broke:''' | ||
Line 18: | Line 21: | ||
* 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. | * 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:''' | + | |
− | No point in rewriting known steps, follow instructions here: [http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html MySQL Replication Documentation] | + | '''Steps to setup replicaiton:'''<br /> |
+ | * No point in rewriting known steps, follow instructions here: [http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html MySQL Replication Documentation] | ||
+ | |||
+ | |||
+ | '''Pros/Cons''' | ||
+ | {| class="wikitable" | ||
+ | |- | ||
+ | ! 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 || Example | ||
+ | |- | ||
+ | | Opensource tools available for monitoring/troubleshooting || Example | ||
+ | |- | ||
+ | | Great for making backup or reporting slaves || Example | ||
+ | |- | ||
+ | | Good general purpose replication || | ||
+ | |} |
Revision as of 21:46, 1 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:
- No point in rewriting known steps, follow instructions here: MySQL Replication Documentation
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 | Example |
Opensource tools available for monitoring/troubleshooting | Example |
Great for making backup or reporting slaves | Example |
Good general purpose replication |