Jump to content

Backup Your Database Often


Recommended Posts

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. :)

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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");

Link to post
Share on other sites

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites
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 by borfast
Link to post
Share on other sites

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

 

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 by Rickvz
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 3 months later...

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 ?

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
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?

Link to post
Share on other sites
  • 1 year later...

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 by LaMpiR
Link to post
Share on other sites

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

Link to post
Share on other sites

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.

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.

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

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

Link to post
Share on other sites
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. :)

Link to post
Share on other sites
  • 3 months later...

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
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'.

Link to post
Share on other sites
  • 2 months later...
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

Link to post
Share on other sites
  • 9 months later...

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

Link to post
Share on other sites
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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
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. :)

Link to post
Share on other sites
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?

Link to post
Share on other sites
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"

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...