Recovering files from broken Raspberry Pi - Part 2

Tue, Dec 5, 2017 4-minute read

This is the second part of a guide on recovering MySQL data from a broken Raspberry Pi.

Part 1: Rebuild the Raspberry Pi
Part 2: Recover the MySQL data and restore to the new environment.

With a newly functional Raspberry Pi, it was now time to try and recover MySQL data. Clearly this will only work if the SD card in the old Pi itself is actually functional and readable.

The good news is that MySQL stores its data on the file system in a reasonably compact and accessible format. There are a few differences in structure based on whether your database tables are in MyISAM or InnoDB format. This post assumes your tables are in InnoDB format (since that’s what mine were) but as far as I know, the only difference is that if your tables are in MyISAM format, then there’s no need to copy the ibdata* files over.

In order to do this, you’ll need:

  1. Access to a functional Linux environment. You could use your new Pi, but I opted for an Ubuntu VM.
  2. The old SD card and a card reader
  3. A FAT32 USB stick or similar means to get the files off your old card and on to the new SD card (assuming you’re not using your Pi for this.)

As I write this, I realise using the new Pi environment would probably have been the better route as it would save some jimmying around. The benefit with the Ubuntu route was it saved some effort in terms of mounting volumes and being able to use a GUI for copying.

But maybe I’ll do that if I have to do this again… but I’ll assume you’re following along with an Ubuntu VM but obviously ignore any unnecessary steps as appropriate!

  1. Boot your Linux environment

  2. Attach your card reader and SD card

  3. Attach your USB stick

  4. By default, the Ubuntu file explorer does not run as root so you will need to launch it as root in order to access the filesystem of your old SD card. Do this with: 

    sudo nautilus

  5. This should open a file explorer with root permissions. Find your SD card volumes in the side - it will either be ‘rootfs’ or a bunch of numbers or similar. It’s not the ‘boot’ partition.

  6. Navigate through to /var/lib/mysql. You should see some files and then folders for all your databases. In my case, I only wanted one specific database. Copy that whole folder to your USB stick.

  7. Bear in mind that if you use stored procedures in your MySQL database, then the definitions of these are actually stored in mysql.proc. You can either just copy the whole mysql database folder, or just copy the proc* files

  8. If your database is InnoDB, also copy the ibdata1, ib_logfile0 and ib_logfile1 files to your USB stick. If you don’t copy these files, then you will likely see an error message when accessing MySQL such as ‘table database.table does not exist in the engine'

  9. Unmount / eject your SD card

  10. Insert / mount the new SD card

  11. You essentially now repeat the above steps… but in this case, you copy the database folder(s) and ibdata* files to /var/lib/mysql to the SD card

  12. Unmount / eject your SD card

  13. Put the SD card in the Pi and boot

  14. Important: at this point, the mysql daemon probably won’t start. This is fine.

  15. Either login or SSH in to the Pi. Issue the command: 

    sudo chown mysql:mysql /var/lib/mysql/ -R

  16. Either restart mysql: 

    sudo /etc/init.d/mysql restart

or if you're feeling lazy just reboot the Pi: 

<pre>sudo reboot</pre>

And this *should* be everything you need to do. For my purposes this was it. Your mileage may vary, especially if you had more than InnoDB database stored on the old MySQL server.

Update: be sure to check out this - allow connections from non-localhost and this - MySQL table name case sensitivity in case you experience any issues.

Posts in this Series