Script to Dump MySQL Databases

[Chris Schreiber]

This is based on a script I posted a little while back, basically I just cleaned this up a little bit. Just copy the code and save it into a text file on your server (I called it /usr/local/sbin/dumpdatabases, but feel free to place it in any directory in your path, and call it whatever you want). Just make sure you change the list of databases at the top to reflect the ones you want to backup, and change the username and password for MySQL. Most other things should be ok, but you might need to change the pathnames if different on your system. Also make sure you do a chmod +x /usr/local/sbin/dumpdatabases to make it executable.


#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="mysql forum"

# Directory where you want the backup files to be placed
backupdir=/backup

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/local/mysql/bin/mysqldump

# MySQL Username and password
userpassword=" --user=root --password=password"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="Your MySQL Backup"
mailto="you@yourdomain.com"

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
$gzip ${backupdir}/${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${database}.sql.gz.uu
done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit


If you want to run this automatically through cron (recommended so you don't forget): just do a crontab -e and add the following line:

0 3 * * * /usr/local/sbin/dumpdatabases


This will run it everyday at 3:00am. Feel free to change the time to something else if you forums aren't as busy at some other time.

-Chris

[thefenixboy]

hello, all is working, but uuencode is not working.

when i made:

uuencode /home/path/to/database.sql.gz > /home/path/to/database.sql.gz.uu

the $bash or cmd prompt dont return.... ¿what happend?

Help me please.

Thanks....

[Chris Schreiber]

Originally posted by thefenixboy
hello, all is working, but uuencode is not working.

Is it possible it was just taking a long time? Depending on the size of your database, it could take quite a while to convert the compressed binary (.gz) to uuencode format for emailing.

-Chris

[thefenixboy]

30 minutes??? so long?
¿? for only 8MB

I try again and come back if dont work...

[Chris Schreiber]

Hummm no it should only take a few minutes at most for an 8MB file..... the command looks like it is running though?

[thefenixboy]

i try again, 1 hour and nothing, the cmd prompt dont return :(

[Lotus]

how can i start the script????

i saved it on server (xodox) 755 chmod on it

so now... how can i activate it?

thx 4 help

[Chris Schreiber]

Originally posted by Lotus
how can i start the script????

Just run it from a telnet prompt, with whatever name you gave it.

[Lotus]

but XodoX.com doesnt have telnet support :(

only SSH
does it work with it?

[Chris Schreiber]

Originally posted by Lotus
only SSH
does it work with it?

Yes, SSH is the same thing as telnet, it's just more secure since the connection is encrypted.... just SSH to the server, and type in the full pathname to the file, ex: /usr/local/sbin/dumpdatabases

-Chris

[Lotus]

must it be in a /bin/ dir ?????
or can it be in the html dir ?

[Chris Schreiber]

Originally posted by Lotus
must it be in a /bin/ dir ?????


No I can be located in any directory, it's just more secure to keep it out of the html directories, otherwise a web user could run the script if they knew the location where it was stored....

-Chris

[Lotus]

hmmm .. thx 4 your help

but :)

i have uploaded the file ...
how can i find the real path to it ..
sorry , i am out of mind today ;)

[Sharg]

I'm planning to cron this script.
Isn't it better to first turn off the mysql server or shut down vb prior to running the script ?

What happens if some one post a message or do a search right when the backup occurs ?

Thanks,
Benj

[Sharg]

Wouldn't it be possible to have this script not ereasing previous backup, and instead, add a unique name to the database backup file each time its generated (using unix time stamp) ?

Benj

[Edited by Benj on 11-14-2000 at 07:36 AM]

[TechTalk]

Originally posted by Benj

What happens if some one post a message or do a search right when the backup occurs ?

Thanks,
Benj

If any queries are sent to the database while you are dumping, copying, or adding to it, they will just remain in cue untill the previous jobs are complete. :)

~Chris

[Chris Schreiber]

Originally posted by Benj
Wouldn't it be possible to have this script not ereasing previous backup, and instead, add a unique name to the database backup file each time its generated (using unix time stamp) ?

Sure, actually I was thinking about doing this anyway, so let me work on it sometime today and I'll post an update for you :)

-Chris

[Sharg]

:) Thanks chris.

[chrispadfield]

and word on what the script will do that is coming with v2.0. I see as what it should as:

1) Backup at scheduled times, once ever 12 hours i would think.
2) Zip the backups
3) Email the newest backup off server.
4) Delete backups older than 2 weeks.

(with all variables customizable)

It should be able to run:

1) by cron job (with instructions!!!)
2) manually ie an admin link to set it off
3) if cron not avaliable then through a test like every time index.php is loaded, test to see if it has run in 24 hours if not then run it, much like some top scripts work. As people who can not run cron have no dedi server and thus not a huge board this will probably not be a significant performance problem.

[Sharg]

Chris, I've setup your script, try to cron it, and it returned this:

Output from command /var/mysqlcron

/bin/sh: /var/mysqlcron: No such file or directory

Any clue why I get this ?

Thanks,
Benj

[Sharg]

Ok, now I tried to run right from the ssh prompt:
I typed /var/mysqlcron and it returned:

bash: /var/mysqlcron: No such file or directory

How can it say so since the file script file IS there !
This is insane.

Benj

[KeithMcL]

Check out my new article on "Backing up MySQL driven forums".

It can be found here (http://www.webdevinfo.com/articles.php?articleId=14).

Enjoy !!

[eva2000]

Originally posted by Chris Schreiber


Sure, actually I was thinking about doing this anyway, so let me work on it sometime today and I'll post an update for you :)

-Chris hey chris did you have time to work on it ? :)

i bumped up another thread at - http://vbulletin.com/forum/showthread.php?s=&threadid=5386

I started to use Chris' script but would like to have the dumps with alternating dumps for the past 7 days

forum-DDMMYY.sql.gz ?

[Nicholas Brown]

<< Nick Snip >>
It works ok now :) - i iz stupid
<< Nick Snip >>

[gjgjgj]

hi,

i uploaded the file with all the correct paths.
I chmod it to be executed.. infact i gave it 777.

then on telnet i typed the name of the file (say dumpdatabase). it said command not found

then i typed ./dumpdatabase it said ./dumpdatabase command not found !! !


What could be the error ??

[eva2000]

Originally posted by gjgjgj
hi,

i uploaded the file with all the correct paths.
I chmod it to be executed.. infact i gave it 777.

then on telnet i typed the name of the file (say dumpdatabase). it said command not found

then i typed ./dumpdatabase it said ./dumpdatabase command not found !! !


What could be the error ??

do you have root access ? did you switch to root to run the command ?

did you put dumpdatabase file in your root of your server ?

[gjgjgj]

>> do you have root access ?
NO..but i dont think this is the problem... otherwise i would have got access denied error....

>> did you switch to root to run the command ?
I cannot.... I have a virtual account



>>did you put dumpdatabase file in your root of your server??
Chris said that u could put it in any directory !!

[chrispadfield]

i get:


syntax error near unexpected token `>'
/home/backup/db: /home/backup/db: line 43: ` $mysqldumpcmd $userpassword $dumpoptions $database >'


any idea???

[chrispadfield]

ok sorted that out, copying and pasting created line breaks which caused problems.


only problem is it now appears to hang when creating the .uu files. Logging in via ftp i see the .sql.gzip files and then just mysql.sql.gzip.uu which is 0 bytes.

any suggestions?

[eva2000]

what is UU for the send via email ?

[chrispadfield]

yes it apparently needs to have something changed to send it by email, that stage is not working.

I have a script i installed that allows me to directly attach things to perl messages, i installed it because i wanted the iamges uploaded using picturepost at imagefolio to be sent directly to me. I think i will set up a small perl script to email me the gziped file and then just cron it go 1 hour after the the backing up has been done. That should work.

[Dark_Wizard]

Originally posted by eva2000
what is UU for the send via email ?

The uu is for uuencoding to send thru email...

[Life_Sux]

thanks for this script
its great :)

[Surfer]

Thanks for the great script!

Everything works until it tries to uuencode the databases!
It creates the first .uu file but it contains 0 bytes.
Anyone know why it hangs here?

Thanks

[JWS]

Check out my sig for the updated rolling backup script that has the non-hanging UUencode process.

Originally posted by Surfer
Thanks for the great script!

Everything works until it tries to uuencode the databases!
It creates the first .uu file but it contains 0 bytes.
Anyone know why it hangs here?

Thanks

[Surfer]

Thanks, it works now!

My second problem is that data seems to disappear when the sql docs get gzipped! When I did a manual mysqldump the file size was around 7,8MB and when I get the gzipped docs in the mail, they are around 4.1 MB.
The file seems corrupt!!!
The other sql databases seem fine!

Any ideas to whats wrong?

Thanks

[Surfer]

hmmm, seems to work fine when I remove all the mysql extensions in the script! What extensions are neccessary to have? Am I fine without any?

Thanks

[JWS]

When the file gets gzipped, it's like running it through WinZip, so it's going to be smaller and a binary file, so you can't read it. You have to gunzip it first. Thats also why it's smaller!

Originally posted by Surfer
Thanks, it works now!

My second problem is that data seems to disappear when the sql docs get gzipped! When I did a manual mysqldump the file size was around 7,8MB and when I get the gzipped docs in the mail, they are around 4.1 MB.
The file seems corrupt!!!
The other sql databases seem fine!

Any ideas to whats wrong?

Thanks

[Byron]

Hi,

I am testing this script with the backupdir as follows

backupdir=/home/domain/public_html/vbdump/backup


The emailed file comes with a long name

homedomainpublic_htmlvbdumpvb.sql.gz


Am i on the right track?

How do we open the .gz file on Windows?

[JWS]

You can open the .gz file with WinZip. That recognizes the .gz format just fine.


Originally posted by Byron
Hi,

I am testing this script with the backupdir as follows

backupdir=/home/domain/public_html/vbdump/backup


The emailed file comes with a long name

homedomainpublic_htmlvbdumpvb.sql.gz


Am i on the right track?

How do we open the .gz file on Windows?

[Byron]

shd the file name be this long?

homedomainpublic_htmlvbdumpvb.sql.gz

my winzip is unable to open the file.

[jucs]

How do you then load it into VB?

[Flashfunk]

Originally posted by jucs
How do you then load it into VB? the script published simply gzips the output. winzip is only able to open gziped tar-archives. get back onto your shell and use:

gzip -d backupfile.sql.gz

the other way is changing the script, so it uses tar cfvz newfile.tar.gz oldfile.sql --> winzip can now open and extract these files.

greetings,
Flashfunk

[dellow]

I get this error :(



ERROR 1064 at line 5: You have an error in your SQL syntax near 'databases="onaiz1"
backupdir=/backup
mysqldumpcmd=/usr/local/mysql/bin/mysqldump' at line 1
Bye


what can i do ?

[Dave#]

Originally posted by Sharg
Ok, now I tried to run right from the ssh prompt:
I typed /var/mysqlcron and it returned:

bash: /var/mysqlcron: No such file or directory

How can it say so since the file script file IS there !
This is insane.

Benj

The shell will return not found if any of the pahs are imcorrect ie gzip or uuencode

This is the first time I have looked at this script. It's nice

Dave

[qasic]

There was a user who was looking to add a datetime stamp .. here's how I did mine:

find:


exit 1
fi


add below:


#get currentdate
today=`date +%Y-%m-%d`


find:


$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql


and change to:


$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}-$today.sql

You can change the date format to whatever the date format allows you to do. Run "man date" in the UNIX prompt to get more details.

qasic

[DarkReaper]

In another thread about this someone mentioned the possibility of getting a corrupt database if a write is made to the DB while the backup is in progress.

In this thread it was said that any writes would be queued.

Which is true?

[qasic]

False. MySQLDump as configured here, locks each table as it access it so no other processes can access it until it's done.

qasic

[rewen]

Here's a reloader script. read my new post for a little info

[Jet]

Hi. I used successfully the script you wrote (modified with the timestamp)...
I have - as said before by someone - a lot of problems with the uuencoding function (it take a lot of time and seems idle with a 0-byte .uu file wrote and not more....), so I had to disable the email function.

Well, this is the DUMMY question: as my host provider sends an email of the output ('echo ..... ' etc.) of the .sh croned script, is there any way to know - within the same script - if it was runned by the telnet command line or the daemon ?
(instead of checking the running time, not too generic check, which I'm not able to do too .... ;) )

Thanks a lot.
Bye

[TurboFC3S]

So why would I get this when I run the script?

bad interpreter: No such file or directory

Also, since I guess this is as close to the official backup script as there is, are there plans to extend it? Mainly need the ability to set how many backups to keep. Also should be able to turn gzip on or off for large databases. gzip'ing a 800 meg database isn't a good idea.

Something with some options would be much appreciated.

[eva2000]

Originally posted by TurboFC3S
So why would I get this when I run the script?

bad interpreter: No such file or directory

Also, since I guess this is as close to the official backup script as there is, are there plans to extend it? Mainly need the ability to set how many backups to keep. Also should be able to turn gzip on or off for large databases. gzip'ing a 800 meg database isn't a good idea.

Something with some options would be much appreciated. how are you running it ? check to see if the path to mysqldump and other commands is correct for your server

for me i just run 2 or 3 instances of the cron backing up every 12 hrs 1.9GB of databases

1 set for morning backup to /morningbackup directory for vB only database
1 set for night backup (12 hrs apart) to /nightbackup directory for vB only database
1 set for backup every 24 hrs for non-vb databases such as for phpadsnew/guestbooks, helpdesk support scripts, polls etc

[TurboFC3S]

I'm pretty sure I had my path to mysqldump correct ... I was just trying to run it from the command line logged in as root.

And why didn't I think of that? Just setup a couple different cron jobs that backup to different dirs :D

[TurboFC3S]

Ok, I can't figure out while I'm still getting the bad interpreter: No such file or directory error.

Script is executable, it has #!/bin/sh at the beginning. But it won't run. I don't get it?

Ryan

[eva2000]

Originally posted by TurboFC3S
I'm pretty sure I had my path to mysqldump correct ... I was just trying to run it from the command line logged in as root.

And why didn't I think of that? Just setup a couple different cron jobs that backup to different dirs :D that's exactly what i do :) backup every 12 hrs :D

[Jet]

Originally posted by TurboFC3S
Ok, I can't figure out while I'm still getting the bad interpreter: No such file or directory error.

It means that somewhere in the script code a file or directory referenced doesn't exist, not the script itself isn't executable.
Try and split the script in smaller parts to find the extact point, or execute step by step all the instructions within from the shell.

Bye

[TurboFC3S]

Originally posted by Jet

It means that somewhere in the script code a file or directory referenced doesn't exist, not the script itself isn't executable.
Try and split the script in smaller parts to find the extact point, or execute step by step all the instructions within from the shell.

Bye

How about I just put a set -x at the top of the script :D

[TurboFC3S]

Originally posted by TurboFC3S
How about I just put a set -x at the top of the script :D

Hmmm ... that gained me nothing. I'll keep working.

[TurboFC3S]

Weirdness ... put an export in front of all the variable declarations and it works now. Don't know why the vars would need to be exported, but in my case they do.

[Tungsten]

First, visit www.cmdtools.com and download their NameDate (http://www.cmdtools.com/namedate.zip) utility. It's a free command-line program that renames a target file by appending today's date. Pretty slick.

Drop the namedate.exe file into a directory that is in your server's PATH statement. c:\winnt or something similar would be great.

Next, write a small batch file that does the MySQL dump commands to export your database and tables to a "vault" (that's what I call my directory for stuff like this) directory and then runs the namedate program to rename it.

My batch file is called "mysql_backup.cmd" and I placed it in my d:\mysql directory for the sake of convenience.

My "vault" directory is located at d:\vault

So my batch file reads as follows:


@ECHO OFF
D:
cd\mysql\bin
mysqldump --user=root --password=secret --quick --add-drop-table --add-locks --extended-insert --lock-tables forums > d:\vault\forums_backup.sql

cd\vault
namedate forums_backup.sql
:end


Line by line it does the following:

Turns off console echo

Ensures that we are on the D: drive (where my installation of MySQL is)

Switches to the d:\mysql\bin directory where the mysqldump program resides

Runs the mysqldump program against the "forums" database (the actual name of the MySQL database, highlighted above in red) and pipes the output file into the d:\vault directory as a file named forums_backup.sql

Runs the namedate program against forums_backup.sql so that it ends up being named something like forums_backup 07-Feb-2002.sql



I then went into the system task scheduler and set the mysql_backup.cmd batch file to be run every night at 12:01am under the local administrator's account.

Works like a champ, although not as elegant as the one that Chris Schrieber has provided the *Nix guys. ;)

I may sit down and re-write this in Visual Basic script so that it can be more easily modified by other users. In the meantime, I wanted to provide this short work-around for others who might be wondering how to tackle this problem on a Win32 server.

[Marshalus]

No offence, but that needs to be in it's own thread. A windows backup is an entirely new can of worms.

[Tungsten]

Originally posted by Marshalus
No offence, but that needs to be in it's own thread. A windows backup is an entirely new can of worms.

If the moderators feel that it needs to be in it's own thread, they can certainly move it. Of course, if you will read back toward the first of this thread you will see that someone specifically asked for details on doing this on a Win32 platform.

So, as such, I fail to see how this is irrelevant to the topic at hand. <shrug>

Again, the powers that be can move it if they wish. However, I would imagine that other Win32 server folks (like me) could have subscribed to this thread just in case someone had something that pertained to their platform...

[be-hosted]

How would I go about using FTP to transfer the files instead of email. Would it be better to set up a seperate cron job to do this?

[eva2000]

Originally posted by be-hosted
How would I go about using FTP to transfer the files instead of email. Would it be better to set up a seperate cron job to do this? Please enter your vBulletin.com forum username on the form on this page to receive support for this issue: http://www.vbulletin.com/members/forums.php
- you will need your vB license customer number and password to access this form.

[be-hosted]

I am actually not a vBulletin customer, but rather I was pointed in this direction by a post on the webhostingtalk forums.

If I should not post, just let me know. I did however make some simple edits to get this to work last night for those who are interested.

I modified the script above to include the following directly above the email portion of the script. I am actually doing this from memory, but I think it is pretty close. For simplicity, I have chosen to ftp all files in the /backup directory, but you could modify it to just transfer certain files. You can also add a mv command to archive backup files, but I have chosen to currently just overwrite the files each night.



# Code for FTPing backups
# Be sure to replace relevant information with your own
ftp -n 123.345.456.677 <<EOF
user USERNAME PASSWORD
bin
prompt
mput /backup/*
EOF



Hope this helps someone.

Best Regards

[kigiweb]

hey
this is the first time i setup a vbb.......before i used to setup ik,phpnuke.
so i hope i can know more about vbb here
please help me
love:kigi

[Steve Machol]

kigiweb,

Please follow the instructions in the last line of my sig to get support on these forums. Thanks.

[b-b00gie]

My forum is on a dedicated sql server, how can I modify this to connect to it?


I dont have shell access to the sql server, only the webserver.

[eva2000]

Originally posted by b-b00gie
My forum is on a dedicated sql server, how can I modify this to connect to it?


I dont have shell access to the sql server, only the webserver. wouldn't you just install this script on the mysql server itself ?

have you tried from web server type

telnet mysqlserver's ip address

[vicos]

I took the original script and melded it with the various suggestions and code snippets posted thereafter and came up with this. This varies from the original script as follows:

- Option to FTP files to an off-site server
- backup files are prefixed with the numberic value of the day of the week, e.g. 0-mydbname.sql.gz where 0 represents Sunday. This is a quick way to ensure that you have a backup for the last 7 days. Old files are overwritten as the week rotates out.

=========================


#!/bin/sh

# This script will backup one or more mySQL databases
# and then optionally email them and/or FTP them

# This script will create a different backup file for each database by day of the week
# i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)
# This is a trick so that you never have more than 7 days worth of backups on your FTP server.
# as the weeks rotate, the files from the same day of the prev week are overwritten.

################################################## ##########
#===> site-specific variables - customize for your site

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="mydbname"

# Directory where you want the backup files to be placed
backupdir=/home/mydomain/backups

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=myuser --password=mypasswd"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="mySQL Backup"
mailto="me@mydomain.com"

#===> site-specific variables for FTP
ftpbackup="y"
ftpserver="myftpserver.com"
ftpuser="myftpuser"
ftppasswd="myftppasswd"
# If you are keeping the backups in a subdir to your FTP root
ftpdir="forums"

#===> END site-specific variables - customize for your site
################################################## ##########

# Get the Day of the Week (0-6)
# This allows to save one backup for each day of the week
# Just alter the date command if you want to use a timestamp
DOW=`date +%w`

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${DOW}-${database}.sql.gz
$gzip ${backupdir}/${DOW}-${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu
done
fi

# FTP it to the off-site server
echo "FTP file to $ftpserver FTP server"
if [ $ftpbackup = "y" ]
then
for database in $databases
do
echo "==> ${backupdir}/${DOW}-${database}.sql.gz"
ftp -n $ftpserver <<EOF
user $ftpuser $ftppasswd
bin
prompt
cd $ftpdir
lcd ${backupdir}
put ${DOW}-${database}.sql.gz
quit
EOF
done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit

[Ralf]

I´ve tried to install this cron-job:

--
#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="DB001 DB0001 GS001"

# Directory where you want the backup files to be placed
backupdir=/bak_hdb4/backups

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=***** --password=*******"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip


# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
$gzip ${backupdir}/${database}.sql
done


# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit

But every time i get this log-entry:

---
020811 4:15:25 Aborted connection 4010 to db: 'DB001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4024 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4001 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4023 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4009 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4011 to db: 'DB001' user: 'admin' host: `localhost' (Got an error reading communication packets)
---

Can anybody help?

[Dave#]

Originally posted by Ralf
I´ve tried to install this cron-job:

--
#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="DB001 DB0001 GS001"

# Directory where you want the backup files to be placed
backupdir=/bak_hdb4/backups

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=***** --password=*******"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip


# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
$gzip ${backupdir}/${database}.sql
done


# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit

But every time i get this log-entry:

---
020811 4:15:25 Aborted connection 4010 to db: 'DB001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4024 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4001 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4023 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4009 to db: 'GS001' user: 'admin' host: `localhost' (Got an error reading communication packets)

020811 4:15:25 Aborted connection 4011 to db: 'DB001' user: 'admin' host: `localhost' (Got an error reading communication packets)
---

Can anybody help?

Looks like your maxing out Mysql when doing a dump - letmeguess shared server?

[eva2000]

http://www.mysql.com/doc/en/Communication_errors.html

most likely either your connect_timeout or max_allowed_packet mysql variable is too low or too small

ask your web host what they're set at for your server

or if you have your own server and root access type in shell as

mysqladmin -u root -p variables

it will prompt you for mysql root password and output your variables/config settings for mysql

fine the value for connect_timeout and max_allowed_packet

[Ralf]

No, it´s my own Server, Sun Cobalt RaQ4.

Here are the values you asked for.


---------------------------------------------
Cobalt Linux release 6.0 (Shinkansen-Decaf)
Kernel 2.2.16C28_III on an i586

|
| binlog_cache_size | 32768

|| connect_timeout | 5 |

| max_allowed_packet | 1048576
----------------------------------------------

Do I have to change this?

If yes, how do I change?

The database-dump is uncompressed about 70MB large.

The dump with the Control-Panel does work fine.

Thanks, Ralf

[eva2000]

as root user in telnet/shell create a file named my.cnf at /etc/my.cnf and place in it the following and then restart mysql


[mysqld]
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=512
set-variable = thread_cache_size=128
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[Ralf]

Thanks a lot, now it works fine.

[eva2000]

Originally posted by Ralf
Thanks a lot, now it works fine. no probs ... in future might want to start a new thread ;)

[Ph0eniX]

Great script! ...Thanks!

[LancerForums]

Hi,

I just setup this script and it's great! I'd like to modify it a little bit though. I have a lot of images stored in directories instead of the db. How could I go about taring my entire www site folder so that I maintain all pages and images as well? I don't know perl at all so I'm not sure how add this little addition.

Also, from my web stats it seems like traffic slows around 6-7PM:

http://www.evolutionm.net/usage/usage_200209.html#HOURSTATS

I think my server time is off, but assuming this is correct, how would I set my crontab to run every sunday night at 6 or 7PM?

Thanks,

Mark

[Ciaran]

I knocked up a simple script which zips up our entire site:


###
#backup-site.bash
###

datestamp=`date +%C%y%m%d`
zip -9 -y -rn .jpg:.gif:.Z:.zip:.gz /backups/backup-$datestamp.zip /home/www/*


You would change the '/home/www/' directory to whatever directory you want the backup to start in, and the /backups directory to whatever directory you will be writing out to.

to run it in cron as per your time, you'd have the following cron entry (change the 18 to 19 if U need 7PM):


00 18 * * 0 /home/scripts/backup-site.bash 1>/home/scripts/backup-site.log 2>&1


Remember to change '/home/scripts' to the actual location of the backup-site.bash script on your server.

Forgot to mention, if the zip command doesn't work on your server, try changing it to gzip.

[SkuZZy]

What do I save the file as?

[Ciaran]

Originally posted by SkuZZy
What do I save the file as?

Don't know if you are asking me or not, but in my example the UNIX script is saved as backup-site.bash in the directory /home/scripts .

This is reflected in the example cron entry, which you should change to suit your own particular setup.

ie

change '/home/scripts/backup-site.bash' to be '/yourpath/yourscriptname'

change '/home/scripts/backup-site.log' to where you want to store the log file

[strokez]

thanks man...this helped a lot..appreciate it

[RandomLove]

Excellent script.

How do I check the server load before I execute the script?
I have 50MB forum, I don't want the script to run if CPU load is above 10 for example.

take care

R.L.

[floleb7]

i get this error : bad interpreter : no such files or directory

but if i give 644 as chmod i get : permission denied
so the file exist

what can i do :confused:

[floleb7]

ok now i can access to my file but i have this message :

line 41: syntax error near unexpected token `do


# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done


do is on line 41

[cirisme]

i get this error : bad interpreter : no such files or directory

but if i give 644 as chmod i get : permission denied
so the file exist

what can i do :confused:
I was having similar problems, but it tunrs out my FTP program was uploading the file in Binary. When I set it to ASCII, it worked fine :)

[trackpads]

I am also getting this error-


[trackpads@trackpads trackpads]$ bash backupdbase
: command not found
: command not found
: command not found
: command not found
: command not found
: command not found
: command not found
: command not found
'ackupdbase: line 41: syntax error near unexpected token `do
'ackupdbase: line 41: `do
[trackpads@trackpads trackpads]$

[Dave#]

I am also getting this error-


[trackpads@trackpads trackpads]$ bash backupdbase
: command not found
: command not found
: command not found
: command not found
: command not found
: command not found
: command not found
: command not found
'ackupdbase: line 41: syntax error near unexpected token `do
'ackupdbase: line 41: `do
[trackpads@trackpads trackpads]$


post the output of

ksh -x scriptname.sh

[trackpads]

Here you go- Thanks again-

[root@trackpads trackpads]# ksh -x scriptname.sh
bash: ksh: command not found

or

[root@trackpads trackpads]# ksh -x backupdbase.sh
bash: ksh: command not found

[Dave#]

Here you go- Thanks again-

[root@trackpads trackpads]# ksh -x scriptname.sh
bash: ksh: command not found

or

[root@trackpads trackpads]# ksh -x backupdbase.sh
bash: ksh: command not found


sorry - I meant

sh -x backupdbase.sh

[trackpads]

Here you go, thanks again-


[trackpads@trackpads trackpads]$ sh -x backupdbase.sh
backupdbase.sh: backupdbase.sh: No such file or directory
[trackpads@trackpads trackpads]$ sh -x backupdbase
+
: command not found
+ databases=forums forum photopost
+
: command not found
+ backupdir=/home/trackpads/databases
+
: command not found
+ mysqldumpcmd=/usr/local/mysql/bin/mysqldump
+
: command not found
+ userpassword= --user=trackpads --password=track
+
: command not found
+ dumpoptions= --quick --add-drop-table --add-locks --extended-insert --lock-tables
+
: command not found
+ gzip=/bin/gzip
+ uuencode=/usr/bin/uuencode
+ mail=/bin/mail
+
: command not found
+ sendbackup=n
+ subject=Your MySQL Backup
+ mailto=you@yourdomain.com
+
: command not found
+ mkdir -p /home/trackpads/databases
'ackupdbase: line 41: syntax error near unexpected token `do
'ackupdbase: line 41: `do
[trackpads@trackpads trackpads]$

[trackpads]

Any idea? Thanks again,

-Jason

[Dave#]

Any idea? Thanks again,

-Jason


the shell isn't reading your script correctly - assigning variables and what not

are you using a funky OS or shell? C shell perhaps?

make sure all the variables actually exist

example check that is path is correct

mysqldumpcmd=/usr/local/mysql/bin/mysqldump

[godawgs]

I get this error... /bin/sh: 0: command not found
any idea why guys?

[Dave#]

I get this error... /bin/sh: 0: command not found
any idea why guys?


type

which sh

[DrExploit]

that´s my own, and worked faster.


$rip_start = 0; // begin leeching from this position (0 is o.k.)
$rip_end = 50000; // leech how many passes?
$order_by = "ASC"; // possible: ASC and DESC
$timeout = 0; // timeout in seconds, 0 for no timeout (recommend)
$file = ""; // filename the passwords will be saved into

$mysql_host = ""; // mysql host of
$mysql_user = ""; // mysql username
$mysql_pass = ""; // password...
$mysql_dtbs = ""; // database, was it? ;P
$mysql_tble = ""; // tablename where the logins are stored



/* DON'T EDIT BELOW THIS LINE WHEN YOU DON'T KNOW WHAT YOU DO! */


ini_alter("max_execution_time", $timeout);

$mysql_conn = @mysql_connect($mysql_host, $mysql_user, $mysql_pass);
if($mysql_conn == false) {
echo "MySQL setting are wrong, I couldn't connect to <b>".$mysql_host."</b> (".$mysql_user.":".$mysql_pass.")";
exit;
}

if(!@mysql_select_db($mysql_dtbs, $mysql_conn)) {
echo "Couldn't select database <b>".$mysql_dtbs."</b>.";
exit;
}

echo "Connected to <b>".$mysql_host."</b> (".$mysql_user.":".$mysql_pass.")<br>\n";

$command = "SELECT user,pass FROM ".$mysql_tble." ORDER BY user ".$order_by." LIMIT ".$rip_start.", ".$rip_end;
$result = mysql_query($command);
$records = mysql_num_rows($result);

echo "Got <b>".$records." records</b> from MySQL<br>\n";
if ($records == 0) {
echo "<b>Aborting...</b><br>\n";
exit;
}

if (!is_writeable($file) && file_exists($file))
{
echo "Cannot open <b>".$file."</b>, I'm gonna print the results here.";
$output_browser = true;
}

if (!$output_browser)
{
$fp = fopen($file, "w");
fwrite($fp, "Created this file with named.php by Doc in 2003\n\n");
while ($current = mysql_fetch_array($result))
{
fwrite($fp, $current['user'].":".$current['pass']."\n");
}
fwrite($fp, "\n\nEOF");
fclose($file);
echo "Created <b>".$file."</b>, go and check it out.<br>\n";
}
else
{
while ($current = mysql_fetch_array($result))
{
echo $current['user'].":".$current['pass']."<br>\n";
}
}

echo "<p><b>All done.</b>";

?>

[godawgs]

What do you mean?

[godawgs]

dr exploit, for "tablename where the logins are stored" what is that again? i got everything else.

[SilentDeath]

when trying to run the original script (i named it mysqldump.sh) it always says ": bad interpreter: No such file or directory"
but when i type in the script line by line in the shell, everything works fine.
does anyone know why?! :confused:
every path in my script is correct, and when executing it line by line everything works fine. but when running the script itself or from a cronjob nothing happens :(
i'm logged in as root.
i only want 1 database to be dumped, i left all variables and loops away, just typed in the most important functions (only mysqldump [, gzip]) with correct paths, and nothing happens :(
can anyone please help me?
btw: i'm using suse linux 9.0 pro ...

[vicos]

The first line in the file tells the system which command shell to use to process the commands in the script:

#!/bin/sh

These commands are written for "sh". sh on your system may not be in /bin. To find out where it is, login using telnet or SSH and type "which sh" or "whereis sh" at the command prompt and it will give you the path. Then update the script file to use the path for your system. Make sure there are no extra spaces in that line.

Also make sure you have the script file set for execute priv via "chmod 755 script.sh" or probably even better "chmod 750 script.sh" or whatever you call your script filename.

If you run the script from the command line, you usually have to prefix it with "./", e.g.: ./script.sh

If you want to run the script from cron, you have to give the complete path to the script:

* 1 * * * /home/vhosts/mydir/backups/script.sh

[SilentDeath]

hi vicos!
thanks for your quick answer!
but...
it's not the first shell-script i run on that machine. i have set the hash-bang-line correctly, my "sh" in /bin/sh is a link to /bin/bash, but that doesn't matter! even when i set chmod to 777 the error appears!
and "unfortunately" the problem is not, to execute the script ;)
i don't have to login via ssh or telnet due to having my linux-computer at home...

yesterday i've tried another script, a php-script that dumps a mysql-database as well. it works rather good, but i'd like to backup my data via mysqldump... (less file size)

if anyone has an idea why i get ": bad interpreter: No such file or directory", please tell me! :confused:

btw: every path in the script is absolute, and i'm logged in as root...

[vicos]

I've also seen the problem if you upload the script via FTP in bin mode. Try uploading it again and force it to use ASCII xfer.

[SilentDeath]

I'm working on that Linux PC, so I don't have to upload in ASCII...

But now I have found a way to call mysqldump:
In a shell-script I open a PHP-script in which I call mysqldump via the exec()-command:
exec("$mysqldumpcmd $userpassword $options $database > $backupdir/$database$date.sql");
It works very well, so my problem is solved.

Thank you for your help! :)

[reefland]

Someone asked earlier in this thread if there was a way to shut the forum down prior to the script dumping the db. Anyone know how to accomplish this so users get the "Forum Down" message that is set in the AdminCP?

[kippesp]

If you're using Apache and you configure permissions correctly and permit .htaccess, creating an .htaccess file with a Redirect to a special page will do this. I rename the file to something else once the backup is finished.

[Dave#]

Someone asked earlier in this thread if there was a way to shut the forum down prior to the script dumping the db. Anyone know how to accomplish this so users get the "Forum Down" message that is set in the AdminCP?

Create a file called closeforums.sql

UPDATE template SET template=CONCAT(template,'\n\$bbactive = 0;\n') WHERE title='options';

Create a file called openforums.sql

UPDATE template SET template=CONCAT(template,'\n\$bbactive = 1;\n') WHERE title='options';

In your script earlier - up near the start add

/full/pathto/mysql_binary -uusername -ppassword dbname < /path/to /closeforums.sql

and at the end of the script add

## reopen forums
/full/pathto/mysql_binary -uusername -ppassword dbname < /path/to /closeforums.sql

[reefland]

Thanks!

What is the correct mysql_binary though? This is the output I get:
/tmp/cronsqldump: /usr/bin/mysql_binary: No such file or directory

Edit:
I tried this:

/usr/sbin/mysqld --user=username --password=password databasename < closeforums.sql

But I get an error that --password in an unknown command.

Edit:
I have also tried
mysql -uusername -ppassword databasename < closeforums.sql

[reefland]

Ah ah! The queries you suggested must have been for vb2, 2 UPDATE queries are required to shut down and reopen vb3. :)

[Tech]

My web host (1and1) uses a non-local SQL server. When I used the mysqldump command I used the argument -h db101.perfora.net. Is there a way I can get this script to run?

[Tech]

Bump.

[Dave#]

My web host (1and1) uses a non-local SQL server. When I used the mysqldump command I used the argument -h db101.perfora.net. Is there a way I can get this script to run?

# MySQL dump options
dumpoptions="-h db101.perfora.net --quick --add-drop-table --add-locks --extended-insert --lock-tables"

[JimF]

The script backs up the db okay, compresses it okay, and sends it okay. But it doesn't appear to be attaching it properly.

Sending to my Yahoo and GMail accounts, there is no attachment and the body of the email message is binary data, like:
begin 644 db_1.sql.backup2.gz
M'XL("`L$PT$``VIF<F%S8VA?,2YS<6PN8F%C:W5P,@"=6?M3&SD2_OGX*W1;
M=678`H/-(^2Q.0R8`,&&LYW-<I4J5IZ1/8IGI%F-)H[SU]_7THQ?,(1;RI3U
M:&E:W5]_W1KO[+#.K/^?&Q;F2<J.Z\W]C9T=?-BESNP;%NN`QQ&:#'_GW/(A
MS\0;]G5D>!9$#PU(_LT_>D9?F&_",/QG4JM_[./I]</CC4*#`1_&@F76Y('-
MC6`C;9AU8W_F&9;\27(;Y[W;.S9HG=ZTV=4%:_]QU1_TF9M_NW'6:[<&[6+
But sending it to my POP account and retrieving it with Outlook, it is displayed as an attachment and everything looks fine within the file.

Any ideas as to what's going on here?

-jim

[Dave#]

Any ideas as to what's going on here?The webmail clients are compliant.

[JimF]

The webmail clients are compliant.So what you're saying is that this script sends non-compliant emails?

[Dave#]

soory - should of read non compliant

[Tech]

Thanks to Dave#, I got it working with the external SQL server. I'm still having a little problem though, it gets to Compressing Dump Files and just stops. I let it sit there for a long time and nothing happens. The file that's outputted looks like it is complete but the script won't finish. Any ideas what could be the problem?

[Dave#]

Could be a for a number of reasons - if the qzip process is still running then we could see what it was doing with strace

strace -p <pid>

Is there enough space on the device?

[Tech]

I don't have root access on the server. There is enough space.

[gpdoyon]

I went through this whole thread and had two problems, one of which I solved. I originally copied and pasted the script from the very first page, saved it on my local PC's hard drive as "backup.sh" and then ftp'd it to my server.

When I ran it I got lots of errors. I couldn't seem to fix it until something hit me that I learned a couple years ago. I Re-copied and pasted the script in to a new file and saved it on my hard drive as "backup.txt". Then I ftp'd it over to my server and renamed it to "backup.sh" there. Worked great, except...

Problem #2: It wont uuencode the file. The script hangs forever. I only get a zero byte file. Has anyone solved this problem? My forums haven't even gone public yet so the gzip file is less than 1MB. I am using the same uuencode path as gzip. I verified that uuencode is there. Hmmmm...

ANy thoughts would be appreciated.

Thanks!

[gpdoyon]

Adding on to my last post, why can't "mail" just send the ".gz" file itself? UUencoding it puts it in a text file wrapped within a gzip. How am I supposed to use this? Must I uuDECODE it first??? Ouch. My head hurts. :D

- Gerry

[Jimmy Kane]

do you know what the vB3 queries would be? or are they the ones that dave posted?

[gpdoyon]

do you know what the vB3 queries would be? or are they the ones that dave posted?

They are the ones that Dave posted.

[Jimmy Kane]

aha. someone said that they were the vB2 ones. thanks for the answer :)

[Ugur Cetin]

is it possible to save last 5 backups witb different names, like backup1.sql.gz backup2.sql.gz ...

if its possible, this is very good :)

best regards.

[Ugur Cetin]

is it possible to save last 5 backups witb different names, like backup1.sql.gz backup2.sql.gz ...

if its possible, this is very good :)

best regards.

is there anyone knows this?

[ahsemega]

How would you run this script if your server does not allow SSH/Telnet connections? I'm moving to a new server and my current server doesn't even offer phpmyadmin and i have no way of backing up my database.:confused:

[kur1j]

How would you run this script if your server does not allow SSH/Telnet connections? I'm moving to a new server and my current server doesn't even offer phpmyadmin and i have no way of backing up my database.:confused:

There is a feature in the admin control panel of vbulletin to backup your database.

To the poster above this:
I just name the files what day it is (Monday) that way I have 7 days worth of backups then after 7 days it will over write its self from the previous week.

[LanciaStratos]

I want to edit when my script runs, but when I run crontab -e, I don't see my backup script in the file. What should I do?

[kippesp]

I want to edit when my script runs, but when I run crontab -e, I don't see my backup script in the file. What should I do?
Is it in the system /etc/crontab file? Or another account? Or is it someplace like /etc/cron.daily?

[LanciaStratos]

No, it's in the system's /etc/crontab.

[kippesp]

No, it's in the system's /etc/crontab.For me, crontab -e will only edit a user's crontab file. For the system's, I have to edit /etc/crontab directly under the root account. I'm using RedHat Linux and that's the only way I know how to do it.

[Joe Gronlund]

Chris can you edit this at all to be used on a server without root access, i just have shared SSH access..

[NIS-Francisco]

Can you make this script log into FTP and upload it to a backup server?

[RFViet]

This is based on a script I posted a little while back, basically I just cleaned this up a little bit. Just copy the code and save it into a text file on your server (I called it /usr/local/sbin/dumpdatabases, but feel free to place it in any directory in your path, and call it whatever you want). Just make sure you change the list of databases at the top to reflect the ones you want to backup, and change the username and password for MySQL. Most other things should be ok, but you might need to change the pathnames if different on your system. Also make sure you do a chmod +x /usr/local/sbin/dumpdatabases to make it executable.


#!/bin/sh

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="mysql forum"

# Directory where you want the backup files to be placed
backupdir=/backup

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/local/mysql/bin/mysqldump

# MySQL Username and password
userpassword=" --user=root --password=password"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail

# Send Backup? Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="Your MySQL Backup"
mailto="you@yourdomain.com"

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
$gzip ${backupdir}/${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
for database in $databases
do
$uuencode ${backupdir}/${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
$mail -s "$subject : $database" $mailto < ${backupdir}/${database}.sql.gz.uu
done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit
If you want to run this automatically through cron (recommended so you don't forget): just do a crontab -e and add the following line:

0 3 * * * /usr/local/sbin/dumpdatabases
This will run it everyday at 3:00am. Feel free to change the time to something else if you forums aren't as busy at some other time.

-Chris

So, the file has no extension ???