How to perform actions after a database hook has completed but before the files gets backed up? #500

New Issue

Closed

opened 2022-02-16 08:22:21 +00:00 by maxiride · 3 comments

maxiride commented 2022-02-16 08:22:21 +00:00

Copy Link

What I'm trying to do and why

I want to perform actions on the mysqldump .sql file generated by the mysql hook.
Since mysqldump (the utility executed by the mysql hook) generates a consolidated single .sql file I want to execute the tool mysqldumpsplitter on the dump file to further fine tune the resulting backup.

The utility offers several ways to operate on a mysql dump, namely I want to split the single .sql into a file for each of its tabale, an action mysqldumpsplitter can peform very easily.

Expected behavior (if a bug)

1. borgmatic execute the database dump
2. a custom shell command is run
3. files get backed up in the borg format

#### What I'm trying to do and why I want to perform actions on the mysqldump .sql file generated by the mysql hook. Since mysqldump (the utility executed by the mysql hook) generates a consolidated single .sql file I want to execute the tool [mysqldumpsplitter](https://github.com/kedarvj/mysqldumpsplitter) on the dump file to further fine tune the resulting backup. The utility offers several ways to operate on a mysql dump, namely I want to split the single .sql into a file for each of its tabale, an action mysqldumpsplitter can peform very easily. #### Expected behavior (if a bug) 1. borgmatic execute the database dump 2. a custom shell command is run 3. files get backed up in the borg format

witten commented 2022-02-17 22:09:30 +00:00

Owner

Copy Link

Thanks for the question! Could you talk a little about your use case? Is it that you want to be able to restore a single table from backups without restoring the whole database? Is so, why? Do you plan to do that via borgmatic or directly with mysql via the command-line?

Unfortunately, the way that borgmatic dumps from MySQL is to stream the database dump directly from MySQL to Borg. This is for performance and disk use reasons. So there's not (currently) a good place for a third-party tool to hook in. But there might be a way to enhance borgmatic to support your use case if you tell me a little more about it.

Thanks for the question! Could you talk a little about your use case? Is it that you want to be able to restore a single table from backups without restoring the whole database? Is so, why? Do you plan to do that via borgmatic or directly with `mysql` via the command-line? Unfortunately, the way that borgmatic dumps from MySQL is to stream the database dump directly from MySQL to Borg. This is for performance and disk use reasons. So there's not (currently) a good place for a third-party tool to hook in. But there might be a way to enhance borgmatic to support your use case if you tell me a little more about it.

maxiride commented 2022-02-21 11:58:22 +00:00

Author

Copy Link

Could you talk a little about your use case?

The use case I've faced several times is to restore a subset of tables either in the original database or in a new one spun up temporarly for investigation and debugging. Restoring the whole database for such occasions very often takes unnecessary time, load and disk space.

Do you plan to do that via borgmatic or directly with mysql via the command-line?

I strongly believe (and it's also very often reccomanded) that it's better to restore a whole datatabse at a given point in time (optionally re-running binlogs in the case of MySQL) since there is no practical way to know the exact state of the database when a crash\issue happened. Restoring a subset of tables should be a manual approach only for very specific scenarios to avoid breaking relationships between tables and the like. I don't think an automatic borgmatic pathway fits here.

So the behaviour of restoring a subset of tables I expect it to be a manual pathway where one either extract or mount a repository and like a surgeon restore what's deemed necessary. Having a backup already populated with a single .sql dump file plus the single tables would enhance and streamline such flow.

---

While replying I also thought of a question, how does borgmatic behave when a mysqldump is run with the --tab option? The dump contains schema and data split in different files, does borgmatic also perform a LOAD DATA statement on behalf of the user?

---

Closing lines

Given the sporadic need and niche use case I suppose we are better off extracting\mounting an archive whith the full database dump and manually run the mysqldump splitter tool.

> Could you talk a little about your use case? The use case I've faced several times is to restore a subset of tables either in the original database or in a new one spun up temporarly for investigation and debugging. Restoring the whole database for such occasions very often takes unnecessary time, load and disk space. > Do you plan to do that via borgmatic or directly with mysql via the command-line? I strongly believe (and it's also very often reccomanded) that it's better to restore a whole datatabse at a given point in time (optionally re-running binlogs in the case of MySQL) since there is no practical way to know the exact state of the database when a crash\issue happened. Restoring a subset of tables should be a manual approach only for very specific scenarios to avoid breaking relationships between tables and the like. I don't think an automatic borgmatic pathway fits here. So the behaviour of restoring a subset of tables I expect it to be a manual pathway where one either extract or mount a repository and like a surgeon restore what's deemed necessary. Having a backup already populated with a single .sql dump file **plus** the single tables would enhance and streamline such flow. --- While replying I also thought of a question, how does borgmatic behave when a mysqldump is run with the [--tab option](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab)? The dump contains schema and data split in different files, does borgmatic also perform a `LOAD DATA` statement on behalf of the user? --- Closing lines Given the sporadic need and niche use case I suppose we are better off extracting\mounting an archive whith the full database dump and manually run the mysqldump splitter tool.

witten commented 2022-03-05 22:32:38 +00:00

Owner

Copy Link

While replying I also thought of a question, how does borgmatic behave when a mysqldump is run with the --tab option? The dump contains schema and data split in different files, does borgmatic also perform a LOAD DATA statement on behalf of the user?

I wasn't aware of this option, but my guess is that at least currently, it wouldn't work. That's because borgmatic expects mysqldump to stream a single dump file to standard output. However, you are welcome to try this option as an experiment, selecting a destination directory that you've configured to be backed up as part of borgmatic's source_directories. That would probably mean you couldn't restore those tab-separate files with borgmatic restore, but you might be able to restore them manually.

Given the sporadic need and niche use case I suppose we are better off extracting\mounting an archive whith the full database dump and manually run the mysqldump splitter tool.

Sounds good. I'll close this for now, but feel free to add any other thoughts/feedback here.

> While replying I also thought of a question, how does borgmatic behave when a mysqldump is run with the --tab option? The dump contains schema and data split in different files, does borgmatic also perform a LOAD DATA statement on behalf of the user? I wasn't aware of this option, but my guess is that at least currently, it wouldn't work. That's because borgmatic expects `mysqldump` to stream a single dump file to standard output. However, you are welcome to try this option as an experiment, selecting a destination directory that you've configured to be backed up as part of borgmatic's `source_directories`. That would probably mean you couldn't restore those tab-separate files with `borgmatic restore`, but you might be able to restore them manually. > Given the sporadic need and niche use case I suppose we are better off extracting\mounting an archive whith the full database dump and manually run the mysqldump splitter tool. Sounds good. I'll close this for now, but feel free to add any other thoughts/feedback here.

witten closed this issue 2022-03-05 22:32:38 +00:00

witten added the

question / support

label 2022-03-05 22:32:46 +00:00

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

main

spot-check

1.8.11

1.8.10

1.8.9

1.8.8

1.8.7

1.8.6

1.8.5

1.8.4

1.8.3

1.8.2

1.8.1

1.8.0

1.7.15

1.7.14

1.7.13

1.7.12

1.7.11

1.7.10

1.7.9

1.7.8

1.7.7

1.7.6

1.7.5

1.7.4

1.7.3

1.7.2

1.7.1

1.7.0

1.6.6

1.6.5

1.6.4

1.6.3

1.6.2

1.6.1

1.6.0

1.5.24

1.5.23

1.5.22

1.5.21

1.5.20

1.5.19

1.5.18

1.5.17

1.5.16

1.5.15

1.5.14

1.5.13

1.5.12

1.5.11

1.5.10

1.5.9

1.5.8

1.5.7

1.5.7.dev0

1.5.6

1.5.5

1.5.4

1.5.3

1.5.2

1.5.1

1.5.0

1.4.22

1.4.21

1.4.20

1.4.19

1.4.18

1.4.17

1.4.16

1.4.15

1.4.14

1.4.13

1.4.12

1.4.11

1.4.10

1.4.9

1.4.8

1.4.7

1.4.6

1.4.5

1.4.4

1.4.3

1.4.2

1.4.1

1.4.0

1.3.26

1.3.25

1.3.24

1.3.23

1.3.22

1.3.21

1.3.20

1.3.19

1.3.18

1.3.17

1.3.16

1.3.15

1.3.14

1.3.13

1.3.12

1.3.11

1.3.10

1.3.9

1.3.8

1.3.7

1.3.6

1.3.5

1.3.4

1.3.3

1.3.2

1.3.1

1.3.0

1.2.18

1.2.17

1.2.16

1.2.15

1.2.14

1.2.13

1.2.12

1.2.11

1.2.10

1.2.9

1.2.8

1.2.7

1.2.6

1.2.5

1.2.4

1.2.3

1.2.2

1.2.1

1.2.0

1.1.15

1.1.14

1.1.13

1.1.12

1.1.11

1.1.10

1.1.9

1.1.7

1.1.6

1.1.5

1.1.4

1.1.3

1.1.2

1.1.1

1.1.0

1.0.3

1.0.2

1.0.1

1.0.0

0.1.8

0.1.7

0.1.6

0.1.5

0.1.4

0.1.3

0.1.2

0.1.1

0.1.0

0.0.7

0.0.6

0.0.5

0.0.4

0.0.3

0.0.2

Labels

Clear labels   

bug

  

data loss

  

design finalized

  

good first issue

  

new feature area

  

question / support

  

security

  

waiting for response

No Label

bug

data loss

design finalized

good first issue

new feature area

question / support

security

waiting for response

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

2 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: borgmatic-collective/borgmatic#500

No description provided.