Need script to dump database, ftp file to backup server

glock19 · 05-12-2003, 01:26 PM

I want to write a script that will dump the mysql database to a file, and then ftp this file to a predefined location for backups.

Anyone know how this can be accomplished in linux?

markus1982 · 05-12-2003, 02:30 PM

Oh come on this is pretty easy task. First of all you need to know which database type you use your database with. If it's other than InnoDB you could use the mysqldump one ... if it's InnoDB you can't do a live backup.

Afterwards just a simple FTP ...

Do you actually want help or a script?

david_ross · 05-12-2003, 02:34 PM

How about:

Code:

#!/bin/bash

# Create an archive of the data
tar -czf /tmp/mysql-backup.tar.gz /var/lib/mysql/*

# Then use scp to transfer the file
scp /tmp/mysql-backup.tar.gz user@host:/path/to/backup/dir/

# Remove the local temp file
rm -f /tmp/mysql-backup.tar.gz

glock19 · 05-12-2003, 02:42 PM

Quote:

Originally posted by markus1982
Oh come on this is pretty easy task. First of all you need to know which database type you use your database with. If it's other than InnoDB you could use the mysqldump one ... if it's InnoDB you can't do a live backup.

Afterwards just a simple FTP ...

Do you actually want help or a script?

Ok so I can use the mysqldump command which will dump the database to a file. Then I can tar the file up.

But, how do I automate the ftp transfer? I know which ftp server I need to transfer it to, and I know the logon username/pass. By the way, it's a Windows FTP server I don't know if that would matter.

I know how to do the ftp transfer manually, but I don't know how to get the file to be transferred all in one script command

Thanks to whoever knows and can help me.

david_ross · 05-12-2003, 02:49 PM

Write an "expect" script:
man expect

glock19 · 05-12-2003, 02:55 PM

Ok, thanks. I'll work on the expect script thing.

Another question, how do I make a command that will dump the database to a file, but each time the file name is bumped by 1 (to avoid overwriting the existing backup). So for example, dump1 then dump2 then dump3 etc.

david_ross · 05-12-2003, 03:15 PM

What about:

Code:

#!/bin/bash
# Get the number from a file
count=`cat count.txt`

#do what you want with $count

# Increment the count
let newcount=count+1

# Update the file
echo -n $newcount > count.txt

glock19 · 05-12-2003, 03:51 PM

Don't think that script is what I need.

The problem is that I don't want to overwrite the database dump from the previous ftp transfer. That's why I'd like a script that will rename each new database dump to a new name, that way I won't overwrite the old file on the ftp server.

glock19 · 05-12-2003, 04:13 PM

Does anyone have an ftp script setup using expect to copy a file from your local system to the ftp server? If you do, would you please post here? Thanks.

david_ross · 05-12-2003, 04:15 PM

Quote:

Originally posted by glock19
Don't think that script is what I need.

The problem is that I don't want to overwrite the database dump from the previous ftp transfer. That's why I'd like a script that will rename each new database dump to a new name, that way I won't overwrite the old file on the ftp server.

I know - you can append $count to the filename. I was just showing you how to increment a number.

glock19 · 05-12-2003, 04:19 PM

Quote:

Originally posted by david_ross
I know - you can append $count to the filename. I was just showing you how to increment a number.

Sorry, I'm not a guru LOL. I consider myself an "experienced newbie" at best hehe.

Would you mind posting the exact command to do the appending to the actual dump file that I need? Thanks.

trickykid · 05-12-2003, 04:21 PM

May I suggest that there are really good sites on bash scripting out there you may want to check out. Cause you started out asking for help, now your just asking for scripts that others are writing for you. How do you expect to know how they work by just having others do it for you? Oh well.... http://www.google.com/linux?hl=en&lr...bash+scripting

glock19 · 05-12-2003, 04:24 PM

Ok fine, point taken. I've already learned alot about this thanks to help from kind members on this forum. But as they say "give em an inch, they take a mile" hehe. I'll go looking on my own now....thanks again for the help.

trickykid · 05-12-2003, 04:27 PM

Quote:

Originally posted by glock19
Ok fine, point taken. I've already learned alot about this thanks to help from kind members on this forum. But as they say "give em an inch, they take a mile" hehe. I'll go looking on my own now....thanks again for the help.

I didn't mean to be rude or make it sound bad on what your doing, but more of a friendly way of "if your going to have scripts, its best to understand them and what they are doing."

If you run into more specific problems with a script, ask anytime. But I think its just best to know how to do it yourself really. You'll learn more about how your system operates. You can learn alot from examples, but learn more if you can make one yourself.

Regards.

david_ross · 05-12-2003, 05:16 PM

trickykid is right but you should be able to stick the 2 examples I have given you together and get a working script with scp. The only way I know to do ftp is with expect or ftpcopy.