First Time MySQL Dump

Hi there I’m trying to do a Mysql dump with a combination of restic back up for the first time. Is this even possible?

restic -r /run/media/orca/DataCabinet backup mysqldump -u root -pPassword program > program.sql

Results in

unknown shorthand flag: ‘u’ in -u

I don’t get it -u is the user?

mysqldump -u root -pPassword program > program.sql
works but the backup goes to the same directory the command was ran from. This is why I wanted to add the restic command in there so the sql dump goes to my external harddrive DataCabinet.

I guess I could just do the sql dump then move the .sql file to the external hard drive however, what I want to do is make one command line to back up all directories on my server making the backup process as simple as possible.

Thoughts?

If you don’t want to have the sql dump file on your local drive, you can pipe it directly into restic. I think the restic docs provide a nice example of how to do this.

In your case, does this work?

set -o pipefail
mysqldump -u root -pPassword program | restic -r /run/media/orca/DataCabinet backup --stdin --stdin-filename program.sql

Note that this only backs-up the mysqldump output. You state that you also want to back up other directories. You can easily do that with a second call to restic. 3 lines total; admittedly not the single-line command you were hoping for, but still pretty simple!

Afaik restic doesn’t have the feature the “run the command you’d like to backup the result” like your command does. After the “backup” command, what you enter is either registered as “target file/dir to backup” (which makes restic think “mysqldump” is a file or folder you want to backup) or a parameter (which why you get the flag error, because since “restic” tries to evaluate the -u flag).

So your options are:

  • Backup with normal dump command and give the outputting program.sql (or its folder) to the restic backup command as parameter
  • Use the stdin method like @Nev suggested
1 Like

Thanks for the reminder about the documentation. That’s what attracted me to restic in the first place.

Do I just type in “set -o pipefail” first? It goes through.
When I try your command I get

read password from stdin
Fatal: wrong password or no key found
mysqldump: Got errno 32 on write

When I

mysql -u root -pPassword
I log in successfully so the syntax must be off in the command.

Also while I am here I have multiple databases, can I back all of them up at once?

Okay, so can’t I run a bashscript for this? Like

  1. Back up all databases
  2. Back up all directories
    Can I make this into one snapshot somehow? If not no problem just curious.

You can give restic its password by exporting it in the RESTIC_PASSWORD. You can also give mysql its password though a config file. The backup process shouldn’t need to prompt for passwords; manual backups get forgotten, only automatic backups are reliable.

The way we do this is to run two backups from the same btrfs snapshot: one tagged system which has all of the files, excluding database data files, and one tagged database which has SQL dumps. We do use a temporary directory for database dumps because we need a file per database and we additionally compress each file with gzip --rsyncable. This gives us compression but also allows restic’s deduplication to work.

2 Likes

Thanks for the help guys. I got the backup done however I have five snapshots which is “fine”. However, in the event I have to restore from backup which we are doing about a month as I purchased a pci-ssd 1TB harddrive (super pumped about that) I will have to look for five separate snapshots to restore. Which will be a nightmare.

> run two backups from the same btrfs snapshot:

Does this mean the backup will be one snapshot? I tried using the tags but didn’t get the result I wanted which was one snapshot if that’s even possible.

I made a bash script as follows:

#backup databases
export RESTIC_PASSWORD=(password)
set -eo pipefail
mysqldump -u root -(password) --all-databases | restic -r /run/media/orca/DataCabinet backup --tag database --stdin --stdin-filename program.sql

restic -r /run/media/orca/DataCabinet backup --tag system /home/program /etc/httpd/conf /etc/httpd/conf.d /var/www/html /var/www/cgi-bin /home/vpopmail/domains /var/qmail/control /var/qmail/users
restic -r /run/media/orca/DataCabinet backup --tag system /etc/cron.d/program*
crontab -u root -l | restic -r /run/media/orca/DataCabinet backup --tag system --stdin --stdin-filename root.cron
restic -r /run/media/orca/DataCabinet backup --tag system /var/tinydns/root/data /etc/sysconfig/network-scripts

Some wrappers around restic offer the possibility to define scripts which are executed beforehand.
I use restaround, and the pre script looks like

dump_db() {
cd /var/lib
test -d sqldump || mkdir sqldump
cd sqldump
mysqldump --single-transaction --user=$2 --password="$3" $4 | grep -v 'Dump completed on ’ > $1.dump 2> >(grep -v ‘can be insecure’)
}

dump_db wordpress xxx xxx xxx
cd /var/www/nextcloud && sudo -u www-data php ./occ maintenance:mode --on >/dev/null
dump_db nextcloud nextcloud ahsh0thizoox0Phap3me9aen3paeth7y nextcloud
cd /var/www/nextcloud && sudo -u www-data php ./occ maintenance:mode --off >/dev/null

1 Like