Shell Script to Dump / Import Magento Sales and Customers

mage-dbdump-sales-users

#!/bin/bash

# VARIABLES
CONFIG_FILE="./app/etc/local.xml"
DUMP_FILE="./var/db-sales-users.sql"
TABLES="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_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_status sales_order_status_label sales_order_status_state 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 customer_address_entity customer_address_entity_datetime customer_address_entity_decimal customer_address_entity_int customer_address_entity_text customer_address_entity_varchar customer_eav_attribute customer_eav_attribute_website
customer_entity customer_entity_datetime customer_entity_decimal customer_entity_int customer_entity_text customer_entity_varchar customer_form_attribute customer_group eav_entity_store"

# USAGE
function usage()
{
cat <<EOF
Usage:     $0 [OPTIONS]
Version:   1.1
Author:    Sean Grünböck / studio19.at
Changedate: 26.06.2015

Use this script to dump or import Users, Sales (Invoices,etc.) from LIVE DB to STAGING DB

OPTIONS:
      -d             Dump
      -i             Import

EOF
}

# FUNCTIONS
function message()
{
  STRIP=$(for i in {1..38}; do echo -n "#"; done)
  echo -e "$STRIP\n$1\n$STRIP"
}

function question()
{
  [[ ! "$OPT_F" == "" ]] && return 0
  echo -n "$1 [y/N]: "
  read CONFIRM
  [[ "$CONFIRM" == "y" ]] || [[ "$CONFIRM" == "Y" ]] && return 0
  return 1
}

# GET OPTIONS
while getopts ":di" OPTION; do
  case $OPTION in
    h)
      usage
      exit 0
      ;;
    *)
      [[ "$OPTARG" == "" ]] && OPTARG='"-'$OPTION' 1"'
      OPTION="OPT_$OPTION"
      eval ${OPTION}=$OPTARG
      ;;
  esac
done

[[ "$OPT_d$OPT_i" == "" ]] && usage && exit 1

# GET PARAMETERS FROM LOCAL.XML
function getParam()
{
  RETVAL=$(grep -Eoh "<$1>(<!\[CDATA\[)?(.*)(\]\]>)?<\/$1>" $TMP_FILE | sed "s#<$1><!\[CDATA\[##g;s#\]\]><\/$1>##g")
  if [[ "$2" == "sanitise" ]]; then
    RETVAL=$(echo "$RETVAL" | sed 's/"/\\\"/g')
  fi
  echo -e "$RETVAL"
}

which mktemp >/dev/null 2>&1
[ $? -eq 0 ] && TMP_FILE=$(mktemp ./var/local.xml.XXXXX) || TMP_FILE="./var/.tmp.local.xml"
sed -ne '/default_setup/,/\/default_setup/p' $CONFIG_FILE > $TMP_FILE

IGNORE_STRING=""
DBHOST=$(getParam "host")
DBUSER=$(getParam "username")
DBPASS=$(getParam "password" "sanitise" )
DBNAME=$(getParam "dbname")
TABLE_PREFIX=$(getParam "table_prefix")
[ -f $TMP_FILE ] && rm $TMP_FILE

if [[ ! "$OPT_d" == "" ]]; then
  mysqldump -h $DBHOST -u $DBUSER -p$DBPASS --routines --triggers --single-transaction $DBNAME $TABLES >> $DUMP_FILE
  message "dumped"
elif [[ ! "$OPT_i" == "" ]]; then
  [ ! -f "$DUMP_FILE" ] && error "SQL file does not exist"
  question "Are you sure you want to restore $DUMP_FILE to $DBNAME?"
  if [ $? -eq 0 ]; then
    mysql -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME <$DUMP_FILE
    message "MYSQL IMPORT COMPLETE"
  fi
  exit 0
  
fi

Sean this is so hugely helpful. thanks.
One issue I ran into was that the TABLE_PREFIX was not attached to the table names, thus no tables were found until I changed the variables.

Thank you very much, it works!

I changed the file name to mage.sh
then I run $ sh mage.sh
and it shows me below error
mage.sh: 12: mage.sh: Syntax error: "(" unexpected

Can you please tell me what I am doing wrong here?

Thanks for the script. I tried it with Magento 1.9.2.4 and it works great but I think you missed the table "sales_flat_quote_address". Or is there any reason to leave this table out?

This script doesn't do anything with the TABLE_PREFIX parameter, but the tables I wanted to export all had "mage_" as prefix.

I changed the script to prepend the prefix by adding the following lines:

TABLE_PREFIX="mage_"
for table in $TABLES; do
  PREFIXED_TABLE=$TABLE_PREFIX$table
  NEWTABLES+=" $PREFIXED_TABLE"
done
TABLES=$NEWTABLES

Adding after the non-functional "TABLE_PREFIX=$(getParam "table_prefix")" is the best place to paste the block.