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