Holy God, It's Done At Last

After a lot of faffing about, I've accomplished the following on the backup server at $WORK:

• Broken out /var/lib/mysql to a separate, mirrored, Linux software raid-1 275 GB partition; it's using about 36 GB of that at the moment, which is 15% -- the lowest it's been in a long, LONG-ass time.

• Migrated the Bacula catalog db to Innodb.

• Shrunk the raid-0 spool partition to about 1.6 TB, down from 2 TB; did this to free up the two disks for the mirrored partition

• Ensured that MySQL will use /dev/shm as a temporary area

• Sped up the restoration of files (which was mostly because of earlier "analyze" commands on the File table while it was still MyISAM)

• innodbfilepertable is on; innodbbufferpoolsize=10G; ``` defaultstorageengine=InnoDB

I encountered the following problems:

   * The stupid raid card in the backup server only supports two RAID drives --

thus, the mirrored drive for /var/lib/mysql is Linux software raid. I'd have preferred to keep things consistent, but it was not to be. ```

• The many "analyze" and "repair" steps took HOURS...only to turn out to be deadlocked because it was running out of tmp space.

• I had to copy the mysql files to the raid-0 drive to have enough space to do the conversion.

• Knock-on effects included lack of sleep and backups not being run last night

• Basically, this took a lot of tries to get right, and about all ``` of my time for the last 24 hours.

I learned:

   * The repair of the File table while MyISAM, with tmp files in

/dev/shm, took about 15 minutes. That compares with leaving it overnight and still not having it done. ```

• You have to watch the mysql log file for errors about disk space, and/or watch df -h to see /tmp or whatever fill up.

• You can interrupt a repair and go back to it afterward if you have to. At least, I was able to...I wouldn't do it on a regular basis, but it gives me cautious optimism that it's not an automatic ticket to backups.

• Importing the File.sql file (nominally 18 GB but du shows 5 ``` GB...sparse?), which converted it to InnoDB, took 2.5 hours.

I still have to do these things:

   * Update documentation.
   * Update Bacula checks to include /var/lib/mysql.
   * Perhaps up pool_size to 20 GB.
   * Set up a slave server again.
   * A better way of doing this might've been to set up LVM on md0, then use snapshots for database backup.
   * Test with a reboot!  Right now I need to get some sleep.