Has anyone here played with database replication using MySQL on
Debian/Ubuntu?
What I have is a database server running a few dozen databases, of which
a handful I need to replicate at the customer's premises. That is, for
sake of argument, 20 databases on the server, 5 of which need
individually replicating to 5 customer offices.
Last time I looked at this there were two options, described below for
those who care. My problem is that both require me to run multiple
database instances and every time I've tried to make MySQL do that from
a Debian/Ubuntu binary installation I've failed.
Does anyone here have any experience in that area? (I had no joy at all
from the MySQL forums last time I went through this; the Debian builds
seemed of little interest to MySQL devs.)
----
Option 1. Tell MySQL to create a binary log, with logging enabled on the
5 databases. Run 5 additional database instances using the blackhole
database engine, which replicate one database each from the master
binary log and use it to create their own logs. Set up MySQL at each
customer site to replicate from their respective blackhole database
instance.
[Background: MySQL can (or could?) only log to a single binary file, so
this messing around is required to split the logs out; each blackhole
database instance receives the data for all 5 logged databases but
ignores everything except that for its own database, which it then logs.
The customer's server replicates against that. Otherwise the customer's
server has to pull the data for all 5 databases to ignore 4 of them, a
bandwidth hog and (more importantly) security flaw. The blackhole
database engine simply dumps all data to /dev/null (ie it stores
nothing) but in the process maintains the logs correctly.]
Option 2. Remove the 5 databases in question from the main MySQL
instance, and create new 5 MySQL instances (one for each database that
needs logging). Let each separately create binary logs; client databases
replicate from them as above.
In my opinion both options are poor fixes for what ought to be a trivial
change to the master to make it log to separate databases, but my
opinions don't often count for much :-)
--
Mark Rogers // More Solutions Ltd (Peterborough Office) // 0845 45 89 555
Registered in England (0456 0902) at 13 Clarke Rd, Milton Keynes, MK1 1LG