3

Several fine answers on here have already exquisitely argued that

mysqldump --hex-blob --events --routines --triggers  [...]  --all-databases

should be all - modulo storage-engine considerations - one needs to get a

mysqldump of an entire mysql instance ... everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords....

And I wanted to believe - and use - that; however, reading up on the manpage of mysqldump(1) (for MySQL 5.7.27) I find that:

  • --events, -E

    [...]

    If you require events to be created with their original timestamp attributes, do not use --events. Instead, dump and reload the contents of the mysql.event table directly, using a MySQL account that has appropriate privileges for the mysql database.

... and that:

  • --routines, -R

    [...]

    If you require routines to be created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.

This leads me to believe that since --all-databases will also cause the mysql database to be included in the dump, and by that, the mysql.event and mysql.proc tables, that I would not have to include the --events and --routines options at all to arrive at an as-complete-as-possible dump.

What's more, including those options, I expect, would get me a less-than-complete dump instead because:

The output generated by using --events contains CREATE EVENT statements to create the events. However, these statements do not include attributes such as the event creation and modification timestamps, so when the events are reloaded, they are created with timestamps equal to the reload time.

... and likewise:

The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps, so when the routines are reloaded, they are created with timestamps equal to the reload time.

Although, the mysql database would still also be included, so I'm not sure what the net effect combining --events and --routines with --all-databases will be in the resulting dump. I suspect it will depend on whether mysql will be dumped last, first, or somewhere in-between.

Does this reasoning make any sense or am I missing something?

cueedee
  • 131
  • 1
  • 6
  • Astute observations. – Rick James Aug 21 '19 at 22:47
  • (Note that much of this discussion applies when using --all-databases. Adjustments need to be made when thinking about dumping individual database(s). – Rick James Aug 21 '19 at 22:48
  • @RickJames; the intent is to produce easily restorable, periodic instance dumps that get preserved with subsequent volume-snapshots, so --all-databases is definitely what I'm aiming for here. – cueedee Aug 22 '19 at 07:34

0 Answers0