1

I'm connecting to a Ubuntu Server remotely using SSH, where are x number of MariaDB databases. Now I want to set a crontab job to automatically create backup for all of them to another server where are stored back-ups from differents servers. How can I do this?

dan morcov
  • 25
  • 5

1 Answers1

1

Your task will not fit into one command.

You need a bash script. If your backup server has FTP then here is a good script . If FTP is not installed on the server, and you do not want to bring it up, then you can transfer the file via ssh using rsync.

To use rsync in a script, you need access ssh via ssh keys. If this is not configured, then it is very simple to do it:

First, generate the key:

ssh-keygen

The utility will ask you to enter the file name and passphrase (optional). To leave the default values, just press Enter.

Then, transfer the public key to the backup server:

ssh-copy-id <my-remoute-user>@<my-remoute-host>

Note, if something goes wrong, you need to receive a notification. The easiest way is to receive an email. You can do it via postfix mailutils packages. To instal tit, please execute:

sudo apt-get install postfix mailutils

Well, the script itself. As a basis, I took the script that I recommended above, adding sending a backup via rsync.

At the beginning of the script, specify the settings:

set -x
# MySQL user
user=ivan
# MySQL password
password=my-secret-password
# Local backup storage directory
local_backupfolder=mysql/backups
# Notification email address
recipient_email=my-email@gmail.com
# Number of days to store the backup
keep_day=30
# remote backup storage
remoute_backupfolder=backup-admin@my-backup-server.com:/home/backup-admin/
sqlfile=$local_backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql
#create backup folder
mkdir -p $local_backupfolder

mysqldump -u $user -p$password --all-databases > $sqlfile;


Create a backup


if gzip $sqlfile; then
   echo 'Backup compressed'
else
   echo 'gzip return non-zero code' | mailx -s 'No backup was created!' $recipient_email
   exit
fi


Delete old backups


find $local_backupfolder -mtime +$keep_day -delete


Local and remote storage sync


if rsync -avh --delete $local_backupfolder $remoute_backupfolder ; then
   echo 'Backup sended'
else
   echo 'rsync return non-zero code' | mailx -s 'No backup was sended!' $recipient_email
   exit
fi


echo $sqlfile.gz | mailx -s 'Backup was successfully created' $recipient_email

This "backup script" backups all databases on the server at once. If you need to backup only specific databases, then instead of the --all-database parameter, use the --database parameter specifying the name of the database you want to backup.

Ivan
  • 614
  • 7
  • 15