I am able to take mysql dump with restic .however the snapshots stored in my local keeps on growing day by day…It would be great If I am able to rotate the backup i.e to retain the backup of last 7 days…I am able to rotate web backup taken with the help of tag…but found no luck with sql dump.I followed this doc https://www.hostwinds.com/guide/creating-mysql-backups-restic/ to setup restic Mysql dump…but it doesnot tell about any rotation policy
1 Like
@fd0 Thankyou for the response. yes…I refered the doc but found no luck with deleting the sql files…I am able to delete the web backups using above doc.
Using this command dumping db
mysqldump $DBs | restic backup --stdin --stdin-filename $DBs-dump.$(date +%Y-%m-%d).sql --tag=mysql_dump
Try to delete the sql file with the help of tag mysql_dump using below command
restic forget --keep-within 7d --prune --keep-tag mysql_dump
But the above command didnot delete the sql files…It would be greate if some one assist me with this.
Perhaps the problem is that you tell restic to keep the snapshots that has the tag “mysql_dump”? Looking at your backup command, probably every snapshot has that tag, and if you then tell restic to keep all snapshots that have that tad, you will of course never forget any snapshots.
1 Like
@rawtaz thats valid point but i have give keep tag for only 7 days rite? I really dont know how to rotate the backups with mysql files…It will be appreciated if you guide me to resolve my issue.
Eventhough I gave the below command it still list all the snapshots.
restic forget --keep-within 7d --prune
Please paste the output of restic snapshots before and after the prune command.
@rawtaz before running the prune command I am getting the snapshot list.I am giving here the sample one
352b9ec0 2019-11-27 02:00:46 server name mysql_dump /path to db_dump/db_backups/daily/mysql-dump.2019-11-27.sql
2257336b 2019-11-27 02:00:48 server name mysql_dump /path to db_dump/db_backups/daily/sys-dump.2019-11-27.sql
829d6bb8 2019-11-27 02:00:50 server name mysql_dump /path to db_dump/db_backups/daily/testdb-dump.2019-11-27.sql
------------------------------------------------------------------------------------------------------------------------------------
115 snapshots
listing the number of snapshots.After executing the prune command I am getting following output, here I am giving the sample output
---------------------------------------------------------------------------------------------------------------------------------------
54b5db78 2019-10-28 02:00:44 server name mysql_dump within 7d /backup/restic-backup/db_backups/daily/sys-dump.2019-10-28.sql
---------------------------------------------------------------------------------------------------------------------------------------
1 snapshots
keep 1 snapshots:
ID Time Host Tags Reasons Paths
-----------------------------------------------------------------------------------------------------------------------------------------
cdbdbb45 2019-11-06 02:00:47 server name mysql_dump within 7d /backup/restic-backup/db_backups/daily/mysql-dump.2019-11-06.sql
-----------------------------------------------------------------------------------------------------------------------------------------
1 snapshots
keep 1 snapshots:
ID Time Host Tags Reasons Paths
-----------------------------------------------------------------------------------------------------------------------------------------------
22aa8180 2019-11-07 02:00:40 server name mysql_dump within 7d /backup/restic-backup/db_backups/daily/testdb-dump.2019-11-07.sql
-----------------------------------------------------------------------------------------------------------------------------------------------
1 snapshots
keep 1 snapshots:
ID Time Host Tags Reasons Paths
---------------------------------------------------------------------------------------------------------------------------------------
b4be7b37 2019-11-06 02:00:49 server name mysql_dump within 7d /backup/restic-backup/db_backups/daily/sys-dump.2019-11-06.sql
---------------------------------------------------------------------------------------------------------------------------------------
1 snapshots
keep 1 snapshots:
ID Time Host Tags Reasons Paths
-----------------------------------------------------------------------------------------------------------------------------------------------
bd80e2ca 2019-11-04 02:00:40 server name mysql_dump within 7d /backup/restic-backup/db_backups/daily/testdb-dump.2019-11-04.sql
-----------------------------------------------------------------------------------------------------------------------------------------------
1 snapshots
The cause of your problem is that you are letting restic group by paths. Please read https://restic.readthedocs.io/en/latest/060_forget.html#removing-snapshots-according-to-a-policy , in particular this paragraph:
When `forget` is run with a policy, restic loads the list of all snapshots, then groups these by host name and list of directories. The grouping options can be set with `--group-by` , to only group snapshots by paths and tags use `--group-by paths,tags` . The policy is then applied to each group of snapshots separately. This is a safety feature.
In your case, you are not giving a specific --group-by argument, which means restic will group the snapshots by the default of host and then paths:
-g, --group-by string string for grouping snapshots by host,paths,tags (default "host,paths")`
Since the path is always different (due to you having the date in the filename) every snapshot will be in its own group. This, combined with the fact that restic always keeps at least one snapshot in each group, means that you will never get rid of that last snapshot in each group, i.e. in every backup run.
So, I think adding --group-by host,tags or even just --group-by tags will solve your problem.
NOTE: Very important: Since you didn’t paste the entire list of snapshots, I cannot tell if you have other snapshots that you might not want to affect in the way that will happen if you follow my suggestion. I cannot promise you that you will not have undesired side effects. But if you only have these database backups in that repository, it’s probably safe to try it.
3 Likes
Note that you can reduce the size of the backups by compressing them first with gzip --rsyncable. (The --rsyncable flag creates files whose chunks can be more easily deduplicated even if data in the middle of the file is changed.)
3 Likes
@rawtaz Thanks a Lot…That saves my time. I used the below command to retain the last 7 days backup and it exactly do the task what I really want.
restic forget --keep-within 7d --group-by tags --prune