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:
[...]
If you require events to be created with their original timestamp attributes, do not use
--events
. Instead, dump and reload the contents of themysql.event
table directly, using a MySQL account that has appropriate privileges for themysql
database.
... and that:
[...]
If you require routines to be created with their original timestamp attributes, do not use
--routines
. Instead, dump and reload the contents of themysql.proc
table directly, using a MySQL account that has appropriate privileges for themysql
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
containsCREATE 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
containsCREATE PROCEDURE
andCREATE 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?
--all-databases
. Adjustments need to be made when thinking about dumping individual database(s).--all-databases
is definitely what I'm aiming for here.