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!