Support for additional `mysqldump` options (`events`, `triggers`, `routines`, etc.)

[dabico]

I did not manage to find any information in the documentation (both the dump --help and in the configuration.md file), but is there a way I can pass custom flags to the dump command to include objects like triggers and events in my dump? For example, I use the following for mysqldump:

[mysqldump]
quick
events
triggers
routines
single-transaction
max-allowed-packet=1G

[deitch]

That is because mysql-backup (v1.0.0+ versions) doesn't actually use mysqldump. It is not a script, but a single binary, that incorporates mysql functionality to create the dump.

If there is functionality missing, definitely open to adding it; that is how it has grown to this point. And Pull Requests obviously are much appreciated.

[dabico]

Thank you for the fast answer! 😄

I've never programmed in Go, but I'll take a glance at the code and give it a try. Always wanted to learn the language too.

[deitch]

Sure thing. Feel free to ask questions here.

[francescotau]

@deitch Regarding the single-transaction command, how does mysql-backup behave when used in a docker-compose setup alongside a running MySQL instance? Is data consistency in the backup guaranteed, or can there be data inconsistencies if write access to the database occurs during the backup process? Thank you.

[deitch]

used in a docker-compose setup alongside a running MySQL instance

Docker vs not docker should not impact it.

Regarding the single-transaction command, how does mysql-backup behave

As of now, the backup pretty much always does something like:

1. Start a transaction, with transaction options ReadOnly and RepeatableRead
2. lock tables using LOCK TABLES TABLE1 READ, TABLE2 READ, ... (except for Percona, where it does LOCK TABLES FOR BACKUP)
3. do the dumps
4. UNLOCK TABLES

That is mostly equivalent to single-transaction.

I don't see why we could not support other types of database management/locking/etc., as long as someone can describe really clearly how they are implemented.

[francescotau]

@deitch Thank you for clarifying how mysql-backup handles locking.

This might be off-topic: do you have any performance benchmarks showing how mysql-backup behaves with large databases? Something like:

• Performance metrics for different database sizes (10GB, 100GB, 1TB+)
• Whether blocking time scales linearly with database size
• Recommendations for minimizing impact in high-availability environments.

Thank you.

[deitch]

Nope, I do not. I wouldn't mind having them, but that level of DBA (it really is deeper than that) is not my expertise. It is a general "how to optimize backups" question. I know people who do that, but I do not.

[cyberlink0101-commits]

I get that this does not use mysqldump, but not backing up the stored routines (procedures/functions), triggers, or scheduled events, means your creating a backup that in my case will not work when restored.

Sure the raw db data is there but without the routines, triggers, and events, the site will not come back up.

Im wondering if others out there think they are getting a working backup because they have not yet tried to restore it.

[deitch]

not backing up the stored routines (procedures/functions), triggers, or scheduled events, means your creating a backup that in my case will not work when restored

I agree that would be an issue, if it does not. But mysql-backup does have the ability to back those up, if --routines is set.

Can you give as simple reproduction case?

[github-actions]

This issue has been automatically marked as stale due to inactivity.