1

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 [email protected] 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

4
  • This question may be better suited for serverfault.com.
    – nhinkle
    Sep 13, 2010 at 22:01
  • @nhinkle I don't agree... I think it would fit here as well.
    – Pylsa
    Sep 13, 2010 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, 2010 at 22:54
  • @nhinkle Okay! =)
    – Pylsa
    Sep 14, 2010 at 22:26

2 Answers 2

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"

4
  • 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, 2010 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, 2010 at 23:01
  • 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. ` Mar 11, 2011 at 7:02
0

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.

1
  • 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, 2010 at 22:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .