Mysql replication

From wikinotes

Replication lets you mirror all data written to a database to one or more others.
Generally, this is used as a read-optimization.
If you'd like write-optimization, see mysql partitioning.

NOTE:

much more information in my textbook, return as needed

Overview

Replication Types

logical: every sql statement is repeated on the slave database
row:     every row is replicated to the slave database

Replication works by:

  • recording a binary log of events on the master
  • copying the master's binary log to the slave's relay log
  • the slave replays events from it's relay log

Topologies

Single Master, Multi Slave

        master
          |
  +-------+-------+
  |       |       |

slave   slave   slave
  • distribute reads
  • host closer to users

Dual Master Replication

 master
(active)
   |
   |
 master
(passive)

I believe this is the same as 'single master, multi slave'
except that you can elect the passive server as a master if required.

You may also have slaves attached to this master for redundancy, and further distribute reads.

Pyramids, Blackholes

Increasing the number of slaves associated with a specific server increases the load on that server.
You can alleviate this with a blackhole replicator, or using a pyramind of slaves.
(god this terminology is irksome).

         master
           |
           |
           |
slave (blackhole storage engine)
           |
           +
          / \
        /     \
    slave     slave


The blackhole storage engine does not persist any data.
We are only using this server to handle forwarding replication onto other slave servers.

           master
             |
      +------+------+
      |             |
   slave           slave
    /  \           /   \
slave  slave    slave  slave