MySQL periodic backup with PHP

[RedRose]

what is the way to make this lovely script make a new folder every time it is run, this folder would be called like 2003-08-12

?

how could we do this ??

[elgumbo]

Hi RedRose

Well nobody's laughed at me yet and I've asked stupider questions than yours

1: Should you call this script from your browser?

You can if you wish. It will work fine. But as I'm lazy and prone to forget to do stuff like database backups I set up a cronjob (it's like a schedule manager in windows) on the server to run the script at predefined intervals.

Check with your host if you can do cron jobs on your site - it will make life a lot easier for you.

2: Is it safe to have this script in the public_html folder?

Put it in a password protected directory and you should be fine. But if you use the cron job method (recommended) you can place the file outside of the public folder - meaning its more secure again.

3: Is it for security reasons that you are not including the username and password of the database in the same file?

Yes. Ideally, you should put your database connection info above the public folder and use an include to get the info. It makes your scripts a little more secure. (highly recommended)

Hope that helps but post back if you need any more info and, if I can't help, I'm sure someone with a lot more experience will help you out.

[lacerus]

Should you call this script from your browser?

No. You should set it up as a "cronjob" (see above). If you have root access to your server you can do that in /etc/crontab (read 'man crontab' for info). If you don't have root access or have no idea what I'm talking about, you should check with your hosting provider.

Is it safe to have this script in the public_html folder?

No!! If you do that and someone finds it, they could easily bring your server down because the database backup is usually a pretty expensive thing for the server to do. You should at least .htaccess-secure it, if you don't have any other option.

Is it for security reasons that you are not including the username and password of the database in the same file?

It's for two reasons. One is security (even though this doesn't help a whole lot) and the other is lazyness. If you save your login data (and some other config stuff) in an external config file, you can include it wherever you want... programmers are lazy sods

[elgumbo]

what is the way to make this lovely script make a new folder every time it is run, this folder would be called like 2003-08-12

?

how could we do this ??

This is untested but you should be able to add the following code after the $password = $pass; // password for username line

$date = date("dmy" );


Then you could amend the path to save the database to something like

// Database Backup Filename & Location
$backupto = '/home/sitename/$date'; // absolute path to folder containing database - no trailing slash.

Alternatively, you could still put the date line in and amend the $backupas = $database . '.sql' . '.gz'; line to:

$backupas = $database . $date . '.sql' . '.gz';

this will save all the backups in the same directory but add the date to file name eg. DATABASE2003-13-08.sql.gzip

Good luck

[RedRose]

Hi RedRose

Well nobody's laughed at me yet and I've asked stupider questions than yours

thank you for your very kind encouraging elgumbo..

1: Should you call this script from your browser?

good. But if you set this to run as a cron job, it would make an error if the file already exists…

I have either to use a rm –rf command before the dump command, or I will have to make sure that the cron job does not run in less than 24 hours.

In the first case, you are taking high risks, for if your board is hacked just before the dump, your "security" backup would be your killer, as it would delete your most important backup and write over it.


I wish there were a way to run this every 24 hours and 1 minute…. In this way, the timing of the cron job would not move in a few days to the server's busy hours, and at the same time it would not have to delete your file before the dump.

The problem is that cron jobs as I know are programmed on a fixed daily time, not every "this much" of hours and minutes.


Any work around? I think we need a few lines of php code here to set this running every 24 hour and 1 minute issue.

You can if you wish. It will work fine.

sure enough, I tried it and made it backup even many databases yesterday… However my question was aiming at finding out if I am taking any serious risks by leaving this file in the public_html folder.

(I think, and I might be wrong, that calling scripts from browsers can ONLY happen if your script is in the public_html folder. Please correct me if I am wrong. )

It goes without saying that I am using a .htaccess and .htpasswd.

Put it in a password protected directory and you should be fine. But if you use the cron job method (recommended) you can place the file outside of the public folder - meaning its more secure again.

thank you very much. That answers my question. Now we need to find a work around as described above.

Yes. Ideally, you should put your database connection info above the public folder and use an include to get the info. It makes your scripts a little more secure. (highly recommended)

thank you. Is it as secure as this, to use a .htpasswd that is in the .htpasswds upstream from the public_html folder ??

Hope that helps but post back if you need any more info and, if I can't help, I'm sure someone with a lot more experience will help you out.

thanks buddy, i am looking forward to reading you …. ;-)

[RedRose]

No. You should set it up as a "cronjob" (see above)

Okay..

If you have root access to your server

I don't, but I do have cron jobs.

No!! If you do that and someone finds it, they could easily bring your server down because the database backup is usually a pretty expensive thing for the server to do. You should at least .htaccess-secure it, if you don't have any other option.

thank you very much. You are totally right.

It's for two reasons. One is security (even though this doesn't help a whole lot)

what could be more secure than using a .htacess and a .htpasswd (this last in the .htpasswds behind the public_html folder) ???

In addition, if I have a index.html file in every folder, how could anyone see what other folders are there, and what other files are there in this or that folder??

If you save your login data (and some other config stuff) in an external config file, you can include it wherever you want

ty for this info…. ;-)

[RedRose]

This is untested but you should be able to

........

// Database Backup Filename & Location
$backupto = '/home/sitename/$date'; // absolute path to folder containing database - no trailing slash.

it is a small world !!!



I did try this yesterday and no, it did not work....

I even tried to make the folder with a separate command, and double check the chmod 777 of this command.... BEFORE the dumping process, and still the dumping process would not understand the $date and it dumped in the parent folder of the $date folder.


:-) it is a small world.... two guys on two sides of the earth thinking of the same solution to one problem.... ;-)

let us try something else.

[elgumbo]

Global Village

RedRose,

The second method (with the date apended to the filename) is the one I use and it works fine.

Cron jobs can be set up for any time period you wish, eg you could specify it to run your script every minute of every third sunday in september if you wish. I have mine runnning at 03.50 every 14 days.

Do a search on crojobs and you'll find a lot of info.

[RedRose]

you are right elgumbo ....

thank you very much ...

long time I have not used cron jobs, and thus i forgot how to

i was scared of cron jobs, because my host kept moving servers all the time and every time he moved, i lost all of my cron jobs....

and as I was a newbie, I had many separate commands for every little job.... and thus re-entering those lines was soooo time-needy.. (talking about many sites here)

I thus kind of gave up on cron jobs....

now we are back..


is this the proper way to request a cron job for my script every 24 hours and 1 minute??? or would it do the job at midnight and 1 minute everyday??

see the attached gif...

[RedRose]

vb3 .... where is the link to my attachment ??

[Toly]

is this the proper way to request a cron job for my script every 24 hours and 1 minute??? or would it do the job at midnight and 1 minute everyday??

From the image you attached above, just change the hour field from 24 to 0. The script will then run at 12:01 am every day.

[RedRose]

From the image you attached above, just change the hour field from 24 to 0. The script will then run at 12:01 am every day.

thank you Toly , but that is NOT what I want to do

I would like this script to run every 24 hours and 1 minute.


how can you do that ?

[lacerus]

I would like this script to run every 24 hours and 1 minute.

You can't do that with vcron because it only accepts 'absolute' time values (like 0:01 every night) not relative. If I understand you right you want it to start 0:01 the first day, then 0:02 next and so on... cron can't do that. But I also don't know why you would need that kind of behavior.

what could be more secure than using a .htacess and a .htpasswd (this last in the .htpasswds behind the public_html folder) ???

Well... if the .htpasswd is not accessible over the webserver, this is pretty good as long as you choose a good password. There are tons of good brute forcing tools for .htaccess secured sites out there and they can try tons of password per minute. It would be better not to have anything admin related in the webtree at all and do everything over ssh, but I agree that's ugly. A good .htaccess and password should be enough if your not ebay or yahoo. Apache is also f*in secure I believe...

[Toly]

thank you Toly , but that is NOT what I want to do

I would like this script to run every 24 hours and 1 minute.


how can you do that ?

I think I'm not very clear about what you want to do. If you run the script at 12:01 am on monday and then you run it back at 12:01 am on tuesday, the difference is 24 hours right? It wouldn't be 24 hours and 1 minute as you wanted, though.

[RedRose]

You can't do that with vcron because it only accepts 'absolute' time values (like 0:01 every night) not relative.

thank you very much, that does answer my question indeed.

Well, how can you then define a random parameter in php?

I will give a random string to the end of my files, and this way, I am sure, that backing up my bases would not mean kill the previous one if it was made the same day.

If I understand you right you want it to start 0:01 the first day, then 0:02 next and so on...

that is correct.

cron can't do that.
But I also don't know why you would need that kind of behavior.

it was said above that I am using a script to backup my database.. this script would call my file like

databasename_date-of-today.gzip

however, if I run this backup with a cron, it would simply make a new file every day at a fixed hour.

Manually running the script would kill my first backup and replace it with the new one…. Which is not my purpose…

If I can make a random short string at the end of the file name, I would be fine ;-)

Well... if the .htpasswd is not accessible over the webserver, this is pretty good as long as you choose a good password. There are tons of good brute forcing tools for .htaccess secured sites out there and they can try tons of password per minute. It would be better not to have anything admin related in the webtree at all and do everything over ssh, but I agree that's ugly. A good .htaccess and password should be enough if your not ebay or yahoo. Apache is also f*in secure I believe...

Thank you VERY much for this very helpful information


I sincerely appreciate this.

That DOES answer my question. Thank you lacerus.

Herzlichen Dank…. Na ja, ich wohne im Schweiz und ich kan ein bisschen Deutsch.

[RedRose]

I think I'm not very clear about what you want to do. If you run the script at 12:01 am on monday and then you run it back at 12:01 am on tuesday, the difference is 24 hours right? It wouldn't be 24 hours and 1 minute as you wanted, though.

you are right Toly.


Thank you so much for your very appreciated help

[TW.]

Hey I know this is an old thread but I was trying this script out and am having a problem setting it up.

This is the script as I have it on my server now:

<?php
// Include your user and password $user $pass
// outside the public folder
include("/home/USER/domains/mysql_backup/user.php" ); 
// Mysql Database Information:
    $database = 'My_database';   // name of the database.
    $username = $user;  // username with access to database.
    $password = $pass;  // password for username.
    $date = date("dmy" );
// Database Backup Filename & Location
    $backupto = '/home/USER/domains/mysql_backup/backups';  // absolute path to folder containing database - no trailing slash.
    $backupas = $database . $date . '.sql' . '.gz';
// Perform backup
$backupcommand = "mysqldump --opt -u$username -p$password $database | gzip >$backupto/$backupas";
passthru ("$backupcommand", $error);
if($error) {
   echo ("Problem: $error\n" ); exit;
}
?>

And the user.php file looks like this:

<?php
$user='My_username';
$pass='My_pass';
?>

My problem is the resulting back_up file is virtually empty?
This is what I get:

-- MySQL dump 9.07
--
-- Host: localhost    Database: My_database
---------------------------------------------------------
-- Server version	4.0.12-log

I have tried the usename and pass for the database and also my root usename and pass and I get the same result.

What am I doing wrong?

Also is there anyway to make this script e-mail and/or FTP the file to me? So that my back_up will not be on the same server just in case the server goes completly down.

After I get the script working correctly I will move it to a directory above my web tree and set it up as a cron job.

Any help will be appreciated!
Thanx
TW

[TW.]

I edited the above post to take out my passwords and usernames!

[Toly]

Usually when you get a blank backup file like that, it means that the username or password wasn't accepted. Verify that your username and password are correct and use double quotes.

$user = "username";
$pass = "password"; 

Edit:

Also is there anyway to make this script e-mail and/or FTP the file to me? So that my back_up will not be on the same server just in case the server goes completly down.

I think I've seen an effictive way of doing this. I'll try to search for it and post the code here.

[Gaheris]

An email wouldn't be that great since those db backups can get big. If you have a FTP server available then you can use PHP's ftp functions.

[TW.]

Usually when you get a blank backup file like that, it means that the username or password wasn't accepted. Verify that your username and password are correct and use double quotes.

$user = "username";
$pass = "password"; 

Well I tried useing double quotes for pass and usename but I got the same thing then I even tried it with the spaces like in your example even though I know there should be no spaces and got a totally empty file without even the MySQL Dump info from before.

Like I stated above I have tried my individual usename and pass and also my root usename and pass and it always comes out the same.

I am going to try it with the usename and pass in the 1st file instead of calling it from user.php just to check, will let you know what I get.

TW

[marcel]

I know your already working on some code. But here is a script which can email or FTP the backup.

http://www.hotscripts.com/Detailed/27576.html

[TW.]

OK I have tried double quotes "user" and single quotes 'user' with the same results and with spaces or without with no difference!
I have put my "user" and "pass" in original file and comment out the include line and the "$username = $user;" and "$password = $pass;" lines and all does the same.
The only change I can get is if I intentionally put wrong user or pass I get a totally empty file instead of this:

-- MySQL dump 9.07
--
-- Host: localhost    Database: My_database
---------------------------------------------------------
-- Server version	4.0.12-log

So I am going to go out on a limb and say it is not my passwords doing it.

marcel thanx for the link I will try that one out too only thing I can lose is time and if I try enough I am sure I will come out on top with a working solution.

TW

[Toly]

Although the --opt method is used to optimize the backup process, try removing it from this line:

$backupcommand = "mysqldump --opt -u$username -p$password $database | gzip >$backupto/$backupas"; 

Also, since your user and password seem to be ok, check that your dbase name is correct, just in case.

[TW.]

Although the --opt method is used to optimize the backup process, try removing it from this line:

$backupcommand = "mysqldump --opt -u$username -p$password $database | gzip >$backupto/$backupas"; 

Also, since your user and password seem to be ok, check that your dbase name is correct, just in case.

You hit the preverbal nail on the head with the "--opt" flag
That gives me a nice 3.91mb gziped file with all my create and insert commands when done on my forum database.

Ok now I am off to see about adding ftp funtions to it and then I am done!

I will be moving this script to above my web folder after I get ftp working and was wondering if it would be safe to just add my usename and pass to file instead of calling user.php for these variables since it will no longer be accessable from a browser?

I will also want to .htaccess it and would like to know if crontrab will be able to bypass the .htaccess?

I want to thank you again for holding my hand though this process!

TW