Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
This question may be better suited for serverfault.com. –  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
add comment

2 Answers

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

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

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.

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

Your Answer

 
discard

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.