Migrating Bacula database to Innodb: a failed attempt

On Tuesday I attempted migrating the Bacula database at work from MyISAM to InnoDB. In the process, I was also hoping to get the disk space down on the /var partition where the tables resided; I was runnig out of room. My basic plan was:

• Shut down Bacula
• Shut down MySQL
• Upgrade to version 5.5.27 (Community RPMs from Oracle)
• Restart MySQL
• Reload the Bacula database from the last catalog backup

Here was the shell script I used to split the dump file, change the engine, and reload the tables:

csplit -ftable /net/conodonta-private/export/bulk/bacula/bacula.sql '/DROP TABLE/' {*}
sed -i  's/ENGINE=MyISAM/ENGINE=InnoDB/' table*
for i in table* ; do  mv $i $(head -1 $i | awk '{print $NF}' | tr -d '`' | sed -e's/;/.sql/') ; done
for i in $(du *sql | sort -n | awk '{print $NF}') ; do echo $i; mysql -u bacula -ppassword bacula < $i ; done

(This actually took a while to settle on, and I should have done this part of things well in advance.)

Out of 31 tables, all but three were trivially small; the big ones are Path, Filename and File. File in turn is huge compared with the others.

I had neglected to turn off binary logging, so the partition kept filling up with binary logs...which took me more than a few runs through to figure out. Eventually, late in the day, I switched the engines back to MyISAM and reloaded. That brought disk space down to 64% (from 85%). This was okay, but it was a stressful day and one that I'd brought on myself for now preparing well.

When next I do this, I will follow this sequence:

• Add the separate-file-per-table option for InnoDB!
• Run that csplit/sed/sort script again, but leave the three big tables
• Run each of the three tables one at a time, to allow for daily backups to continue