I have a Raspberry Pi 3 which serves solely as a MySQL server. It does have an important (production-esque) purpose but despite that… I didn’t have it backed up properly. Oops.
So when it spectacularly died the other day, and, um… production things… stopped productioning… I worried a little. Well after I changed my pants, I worried a lot.
Not so much about the server, or even in fact the data, but the structure of the databases.
Fortunately, after a bit of trial and error, rather than waste days trying to recover the Pi, I was able to rebuild the Raspberry Pi, recover the MySQL data and then restore this to a new Pi. And we’re back in the game.
This post is in two parts:
Part 1 (this one): Rebuild the Raspberry Pi
Part 2: Recover the MySQL data and restore to the new environment.
So this is relatively straight-forward. Fortunately, I’d documented the key parts of this process not too long ago so now was a good time to run through that and verify (and to some extent, update it.) Obviously, in this case, I really only care about the MySQL bits and getting phpmyadmin working.
If you’re doing this, then commands you need to type
will look like this.
- Set the Pi up on a screen with keyboard, boot it, login using default credentials (user pi, pass: raspberry)
sudo raspi-config. From here: enable SSH, assign a hostname (if you like) and also change the root password
- Login again.
ifconfig. In the first section (eth0) make a note of your IP address. Something like 192.168.1.2. Note: it’s a good idea to set a static IP address for the next bit, but I couldn’t for the life of me get this working using the ‘new’ Raspbian way using dhcpcd.conf. (I didn’t want to do the old /etc/network/interfaces route, even though I know this works.) So instead I have just configured my router to ensure the Pi gets the same IP address every time, but you may like to set a static IP if you can’t do that.
- On your Mac, open up terminal and type
sudo nano /etc/hosts. Enter your Mac password. At the bottom of the file create a new line with the ip address a space and the hostname you’d like to use for your Pi. (Possibly what you set it to earlier.)
192.168.1.2 mypi. This is just so we don’t have to reference the Pi by IP every time.
- Now time to start installing some stuff. Let’s start with the easy stuff.
- In Terminal,
ssh pi@mypiand enter your password. Type ‘yes’ when prompted.
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install php. Update: PHP5 now appears to be obsolete, so this will install PHP7. But the good news is that mysql-client now installs with no issues.
sudo apt-get install mysql-server --fix-missing
sudo apt-get install mysql-client
- Apache/PHP/MySQL should now be installed. The first thing to do is sort out a few security issues.
sudo mysql_secure_installationwill open a graphical wizard and step you through basic things, such as setting a root password and so on. Be aware that if you follow its advice and remote non-localhost login for the root user then you will need to add a new user which can login from non-localhost in order to be able to use phpmyadmin (etc.) from non-Pi places.
- You can do this with:
GRANT ALL PRIVILEGES ON *.* To 'user'@'%' IDENTIFIED BY 'password';. Note that the % indicates a hostname wildcard which may not be desirable in your situation. This gives your ‘user’ account access to everything, so there’s little difference to leaving it as root, but hey ho – you can always scale back its permissions once you have things working.
sudo apt-get install phpmyadmin. This will install phpmyadmin. At the end a graphical wizard should step you through the configuration. You should now be able to get in to phpmyadmin at http://mypi.local/phpmyadmin using the user created above.
This should be all the setup you need to do. Onwards to Part 2 – Recovering the MySQL data!