I have a script that I am trying to use to backup my MySQL DBs and it almost works the way I want. I will use cron to run it after I get it working correctly. I am not much at scripting so I was hoping yall might help me out.

The problem with the script is that I need a place to tell it the name or better yet names of the DBs I want it to backup. As it is it backs up all DBs on the server, this is a shared hosting account so there are lots. Of course the ones I do not have access to are blank but still, lots of files to delete.

System is BSD 4.8-RELEASE running MySQL 3.23.58

I will just paste the code here:

#!/bin/bash

#####################################
### MySQL Configuration Variables ###
#####################################

# MySQL Hostname
DBHOST='localhost'

# MySQL Username
DBUSER='root'

# MySQL Password
DBPASSWD='password'

#####################################
### FTP Configuration Variables #####
#####################################

# FTP Hostname
FTPHOST='www.example.com'

# FTP Username
FTPUSER='username'

# FTP Password
FTPPASSWD='password'

# Local Directory for Dump Files
LOCALDIR=/path/to/local/directory/

# Remote Directory for Offsite Backup
REMOTEDIR=/path/to/remote/directory/

# Prefix for offsite .tar file backup
TARPREFIX=db1

#####################################
### Edit Below If Necessary #########
#####################################

cd $LOCALDIR
SUFFIX=`eval date +%y%m%d`

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-$DATABASE.gz
mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME
fi
done

chmod 400 $LOCALDIR*.gz

tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.gz

ftp -n $FTPHOST <<END_SCRIPT
quote USER $FTPUSER
quote PASS $FTPPASSWD
cd $REMOTEDIR
put $TARPREFIX-$SUFFIX.tar
quit
END_SCRIPT

rm -f $TARPREFIX-$SUFFIX.tar

exit 0

if your database name is 'foobar' .. ( not including single quotes )

what you want to do is replace the lines:

---
for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-$DATABASE.gz
mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME
fi
done
---

with
---
DATABASE=foobar
FILENAME=$SUFFIX-$DATABASE.gz
mysqldump -u$DBUSER -p$DBPASSWORD -h$DBHOST $DATABASE |gzip --best > $FILENAME
---

Thanks sung, I made the changes but now it does nothing. Not knowing much about scripting I think I may have misunderstood what you said to do and removed to much code. Here is a snip of what the code looks like after I changed it.

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

DATABASE=db_name
FILENAME=$SUFFIX-$DATABASE.gz
mysqldump -u$DBUSER -p$DBPASSWORD -h$DBHOST $DATABASE |gzip --best > $FILENAME

chmod 400 $LOCALDIR*.gz

I took out all the code between DBS= and chmod and replaced it with what you suggested adding my db name but it seems that gets rid of some things that need to be there. Of course I don't really know it just seems that way to me.

Ok I have it working now, it was an operator error LOL.

I tried to enter more than one DB name seperated by a space hoping it would let me backup several at one time but it just gives an error.

Would that be hard to implement, being able to give it several names to backup all at once?

in the original script, replace:
--
for DATABASE in $DBS
--

with

--
for DATABASE in db1 db2 db3 db4
--

replace db1, db2, db3, db4 with your databases that you want to back up.
eg
for DATABASE in MainDB Users FooBar

would back up
MainDB, Users, FooBar

Thanks again! that was what I was needing!

it was my pleasure