Keep MySQL Databases During Laravel Homestead Upgrade

This question has been documented about a million places around the internet with a variety of convoluted answers. How can we upgrade Laravel Homestead without losing our MySQL databases?

Instead of writing your own mysqldump commands, or copying and pasting one from an internet stranger, there’s actually functionality for this built right into Homestead. If you look in Homestead’s aliases file, you’ll see import and export functions:

function dbexport() {
    FILE=${1:-/vagrant/mysqldump.sql.gz}

    # This gives an estimate of the size of the SQL file
    # It appears that 80% is a good approximation of
    # the ratio of estimated size to actual size
    SIZE_QUERY="select ceil(sum(data_length) * 0.8) as size from information_schema.TABLES"

    __pv_install_message "Want to see export progress?"

    echo "Exporting databases to '$FILE'"

    if __has_pv; then
        ADJUSTED_SIZE=$(mysql --vertical -uhomestead -psecret -e "$SIZE_QUERY" 2>/dev/null | grep 'size' | awk '{print $2}')
        HUMAN_READABLE_SIZE=$(numfmt --to=iec-i --suffix=B --format="%.3f" $ADJUSTED_SIZE)

        echo "Estimated uncompressed size: $HUMAN_READABLE_SIZE"
        mysqldump -uhomestead -psecret --all-databases --skip-lock-tables --routines 2>/dev/null | pv  --size=$ADJUSTED_SIZE | gzip > "$FILE"
    else
        mysqldump -uhomestead -psecret --all-databases --skip-lock-tables --routines 2>/dev/null | gzip > "$FILE"
    fi

    echo "Done."
}

function dbimport() {
    FILE=${1:-/vagrant/mysqldump.sql.gz}

    __pv_install_message "Want to see import progress?"

    echo "Importing databases from '$FILE'"

    if __has_pv; then
        pv "$FILE" --progress --eta | zcat | mysql -uhomestead -psecret 2>/dev/null
    else
        cat "$FILE" | zcat | mysql -uhomestead -psecret 2>/dev/null
    fi

    echo "Done."
}Code language: PHP (php)

These will be the most reliable way to export, and subsequently import, all your MySQL databases when upgrading Homestead.

1. SSH into your Vagrant box
2. Run dbexport. Note this ends up in your Homestead directory
3. Follow the Laravel Homestead update instructions
4. SSH into your Vagrant box
5. Run dbimport

Done. Easy.

Why?

Treat your servers like cattle, not pets.

Joe Ferguson, Homestead maintainer

In a perfect world, we would have seeders that can get us ready for development on an application in a test environment. For many business applications, this is fairly straightforward and makes the data in your databases easy to replace.

Unfortunately, for those of us using Homestead for WordPress development, that’s much less feasible. WordPress stores tons of configuration in the database vital for getting a site up and running in a testable or development-ready state. And many WordPress customers are running mom-and-pop businesses with websites that don’t always financially justify test suites and automated seeders. And pulling data from production yet again is time consuming.

For folks maintaining many WordPress sites on a single Homestead instance, these functions are invaluable.