Re: Database Upgrade with no disruption

[Rick James]

> am I worrying about nothing?

Your concerns are reasonable.

Minor upgrades (5.1.xx to 5.1.yy) can be done in-place with only seconds (or minutes) of downtime.

A major upgrade should be done more like what you describe.

If you already have Master-Slave (or dual-master) set up: Stop the slave; upgrade it to 5.5; start it and let replication catchup; stop writes to the master; again catchup; redirect writes to Slave, which is now the master. There is a short outage for the last couple of steps.

If you don't have a Slave with data, the first step is to get such...

Percona's XtraBackup can take a dump and set up a slave with virtually no downtime (if all InnoDB). But, alas, you are running on Windows.

LVM (or other) snapshotting technology can also do such. Again, alas.

> Now I have to get the rest of the old data

That concept works only for "log"-like data. That is, data where you can easily predict 'old' vs 'new'. It does not work well for UPDATEs, DELETEs, and INSERTs into the 'middle' of 'old' data. It _may_ be possible to invent some ad hoc way to achieve it, but it is rather risky.

Percona's 'online alter' is almost an all-purpose solution; it uses TRIGGERs to keep track of what has come in since you started the table copy. It would take some adapting to make it usable for a dump.

Why do you "have to" upgrade to 5.5? (I agree that it is a good idea.)

5.1.29 is rather old; you could upgrade to the latest 5.1, and actually get _some_ of the fixes/features of 5.5. Such, as I say, can be done "in place".

To everyone out there listening: Don't get caught in Gary's situation; plan ahead. At a minimum, have Master+Slave set up _before_ you have so much data that you can't afford the downtime required to set up the first Slave.