MySQL periodic backup with PHP

[Chromate222]

Hi all,

I'm dealing with quite a large database as a backend for a website. It's quite an active site and I think that it's essential that the database gets regularly backed up. How can this be done on a periodic bases. Say, at the end of each day for example? Can this be coded to happen automatically as a server processes or does someone actually have to click a button for it to happen?

Any advice would help me no end!

Many thanks,

Richard.

[Toly]

This is the script I use to backup my databases everyday, well actually It backups every hour.

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

Then you could upload the script anywhere on your server and set up a cron job to run this script everyday or every hour or whatever.

[poLka]

that's a sweet script Toly, you don't mind if i use it do you?

[Toly]

that's a sweet script Toly, you don't mind if i use it do you?

I didn't write that script, just did some slight modifications. Actually, I think someone posted it in this same forum.

[Chromate222]

Excellent. The thing I was really looking for was how I go about running a "cron job" to run the script? Not really sure how to go about setting it up? What do I need to do?

[Toly]

Excellent. The thing I was really looking for was how I go about running a "cron job" to run the script? Not really sure how to go about setting it up? What do I need to do?

Well, the first thing you need to know if your host supports cron jobs. I guess you could go to your control panel search for that option.

If you can, then you would just enter the full path to your script and set up the time to run it.

Here's a link for setting up cron jobs:
http://www.redhat.com/support/resour...cron/cron.html

Hope that helps.

[Chromate222]

Thanks for the script advice. I already have something like that in place However, I guess my real problem is I don't know how to get the server to run it periodically? How should I go about getting it to do this?

[Chromate222]

Please excuse that last post. My otherone didn't show up for some reason. But it's there now. Oh well.

[Toly]

Alright but did you check your host to see if you have cron jobs installed?

[elgumbo]

Hi Toly

I am playing around with the backup script but I have an error.

When running the script I get a "Problem 1" error - Any idea what this could be?

Cheers

[GamerZ]

Hi Toly, Can I know let say i want to back up 2 tables in my database only and i do not want gzip, just the .sql, how do I go about it?


Thanks alot

[GamerZ]

And 1 more thing, I notice that when using mysqldump for the INSERT INTO statement then to be on 1 line only, so lets say I got 1000 posts, the values for 1000 posts will all be in a line

Any ideas?

[Toly]

I am playing around with the backup script but I have an error.

When running the script I get a "Problem 1" error - Any idea what this could be?

I'm not sure why you are having problems with that script. You might want to check the path to the directory you are saving the databases. In any case if you could post the code here with your modifications, that would help.

Can I know let say i want to back up 2 tables in my database only and i do not want gzip, just the .sql, how do I go about it?

I know you can do that with phpmyadim but I have no clue of how you would do it with this script.

For saving as .sql only, you can change it to this:

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

And 1 more thing, I notice that when using mysqldump for the INSERT INTO statement then to be on 1 line only, so lets say I got 1000 posts, the values for 1000 posts will all be in a line

I'm not sure what you mean. Can you explain the problem with more details?

[GamerZ]

Thanks Toly for the reply.

Regarding my 2nd question, when i backup using mysqldump, all the INSERT values then to be crowded into 1 statement. Here is an example

INSERT INTO gamerz_b2useronline VALUES (1056372803,'Guest','195.145.249.80','/orientek/kore/gamerz/b2archives.php'),(1056373085,'GamerZ','202.156.40.184','/orientek/kore/gamerz/'); 

while if you are using phpmyadmin it will give you

INSERT INTO gamerz_b2useronline VALUES (1056372803,'Guest','195.145.249.80','/orientek/kore/gamerz/b2archives.php')
INSERT INTO gamerz_b2useronline VALUES (1056373085,'GamerZ','202.156.40.184','/orientek/kore/gamerz/'); 

Is much more neater on the phpmyadmin side.

[Toly]

I see what you mean. I haven't had that problem not even when I used to back up my databases manually through my shell. Did you try perhaps restoring everything to a test dbase to see if it works the same way?

[GamerZ]

I see what you mean. I haven't had that problem not even when I used to back up my databases manually through my shell. Did you try perhaps restoring everything to a test dbase to see if it works the same way?

oh I think i found my error, i use --opt. hehe now it works. Thanks alot

[Toly]

oh I think i found my error, i use --opt. hehe now it works. Thanks alot

Well, the --opt method is used to optimize the dump process.

Here's a quote from a mysqldump article on sitepoint:

Specifying the --opt argument when we back up our database should theoretically give us the fastest possible dump for reading back into MySQL server (the "opt" stands for optimize). When we specify the --opt argument, the mysqldump utility creates a more sophisticated set of dump commands, which includes the "Drop TABLE IF EXISTS" statment. This deletes the table from the database if it already exists when the dump file is being used to restore the database. The dump also includes several table locking statements.

Just to give you an idea.

[GamerZ]

Well, the --opt method is used to optimize the dump process.

Here's a quote from a mysqldump article on sitepoint:

Just to give you an idea.

thanks alot

[elgumbo]

Hi Toly

Here is the script I am using :

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

The user.php is :

<?php
$user='db_username';
$pass='db_password';
?>

I am using the correct values for sitename, db_username and db_password

Again, all I get is "Problem: 1 " displayed on the screen.

[Toly]

Ok, I think the problem is with the directory you are storing your backups. You need to change the permissions for that directory to 777. That will allow the script to write to that directory.

Let me know if that works out for you.

[elgumbo]

Hey Toly ! You're a star !

The chmod was the problem - Now I'm off to find out about cron jobs

[Toly]

Cool. I'm glad it worked out.

For cron jobs, just check if you have that option in your control panel of your hosting account. If you do, go to this link:

http://www.redhat.com/support/resou.../cron/cron.html

It has good examples of how to set up cron jobs.

[elgumbo]

Cheers Toly

I got it working and did my first backup last night

I also added the following code to yours

 $date = date("dmy"); 

and amended

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

to

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

So I get a unique filename for incremental backups. I know this will take up more space (as the old file is not overwritten) but the db on this site is only small and will probably only require backing up every couple of weeks. I just liked the idea of being able to go back to an older version of the DB if required.

Thanks again for all your help.

[Toly]

Actually, that's how the script was set up in the first place. I took off the date option because since I got five dbases, they were taking a lot of space and it was annoying to delete the old versions all the time.

But it's always good to have the old versions of your dbase anyhow. I'll have to consider putting it back again.

[RedRose]

Hi.

I was really ashamed of asking my newbie questions here, but thanks to the support and help of sincere people I know, I now have the courage to ask.. I hope you would help me without laughing at me.

My first question is:

Should you call this script from your browser?

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

// Include your user and password $user $pass
// outside the public folder

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

My script is working ….