The Blog of Someone Who Builds Things on the Internet

I google how to do things, then post the answers here cause I'll probably forget. Maybe someone else finds it useful.

Script to copy MySQL db from production server to dev machine

Published February 24, 2017

I like to use a copy of the production data when I'm testing out new features I'm building on my development version of the site. I just find that having real data leads to less unexpected issues. Here is a script I wrote to download that data from the production machine and copy it to my development machine. It also backups a copy of the database on production and keeps a copy of the db you're replacing on dev.

#! /bin/bash
d=$(date +%Y%m%d%H%M%S)
ssh "mysqldump -u root -p'1234' database | gzip -c | cat > ~/db_backups/database_$d.sql.gz"
echo "production/database backed up"
scp$d.sql.gz .
ssh "mysqldump -u root -p'1234' database > ~/database_last_overwrite.sql"
echo "dev/database was backed up to ~/database_last_overwrite.sql"
gzip -d database_$d.sql.gz
cat database_$d.sql | ssh "mysql -u root -p'1234' database"
rm database_$d.sql
echo "copied production/database to"

Both servers are running ubuntu. Executing from a macOS machine.