Script to backup Magento DB and Update Preprod Env from this

backup-prod-and-update-preprod.sh

#!/bin/bash
NOW=$(date +"%d-%m-%Y-%H-%M-%S")
SQL_DUMP_FILE="backupProd.$NOW.sql"
SQL_PREPROD_FILE="preprod.sql"
NEW_BACKUP_FILE="lastBackup.gz"
OLD_BACKUP_FILE="previousBackup.gz"
SQL_USER="--MYSQL_USER--"
SQL_PASSWORD="--MYSQL_PASSWORD--"
SQL_SOURCE_DB_NAME="--MYSQL_SOURCE_DB_NAME--"
SQL_TARGET_DB_NAME="--MYSQL_TARGET_DB_NAME--"
SQL_TABLE_PREFIX=""
STORE_IDS=(1 2 3)
ANALYTICS_PREPROD_ACOUNTS=("" "")
PROD_DOMAINS=("www.mysite.com" "www.mysecondsite.com")
PREPROD_DOMAINS=("preprod.mysite.com" "preprod.mysecondsite.com")
GSUB_REPLACE_STRING=""
BACKUP_PATH="/root/backups/"

echo -e "\033[1;33;40mSTEP #1 // BACKUP \033[0m"

echo "- Dumping DB to create $BACKUP_PATH$SQL_DUMP_FILE file, please wait..."
mysqldump -u $SQL_USER -p$SQL_PASSWORD $SQL_SOURCE_DB_NAME>$BACKUP_PATH$SQL_DUMP_FILE

chmod 755 $BACKUP_PATH$SQL_DUMP_FILE

DEST=`readlink $BACKUP_PATH$OLD_BACKUP_FILE`
echo "Delete previous backup ($OLD_BACKUP_FILE -> $DEST)"
touch $BACKUP_PATH$OLD_BACKUP_FILE;
rm $BACKUP_PATH$OLD_BACKUP_FILE;
rm $DEST;

echo "Archive last backup ($NEW_BACKUP_FILE to $OLD_BACKUP_FILE)"
touch $BACKUP_PATH$NEW_BACKUP_FILE
mv $BACKUP_PATH$NEW_BACKUP_FILE $BACKUP_PATH$OLD_BACKUP_FILE

echo "- Zipping current dump $BACKUP_PATH$SQL_DUMP_FILE to $BACKUP_PATH$SQL_DUMP_FILE.gz"
gzip -fc -9 $BACKUP_PATH$SQL_DUMP_FILE > $BACKUP_PATH$SQL_DUMP_FILE.gz

echo "- create symbolic link $BACKUP_PATH$NEW_BACKUP_FILE > $BACKUP_PATH$SQL_DUMP_FILE.gz"
ln -s $BACKUP_PATH$SQL_DUMP_FILE.gz $BACKUP_PATH$NEW_BACKUP_FILE

echo -e "\033[1;33;40mSTEP #2 // UPDATE PREPROD \033[0m"

echo "- replace prod urls by preprod ones"
echo "$BACKUP_PATH$SQL_DUMP_FILE > $BACKUP_PATH$SQL_PREPROD_FILE"
for i in `seq 1 ${#PROD_DOMAINS[@]}`
do
GSUB_REPLACE_STRING="gsub(\"${PROD_DOMAINS[i-1]}\",\"${PREPROD_DOMAINS[i-1]}\");$GSUB_REPLACE_STRING"
done
echo "{${GSUB_REPLACE_STRING}print}"
awk "{${GSUB_REPLACE_STRING}print}" $BACKUP_PATH$SQL_DUMP_FILE > $BACKUP_PATH$SQL_PREPROD_FILE

echo "-import all tables to preprod db"
mysql -u $SQL_USER -p$SQL_PASSWORD $SQL_TARGET_DB_NAME < $BACKUP_PATH$SQL_PREPROD_FILE

echo "-sql safe updates off"
mysql -u $SQL_USER -p$SQL_PASSWORD -e 'SET SQL_SAFE_UPDATES = 0;'

echo "-sql do not send emails in preprod"
mysql -u $SQL_USER -p$SQL_PASSWORD $SQL_TARGET_DB_NAME -e "UPDATE ${SQL_TABLE_PREFIX}core_config_data SET value='1' WHERE path = 'system/smtp/disable';"

echo "-use google Analytics preprod Account"
for i in `seq 1 ${#STORE_IDS[@]}`
do
echo -e "\033[3;37mSET value='${ANALYTICS_PREPROD_ACOUNTS[i-1]}' for store_id = ${STORE_IDS[i-1]}\033[0m"
mysql -u $SQL_USER -p$SQL_PASSWORD $SQL_TARGET_DB_NAME -e "UPDATE ${SQL_TABLE_PREFIX}core_config_data SET value='${ANALYTICS_PREPROD_ACOUNTS[i-1]}' WHERE path = 'google/analytics/account' AND scope_id = '${STORE_IDS[i-1]}';"
done

echo "-sql safe updates on"
mysql -u $SQL_USER -p$SQL_PASSWORD -e 'SET SQL_SAFE_UPDATES = 1;'

echo -e "\033[1;33;40mSTEP #3 // CLEAN UP\033[0m"

echo -e "- remove \033[3m$BACKUP_PATH$SQL_DUMP_FILE\033[0m"
rm $BACKUP_PATH$SQL_DUMP_FILE

echo -e "- remove \033[3m$BACKUP_PATH$SQL_PREPROD_FILE\033[0m"
rm $BACKUP_PATH$SQL_PREPROD_FILE

echo -e "\033[1;32;40mall done :)\033[0m"