Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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

share|improve this question
This question may be better suited for – nhinkle Sep 13 '10 at 22:01
@nhinkle I don't agree... I think it would fit here as well. – BloodPhilia Sep 13 '10 at 22:07
It could work fine - I didn't vote to close, I was just thinking he may get better results from people who more regularly deal with this sort of thing (backing up databases, etc.). – nhinkle Sep 13 '10 at 22:54
@nhinkle Okay! =) – BloodPhilia Sep 14 '10 at 22:26
up vote 1 down vote accepted

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
echo "open 
      user USER PASSWORD  
      cd /some/directory/on/remote/server
      mput $MYSQLBACKUP
      quit" | ftp -n >> /tmp/somelogfile

#Remove the file post transfer.  Need to be sure it was ftp'd first

share|improve this answer
Hi! Thanks for your answer. I will try it out for sure. One thing...I did not understand the line: /usr/bin/mysqldump --all-databases -S /tmp/mysql.sock -uUSER -pPASSWORD | gzip -c > $MYSQLBACKUP. What does the -S /tmp/mysql.sock do? And can the mysqldump direct the backup to the gzip/tar in one step without physically creating the .sql file? Thanks! – TMM Sep 13 '10 at 22:58
you probably won't need the -S for the socket. Check the below link for a detailed example of the mysqldump command. – George Sep 13 '10 at 23:01
Thanks George...I will try that out! :) – TMM Sep 13 '10 at 23:09
I'm on Ubuntu Lucid Lynx and am getting this for ftp: ` ftp: connect: Connection timed out Not connected. Verbose mode on. Not connected. Not connected. Interactive mode off. ` – TheVillageIdiot Mar 11 '11 at 7:02

do you have ssh on the ? - 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.

share|improve this answer
Hi, thanks for your reply. The is a Windows machine so I do not know what the options available are here... – TMM Sep 13 '10 at 22:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.