Jenkins database sync from production to staging/qa

gistfile1.txt

## On production slave (out of rotation)

# Create our database dump
mysqldump ${DATABASE} > ${DATABASE}-${BUILD_ID}.sql
gzip -6 ${DATABASE}-${BUILD_ID}.sql

# Transfer it
scp ${DATABASE}-${BUILD_ID}.sql.gz jenkins@__STAGING__.__DOMAIN__.com:/tmp/${DATABASE}-${BUILD_ID}.sql.gz

# Clean up
rm -rf ${DATABASE}-${BUILD_ID}.sql.gz

## On staging server / databse where dump was transfered

# Switch to maintenance mode
touch /www/site/www/maintenance.flag

gunzip ${DATABASE}-${BUILD_ID}.sql.gz

# Drop existing database and import dump
mysql -u root -h localhost -e "DROP DATABASE IF EXISTS ${DATABASE}; CREATE DATABASE ${DATABASE}"
mysql -u root -h localhost ${DATABASE} < ${DATABASE}-${BUILD_ID}.sql

# Update database configuration for staging

mysql -u root -h localhost ${DATABASE} <<QUERY_INPUT
    UPDATE `configuration` SET foo='bar' WHERE `id` = 1 LIMIT 1;
QUERY_INPUT

# Flush cache
echo 'flush_all' | nc 127.0.0.1 11211

# Clean up
rm -f ${DATABASE}-${BUILD_ID}.sql

# Switch back from maintenance mode
rm -f /www/site/www/maintenance.flag

# Retrieve instance as a warm-up
wget http://__STAGING__.__DOMAIN__.com