Help on writing Linux script to backup and ftp files to another server

Question

I am using MySQL as the database system for my application on a Linux system. Every week I update the system and take backups (mysqldump) of the databases changed (2 databases). I then .tar.gz them and ftp the resulting file to a remote server, after which I remove the original backups and tar.gz files from the Linux server. Being a complete novice when it comes to Unix systems, I would like to know if it is possible to write a script which would do all this automatically, i.e. perform the following steps.

1) Backup database A to A.sql (mysqldump) 2) Backup database B to B.sql (mysqldump) 3) tar -cvzf dest.tar.gz A.sql B.sql 4) ftp dest.tar.gz to ftp@remoteserver.com 5) Delete A.sql, B.sql, dest.tar from local server

It would be great if I could get some steps in the right direction! Thanks in advance, Tim

Answer 1

Below is a basic example. There are a lot of examples for performing mysqldump scripts, I just pasted in a quick one.

#!/bin/bash -e
# 

#Define dumpfile name
MYSQLBACKUP= /tmp/dbackup

#Perform MySQL dump
#Replace appropriate USER, PASSWORD and paths to mysqldump, socket, etc.
#Drop in you preferred method - tar versus gzip, etc.
/usr/bin/mysqldump --all-databases -S /tmp/mysql.sock -uUSER -pPASSWORD | gzip -c > $MYSQLBACKUP

#FTP Backup file to remote FTP server
#Replace 192.1681.1.1 with IP address of remote server
#Replace USER & PASSWORD
echo "open 192.168.1.1 
      user USER PASSWORD  
      verbose 
      cd /some/directory/on/remote/server
      bin 
      prompt
      mput $MYSQLBACKUP
      close 
      quit" | ftp -n >> /tmp/somelogfile

#Remove the file post transfer.  Need to be sure it was ftp'd first
rm -f "MYSQLBACKUP"

Answer 2

do you have ssh on the remoteserver.com ? - if so it'd be easier to script using scp from the ssh suite of tools, and using an ssh key so you can login without a password. That way copying the file / doing an rsync of it is just one command.