Shopware 6 – How to mysqldump

# Make sure to define $FILENAME and $DATABASE!

mysqldump --quick -C --hex-blob --single-transaction $DATABASE | LANG=C LC_CTYPE=C LC_ALL=C sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip -c > $FILENAME.sql.gz

mysqldump –quick -C –hex-blob –single-transaction

–quick, -q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from
the server a row at a time rather than retrieving the entire row set and buffering it in memory
before writing it out.
–compress, -C Compress all information sent between the client and the server if both support compression.
–hex-blob Dump binary columns using hexadecimal notation (for example, ´abc´ becomes 0x616263). The affected
data types are BINARY, VARBINARY, the BLOB types, and BIT.
–single-transaction Dump all data in one transaction to have a valid state

Thanks Marcel for your comment!
When using Mysql 8 you need --no-tablespaces as additional

sed -e ‘s/DEFINER[ ]=[ ][^]*/*/’

Remove DEFINER statement to use default definer (instead of a hard coded mysql user which is most likely not available on any other system)

gzip

gzip the dump

> $FILENAME.sql.gz

And write it to $FILENAME.sql.gz (e.g. 2021-04-07_13:22:12-customer-abc.sql.gz)

How to mysqldump GDPR edition

# Make sure to define $FILENAME and $DATABASE!

mysqldump --quick -C --hex-blob --single-transaction --no-data $DATABASE | LANG=C LC_CTYPE=C LC_ALL=C sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip -c > $FILENAME.sql.gz

mysqldump --quick -C --hex-blob --single-transaction --ignore-table=$DATABASE.product_search_keyword --ignore-table=$DATABASE.seo_url --ignore-table=$DATABASE.product_keyword_dictionary --ignore-table=$DATABASE.payone_payment_card --ignore-table=$DATABASE.payone_payment_mandate --ignore-table=$DATABASE.payone_payment_redirect --ignore-table=$DATABASE.acl_user_role --ignore-table=$DATABASE.cart --ignore-table=$DATABASE.customer --ignore-table=$DATABASE.customer_address --ignore-table=$DATABASE.customer_recovery --ignore-table=$DATABASE.customer_tag --ignore-table=$DATABASE.customer_wishlist --ignore-table=$DATABASE.customer_wishlist_product --ignore-table=$DATABASE.elasticsearch_index_task --ignore-table=$DATABASE.enqueue --ignore-table=$DATABASE.import_export_file --ignore-table=$DATABASE.import_export_log --ignore-table=$DATABASE.integration --ignore-table=$DATABASE.integration_role --ignore-table=$DATABASE.log_entry --ignore-table=$DATABASE.message_queue_stats --ignore-table=$DATABASE.newsletter_recipient --ignore-table=$DATABASE.newsletter_recipient_tag --ignore-table=$DATABASE.order --ignore-table=$DATABASE.order_address --ignore-table=$DATABASE.order_customer --ignore-table=$DATABASE.order_delivery --ignore-table=$DATABASE.order_delivery_position --ignore-table=$DATABASE.order_line_item --ignore-table=$DATABASE.order_tag --ignore-table=$DATABASE.order_transaction --ignore-table=$DATABASE.product_export --ignore-table=$DATABASE.product_review --ignore-table=$DATABASE.promotion_persona_customer --ignore-table=$DATABASE.refresh_token --ignore-table=$DATABASE.sales_channel_api_context --ignore-table=$DATABASE.state_machine_history --ignore-table=$DATABASE.user --ignore-table=$DATABASE.user_access_key --ignore-table=$DATABASE.user_config --ignore-table=$DATABASE.user_recovery --ignore-table=$DATABASE.version --ignore-table=$DATABASE.version_commit --ignore-table=$DATABASE.version_commit_data $DATABASE | LANG=C LC_CTYPE=C LC_ALL=C sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip -c >> $FILENAME.sql.gz

–ignore-table-data Make Do only write CREATE TABLE and don’t write the table data (like customer data, order data, etc. which contain data to identify people)

1. Dump all table structures
2. Dump all data, except GDPR and Indexed tables

* This contains already ignoring for Payone module.

The tables product_search_keyword, product_keyword_dictionary and seo_url are empty as well. They can be filled by running the following command. It might take a while to generate all the data, though.

bin/console dal:refresh:index

Update 9.12.2012: Remove event_* tables from GDPR ignoring