Script to copy MySQL db from production server to dev machine

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 user@server.com "mysqldump -u root -p'1234' database | gzip -c | cat > ~/db_backups/database_$d.sql.gz"
echo "production/database backed up"
scp user@server.com:~/db_backups/database_$d.sql.gz .
ssh user@server.dev "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 user@server.dev "mysql -u root -p'1234' database"
rm database_$d.sql
echo "copied production/database to server.dev"

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

Leave a Reply