Mysql replication: Difference between revisions
(Created page with "Replication lets you mirror all data written to a database to one or more others.<br> Generally, this is used for read-optimizations, but it is possible to use as a write-optimization. {{ NOTE | much more information in my textbook, return as needed }} = Overview = <blockquote> Replication Types <syntaxhighlight lang="yaml"> logical: every sql statement is repeated on the slave database row: every row is replicated to the slave database </syntaxhighlight> Replicat...") |
|||
Line 18: | Line 18: | ||
* the slave replays events from it's relay log | * the slave replays events from it's relay log | ||
</blockquote><!-- Replication Types --> | </blockquote><!-- Replication Types --> | ||
= Topologies = | |||
<blockquote> | |||
== Single Master, Multi Slave == | |||
<blockquote> | |||
<pre> | |||
master | |||
| | |||
+-------+-------+ | |||
| | | | |||
slave slave slave | |||
</pre> | |||
* distribute reads | |||
* host closer to users | |||
</blockquote><!-- Single Master, Multi Slave --> | |||
== Dual Master Replication == | |||
<blockquote> | |||
<pre> | |||
master | |||
(active) | |||
| | |||
| | |||
master | |||
(passive) | |||
</pre> | |||
I believe this is the same as 'single master, multi slave'<br> | |||
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. | |||
</blockquote><!-- Dual Master Replication --> | |||
== Pyramids, Blackholes == | |||
<blockquote> | |||
Increasing the number of slaves associated with a specific server increases the load on that server.<br> | |||
You can alleviate this with a blackhole replicator, or using a pyramind of slaves.<br> | |||
(god this terminology is irksome). | |||
<pre> | |||
master | |||
| | |||
| | |||
| | |||
slave (blackhole storage engine) | |||
| | |||
+ | |||
/ \ | |||
/ \ | |||
slave slave | |||
</pre> | |||
The blackhole storage engine does not persist any data.<br> | |||
We are only using this server to handle forwarding replication onto other slave servers. | |||
<pre> | |||
master | |||
| | |||
+------+------+ | |||
| | | |||
slave slave | |||
/ \ / \ | |||
slave slave slave slave | |||
</pre> | |||
</blockquote><!-- Pyramids, Blackholes --> | |||
</blockquote><!-- Topologies --> |
Revision as of 17:24, 24 September 2022
Replication lets you mirror all data written to a database to one or more others.
Generally, this is used for read-optimizations, but it is possible to use as a write-optimization.
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 databaseReplication 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