# 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)
- Dump all table structures
- 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
When using Mysql 8 you need –no-tablespaces as additional