Magento MySQL Santized Backup

README.md

Run sanitized backup

curl https://gist.githubusercontent.com/adamzero1/90e577229cbfb5907822b50daa7baf37/raw/85aa792ea563418fb9da2dee1fba1fa7669e7bd1/backup.sh | bash

backup.sh

#!/bin/bash -e

if [[ "$(which mysqldump >/dev/null; echo $?)" -gt 0 ]]; then
    echo "Unable to locate 'mysqldump'"
    exit 1
fi

if [[ ! -f app/etc/env.php ]]; then
    echo "app/etc/env.php not found"
    echo "Make sure it exist and you are running this from the root directory (not the pub dir)"
    exit 2
fi

MYSQL_USERNAME=$(php -r '$config = include "app/etc/env.php"; echo $config["db"]["connection"]["default"]["username"];')
MYSQL_PASSWORD=$(php -r '$config = include "app/etc/env.php"; echo $config["db"]["connection"]["default"]["password"];')
MYSQL_HOST=$(php -r '$config = include "app/etc/env.php"; echo $config["db"]["connection"]["default"]["host"];')
MYSQL_DATABASE=$(php -r '$config = include "app/etc/env.php"; echo $config["db"]["connection"]["default"]["dbname"];')

MYSQL_CONNECTION_STRING="--user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} ${MYSQL_DATABASE} "
echo "connection string: ${MYSQL_CONNECTION_STRING}"

SENSITIVE_TABLES="report_event
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
customer_grid_flat
sales_creditmemo
sales_creditmemo_comment
sales_creditmemo_grid
sales_creditmemo_item
sales_invoice
sales_invoice_comment
sales_invoice_grid
sales_invoice_item
sales_invoiced_aggregated
sales_invoiced_aggregated_order
sales_order
sales_order_address
sales_order_aggregated_created
sales_order_aggregated_updated
sales_order_grid
sales_order_item
sales_order_payment
sales_order_status_history
sales_order_status
sales_order_status_label
sales_order_status_state
sales_order_tax
sales_order_tax_item
sales_payment_transaction
sales_refunded_aggregated
sales_refunded_aggregated_order
sales_sequence_meta
sales_sequence_profile
sales_shipment
sales_shipment_comment
sales_shipment_grid
sales_shipment_item
sales_shipment_track
sales_shipping_aggregated
sales_shipping_aggregated_order
quote
quote_address
quote_address_item
quote_id_mask
quote_item
quote_item_option
quote_payment
quote_shipping_rate
magento_sales_creditmemo_grid_archive
magento_sales_invoice_grid_archive
magento_sales_order_grid_archive
magento_sales_shipment_grid_archive
magento_logging_event"

EXCLUSIONS=""
for SENSITIVE_TABLE in ${SENSITIVE_TABLES}; do 
    EXCLUSIONS="${EXCLUSIONS} --ignore-table=${MYSQL_DATABASE}.${SENSITIVE_TABLE} "
done

echo "Exclusions: ${EXCLUSIONS}"

BACKUP_PATH="mysql-backup_$(date +%Y-%m-%d_%H-%M_%S).sql"
echo "Backup path: ${BACKUP_PATH}"

echo "get all the tables without data"
set -x
mysqldump --no-data --skip-triggers --single-transaction ${MYSQL_CONNECTION_STRING} > ${BACKUP_PATH}
set +x

echo "get non-sensitive tables data"
mysqldump --no-create-info --single-transaction --quick ${MYSQL_CONNECTION_STRING} ${EXCLUSIONS} >> ${BACKUP_PATH}

echo "Done"
echo "Backup path: ${BACKUP_PATH}"