Example script to rebuild CiviCRM DBs for upgrade. Will need customisation to suit your setup.

rebuild_dbs.sh

#!/bin/bash
 
# Remote DB server
DBHOST=dbserver
TMPCRMDB=temp
CRMDB=civicrm
WWWUSER=www-data
DEVUSER=developer
DATADIR=/home/data
CRMDATA=civicrm.sql.gz
STOCKSCHEMA=/var/www/civicrm/sites/all/modules/civicrm/sql/civicrm.mysql
# This file is a dump of some schema customisations which we apply before upgrade.
# It does two things: 
# (1) create the schema for the civicrm_value_* and custom_value_* tables, and
# (2) apply some of the longer-running schema changes before upgrade
# Schema changes in (2) are removed from civicrm.mysql above via drush make patches.
CUSTOMSCHEMA=/var/www/civicrm/sites/default/civicrm/civicrm-schema-custom-3.4.8.sql
# Prune .gz off filenames.
CRMSQL_REDUCED=$( echo $CRMDATA | sed -e 's#\.sql\.gz#-reduced.sql#g' )
CRMSQL_HUGE=$( echo $CRMDATA | sed -e 's#\.sql\.gz#.sql#g' )
 
START=$(date +%s)
START_DATE=$( date )
 
rsync $DBHOST:/var/backups/mysql/sqldump/$CRMDATA $DATADIR
 
# MySQL dumps may contain a reference to civicrm@webserver (if backed up
# from live). These are set as DEFINER of some CiviCRM views; the users
# need to be present when loading the DB or else the SQL will bail
# out.
echo "Creating fake user(s) to avoid DEFINER error(s)."
mysql -e 'GRANT SELECT on temp.* TO civicrm@webserver'
 
echo "Loading database from $CRMDATA"
time zcat $DATADIR/$CRMDATA | mysql $TMPCRMDB
 
# This data doesn't need to be stripped for upgrade, but it's several GB of data
# which we don't need, so we strip it out before upgrading to speed things along.
echo "Stripping some historic data."
mysql -e "DELETE FROM civicrm_mailing_event_bounce WHERE time_stamp <= '2012-01-01 00:00:00'" $TMPCRMDB
mysql -e "DELETE FROM civicrm_activity WHERE activity_type_id = 32 AND activity_date_time <= '2012-01-01 00:00:00'" $TMPCRMDB
NOW=$(date +%s); echo $(( $NOW-$START )) s
 
echo "Fixing schema."
# Remove civicrm_task_action_temp_%_%.
mysql -ss -e 'SHOW TABLES LIKE "civicrm_task_action_temp_%"' $TMPCRMDB | while read TABLE ; do echo "Dropping table $TABLE" && mysql -e "DROP TABLE $TABLE" $TMPCRMDB ; done
 
# Custom table patterns.
# civicrm_value_% custom_value_% civicrm_%temp% civicrm_import_job_%
TABLES="$( mysql -s -e "SHOW TABLES LIKE 'civicrm_value_%'" $TMPCRMDB ) $( mysql -s -e "SHOW TABLES LIKE 'custom_value_%'" $TMPCRMDB ) $( mysql -s -e "SHOW TABLES LIKE 'civicrm_%_temp_%'" $TMPCRMDB ) $( mysql -s -e "SHOW TABLES LIKE 'civicrm_import_job_%'" $TMPCRMDB )"
TABLES="$TABLES civicrm_export_temp civicrm_task_action_temp"
 
echo -n "Contacts before dump: " && mysql -ss -e 'SELECT COUNT(*) FROM civicrm_contact' $TMPCRMDB
 
# Disable FK checks during import.
echo -n "Dumping dataset ... "
echo "SET foreign_key_checks = 0;" > $DATADIR/$TMPCRMDB-data.sql
# Dump dataset to .sql file.
mysqldump -c -e -n -t $TMPCRMDB >> $DATADIR/$TMPCRMDB-data.sql
# Enable FK checks after import.
echo "SET foreign_key_checks = 1;" >> $DATADIR/$TMPCRMDB-data.sql
echo " $DATADIR/$TMPCRMDB-data.sql"
 
# Export schema for custom tables, to reload later.
echo -n "Dumping custom tables schema ... "
mysqldump -d $TMPCRMDB $TABLES > $DATADIR/$TMPCRMDB-schema-customtables.sql
echo "$TMPCRMDB-schema-customtables.sql"
 
# Drop entire DB, in order to force reload based on original schema.
echo "Dropping and recreating $TMPCRMDB".
mysql -e "DROP DATABASE $TMPCRMDB; CREATE DATABASE $TMPCRMDB"
echo -n "Loading pristine schema ... "
mysql $TMPCRMDB < $STOCKSCHEMA
echo "$STOCKSCHEMA."
echo -n "Applying custom schema changes to stock tables ... "
mysql $TMPCRMDB < $CUSTOMSCHEMA
echo "$CUSTOMSCHEMA."
echo -n "Loading custom table schema ... "
mysql $TMPCRMDB < $DATADIR/$TMPCRMDB-schema-customtables.sql
echo "$TMPCRMDB-schema-customtables.sql."
echo "Loading dataset from dump ... "
mysql $TMPCRMDB < $DATADIR/$TMPCRMDB-data.sql
echo "$TMPCRMDB-data.sql."
 
# Dump the full DB
mysqldump $TMPCRMDB > $DATADIR/$CRMSQL_HUGE
 
# Create a reduced dataset. I do this because it's quicker to test against
# a smaller dataset, but for production I use the $CRMSQL_HUGE file above.
echo "
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE civicrm_cache;
TRUNCATE TABLE civicrm_log;
TRUNCATE TABLE civicrm_mailing_event_bounce;
TRUNCATE TABLE civicrm_mailing_event_delivered;
TRUNCATE TABLE civicrm_mailing_event_forward;
TRUNCATE TABLE civicrm_mailing_event_opened;
TRUNCATE TABLE civicrm_mailing_event_reply;
TRUNCATE TABLE civicrm_mailing_event_trackable_url_open;
TRUNCATE TABLE civicrm_mailing_event_unsubscribe;
TRUNCATE TABLE civicrm_mailing_event_queue;
TRUNCATE TABLE civicrm_mailing_recipients;
TRUNCATE TABLE civicrm_mailing_spool;
TRUNCATE TABLE civicrm_subscription_history;
SET FOREIGN_KEY_CHECKS=1;
" | mysql $TMPCRMDB
 
mysqldump $TMPCRMDB > $DATADIR/$CRMSQL_REDUCED
 
gzip -v9 $DATADIR/*sql
date