[MySQL] Backup MySQL Database

[mikeaag]

Hey,

I have a database that i want to back up every sunday night.

I have a cron ready and waiting to run a php file, but im having troubles getting the php script to actually backup the database.

Currently i have:

<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "blog";

mysql_connect($host, $username, $password) or die('error1');
mysql_select_db($database) or die('error2');

$backupFile = $database . date("Y-m-d-H-i-s") . '.txt';
$command = "E:\xampplite\mysql\bin\mysqldump --opt -h $host -u $username -p $password $database > $backupFile";
system($command, $status);
if($status) == 1)
{
echo "Back up successful";
}
else
{
echo "Back up failed!";
}

mysql_close();
?>

this doesn't seem to be working though, and I don't seem to be able to get the script to give me an error message saying what's wrong :(

Can anyone see where im going wrong here?

Thanks in advance

[metal_dragen Veteran]

Well, for starters, unless by cron you mean a scheduled task, you are going to have trouble executing a program in a Windows-specific location on a *nix box. Make sure the location of the mysqldump executable is correct (i.e. try pasting the command into a shell window and executing it - if it works, your problem is in the script; see below)

Try using exec() rather than system() since system() is designed to output the result of the external program.

One other thing to check is you may want to ensure the script is not timing out, which is possible once you get above ~20-30MB in size with the default PHP settings. If you can, adjust the max_execution_time variable in your php.ini file to allow the script to run longer.

One last thing to consider is that if you have access to cron on your box, you don't even need a PHP script - you can slap the mysqldump line directly into a cron job and it'll execute it.

[mikeaag]

  On 18/03/2010 at 22:17, metal_dragen said:

Well, for starters, unless by cron you mean a scheduled task, you are going to have trouble executing a program in a Windows-specific location on a *nix box. Make sure the location of the mysqldump executable is correct (i.e. try pasting the command into a shell window and executing it - if it works, your problem is in the script; see below)

Try using exec() rather than system() since system() is designed to output the result of the external program.

One other thing to check is you may want to ensure the script is not timing out, which is possible once you get above ~20-30MB in size with the default PHP settings. If you can, adjust the max_execution_time variable in your php.ini file to allow the script to run longer.

One last thing to consider is that if you have access to cron on your box, you don't even need a PHP script - you can slap the mysqldump line directly into a cron job and it'll execute it.

Thanks for the reply.

Im running the code in XAMPP atm, so the file path i have in there is ok atm :D but will be changed when it goes onto the actual server.

I will try it with exec() instead and see if that works better.

Could you please explain how i can do a mysqldump from the cron job, im not familiar with cron jobs at all :(

Thanks in advance

[bolerodan]

  On 20/03/2010 at 00:17, mikeaag said:

Thanks for the reply.

Im running the code in XAMPP atm, so the file path i have in there is ok atm :D but will be changed when it goes onto the actual server.

I will try it with exec() instead and see if that works better.

Could you please explain how i can do a mysqldump from the cron job, im not familiar with cron jobs at all :(

Thanks in advance

You keep saying Cron yet you're using a windows based path in your script? So we're to assume that you are running this on a windows box, trying to run this with scheduled tasks in windows?

does the script run fine on its own? (when you are not trying to execute it in scheduled / cron tasks?)

[mikeaag]

  On 20/03/2010 at 00:19, bolerodan said:

You keep saying Cron yet you're using a windows based path in your script? So we're to assume that you are running this on a windows box, trying to run this with scheduled tasks in windows?

does the script run fine on its own? (when you are not trying to execute it in scheduled / cron tasks?)

Thanks for the reply.

i thought i was clear in my last post, but i will make it 100% now.

the live site is running Linux.

i am using XAMPP to develop this solution, and my computer is running windows. Therefore, the code i have currently has a windows path in it.

when everything is completed and working, i will upload this to the server, and change the paths to that they are relevant to the server.

so i will be using a Cron, at least i believe this is the best method to run a database backup script every sunday night.

should i not be using a Cron to do this? (remember its a Linux based server NOT windows)

as to your question, no, the script does not work properly. it just creates a blank .txt file with the name i specified, it doesn't actually do the database backup part :(

how would i go about running the backup directly from the cron? can someone give me an example of the code please

Thanks in advance

[Gocom]

  On 20/03/2010 at 00:57, mikeaag said:

how would i go about running the backup directly from the cron? can someone give me an example of the code please

It's the same command, just give it thru cron/bash.

[metal_dragen Veteran]

First off, run this:

E:\xampplite\mysql\bin\mysqldump --opt -h $host -u $username -p $password $database > $backupFile

in a shell window on your Windows box (obviously replacing the PHP variables with actual values) and ensure that it works. Because if that doesn't work on its own, your script is irrelevant; you'll have to fix the issue in the command first.

Once you know that is functioning, then your script should function normally, as there is nothing wrong with the way it is written per se.

As for setting up a cron job on your server, it will depend on what kind of access you have. Do you have shell access to the box? Will you be setting up the cron job through a control panel (like cPanel or similar)?

An example crontab file for what you are doing would be:

SHELL=/bin/sh
0 0 * * 0 /path/to/mysqldump --opt -h HOST -u USER -p PASS DBNAME > /path/to/backup.sql

Once that is created, you drop that file in the cron tabs directory and as long as the cron daemon is running, it will execute the command every Sunday at midnight.

[mikeaag]

  On 20/03/2010 at 12:33, metal_dragen said:

First off, run this:

E:\xampplite\mysql\bin\mysqldump --opt -h $host -u $username -p $password $database > $backupFile

in a shell window on your Windows box (obviously replacing the PHP variables with actual values) and ensure that it works. Because if that doesn't work on its own, your script is irrelevant; you'll have to fix the issue in the command first.

Once you know that is functioning, then your script should function normally, as there is nothing wrong with the way it is written per se.

As for setting up a cron job on your server, it will depend on what kind of access you have. Do you have shell access to the box? Will you be setting up the cron job through a control panel (like cPanel or similar)?

An example crontab file for what you are doing would be:

SHELL=/bin/sh
0 0 * * 0 /path/to/mysqldump --opt -h HOST -u USER -p PASS DBNAME > /path/to/backup.sql

Once that is created, you drop that file in the cron tabs directory and as long as the cron daemon is running, it will execute the command every Sunday at midnight.

thanks for the reply,

So i run the line in shell and it worked fine, first time.

but still doesnt work when i run it through the php script. i will try setting up the Cron and just take out the php all together.

Thanks for all your help :D

[bolerodan]

  On 21/03/2010 at 21:24, mikeaag said:

thanks for the reply,

So i run the line in shell and it worked fine, first time.

but still doesnt work when i run it through the php script. i will try setting up the Cron and just take out the php all together.

Thanks for all your help :D

actually yeah as posted by metal_dragen, you do not need to write a php script to do this. Just have cron run the actual mysqldump program. His example does just that.