Any out of the box SQL backup solution?

Check out Borg backup:

https://borgbackup.readthedocs.io/en/stable/

-- sw

@stevewi isnt that rclone/rsync but slightly better?

Personally I use restic.

It supports plenty of cloud storage providers (including Linode’s object storage via S3) out of the box, and can also link to rclone for any others it doesn’t natively support.

The backup repo is stored completely cloud-side, so you don’t have to do anything other than scheduling the backup. Just mysqldump all databases to a folder and tell restic to backup it up. All backups (they’re called snapshots) are versioned and time-stamped so you don’t have to worry about renaming your files to have timestamps in them or clearing old backups out yourself.

You can run a separate clean up routine to keep the last X number of daily, X number of weekly, X number of monthly backups etc.

I have around 300GB worth of filesystem & SQL backups stored on Backblaze B2 going back nearly a year. It costs me around $2/month in storage.

If you can’t tell already; I love it! So simple - exactly what a backup routine should be.

Edit: don’t worry if you don’t like storing stuff on “the cloud”; it supports storing your backup repo on a local filesystem (e.g. external drive) or on another server over SSH (SFTP.)

@sonuyos --

You write:

isnt that rclone/rsync but slightly better?

rclone/rsync don't support versioning and encryption. If you're just backing up database dumps, you could use git(1). It meets all your requirements…

@andysh --

restic looks very similar to borg.

-- sw

@stevewi i actually like restic, which @andysh provided, i will first give that a try as it has native support with backblaze.

@andysh, any way i can encrypted the file with password or something? without revealing it to backblaze or anyone?

As I understand it the entire archive is encrypted with a password you set when the archive is first created.

You have to pass in the password each time you work with the archive so it is never given to Backblaze.

I store the password in a local file which is only readable by root, and pass the path to the file to restic using the “-p” option so the password isn’t visible on the command line while a backup is running.

@andysh

I am unable to use it.

restic -r RESTIC_REPOSITORY backup ~/backup/

I ran this, my SQL is in backup folder, and it went thru the process, and by the looks of it, it uploaded too, but i cannot see it in the B2.

I am unable to understand what i did wrong.

Here is the output.

enter password for repository:
repository XXXXXXXX opened successfully, password is correct

Files: 1 new, 0 changed, 0 unmodified
Dirs: 0 new, 2 changed, 0 unmodified
Added to the repo: 75.493 MiB

processed 1 files, 75.492 MiB in 0:01
snapshot YYYYYYYY saved

Its empty like this - https://prnt.sc/t4kivp

Something doesn’t look quite right with that command.

restic -r RESTIC_REPOSITORY backup ~/backup/

What is RESTIC_REPOSITORY and where is it defined?

As an example, here is mine:

restic -r b2:my-backups-bucket:mariadb -p /root/.restic-pw -q backup /var/backups/mariadb

Where you have RESTIC_REPOSITORY, I have:

b2:my-backups-bucket:mariadb

“b2” = use the Backblaze driver
“my-backups-bucket” = name of the bucket in Backblaze B2
“mariadb” - the repository is in a folder called “mariadb” within the bucket

I have a second command which backs up the full filesystem to a second repository within the same bucket called “files”:

restic -r b2:my-backups-bucket:files …

If you only want one repository, you can just reference the bucket like so:

restic -r b2:your-bucket-name backup ~/backup/