Automated MySQL backups for Homestead

I guess we all ran into this problem once in a while: our Vagrant / Homestead machine freezes, gets destroyed and all data is lost. Of course we have our migrations and database seeds, but if you are like me and you use the Homestead environment also for smaller projects without migrations and seeds this can get a little annoying. Untill today I used to login to SequelPro and manually export all databases before I destroyed the machine.

While searching for a solution I came across this post by Jacob Graf, it's about dumping a full export into a file from within vagrant ssh. At least that's something, but it didn't do it just yet for me. Why? Because it still requires manually executing a command. Also, I was thinking about dumping each database individually besides having just a single file containing all backups, you know, just in case. That eventually led to the a bash script.. that still had to be run manually.

Next step: figure out how to get Vagrant/Homestead to do the back-ups when suspending and/or destroying the machine. Luckily there's a plugin called vagrant-triggers, this plugin gives you the ability to trigger events, for example before suspending. Exactly what we need! Curious how to set this up yourself? Follow these steps:

Installing Vagrant Triggers

Run the following command from your terminal:

vagrant plugin install vagrant-triggers

 

Create a folder for your back-ups

I choose to create this folder inside my Projects folder which is mapped by the Homestead.yaml file: ~/Projects/_dbdumps/, but you are ofcourse free to choose whatever folder you like. Inside this folder create a a new file create-backups.sh with the following content (view a Github Gist):

#!/bin/bash

NOW=$(date +%Y%m%d%H%M)

USER="MYSQL_USERNAME"
PASSWORD="MYSQL_PASSWORD"
FOLDER="/home/vagrant/Projects/_dbdumps/"

cd $FOLDER
mkdir -p $NOW

databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $NOW/$db.sql
    fi
done

mysqldump --user=$USER --password=$PASSWORD --all-databases > $NOW/_full.sql

chmod -R +rw $NOW

 

Setup the Vagrant hook

Last thing to do is to setup the hook in your Vagrantfile, mine was located at /Users/username/Homestead/Vagrantfile. Since it's quiet a bunch of lines, I'll link you to the Gist containing my Vagrantfile. It's line 43-46 that matter in this case: when suspending the machine, execute the bash file to back-up all databases. You can copy these lines and switch :suspend with :destroy to have your databases back-up in both cases!

 

That's it!

Make sure you correct all paths to match your own setup (Vagrantfile:45 and create-backups.sh:7).

Now enjoy your automated database back-ups and don't every worry again about losing your local data! The only downside would be the list of warnings you receive when suspending or destroying your machine. If anyone knows a fix that would be appreciated! I tried adding &>/dev/null at the end of the line, but that results in empty database dumps.

If you have any suggestions or questions, feel free to ask!

Share

Automated MySQL backups for Homestead Github Gist

Reageren?

Reageer gerust als er vragen of opmerkingen zijn, we horen niets liever dan de reacties van onze klanten en volgers om ervoor te zorgen dat we scherp blijven!