Archive for April, 2013

Recovering InnoDB Tables In MySQL 5

Saturday, April 27th, 2013

The following procedure can be used to recover InnoDB database tables from a backup of a MySQL server that had the “innodb_file_per_table” setting but all the metadata (in the “ibdata*” files) was lost – for example due to a server crash.

The process involves two steps:

  1. Recover the table structure from the .frm files
  2. Recover the data from the .ibd files (InnoDB tablespace)

There is a lot of copying the backed up files over and over to the MySQL datadir, so its useful to have the backup available on the database server machine. In my setup the backup for the databases was copied to the directory “backup” under the database’s datadir, so – for example – for the table somedb.sometable there exist files somedb/backup/sometable.frm and somedb/backup/sometable.ibd.

Additionally the process for recovering the table structures creates a lot of superfluous metadata in the InnoDB data files, so after the first stage I’m going to destroy the InnoDB data files and let the InnoDB engine re-generate them – as a result any existing InnoDB tables will be destroyed. This is important so I’ll iterate: using the procedure detailed here will destroy any existing and working InnoDB databases! So this procedure is useful to recover a destroyed database server to a new server or as a temporary measure on a temporary server to be able to dump the data to SQL files that will later be loaded into an existing server.

There likely a way to do this which is less heavy handed – for example, check out this article from Percona’s MySQL blog, but for my purpose this is enough.


How to fix “No bootable device” after Ubuntu installation

Monday, April 22nd, 2013

When installing Ubuntu on a disk partitioned with a GUID partition table (GPT), the Ubuntu installer doesn’t set the boot flag correctly on the partitions, and on computers with older bioses (like my Intel ICH9 board), the BIOS will not recognize the disk as bootable and will refuse to start the boot loader, claiming that “No bootable device found”.

The solution is to set the boot flag on the boot partition (the real one in the GPT) as well as on the “protective MBR partition”:

  1. Start your computer with the Ubuntu live CD again, and select “try Ubuntu before installing”
  2. When the Ubuntu desktop loads, open the dash and start “gparted”
  3. When gparted starts, select your boot drive and from that select your boot partition – usually the first Linux partition on the drive, and right click it and select “flags” in the menu. In the flags dialog check “boot”.
  4. Open the dash again and start “terminal”
  5. In the terminal type “sudo fdisk /dev/sda” (or whatever your boot drive is). You’d get a bunch of warning about the disk using a GPT scheme – ignore them. type “a” to set the bootable flag and choose partition “1”. Finally type “w” to write out your changes.

Now its safe to reboot and your old computer should have no problem starting the boot loader now.

Spam prevention powered by Akismet