MySQL DBA – Essential Concepts to Learn

Well, first off, if you are upgrading from 5.0 to 5.5, you really need to do this on a test server prior to making the upgrade in a production environment. This will get you comfortable with the steps of upgrading and let you figure out any trouble areas beforehand, which will dramatically reduce your downtime of upgrading.

So to upgrade, I would upgrade from 5.0 to 5.1, then 5.1 to 5.5 as the manual suggests. There will be downtime, but if you do the upgrade steps on a test setup first, you will reduce your downtime dramatically. Make sure you have all the installers downloaded beforehand, don't wait to download them while you're in downtime.

You could do the replication setup as Rolando points out, but please don't do the upgrade on production without testing out the steps first.

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

• MetaData Issues : I wrote about this as well
  • Why does InnoDB store all databases in one file? (See WARNING)
  • How to Recover an InnoDB table whose files were moved around
  • Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
• Performance Issues : I wrote about that back in August 2011
  • MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

• Step 01) mysqldump all databases
• Step 02) shutdown mysql
• Step 03) delete ibdata1
• Step 04) startup mysql
• Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.