Backup Your Database Often

**surefire** · June 12, 2003

It's a VERY good practice to backup your mysql database often. How often depends on how important your data is.

On my job posting site, I'd hate to have to tell a member that their resume is gone. So I backup daily.

Directions.

1- Cpanel

2- phpMyAdmin

3- Click on your database (not the tables, the actual database link above your tables)

4- in the main window, click Export

5- Choose 'Structure and Data', 'Save as File' and any other features you want

6- submit, choose the location to download and store the textfile on your computer

Repeat as often as you like.

Having good security measures in place is good, but there's nothing like having a solid backup of your database in case all h*ll breaks loose.

Also, from experience I can tell you that the biggest threat comes from within. Backup your database before testing out that new script you wrote. I once zapped a precious table of data with one of my own scripts.

And if you don't have a backup and something goes wrong, your only chance at salvation is to ask the Help Desk if they can rescue you, but you need to ask immediately. And ask nicely.

**Lianna** · June 12, 2003

Ok, let's say that I want a backup made of my db everyday and archiving the most recent 5, automatically. Ideas?

**surefire** · June 12, 2003

I've been meaning to look for a backup script. I seem to recall a thread at TCH that had a link to such a script and gave a cron command to execute it.

I think it would also be pretty cool if such a script could email the dump file (backup) in an atachment... not sure if that's possible or not.

I'll look into this and post my findings.

**surefire** · June 12, 2003

This looks VERY promising.

http://www.phpfreaks.com/script/view/11.php

Can't wait to try it. (Note to self... manually backup db before testing this new script.)

**TCH-Rick** · June 12, 2003

Great find, Jack. I downloaded and installed the script and tested it on my test account and it seems to work fine. It is fairly easy to set up (probably easier if you don't set it up at 2 in the morning) and runs fine using crontab.

I set it up outside of my public_html directory and made the changes to the file as explained in the comments.

I then set up a crontab to run it every day in the middle of the night and used /home/username/dbsender.php as the command. I set the permissions to 770.

When the file runs and will use gzip to compress the file then send the file to you via email or ftp.

**borfast** · June 12, 2003

Yep, that's the script I found some time ago and posted somewhere here at the forums.

After changing the variables to suit your needs, you can set up the cron job with a command such as this:

php -q /home/best-alm/public_html/mysql_backup/dbsender.php

and the timing you find appropriate.

Remember to set the appropriate permissions to the folder where you want the script to save your backups.

Oh, and you might want to change line 67 from this:

passthru("mysqldump --opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");

into this:

passthru("mysqldump --add-drop-table --add-locks --all --extended-insert --quick -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");

**surefire** · June 12, 2003

Thanks for the help!

**btrfld** · June 12, 2003

I downloaded the file, but can't open it on my Windows machine. So I uploaded it to a folder on my TCH site, and tried to extract it in File Manager. I get the following error:

tar: This does not look like a tar archive
tar: Skipping to next header

tar: Error exit delayed from previous errors

What am I missing?

**surefire** · June 13, 2003

This is a very small one page script. You should be able to extract the file onto your hard drive and ftp it to your site after you read the directions in the script.

I haven't had time today to mess with it. But I looked at the code and it's pretty straightforward.

**leezard** · June 13, 2003

Ok, I'm gonna try and set this up (I'm not very good at remembering to do backups) I do have 1 question tho, i have 4 different dbases on my site, can i install this script in 4 different folders and configure it for each dbase?

**Lianna** · June 13, 2003

*YAY* New toys! Thanks all.

:lol:

**surefire** · June 13, 2003

Ok, I'm gonna try and set this up (I'm not very good at remembering to do backups) I do have 1 question tho, i have 4 different dbases on my site, can i install this script in 4 different folders and configure it for each dbase?

I can't think of any reason why not.

Another solution would be to amend the script to backup each of the db's to the same folder so you have one directory and one script and one cron job.

But let's use the KISS method... unless you're feeling bold, I'd do this

1- manually back up everything

2- read the directions for the script

3- test it out on one database

4- if successful, roll it out to the other three

Edited October 11, 2003 by borfast

**Lianna** · June 13, 2003

Thanks Rick (who just implemented the script on one of my db's and saved me a ton of time having to actually learn something)! You rock!

**TCH-Rick** · June 13, 2003

<resist>urge to make comment about old dogs and new tricks</resist>

I did a little more testing and here is how I set my account up to send multiple db's.

1. I created an empty file in the directory I wanted to use and copied the script into it. I named this file send_mt.php.

2. I editted the file to the information about my Movable Type db. Set the email and other options, etc.

3. Created a Cron to run this script every other min.

4. Editted stupid typos and changed the permission of the file to 770 (after the first backup didn't get made.)

5. Got a good backup sent after repeating step 4 a time or two. (Really shouldn't watch TV while doing this)

6. Changed the cron to send once a day in the middle of the night.

7. Copied the seditted script to another empty file and named it send_forum.php.

8. Changed data about the database.

9. Copied cron statement from first one and did step 3.

10. Changed name of file in second cron to send_forum.php from send_mt.php after not receiving the backup when expected.

11. Changed cron to run once a day in the middle of the night after receiving backup file.

12. Got a cold beverage.

Bottom line -- changing the names of the files and setting separate crons works and all files can be in the same directory.

Edited June 13, 2003 by Rickvz

**surefire** · June 13, 2003

Rick, if you don't mind... would you be able to post an example of the cron you wrote... as close to the real deal as possible.

It seems that writing cron gives lots of us difficulties. (Yours truly included)

Thanks.

**TCH-Rick** · June 13, 2003

I just put in /home/username/scriptdir/filename.php for the cron entry.

Note that I installed the script below the /public_html directory so it is not easily visible on the web so you won't be able to run it manually. If it is in public_html you would use /home/user/public_html/scriptdir/filename.php. You would be able to run it manually. Not sure what would happen if you had it in a protected directory.

A couple of trouble shooting tips:

If it doesn't run check that the permissions allow executing and that the filename in the cron is right.

If it returns an empty file then check that the directory you put in the script is where the file resides.

**arvind** · September 25, 2003

I can't seem 2 get it to work. I've edited it correctly. But when I run the script i get the following error

Warning: filetype(): Lstat failed for (null) (errno=2 - No such file or directory) in /home/******/public_html/dbsender/dbsender.php on line 85

Warning: fopen(home/******/public_html/dbsender/******-092503-0628am_sql.tar.gz): failed to open stream: No such file or directory in /home/******/public_html/dbsender/dbsender.php on line 91

Warning: filesize(): Stat failed for home/******/public_html/dbsender/******-092503-0628am_sql.tar.gz (errno=2 - No such file or directory) in /home/******/public_html/dbsender/dbsender.php on line 92

Warning: fread(): supplied argument is not a valid stream resource in /home/******/public_html/dbsender/dbsender.php on line 92

Warning: fclose(): supplied argument is not a valid stream resource in /home/******/public_html/dbsender/dbsender.php on line 93

Database backup created and sent! File name home/******/public_html/dbsender/******-092503-0628am_sql.tar.gz

I've no idea what I'm doing wrong ! The file appears in the directory but the file that is sent 2 my email is empty ! Oh Crap I didn't back up my sql table before doing this will it screw it up ?

**surefire** · September 25, 2003

I think that the errors indicate that the setup variables aren't correct. I think you already knew that.

I would guess that you probably put the $dbname (name of your database) incorrectly. Or you forgot to assign the user to the database.

It took a bit of tweaking on my part to get the script running correctly. The way I solved it was to open an ftp connection to my site and see that the script was sucessfully uploading a file. I saw where the file was being put (not where I wanted) and went back into the script to find out why.

A little tip... if you are emailing the file to yourself, then you don't need to ftp the file to your site. It's redundant... and you'll still have a file on your site if you set up the vars correctly.

Also, I noticed that you put the script in the public_html folder... you should strongly consider moving it outside the public_html directory. Put it in your home folder.

**arvind** · September 25, 2003

rite i've removed it from my public_html folder but now that its in the home folder how do i run it to make sure it works ?

**surefire** · September 25, 2003

Set up a cron job. This could be tricky if you've never done it before. There is info in this thread and others on the forum that explain it. For testing, you might want to set up to execute every five minutes or so, as long as your database isn't too huge.

**kellybellydotnet** · September 27, 2003

Directions.

1- Cpanel

2- phpMyAdmin

3- Click on your database (not the tables, the actual database link above your tables)

4- in the main window, click Export

5- Choose 'Structure and Data', 'Save as File' and any other features you want

6- submit, choose the location to download and store the textfile on your computer

Repeat as often as you like.

Okay, so I've been backing up my sites' databases like this:

1- Cpanel

2- Backup

3- Click on the database name(s) under Download a SQL DB Backup (shouldn't this be an SQL?)

4- Save the file to my harddrive

Is this doing the same thing?

**surefire** · September 28, 2003

Yes... this script just does it automatically.

**LaMpiR** · July 15, 2005

I've downloaded it but i don't have ssh acccess to my hosting so i've uploaded it to /httpdocs as dbsender.php and chmoded it to 770.

I have also modified line 67 as someone has mention it before.

I have plesk 7.5.3 and i've added this to my crontab

* 12 * * * php -q /httpdocs/dbsender.php

I beleive that this command should be executed every 12 hours?

It was setted for every 5 minutes i have waited for half hour but nothing happened.

This is how my config looks like

// Optional Options You May Optionally Configure

$use_gzip = "yes"; // Set to No if you don't want the files sent in .gz format

$remove_sql_file = "yes"; // Set this to yes if you want to remove the .sql file after gzipping. Yes is recommended.

$remove_gzip_file = "no"; // Set this to yes if you want to delete the gzip file also. I recommend leaving it to "no"

// Configure the path that this script resides on your server.

$savepath = "/httpdocs/backup"; // Full path to this directory. Do not use trailing slash!

$send_email = "yes"; // Do you want this database backup sent to your email? Fill out the next 2 lines

$to = "lampir@gmail.com"; // Who to send the emails to

$from = "backup@yumirc.net"; // Who should the emails be sent from?

$senddate = date("j F Y");

$subject = "MySQL Database Backup - $senddate"; // Subject in the email to be sent.

$message = "Your MySQL database has been backed up and is attached to this email"; // Brief Message.

$use_ftp = "no"; // Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines

$ftp_server = "localhost"; // FTP hostname

$ftp_user_name = "ftp_username"; // FTP username

$ftp_user_pass = "ftp_password"; // FTP password

$ftp_path = "/"; // This is the path to upload on your ftp server!

Does anyone have any ides why it doesn't work ?

p.s. i just saw date of the last post :flex:

Edited July 15, 2005 by LaMpiR

**TCH-Bruce** · July 15, 2005

Welcome to the forum LaMpiR

Depends on your server if it is configured to run PHP scripts with permissions set to 770. You may have to set them to 777.

Does the /httpdocs/backup folder exist? Does it have write permissions?

I beleive the path should be the FULL path to your backup folder. Something like /home/username/httpdocs/backup

**TCH-Rob** · July 15, 2005

Welcome to the forum LaMpiR

**TweezerMan** · July 15, 2005

Welcome to the forums, LaMpiR!

I have plesk 7.5.3 and i've added this to my crontab
* 12 * * * php -q /httpdocs/dbsender.php

I beleive that this command should be executed every 12 hours?

It was setted for every 5 minutes i have waited for half hour but nothing happened.

<{POST_SNAPBACK}>

That crontab is set to run at 12:00PM (local server time), not every 12 hours. And since the minutes part of the crontab is "*" (the first "*" before the '12'), it will run once every minute between 12:00PM and 12:59PM.

Depends on your server if it is configured to run PHP scripts with permissions set to 770. You may have to set them to 777.

<{POST_SNAPBACK}>

Since the script is being run from a cron job, it should be running under the user's ID and 0700 file permissions should be more than sufficient.

I believe the path should be the FULL path to your backup folder. Something like /home/username/httpdocs/backup

<{POST_SNAPBACK}>

I'm with TCH-Bruce on this one! :yes:

**TCH-Thomas** · July 15, 2005

Welcome to the forum, LaMpiR.

**TCH-Bruce** · July 15, 2005

Since the script is being run from a cron job, it should be running under the user's ID and 0700 file permissions should be more than sufficient.

I was just going by the contents of the dbsender script itself. Thanks for the assist David.

**stevevan** · July 15, 2005

Welcome to the forums. Hope you were able to get your problem solved!

**TCH-Don** · July 15, 2005

Welcome to the forums, LaMpiR

**dpkyte** · October 23, 2005

Hey all,

I guess we can attempt to keep this going.

I installed this script, with my mods, into a new folder, under the root, same level as the public_html directory, called backup.

When it is executing I keep getting an e-mail saying "No input file specified." My execution script on the CPANEL 'standard cron manager' is: php -q /backup/dbsender.php

Any thoughts?

Also, can I change the chmod via CPANEL? My current FTP does not allow me to.

**dpkyte** · October 23, 2005

Figured out the CHMOD issue, CPANEL, File manager, Click on folder name, change permissions.

**cajunman4life** · October 23, 2005

Also, as a side-bar... In the original post it recommended to do a dump through phpMyAdmin... something easier for those that are new to this would be in the backup section of cpanel. You should see a link for each of the SQL databases you have in your account. Clicking the link will download a gzipped version of the SQL dump.

**dpkyte** · October 23, 2005

Glad to have this forum, all is working well. I like this script, even gives you the recovery SQL if needed.

Thx,

DPK

**TweezerMan** · October 23, 2005

When it is executing I keep getting an e-mail saying "No input file specified." My execution script on the CPANEL 'standard cron manager' is: php -q /backup/dbsender.php

The path to the file name should start with '/home/cpanelName' (where cpanelName is the username you use to log into your CPanel). The '/home/cpanelName' directory is the top most directory that you can view in your account (via FTP or CPanel's File Manager). Your web site's 'public_html' directory is actually '/home/cpanelName/public_html'.

**mriffey** · October 25, 2005

Do some TCH servers support ncftp? Mine doesnt appear to.

I ask cuz the current version of this script refers to NCFTP, rather than using stock php FTP syntax.

Mark

**TweezerMan** · October 25, 2005

I believe the server would need to be running an NcFTPd FTP server in order to use NcFTP commands. As far as I know, TCH servers run ProFTPd for the FTP server.

**rbrown3rd** · January 11, 2006

I believe the server would need to be running an NcFTPd FTP server in order to use NcFTP commands. As far as I know, TCH servers run ProFTPd for the FTP server.

How hard would it be to modify the script so that it would work with a hosting service that has not installed anything beyond the basic ftp function? Oh, this thread has been really helpful. I have the email backup function working by means of a crontab but since my hosting service, another one that will go unamed, does not have NcFTP or ProFTPd how can the script be modified to work?

Thanks to all for some very helpful comments and information.

Bob 3

**ashesnglass** · October 27, 2006

This is a great script, but I'm running into a problem.

When I run the script, it creates the backups in the directory where the script is located. However, it will neither e-mail the backups nor upload them to FTP. I even set the permissions everywhere to 777. It's only in public_html for testing purposes.

// Optional Options You May Optionally Configure

$use_gzip = "yes"; // Set to No if you don't want the files sent in .gz format

$remove_sql_file = "yes"; // Set this to yes if you want to remove the .sql file after gzipping. Yes is recommended.

$remove_gzip_file = "no"; // Set this to yes if you want to delete the gzip file also. I recommend leaving it to "no"

// Configure the path that this script resides on your server.

$savepath = "/home/cpanelname/public_html/dbbackup"; // Full path to this directory. Do not use trailing slash!

$send_email = "yes"; // Do you want this database backup sent to your email? Fill out the next 2 lines

$to = "dave{at for TCH board purposes}yourdomain"; // Who to send the emails to

$from = "backup@yourdomain"; // Who should the emails be sent from?

$senddate = date("n/J/Y");

$subject = "RatDog.Org MySQL Backup - $senddate"; // Subject in the email to be sent.

$message = "Attached..."; // Brief Message.

$use_ftp = "yes"; // Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines

$ftp_server = "domainname"; // FTP hostname

$ftp_user_name = "{TCH username}"; // FTP username

$ftp_user_pass = "{TCH password}"; // FTP password

$ftp_path = "/path/"; // This is the path to upload on your ftp server!

Any ideas? Thanks!

EDIT: TCH-Bruce - removed user sensitive data

**TCH-Bruce** · October 27, 2006

I never tried the FTP function but I do know the email function works.

You can PM your script to me and I'll take a look at it for you.

**secretal** · October 28, 2006

This is a great script, but I'm running into a problem.

When I run the script, it creates the backups in the directory where the script is located. However, it will neither e-mail the backups nor upload them to FTP. I even set the permissions everywhere to 777. It's only in public_html for testing purposes.

Any ideas? Thanks!

EDIT: TCH-Bruce - removed user sensitive data

Please update this thread if you get FTP to work.

**TCH-Bruce** · October 28, 2006

Welcome to the forums secretal

If I get some time this weekend I will play with it.

**TCH-Thomas** · October 28, 2006

Welcome to the forum, secretal.

**secretal** · October 28, 2006

Thanks for the welcome...I'm looking forward to being able to automate FTP.

**TCH-Don** · October 28, 2006

Another option that some of us use is AutoMySQLBackup from SourceForge.

I get an email every morning of my databases.

There is an option for automatically selecting all databases

># List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"

So you are covered when you add a new database.

**TCH-Bruce** · October 29, 2006

I have tried setting up the FTP part of the script. Unfortunately ncftpput is not supported on the server I am on or any of the others I would imagine.

I don't know if the one Don links to above allows you to FTP or not.

I set up a special email address to send the backups to that I make and it works well for me.

**secretal** · October 29, 2006

I have tried setting up the FTP part of the script. Unfortunately ncftpput is not supported on the server I am on or any of the others I would imagine.

I don't know if the one Don links to above allows you to FTP or not.

I set up a special email address to send the backups to that I make and it works well for me.

Thanks for the followup. I assume you mean the other script recommended by Don is the one you got working with email. I'll have to give it a try. Email will be sufficient for now.

I'm researching using a different method to FTP...I'm no script expert so my effort may take a while.

**secretal** · October 29, 2006

I've tried both scripts and the mail feature doesn't work for me with either one. Maybe the mail feature is disabled on server125.

**TCH-Bruce** · October 29, 2006

Should not be having an issue with email. Please open a ticket with the help desk and ask if they would take a look.

**jhollin1138** · October 30, 2006

I have tried setting up the FTP part of the script. Unfortunately ncftpput is not supported on the server I am on or any of the others I would imagine.

I don't know if the one Don links to above allows you to FTP or not.

I set up a special email address to send the backups to that I make and it works well for me.

I just stumbled across this thread. Did you try using FTP functions built into php?

**TCH-Bruce** · October 30, 2006

Hi Jim, I am not the author of the script and I didn't try modifying it to make it work. I'm sure someone could modify it to do so if they were so inclined.

**TCH-Don** · October 30, 2006

I've tried both scripts and the mail feature doesn't work for me with either one. Maybe the mail feature is disabled on server125.

I have been useing AutoMySQLBackup for a long time and no problems.

Double check your setting and the permissions for the script

I have 755 and it is above public_html

make sure the mailsetup is set to files

# Mail setup

MAILCONTENT="files"

**jhollin1138** · October 30, 2006

Hi Jim, I am not the author of the script and I didn't try modifying it to make it work. I'm sure someone could modify it to do so if they were so inclined.

It shouldn't be hard to do. I will see if I have some time this evening.

Sign In

Backup Your Database Often

Recommended Posts

Join the conversation