>Date: Mon, 17 Nov 2008 10:16:16 +0000
>From: "Tompkins Neil" <neil.tompkins@stripped>
>Subject: Using Replication in mySQL version 5
>We are looking to upgrade our version of mySQL to the latest version of
>mySQL 5. One of the main features we are going to think about using is
>replication for our website data. Basically we have 2 websites ...
hm, I can't tell you much yet, my first few experiences are:
- it works. :)
- be aware of auto_increment values, until now I found two possible
sources of error relating to this, causing a 'duplicate entry for
1. if u insert a row on slave-server only, auto_increment happens, and
master-server is one behind, resulting in the 'duplicate entry' error on
next insert on master-server.
2. we're replicating a typo3-cms. typo3 mostly doesn't really delete
table rows, but instead sets a 'deleted' flag. so, basically the same
issue as 1.: create a typo3-record on slave only, delete it via typo3 on
slave only, but row is still occupied, next insert on master will fail.
topics I've still got to look into for our setup:
- harddisc space requirements of master server's binary log. i think
it's about the same as the databases which are being logged, plus some
overhead for the sql statements. one might want to keep this in mind if
you expect your database to grow large.
- performance and bandwidth. u can restrict replication to individual
tables (on slave side - options 'replicate-do-table', etc.), so at least
in our setup it'll be just a fraction of what the webserver/mysql has to
deliver to the outside world (internet).
- master-master replication, both server write to the same table,
replication in both directions. currently this seems to work fine, but
we haven't done any real-life-load-tests yet.