Backup Magento EE 1.14.2.2 DB without production data.

magento-backup-db.sh

#!/bin/bash

################################################################################
# FUNCTIONS
################################################################################

# 1. Check required system tools
_check_installed_tools() {
    local missed=""

    until [ -z "$1" ]; do
        type -t $1 >/dev/null 2>/dev/null
        if (( $? != 0 )); then
            missed="$missed $1"
        fi
        shift
    done

    echo $missed
}

# 2. Selftest for checking tools which will used
checkTools() {
    REQUIRED_UTILS='nice sed tar mysqldump head gzip getopt lsof'
    MISSED_REQUIRED_TOOLS=`_check_installed_tools $REQUIRED_UTILS`
    if (( `echo $MISSED_REQUIRED_TOOLS | wc -w` > 0 ));
    then
        echo -e "Unable to create backup due to missing required bash tools: $MISSED_REQUIRED_TOOLS"
        exit 1
    fi
}

# 4. Create DB dump function
createDbDump() {
    # Set path of local.xml
    LOCALXMLPATH=${MAGENTOROOT}app/etc/local.xml

    # Get mysql credentials from local.xml
    getLocalValue() {
        PARAMVALUE=`sed -n "/<resources>/,/<\/resources>/p" $LOCALXMLPATH | sed -n -e "s/.*<$PARAMNAME><!\[CDATA\[\(.*\)\]\]><\/$PARAMNAME>.*/\1/p" | head -n 1`
    }

    # Connection parameters
    DBHOST=
    DBUSER=
    DBNAME=
    DBPASSWORD=
    TBLPRF=

    # Include DB logs option
    SKIPLOGS=1

    # Ignored table names
    IGNOREDTABLES="
    enterprise_logging_event
    enterprise_logging_event_changes
    index_event
    index_process_event
    report_event
    dataflow_batch_export
    dataflow_batch_import
    enterprise_support_backup
    enterprise_support_backup_item
    adminnotification_inbox
    captcha_log
    catalogsearch_fulltext
    catalogsearch_fulltext_cl
    catalogsearch_query
    catalogsearch_recommendations
    catalogsearch_result
    core_cache
    core_cache_tag
    core_email_queue
    core_email_queue_recipients
    core_session
    cron_schedule
    customer_address_entity
    customer_address_entity_datetime
    customer_address_entity_decimal
    customer_address_entity_int
    customer_address_entity_text
    customer_address_entity_varchar
    customer_entity
    customer_entity_datetime
    customer_entity_decimal
    customer_entity_int
    customer_entity_text
    customer_entity_varchar
    enterprise_customer_sales_flat_order
    enterprise_customer_sales_flat_order_address
    enterprise_customer_sales_flat_quote
    enterprise_customer_sales_flat_quote_address
    enterprise_customerbalance
    enterprise_customerbalance_history
    enterprise_customersegment_customer
    enterprise_customersegment_event
    enterprise_customersegment_segment
    enterprise_customersegment_website
    enterprise_sales_creditmemo_grid_archive
    enterprise_sales_invoice_grid_archive
    enterprise_sales_order_grid_archive
    enterprise_sales_shipment_grid_archive
    enterprise_scheduled_operations
    enterprise_staging
    enterprise_staging_action
    enterprise_staging_item
    enterprise_staging_log
    enterprise_staging_product_unlinked
    log_customer
    log_quote
    log_summary
    log_summary_type
    log_url
    log_url_info
    log_visitor
    log_visitor_info
    log_visitor_online
    newsletter_problem
    newsletter_queue
    newsletter_queue_link
    newsletter_queue_store_link
    newsletter_subscriber
    oauth_consumer
    oauth_nonce
    oauth_token
    paypal_cert
    paypal_payment_transaction
    paypal_settlement_report
    paypal_settlement_report_row
    persistent_session
    poll_vote
    product_alert_price
    product_alert_stock
    rating
    rating_option_vote
    rating_option_vote_aggregated
    report_compared_product_index
    report_viewed_product_aggregated_daily
    report_viewed_product_aggregated_monthly
    report_viewed_product_aggregated_yearly
    report_viewed_product_index
    review
    review_detail
    sales_bestsellers_aggregated_daily
    sales_bestsellers_aggregated_monthly
    sales_bestsellers_aggregated_yearly
    sales_billing_agreement
    sales_billing_agreement_order
    sales_flat_creditmemo
    sales_flat_creditmemo_comment
    sales_flat_creditmemo_grid
    sales_flat_creditmemo_item
    sales_flat_invoice
    sales_flat_invoice_comment
    sales_flat_invoice_grid
    sales_flat_invoice_item
    sales_flat_order
    sales_flat_order_address
    sales_flat_order_grid
    sales_flat_order_item
    sales_flat_order_payment
    sales_flat_order_status_history
    sales_flat_quote
    sales_flat_quote_address
    sales_flat_quote_address_item
    sales_flat_quote_item
    sales_flat_quote_item_option
    sales_flat_quote_payment
    sales_flat_quote_shipping_rate
    sales_flat_shipment
    sales_flat_shipment_comment
    sales_flat_shipment_grid
    sales_flat_shipment_item
    sales_flat_shipment_track
    sales_invoiced_aggregated
    sales_invoiced_aggregated_order
    sales_order_aggregated_created
    sales_order_aggregated_updated
    sales_order_tax
    sales_order_tax_item
    sales_payment_transaction
    sales_recurring_profile
    sales_recurring_profile_order
    sales_refunded_aggregated
    sales_refunded_aggregated_order
    sales_shipping_aggregated
    sales_shipping_aggregated_order
    sendfriend_log
    tax_order_aggregated_created
    tax_order_aggregated_updated
    wishlist
    wishlist_item
    wishlist_item_option"

    # Sanitize data
    SANITIZE=1

    # Sanitazed tables
    SANITIZEDTABLES=""

    # Get DB HOST from local.xml
    if [ -z "$DBHOST" ]; then
        PARAMNAME=host
        getLocalValue
        DBHOST=$PARAMVALUE
    fi

    # Get DB USER from local.xml
    if [ -z "$DBUSER" ]; then
        PARAMNAME=username
        getLocalValue
        DBUSER=$PARAMVALUE
    fi

    # Get DB PASSWORD from local.xml
    if [ -z "$DBPASSWORD" ]; then
        PARAMNAME=password
        getLocalValue
        DBPASSWORD=${PARAMVALUE//\\/\\\\}
        DBPASSWORD=${DBPASSWORD//\"/\\\"}
        DBPASSWORD=${DBPASSWORD//\$/\\\$}
        DBPASSWORD=${DBPASSWORD//\`/\\\`}
    fi

    # Get DB NAME from local.xml
    if [ -z "$DBNAME" ]; then
        PARAMNAME=dbname
        getLocalValue
        DBNAME=$PARAMVALUE
    fi

    # Get DB TABLE PREFIX from local.xml
    if [ -z "$TBLPRF" ]; then
        PARAMNAME=table_prefix
        getLocalValue
        TBLPRF=$PARAMVALUE
    fi

    # Check DB credentials for existsing
    if [ -z "$DBHOST" -o -z "$DBUSER" -o -z "$DBNAME" ]; then
        echo "Skip DB dumping due lack of parameters host=$DBHOST; username=$DBUSER; dbname=$DBNAME;";
        exit 0
    fi

    # Set connection params
    if [ -n "$DBPASSWORD" ]; then
        CONNECTIONPARAMS=" -u$DBUSER -h$DBHOST -p\"$DBPASSWORD\" $DBNAME --force --triggers --single-transaction --opt --skip-lock-tables"
    else
        CONNECTIONPARAMS=" -u$DBUSER -h$DBHOST $DBNAME --force --triggers --single-transaction --opt --skip-lock-tables"
    fi

    # Create DB dump
    IGN_SCH=
    IGN_IGN=
    SAN_CMD=

    if [ -n "$SANITIZE" ] ; then

        for TABLENAME in $SANITIZEDTABLES; do
            SAN_CMD="$SAN_CMD $TBLPRF$TABLENAME"
            IGN_IGN="$IGN_IGN --ignore-table='$DBNAME'.'$TBLPRF$TABLENAME'"
        done
        PHP_CODE='
        while ($line=fgets(STDIN)) {
            if (preg_match("/(^INSERT INTO\s+\S+\s+VALUES\s+)\((.*)\);$/",$line,$matches)) {
                $row = str_getcsv($matches[2],",","\x27");
                foreach($row as $key=>$field) {
                    if ($field == "NULL") {
                        continue;
                    } elseif ( preg_match("/[A-Z]/i", $field)) {
                        $field = md5($field . rand());
                    }
                    $row[$key] = "\x27" . $field . "\x27";
                }
                echo $matches[1] . "(" . implode(",", $row) . ");\n";
                continue;
            }
            echo $line;
        }'
        SAN_CMD="nice -n 15 mysqldump $CONNECTIONPARAMS --skip-extended-insert $SAN_CMD | php -r '$PHP_CODE' ;"
    fi

    if [ -n "$SKIPLOGS" ] ; then
        for TABLENAME in $IGNOREDTABLES; do
            IGN_SCH="$IGN_SCH $TBLPRF$TABLENAME"
            IGN_IGN="$IGN_IGN --ignore-table='$DBNAME'.'$TBLPRF$TABLENAME'"
        done
        IGN_SCH="nice -n 15 mysqldump --no-data $CONNECTIONPARAMS $IGN_SCH ;"
    fi

    IGN_IGN="nice -n 15 mysqldump $CONNECTIONPARAMS $IGN_IGN"

    DBDUMPCMD="( $SAN_CMD $IGN_SCH $IGN_IGN) | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'  > $MAGENTOROOT$DBNAME.$DBFILENAME"

    echo ${DBDUMPCMD//"p\"$DBPASSWORD\""/p[******]}

    eval "$DBDUMPCMD"
}

################################################################################
# CODE
################################################################################

# Self test
checkTools

# Set the mysqldump filename
MAGENTOROOT=./
DATETIME=`date -u +"%Y%m%d.%H%M"`
DBFILENAME="$DATETIME.sql"

# Create dump
createDbDump

exit 0

this is a slight modification of the backup script provided by magento