Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

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

Posted on 2012-08-16
Medium Priority
999 Views
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!
mysql-backup01.php
mysql-backup01.php
0
Question by:786aslamkhan
• 13
• 11
• 4

LVL 29

Expert Comment

ID: 38299588
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 ";

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

LVL 61

Expert Comment

ID: 38299610
0

Author Comment

ID: 38299742
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

Author Comment

ID: 38299754
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

LVL 61

Expert Comment

ID: 38299771
Have you tried call a script that does a mysqldump of the database?
0

LVL 29

Expert Comment

ID: 38299868
786aslamkhan,
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

Author Comment

ID: 38300090

Yes julianH ive tried the mysqldump of the database... But erroneously I'm sure as it doesn't work.
0

LVL 61

Accepted Solution

Julian Hansen earned 2000 total points
ID: 38300153
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";
?>

backup.bat
@REM *** PARAMETERS/VARIABLES ***
SET BackupDir=G:\TEMP
SET mysqldir=C:\Program Files\MySQL\MySQL Server 5.0\bin
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

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

Author Comment

ID: 38300311
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

LVL 61

Expert Comment

ID: 38300476
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

Author Comment

ID: 38300500
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

LVL 61

Expert Comment

ID: 38300528
No problem - just want to make sure you have covered all the options. Will waite for you to post back.
0

Author Comment

ID: 38300547
Thanks for your help and patience julianH!
0

LVL 29

Expert Comment

ID: 38300549
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

Author Comment

ID: 38300610
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

Author Comment

ID: 38304404
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

LVL 61

Expert Comment

ID: 38304479
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 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%

0

Author Comment

ID: 38304824
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

LVL 61

Expert Comment

ID: 38304907
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

Author Comment

ID: 38305121
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

LVL 61

Expert Comment

ID: 38305205
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

Author Comment

ID: 38307306
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

LVL 29

Expert Comment

ID: 38307644
Check what is really happening by placing

echo - %backupDir%\%backupfile% -

after line 9 ofthe bat file above
What is the result?
0

LVL 61

Expert Comment

ID: 38307742
aggree with fibo - lets see what it is outputing.
0

Author Comment

ID: 38308041

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 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

LVL 61

Expert Comment

ID: 38308142
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

Author Comment

ID: 38308280
Thanks a lot julianH!
0

LVL 61

Expert Comment

ID: 38308318
you are welcome
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
###### Suggested Courses
Course of the Month15 days, 9 hours left to enroll