Skip to content

Instantly share code, notes, and snippets.

@wbob
Last active March 23, 2020 11:46
Show Gist options
  • Save wbob/e2bfd429a59420cb060c045030d2c9e8 to your computer and use it in GitHub Desktop.
Save wbob/e2bfd429a59420cb060c045030d2c9e8 to your computer and use it in GitHub Desktop.
shopware-dev-db-dump
#!/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."
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment