Best MySQLdump options

I recently had to configure an automated backup procedure for MySQL databases, related to wordpress sites.

I am a long-time user of MS SQL Server, so I was surprised to find that there is no “binary” option of MySQL backup, only text script version.

Then, I quickly found that MySQLDump is what I need.   So, I start using it as-is, without any doc reading.  

mysqldump --all-databases > backup.sql

Finally, to make sure it works fine, I tried to restore it.

My first surprise was to find lot of invalid data in my backup!  What?

Then, I learned that mysqldump, by default, is NOT set to backup everything the correct way.

Because I use some wordpress plugins that store their data into “blobs”, I need to add this very important option: 

--hex-blob

In fact, I don’t understand why it’s not included in the default --opt option, as it MUST be used to get blob correctly backed up.

Also, I read at some places that I should use the --opt settings, because it enables all of these: --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

But, you don’t need to, as –opt is a “default” option automatically applied when you run mysqldump.exe.

Now, is there others options I need to enable to make sure I backup everything?  Yes, I found 3. And, mysqldump give us warning to enable them if we need to get a full backup!  So, once again, why aren’t they enable it by default?

These options are: –routines, –events and –triggers.

In some versions of mysqldump, using “–all-databases” automatically enabled the --routines and --events, but not if you backup 1 single database.  And, in the version 8+, these options are NOT set by default.

So, don’t take chances, add them.

One last thing: If you want to avoid problems with file encoding, I suggest to use --result-file=filename.sql, instead of > filename.sql, to prevent different encoding between systems.

Finally, the backup options I use are: 

mysqldump.exe --defaults-extra-file=config.txt --all-databases --hex-blob --routines --events --triggers --result-file=backup.sql

The “–trigger” option is ambiguous.  The documentation (5.7 / 8.0) say it is enabled by default, but if you don’t add it, the warning message suggests to add it.  So, don’t take chances, just add it.

The --defaults-extra-file= is used to set my host / uid / pwd in an external file.