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.


much more information in my textbook, return as needed


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


Single Master, Multi Slave

  |       |       |

slave   slave   slave
  • distribute reads
  • host closer to users

Dual Master Replication


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).

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.

      |             |
   slave           slave
    /  \           /   \
slave  slave    slave  slave