Rune Laenen
Rune Laenen
Senior Shopware Consultant

How to dump Shopware 6 database
Using mysqldump while avoiding common pitfalls

mysqldump --compress --routines --hex-blob --single-transaction --no-tablespaces databasename | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql

That's it.
That's the blog post. 🤡
Read further to get the explanation as to why it needs this specific configuration.

mysqldump

The first part of our command is the well known `mysqldump` command. It is almost always available on every machine where mysql itself is available, and thus can be used almost everywhere.
To do a good dump of a Shopware 6 database, you do need to add some extra parameters to the command. --compress

If both server and client support it, all information sent between them will be compressed.


--routines

Adding this makes sure that any routines (stored procedures or functions) are exported as well. Shopware core does not use this, but some of your custom or Store-bought plugins might.


--hex-blob

The hex-blob-flag makes sure that your files are still readable by common text editors by hexing all binary data in your sql dump. This also makes it possible to later use the SED command without any extra issues.


--single-transaction

Dumps all the data in one transaction so your states are valid. Without this flag, every table will be dumped seperately, possible causing issues if the database is also being written to at the moment of the dump.


--no-tablespaces

Does not dump the logfile groups or tablespaces of the database.


databasename

Replace this part of the command with your own database name.

sed

The second part of our command uses the `sed` command. Sed is a streaming text editor which we will use to replace text in our (huge) database export file.
More specifically, we will remove the DEFINER statements so no database usernames are added to our dump as it might be very well possible that the database user in the database where we will be importing the dump is different. By not defining the DEFINER manually, it will automatically use the default DEFINER of the receiving database.

> dump.sql

The last part of our command is simply telling that the output should not be returned in your CLI, but that is should be written to the `dump.sql` file.

Importing the dump

To import the dump again, simply download the generated file to your receiving machine and run `mysql -u user -p newdatabasename < dump.sql` to feed it into the database.
Author: Rune Laenen
Post date: March 17th 2025

Rune Laenen
Hi, I’m Rune Laenen, a Shopware developer and freelance consultant from Belgium. I’ve been working with Shopware 6 since day one, starting with its very first alpha release. Over the years, I’ve gained extensive experience in the Shopware ecosystem. My career began as a developer at a Belgian agency, and I later joined Shopware as a Consultant, helping businesses get the most out of the platform.
Now, as a freelancer, I focus on creating high-quality extensions, optimizing workflows, and delivering scalable high-performant e-commerce solutions. I’ve also released several Shopware plugins in the Shopware Store. I’m passionate about clean code, best practices, and empowering developers to build better software. Being part of the Shopware community and contributing to its growth is something I truly enjoy.

Find me on
Share this article
Rune Laenen
Contact
E-mail: rune@laenen.me
Phone: +32 474 06 53 50
VAT: BE 0670.566.146
Invoice Address
Rune Laenen
Zittaartse Heide 14
2275 Poederlee
Belgium
A.K.A.
Kraftware
The Box Office
KraftAV