On Mon, 2004-01-19 at 17:27, Jeremy Zawodny wrote: > On Mon, Jan 19, 2004 at 03:05:34PM +0100, Jan Kokoska wrote: > > On Mon, 2004-01-19 at 12:30, Francis Tyers wrote: > > > hmm, you might want to look into mysql replication, i just googled and > > > got: > > > > > > http://www.mysql.com/doc/en/Replication.html > > > http://jeremy.zawodny.com/mysql/managing-mysql-replication.html > > > > > if you are on a lower budget, perhaps look at rsync ... > > > > > > http://samba.anu.edu.au/rsync/ > > > > We have this setup running here on a production server with 500+ web > > hosts, fs replicated using rsync (homebrown replication scripts in > > python, can be done in bash or just anything) and MySQL replication as > > described in the documentation (also managed through scripts). > > > > We check the replication using Netsaint and both systems are an exact > > copy, at worst of 5 minutes ago. We stopped at the point of implementing > > some sort of STONITH, so no ip/service takeover yet.. considered how > > MySQL replication is crippled and unreliable (IMHO), this should not be > > done automagically anyway (or you have to provide really extensive > > workarounds and integrity checking). > > Where does the "crippled and unreliable" opinion come from? I'm not > sure if you're talking about replication itself or the failover > options. >
Both. Some commands aren't written to binary logs as of mysql 3.23 (Debian stable), one example that has bogged me for some time is altering user privileges directly and relying on replication of "FLUSH PRIVILEGES" to slaves - it won't happen. This might be fixed in 4.x version or in 5.x version, nevertheless it's not in the stable distro in Debian. In docs I can read there are more cases like this, significant number of commands doesn't get replicated. Some of it is definitely fixed in 4.x, but not all. That concerned the replication itself, now for failover. Supposed the master fails and slave becomes new master.. new data get written, but what happens when master comes back? It would be all ok if I just accept the situation and have the old master fall back into slave mode, but if I was to reverse the situation on-the-fly, during constant db updates, I'm inevitably going to fail. We have tested this extensively here, written python scripts to manage the replication but some of the time it is not even clear what state are the mysql daemons in ;/ Locking tables? No problem, but I want the updates which cannot happen during the lock to be moved to the other server (new master) too and first applied there, as otherwise some other update could meanwhile bump in. I have read your webpage, I think I used mysqlsnapshot, I definitely do use mytop, I have even seen your slides on replication ;) but I have no idea how do you run dual-master or replication ring setups... this master-slave switch on-the-fly is IMHO a special case of dual-master (at some point you may have updating sessions open to both servers simultaneously). So if you could provide more insight on how to do things like these *reliably* (the replication setup I'm talking about concerns about 500 client databases running on dual Xeon machines) it would be most welcome. If you promise it *will* be in your upcoming book (High Performance MySQL) we would even buy it here (when will it be available?), no problem, but to opensource db engine I somehow expected free documentation. Yes, someone would have to write it, no, I didn't ;/ -- Jan Kokoska
