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:
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
--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.
--defaults-extra-file= is used to set my host / uid / pwd in an external file.