shopware-dev-db-dump

dev-dump-no-pii-data.sh

#!/bin/bash
#
# dump db without contents of specific tables

if test -z "$1" || test -z "$2"; then
 echo "usage: script.sh <user> <dbname>"
 exit 1
fi

user="$1"
dbname="$2"

echo -n "enter dbuser password: "
read -s password

# tables not found in the database will not throw an error, so use generously
# s_order_number is kept, as it does not contain pii data
tables=(
s_articles_vote
s_articles_also_bought_ro
s_articles_similar_shown_ro
s_articles_top_seller_ro
s_customer_search_index
s_es_backlog
s_emarketing_lastarticles
s_emarketing_partner
s_emarketing_partner_attributes
s_emarketing_referer
s_emarketing_tellafriend
s_emarketing_voucher_codes
s_emarketing_vouchers
s_emarketing_vouchers_attributes
s_campaigns_groups
s_campaigns_logs
s_campaigns_mailaddresses
s_campaigns_maildata
s_core_payment_data
s_core_payment_instance
s_core_sessions
s_core_sessions_backend
s_core_optin
s_user
s_user_attributes
s_user_addresses
s_user_addresses_attributes
s_user_attributes
s_user_billingaddress
s_user_billingaddress_attributes
s_user_shippingaddress
s_user_shippingaddress_attributes
s_order
s_order_attributes
s_order_basket
s_order_basket_attributes
s_order_basket_signatures
s_order_billingaddress
s_order_billingaddress_attributes
s_order_comparisons
s_order_details
s_order_details_attributes
s_order_documents
s_order_documents_attributes
s_order_esd
s_order_history
s_order_notes
s_order_shippingaddress
s_order_shippingaddress_attributes
s_statistics_article_impression
s_statistics_currentusers
s_statistics_pool
s_statistics_referer
s_statistics_search
s_statistics_visitors
s_shopgate_orders
s_payment_paypal_plus_payment_instruction
s_plugin_widgets_notes
)

# unfold array into options
ignored=$(printf -- "--ignore-table=$dbname.%s " "${tables[@]}")

echo "dumping $dbname schema.."
mysqldump --user "$user" -p"$password" --single-transaction --no-data "$dbname" | gzip > "$dbname".sql.gz

echo "dumping tables holding no user-data.."
mysqldump --user "$user" -p"$password" --opt ${ignored} --single-transaction "$dbname" | gzip >> "$dbname".sql.gz

echo -n "size of dump: "
du -h "$dbname".sql.gz
echo "done."

dev-dump-reapply.sh

#!/usr/bin/env bash
#
# "INSERT only" mysqldump to preserve development user data after PII live-db import (see other snippet)

echo "# USAGE #"
echo "read -s pw (enter db-password);"
echo "script db-username db-name"
echo "mysql db-name < reapply.sql"

# will give you a bare INSERT/REPLACE dump
options=(
--no-create-db
--no-create-info
--skip-triggers
--skip-opt
--complete-insert
--add-locks
--disable-keys
--set-charset
--skip-comments
--quick
--replace
)

# if schemas are identical, dump "_attributes" tables too
tables=(
s_core_licenses
s_user
s_user_addresses
s_user_attributes
s_user_billingaddress
s_user_shippingaddress
s_order_number
)

mysqldump -u $1 -p$pw ${options[@]} $2 ${tables[@]} > reapply.sql

# afterwards adjust s_core_shops: host, hosts, secure, always_secure
# disable any plugins without license validity/errors or use the real hostname
# clear up var/cache/ fully and sw:generate:attributes