Skip to content

Instantly share code, notes, and snippets.

@groggu

groggu/db_dump.sh

Created August 17, 2015 14:50
Download ZIP

Revisions

  1. Greg Croasdill created this gist Aug 17, 2015.
    73 changes: 73 additions & 0 deletions db_dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,73 @@
    #!/bin/bash

    IGNORE_TABLES=( dataflow_batch_export dataflow_batch_import log_customer log_quote log_summary log_summary_type log_url log_url_info log_visitor log_visitor_info log_visitor_online report_viewed_product_index report_compared_product_index report_event index_event enterprise_logging_event_changes )
    CONFIG_FILE="./app/etc/local.xml"
    IGNORE_STRING=""
    TMP_FILE="./var/.tmp.local.xml"

    if [ ! -f "$CONFIG_FILE" ]; then
    echo "$CONFIG_FILE does not exist"
    exit
    fi

    sed -ne '/default_setup/,/\/default_setup/p' $CONFIG_FILE > $TMP_FILE

    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"
    }


    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 [[ "$1" == "--restore" ]] || [[ "$1" == "-r" ]]; then
    echo -n "Are you sure you want to restore ./var/db.sql to $DBNAME? [y/N]: "
    read CONFIRM
    if [[ "$CONFIRM" == "y" ]]; then
    mysql -h$DBHOST -u$DBUSER -p"$DBPASS" $DBNAME < var/db.sql
    cat <<EOT
    #######################################
    MYSQL IMPORT COMPLETE
    ########################################
    EOT
    fi
    exit
    fi

    for TABLE in "${IGNORE_TABLES[@]}"; do
    IGNORE_STRING="$IGNORE_STRING --ignore-table=$DBNAME.$TABLE_PREFIX$TABLE"
    done

    # First dump the structure only
    mysqldump -h$DBHOST -u$DBUSER -p"$DBPASS" $DBNAME --no-data --lock-tables=false > var/$DBNAME.sql

    # Now dump the data
    mysqldump -h$DBHOST -u$DBUSER -p"$DBPASS" $DBNAME $IGNORE_STRING --lock-tables=false >> var/$DBNAME.sql


    sed -i 's/DEFINER=[^*]*\*/\*/g' var/$DBNAME.sql

    gzip var/$DBNAME.sql

    cat <<EOT
    #######################################
    MYSQL DUMP OF $DBNAME COMPLETE
    Backup Location: ./var/$DBNAME.sql.gz
    ########################################
    EOT