Refresh local db bash script

Before my new job, I was used to having a script that would automatically pull a fresh copy of the production database at my command using AWS. With the current setup at work, it’s more of a manual deal which took up a lot of time.

Using my previous knowledge of running mysqldump command line, I decided to create my very own bash script that will accept 3 arguments and will dump and replace your local database with the fresh data.

To get this to work, you’ll need to do a few things:

  • Create a DatabaseBackups folder under your Documents. The script can also be updated to point to wherever you need it to.
  • We use Laravel Forge for our server so the username is always the same. You can update the params if needed. Also, we use SSH keys to access the server.
  • NOTE: The only issue with my script is if you have a database password, the script will ask and will be displayed in plain text. I haven’t quite been able to figure this part out yet.

So here are the params:

  • Database name on the server.
  • Local database to replace/update/create.
  • IP of the server to use when SSH’ing. If this is not provided, it will use the last saved version based on the production database name.
#!/bin/bash

# Params
PROD_DB=$1
LOCAL_DB=$2
PROD_IP=${3:-''}

SQLPATH="/Users/$USER/Documents/DatabaseBackups/$(date +'%Y%m%d')_"$PROD_DB.sql;
if [ -z "$PROD_IP" ]; then
  echo "Connecting to server and cloning...";
  echo "Saving to $SQLPATH";
  ssh forge@$PROD_IP mysqldump -uforge -p --no-tablespaces --no-create-db $PROD_DB > $SQLPATH;
  echo "Clone completed.";
else
  echo "Skipping reclone. Pulling most recent file that contains "$PROD_DB;

  SQLPATH=$(find /Users/$USER/Documents/DatabaseBackups -name "*"$PROD_DB".sql*" -print0 | xargs -r -0 ls -1 -t | head -1)
fi

echo "Dropping $LOCAL_DB";
mysql -u root -D $LOCAL_DB -e "DROP DATABASE $LOCAL_DB";
echo "Database dropped. Recreating..";

mysql -u root -e "CREATE DATABASE $LOCAL_DB";
echo "Database created. Pulling data now.";
mysql -u root $LOCAL_DB < $SQLPATH;
echo "Done! Have a great day~!";

So to use it…. I have it saved under a scripts folder.

bash ~/scripts/mysqlpull.sh cms_staging rw_cms <ip of db server>

It’s my first script and I’m quite proud of it considering I had no prior knowledge before.

Enjoy!