Backup same-named databases on the same host but with different ports #418
Loading…
x
Reference in New Issue
Block a user
No description provided.
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
What I'm trying to do and why
I have two instances on mysql running on my server, one for me on port 3307 and one for my friends running in a chroot on port 3308.
I want to backup both (127.0.0.1:3307 and 127.0.0.1:3308).
Steps to reproduce (if a bug)
Run two instances of mysql listening with different port. The easy way would be with docker, if you don't want to set up a chroot :
docker run --name db1 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=mypassword -e MYSQL_DATABASE=borgmatic mysql
docker run --name db2 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=mypassword -e MYSQL_DATABASE=borgmatic mysql
config.yaml in hooks section :
Actual behavior (if a bug)
Expected behavior (if a bug)
Borgmatic successfully dump both 127.0.0.1:3307 and 127.0.0.1:3307 databases
Other notes / implementation ideas
Environment
borgmatic version: 1.5.13
borgmatic installation method: pip
Borg version: 1.1.9
Python version: 3.7.3
Database version (if applicable): mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Use
psql --version
ormysql --version
on client and server.operating system and version: Debian GNU/Linux 10 (buster)
Ugh! This is a side effect of the way borgmatic namespaces database dump paths by hostname only rather than by hostname + port. The simple fix would be to add the port to the path. However that would break all existing database backup restores that don't contain the port in the path. I think solving this may require updating the restore logic to probe the path: First by hostname + port and then just by hostname.
Thank you for reporting this!
Additionally, there's this noted limitation from the docs:
Or in this case, different host + port combinations. So this limitation would have to be addressed to support this use case.
So to clarify, this issue will come up for all databases and not just MySQL DBs, maybe the issue title can be updated 😅?
Also, I checked
dump.py::make_database_dump_filename()
, it returnsos.path.join(os.path.expanduser(dump_path), hostname or 'localhost', name)
.As this includes
hostname
, shouldn't this function ensure that database backups with same database names but different hosts have different filenames, hence preventing the namespace collisions you mentioned earlier?Other than that, yes, it's clear to me that this does not use the port, so that may cause errors.
I agree.
[bug][mysql_databases] can't backup different mysql instances on same hostto Backup same-named databases on the same host but with different portsDone!
For creating a database dump, yes. For restoring, no! That's because restoration is currently done by database name:
I'm pretty sure
borgmatic/actions/restore.py:get_configured_database()
is part of the problem here.Ah okay, makes sense.
I referred the documentation and have another small doubt though, given that it only takes the database name to restore a database, what does borgmatic do when:
users
.users
.The third point, 2 databases with the same host and different port is covered by this issue of course.
In this case, if the user tries to restore the "users" database on the command-line, the first database in configuration (across all database hooks) that matches "users" is restored. This logic is all in
get_configured_database()
.Same thing as above: The first such matching database in configuration gets used.
I have a couple ideas for solving these restore issues with borgmatic:
postgresql
,mysql
, etc.), hostname, port, etc.In either of these cases, borgmatic could error if it doesn't have enough information to disambiguate. (If there are more than one matching databases based on the info provided.)
I'm of course open to other ideas as well.
I like the second point more, add additional flags to the current restore command, seems to be more user friendly as I won't have to explicitly label every database and worry about uniqueness of these labels, and command usage will also almost remain the same.
By default, if I restore the users database, it will restore the first occurence, and if I pass the hostname/hookname/portname params it will try to match for those first in an order.
Will wait for some more community feedback/other approaches before I start this though.
Sounds good!
Are there any updates on this?
I just stumbled on the same problem,
I'm running local postgres cluster, 4 instances on one host and all of them with the same db name (but each of it, actually has different data) running on different ports. And while I can sugest changing db_name and make each db_name different, I'm looking in ways to fix it from borgmatic side first.
Hey, I started work on some other issues so I could not get a chance to look at this.
I have my exams soon, so I'll be back to contributing actively after the first week of May.
If someone else wants to work on this before that, feel free!
I use this hacky solution ATM to bypass this issue:
This way borgmatic "thinks" that it needs to backup to different hosts so the file conflict is not here anymore.
@diivi do you think this hack could give some problem later?
Thanks
@sy6sy2
Totally forgot about this issue.
For me I just decided to add hostnames like:
And so one (since whole 127.0.0.0/8 is localhost)
This way you can have as many DBs as you like and there shouldn't be problems with restoring them.
But yeah, seeing it resolved would be better I guess
The only problem I can foresee is that currently you have to restore by database name too. So you'll have to remember which one is
localhost
and which one is127.0.0.1
when doing aborgmatic restore
. Not a deal breaker, just maybe annoying.I agree that this issue would be a nice thing to finally fix for real.
A solution for this is implemented in main and will be part of the next release. See the documentation for this feature here: https://torsion.org/borgmatic/docs/how-to/backup-your-databases/#restore-databases-sharing-a-name
Thanks for everyone's patience!
Released in borgmatic 1.9.5!
Not sure if I'm misusing something or if this is a valid use-case:
Which results in the path
postgresql_databases/localhost/all
.I don't really have a problem right now, just wanted to mention that even hostname and port won't cover all cases. I was always wondering whether it would be possible to explicitly define the subpath, e.g.
postgresql_databases/immich
?Are you suggesting a use case where a user has a database inside a container and a database running on the host—and they both have the same name and are both listed in borgmatic's configuration? If so, then yeah, you are correct.. That use case won't be covered by the work in this ticket. What I'll recommend is if this is a use case you're interested in, then filing a new ticket would be a good way to proceed.. I'd be happy to discuss solutions there.
Thank you!