Foot shooting in MySQL for fun and profit, and how to fix the damage

[unixronin]

My main server has two ZFS disk pools, one consisting of a pair of mirrored 80GB boot disks, the other an array containing (currently) eleven 300GB disks.  (There were twelve, but one failed.  This is why hot spares are your friend.)  Today, I decided to move my MySQL databases from the main array onto the mirrored boot pair to get better database performance.  So I shut down the required daemons and started tarring up the databases ready to move them.

...Then, part-way through, I changed my mind about how I wanted to do it, killed the job, and killed it the wrong way, inadvertently blowing away half the /var/mysql directory.  Including the grant tables.

"Oops."

Now, fortunately, being a prudent and careful sort except for the times when I shoot myself in the foot, I have backups.  In particular, I have a full backup of all the databases, including the grant tables, current as of 0330 this morning, except for the Bacula catalog, which is current as of 0950.  But I can't use Bacula to restore them, because the Bacula catalog is blown away.

Here's how to fix a blunder like this one.

1. First, create a new ZFS filesystem on the root pool to hold the relocated databases, and give mysql ownership of it:

   # zfs create rpool/mysql
   # zfs set mountpoint=/var/mysql rpool/mysql
   # chown mysql:mysql /var/mysql
   

2. Next, we need to start mysql to reload the databases.  But how do we do that without grant tables?

   Like this:

   # mysqld --skip-grant-tables &
   

3. But we still have no grant tables.  How do we fix that?

   Well, there's some scripts in mysql/share that we can use.  But they're incomplete.  We need to add the command "use mysql;" at the beginning of each of the three scripts we're going to use.  Plus we need to actually create the new grant tables.

   # cd /opt/mysql/share
   # mysqladmin create mysql
   # mysql < mysql_system_tables.sql
   # mysql < mysql_system_tables_data.sql
   # mysql < fill_help_tables.sql
   

4. Now we have help tables, and at least default grant tables.  But these grant tables have only default rights in them.  We now need to restore the real access permissions and the databases.

   This is where things get specific to my setup.  In this case, it entailed editing a couple of Bacula .bsr files down to just the records I wanted, restarting just the Bacula storage daemon, and using bextract to manually restore the database dumps:

   # cd /opt/bacula
   # echo /var/mysql/databases.sql.gz > databases.i
   # echo /var/mysql/catalog.sql.gz > catalog.i
   # sbin/bextract -v -b babylon4.bsr -i databases.i -V INCR-20100504-03:31 /spool/bacula /root
   # sbin/bextract -v -b CatalogBackup.bsr -i catalog.i -V INCR-20100504-03:31 /spool/bacula /root
   

5. Now we need to uncompress the restored database dumps and load them back into the databases:

   # gunzip /root/var/mysql/*gz
   # mysql < /root/var/mysql/databases.sql
   # mysql < /root/var/mysql/catalog.sql
   

6. All the data is now restored.  All that's left is to restart MySQL properly (and, of course, anything dependent upon it):

   # mysqladmin shutdown
   # /etc/init.d/mysql start
   

And we're all done.  Aside from the part about the head-pounding on the wall for making such a stupid error in the first place...

For references, those database dumps were created essentially like this:

# mysqldump  -f --opt --password=xxxxxx --all-databases | gzip > /var/mysql/databases.sql.gz

This is a simplified form; if you want to dump only certain databases, you need to use --databases db1 db2 db3... instead.  That --opt is important; it saves you a lot of detail work when reloading a database from a dump file.

Comments

[tomatoe333.livejournal.com]

Does bacula have a way of doing a catalog/config backup? Every other backup system I've used has had a way of doing that, and restoring it (relatively) easily.

[unixronin.livejournal.com]

Yup, it does. It's just that, having inadvertently blown its catalog away, I couldn't use a regular restore job through the Director to restore its own catalog, which is why I had to manually restore the backed-up database dumps with bextract and the bootstrap files from last night's backups. Since the nightlies run to disk, it only took a couple minutes to restore the DB dumps, then about 15 minutes or so to load them back into MySQL.