Database backups to multiple files #438

Closed
opened 2021-07-30 18:33:14 +00:00 by jorgemfm · 7 comments

Allow doing database backup to multiple files, one for each database, instead of a single file when using all option.

This is particular usefull when having to do backups of multiple large databases, to better organize and simplify restoring single databases.

Something like:

hooks:
    mysql_databases:
        - name: all-multiple
          username: user
          password: pass
Allow doing database backup to multiple files, one for each database, instead of a single file when using **all** option. This is particular usefull when having to do backups of multiple large databases, to better organize and simplify restoring single databases. Something like: ``` hooks: mysql_databases: - name: all-multiple username: user password: pass ```
Owner

This makes sense to me. But is the idea that, after this backup occurs, you'd like to be able to restore a single database manually outside of borgmatic? Or would you expect borgmatic to be able to restore a single database created in this manner? Or perhaps both?

This makes sense to me. But is the idea that, after this backup occurs, you'd like to be able to restore a single database manually outside of borgmatic? Or would you expect borgmatic to be able to restore a single database created in this manner? Or perhaps both?
Author

Manually is already a given, since the file is on text mode, you can easilly extract the files and restore it yourself.

Using borgmatic to restore a single database would be a good feature, but, at least for me, its not as critical as having the files separated, because of the size and to simplify restoration of single databases manually.

Manually is already a given, since the file is on text mode, you can easilly extract the files and restore it yourself. Using borgmatic to restore a single database would be a good feature, but, at least for me, its not as critical as having the files separated, because of the size and to simplify restoration of single databases manually.
Owner

Gotcha, thanks!

Gotcha, thanks!

Same here, it would be wonderful if there would be a difference between "all" and "all-by-database". Right now I have a bash script that iterates over /usr/bin/mysql -e 'show databases' -s --skip-column-names to dump per-db files to circumvent this, would be cool if this was a borgmatic option.

I do not see borgmatic responsible for a clean restore, just file backup and file restore, not db restore.

Same here, it would be wonderful if there would be a difference between "all" and "all-by-database". Right now I have a bash script that iterates over `/usr/bin/mysql -e 'show databases' -s --skip-column-names` to dump per-db files to circumvent this, would be cool if this was a borgmatic option. I do not see borgmatic responsible for a clean restore, just file backup and file restore, not db restore.

Giving more fuel to this request. As for now, a similar solution like @adrian's is in place, having this as an option included to Borgmatic would be great.

I do not see borgmatic responsible for a clean restore, just file backup and file restore, not db restore.

Agree on this too.

Giving more fuel to this request. As for now, a similar solution like @adrian's is in place, having this as an option included to Borgmatic would be great. > I do not see borgmatic responsible for a clean restore, just file backup and file restore, not db restore. Agree on this too.
Owner

Proposed configuration for this:

hooks:
    mysql_databases:
        - name: all
          format: sql
          username: user
          password: pass

The idea being that if a specified format is present in your configuration, then you're requesting individual files be dumped in that format instead of in a combined dump file. Similar approach would work for PostgreSQL.

I'm open to feedback on this, of course!

Proposed configuration for this: ```yaml hooks: mysql_databases: - name: all format: sql username: user password: pass ``` The idea being that if a specified `format` is present in your configuration, then you're requesting individual files be dumped _in that format_ instead of in a combined dump file. Similar approach would work for PostgreSQL. I'm open to feedback on this, of course!
Owner

This has been implemented in borgmatic 1.7.6! To enable it, add a format option to your "all" database configuration. Then borgmatic will create individual dump files per database. See the documentation for more information: https://torsion.org/borgmatic/docs/how-to/backup-your-databases/#all-databases

This has been implemented in borgmatic 1.7.6! To enable it, add a `format` option to your "all" database configuration. Then borgmatic will create individual dump files per database. See the documentation for more information: https://torsion.org/borgmatic/docs/how-to/backup-your-databases/#all-databases
Sign in to join this conversation.
No Milestone
No Assignees
4 Participants
Notifications
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#438
No description provided.