How do I execute a Mysql database backup with a Php script?

Hi everyone!
I need to make a reliable backup(entire database) of my mySql database each time I logout of my web-application by including the backup script in the begining of logout script. I am using the XAMPP webserver on a Windows7 machine.
I've gone through many posts at our experts-exchange and also at various websites on the net. It seems very simple but I cannot get it to work as is quite evident I'm doing something wrong!
I've tried the "mysqldump" and the "SELECT INTO OUTFILE..." in Php scripts but obviously I am not doing it right!
Can anyone please help me?
Thanks in advance!
mysql-backup01.php
mysql-backup01.php
786aslamkhanAsked:
Who is Participating?
 
Julian HansenCommented:
What happens when you tried the mysqldump - did you get an error or did it just not work?

Idea is - create a batch file that does the dump - test that - if it works try execing that from php using the following script.
<?php
echo "Backing up";
system('G:\projects\sandbox\ee\backup.bat');
echo "done";
?>

Open in new window

backup.bat
@REM *** PARAMETERS/VARIABLES ***
SET BackupDir=G:\TEMP
SET mysqldir=C:\Program Files\MySQL\MySQL Server 5.0\bin
SET mysqlpassword=password
SET mysqluser=root
SET databaselist=database
@REM *** EXECUTION ***
@REM Change to mysqldir
C:
CD %mysqldir%
@REM dump/backup ALL database, this is all in one line
mysqldump -u %mysqluser% -p%mysqlpassword% --databases %databaselist% >%BackupDir%\FullBackup_%random%.sql

Open in new window

The above requires that the Internet Guest account has read / execute access to the %windir%\system32\cmd.exe file - which I personally don't like - but it works.
Another option is to have a filesystem watcher app watching a particular folder for a file. When the file is put there it executes it and have your script create the above batch file (or copy it in).
If the backup takes a long time your php script is going to timeout so you will have to use set_time_limit appropriately or consider a solution such as the filewatcher approach.
0
 
Bernard S.CTOCommented:
Although your script seem OK, there is something wrong.
Your current design packs togeteher several possible source of problems: sprintf, DOCUMENT_ROOT, system, MySQL... I would try to seperate them and to track / trace what is happening.
eg, something like:
  // Execute mysqldump command. 
  // It will produce a file named $db-$year$month$day-$hour$min.gz 
  // under $DOCUMENT_ROOT/$backupdir 
$theCommand = sprintf( 
    'mysqldump --opt -h %s -u %s -p%s %s | gzip > %s/%s/%s-%s%s%s-%s%s.gz',                                                  
    $host, 
    $user, 
    $pass, 
    $db, 
    getenv('DOCUMENT_ROOT'), 
    $backupdir, 
    $db, 
    $year, 
    $month, 
    $day, 
    $hour, 
    $min 
  );
echo "Preparing the system command [ ", $theCommand, " ] ...<hr>";
$result=system($theCommand);
if ($result) {echo "The last line of the result was ", $result; }
       else echo "*** There was an error processing this command ";

Open in new window

Additionally, the comments at page http://www.php.net/manual/en/function.system.php suggest several elements you should consider, all of them related to your command:
- check the directory where you want to place your dump: does it exist? have you right to write there? has THE SERVER the right to write there? in doubt, make it writeable by everybody (unless of course the data should not be visible by colleagues or ousiders)
- if there is an error, open a dos box, paste the system command which was displayed in the web page, and run it: any error message THERE should be traced and corrected, so that you system command is correct.
- use a complete path descriptor for all files, including mysqldump.exe and the output file
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
786aslamkhanAuthor Commented:
fibo Hi!
Thanks for responding to my question! I'll try out your suggestions and get back in a couple of hours as I had to pop out of my work place for something important. Thanks until then!
0
 
786aslamkhanAuthor Commented:
Hi julianH!
Thanks for the links. I've been through all of them earlier and have still not been able to get the job done. I've tried only the PHP options. Not for example the backup to Amazon s3 using Python etc. Thanks a lot though!
0
 
Julian HansenCommented:
Have you tried call a script that does a mysqldump of the database?
0
 
Bernard S.CTOCommented:
786aslamkhan,
Thx for your answer.
The point is that you are probably very close to the solution. Just a problem with some obscure detail that we first need to find and isolate, then solve. (On a purely intellectual basis, we might also find that it is not possible that way. However, you have prepared the work largely enough so that we know that it will eventually work!)
0
 
786aslamkhanAuthor Commented:
Thanks fibo for your encouragement.

Yes julianH ive tried the mysqldump of the database... But erroneously I'm sure as it doesn't work.
0
 
786aslamkhanAuthor Commented:
julianH When I run the mysql dump through PHP, the script echoes a "done" message as it completes execution, but I dont see the backup file in the folder with the current backup. I created the blank .sql file in the designated directory and I think I correctly gave it write permissions... I could have made some mistake there.  Thank you so much for your involvement and interest.

fibo I have put forward slashes (/) in the path... should it be backward slashes (\) instead? I'm not on my machine but when I get back I'll upload the code. I uploaded one file twice by mistake instead of two different files, I'm so sorry for that. I've addressed this to you as you had suggested a review of the path and read/write permissions in your first post I think. In the meanwhile, thanks a ton!
0
 
Julian HansenCommented:
Ok but just so you know it works fine on my Windows system - did a full test before uploading.

Did you try running the bat file from the command prompt to see if that works - if it does not you need to sort the settings in the bat file so that it works - and then test with the php script.

Make sure you adjust the all the settings in the BAT file that should be adjusted.
0
 
786aslamkhanAuthor Commented:
julianH I'm sorry I couldn't test anything as I'm  out and have no access to my machine. I was just responding what I know and experienced from my Cell phone.  I'll get back and test  and report back to you. I'm so sorry for the inconvenience.
0
 
Julian HansenCommented:
No problem - just want to make sure you have covered all the options. Will waite for you to post back.
0
 
786aslamkhanAuthor Commented:
Thanks for your help and patience julianH!
0
 
Bernard S.CTOCommented:
I have put forward slashes (/) in the path...
Not sure it matters at this stage. We really need to get some error message to understand what happens.

Some of the possible errors I have in mind
- the target directory is not what you expect
- there is no path to the dump program

other possible causes, far behind the 2 above:
- there is no path to the gzip program
- the gzip program creates the file in some obscure directory... on which he might have no rights...


Note also at the page http://www.php.net/manual/en/function.system.php
the following text
   Note: When safe mode is enabled, you can only execute files within the safe_mode_exec_dir. For practical reasons, it is currently not allowed to have .. components in the path to the executable.
not sure how this works (or not!) with windows!

Did you try my mod? Did you try to paste the generated command into a dosbox? That would probably tell us something...
0
 
786aslamkhanAuthor Commented:
fibo sorry I couldn't try your suggestion as I had to get out for something important. I'll get back and try the suggestions out and report back. Thanks.
0
 
786aslamkhanAuthor Commented:
julianH thanks for your patience and solution! I tried all the things discussed above and finally your code worked. I only need to change the "random" variable in the .bat file in line no. 12: (...FullBackup_%random%.sql)  to include a timestamp instead and since I don't know the  SHELL SCRIPTING language of the .bat file I'm lost. Can you please edit the variable I should substitute instead of "random"?

fibo Thanks for your suggestions and patience. I have awarded the points to julianH as he had provided me with the solution and the code. Thanks a ton for your advice and patience with me.
0
 
Julian HansenCommented:
You are welcome - glad it worked.
Here you go - added the backupfile parameter which puts a date time stamp on the back.
@REM *** PARAMETERS/VARIABLES ***
SET BackupDir=G:\TEMP
SET mysqldir=C:\Program Files\MySQL\MySQL Server 5.0\bin
SET mysqlpassword=password
SET mysqluser=root
SET databaselist=database
SET backupfile=fullbackup_20%date:~6,2%_%date:~3,2%_%date:~0,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%.sql
@REM *** EXECUTION ***
@REM Change to mysqldir
C:
CD %mysqldir%
@REM dump/backup ALL database, this is all in one line
echo mysqldump -u %mysqluser% -p%mysqlpassword% --databases %databaselist% >%BackupDir%\%backupfile%

Open in new window

0
 
786aslamkhanAuthor Commented:
Thanks a lot julianH! Its a bit complicated and not giving the desired result. I shall try and figure it out or then as a related question. Cheers!
0
 
Julian HansenCommented:
What format would you like? Currently should come out as fullbackup_2008_08_17_16_15_23.sql - but can change it if you need
0
 
786aslamkhanAuthor Commented:
Its not creating the .sql file in the same folder as earlier, in fact I dont know where its being created.
What I mean is that with the new code I cannot see any file in the BackupDir at all

If the format is fullbackup_YYYY_MM_DD_HH_MM_SS.sql its ok.

The folder/directory has changed I think.Thanks!
0
 
Julian HansenCommented:
It should create it in the folder specified by BackupDir - the first parameter in the file. Check that because if you copied the above script it will be set to my local environment settings.

File gets saved to BackupDir\fullbackup_YYYY_MM_DD_HH_MM_SS.sq
0
 
786aslamkhanAuthor Commented:
Its really weird... works perfectly with "random" in the name, but with the %backupDir%\%backupfile% it can't find the specified path (at the command prompt).
0
 
Bernard S.CTOCommented:
Check what is really happening by placing

echo - %backupDir%\%backupfile% -

after line 9 ofthe bat file above
What is the result?
0
 
Julian HansenCommented:
aggree with fibo - lets see what it is outputing.
0
 
786aslamkhanAuthor Commented:
fibo thanks for your comments and interest! Really appreciate it!

julianH and fibo, the output "echoed" in the browser is (I hope that's what you wanted me to do):

Backing up C:\xampp\htdocs\someDir>SET BackupDir=C:\xampp\htdocs\someDir\backup C:\xampp\htdocs\someDir>SET mysqldir=C:\Xampp\mysql\bin C:\xampp\htdocs\someDir>SET mysqlpassword=pword C:\xampp\htdocs\someDir>SET mysqluser=user C:\xampp\htdocs\someDir>SET databaselist=db C:\xampp\htdocs\someDir>SET backupfile=fullbackup_/1_ 0_Sa_21_23_18.sql C:\xampp\htdocs\someDir>echo C:\xampp\htdocs\someDir\backup\fullbackup_/1_ 0_Sa_21_23_18.sql C:\xampp\htdocs\someDir\backup\fullbackup_/1_ 0_Sa_21_23_18.sql C:\xampp\htdocs\someDir>C: C:\xampp\htdocs\someDir>CD C:\Xampp\mysql\bin C:\xampp\mysql\bin>mysqldump -u user -ppword --databases db 0_Sa_21_23_18.sql 1>C:\xampp\htdocs\someDIr\backup\fullbackup_/1_ done

I have changed directory names, usernames, passwords etc.

I changed theSET backupfile=fullbackup_%date:~6,2%_%date:~3,2%_%date:~0,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%.sql
to
SET backupfile=fullbackup_%date:~4,2%%date:~7,2%%date:~10,4%%time:~0,2%%time:~3,2%%time:~6,2%%.sql
Now it works perfectly. I think that there was some error in the date-time variables. What do you think?
Thanks a ton guys for your advice, time, expertise, and interest- I have learned a lot!!!

I'm asking a related question by adding a link to this question. I don't think that it would be fair to go on asking questions without offering any reward! Thanks!
0
 
Julian HansenCommented:
Yup, there is the problem

C:\xampp\htdocs\someDir\backup\fullbackup_/1_ 0_Sa_21_23_18.sql

The date is not converting properly

On my system echo %date% returns 18/08/2012 - so formatting was based on that.

But good that it is working
0
 
786aslamkhanAuthor Commented:
Thanks a lot julianH!
0
 
Julian HansenCommented:
you are welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.