Mysqldump using PHP on Windows

Hi,

I have built a small corporate database driven intranet which is currently running on a Windows 2000 machine.

I would like to give the company the ability to back up the database at will by calling a script rather than pre-scheduling it. I have generated code as detailed below which produces an sql file, but it is an empty 0Kb file, correctly named but not a database backup and I wondered if anyone could shed any light on why this is happening.

I intend also to give the ability to specify a filename for the requested backup but I think I’m ok with that.

Thanks in anticipation.


$filename = (string)$_GET['filename'];
 
if(isset($filename))
 
exec('mysqldump -h localhost -u user -p password fb > insertedfilename.sql');
 

If I’m not mistaken mysqldumb doesn’t take spaces.

mysqldump -hlocalhost -uusername -ppassword

Search mysqldump in this forum. I had a thread about how to do MySQL backups once. Might be of help to you.

I wrote something you might find useful. Its a short script that loops through all db’s in mysql and creates a new directory with todays date. Inside it saves an .sql dump of each db using the naming convention of date_dbname.sql. Feel free to use/alter it to suit your needs. If you have any questions, don’t hesitate to ask. :slight_smile:


<?php

//path to mysql dump utility
$dump_path = "c:\\\\apache\\\\mysql\\\\bin\\\\";

// location to store backups
$save_path = "e:\\\\backup\\\\";

// mysql credentials
$host = "yourhost";
$user = "yourusername";
$pass = "yourpassword";

// mysql connection
mysql_connect( $host , $user , $pass );

// format dir name
$today = date("m-d-Y");

//check if directory exists otherwise create it
if ( !file_exists ( is_dir ($save_path ) ) )
{
    mkdir( $save_path . $today );
}

// list all mysql dbs
$result = mysql_list_dbs();

// init counter var
$i = 0;

// list all databases in mysql
while ( $i < mysql_num_rows ( $result ) )
{
    $tb_names[$i] = mysql_tablename ( $result, $i );
       $i++;
}

// loop through table names and do the dump
for ( $i=0; $i<count($tb_names); $i++ )
{
    $do = $dump_path . "mysqldump -h" . $host . " -u" . $user . " -p" . $pass . " --opt " . $tb_names[$i] . " > " . $save_path . "\\\\" . $today . "\\\\" . $tb_names[$i] . ".sql";
    passthru($do,$result);
}

?>

Thanks for the help, I’ll give it a try and get back to you.

Hi!

I had a small question. I hope that it’s not really off-topic.

If I dump a database remotely with phpmyadmin, than the whole data will be in the memory or swap file until I’ve downloaded it?
Or the mysqldump will save that file, and phpmyadmin points to that file?

Sorry if that’s off-topic.

Don’t quote me on this, however, I believe that dumping from phpMyAdmin loads the entire dump into memory. Once the stream has ended, the memory is then flushed. You could always check the official source documentation to be sure.

Yeah… finally I’ve found the info about that. :slight_smile: Thx.
With phpmyadmin db export, we are limited to the avaiable memory. It loads the data into the memory first. So If I have a large database then I can’t use this functionality… I need to call the mysqldump itself to save a file, and then I can download it.

Having now tried the 2 solutions offered, the results are as follows.

I tried altering my current script to remove the spaces from the user and password fields but the mysqldump still put out an empty file.

I tried the script kindly pointed out to me by Messiah, it worked like a dream in terms of backing up all of my 18 small trial databases but all files output were empty 0Kb files. If I run mysqldump from the command line all is well but it seems that if I use a script I always get empty files.

I really am mystified as to why this should happen as in both cases the same utility is being used with the same commands.

Thanks for any offered help anyway.

echo out the $do variable and compare it with the call from the command line. Is it exactly the same?

That is strange indeed. I concur with Gaheris, trying echoing out my $do variable and compare with the line you are manually using via command line. If they are the same, I haven’t a clue. If they are different, trying changing the $do to match what does work for you via command line. Also, did you actually check the .sql file created? I know its saying 0kb., but did you actually open it up in a text editor to verify that it is indeed a 0byte file?

Problem solved.

Echoing out the $do variable revealed an extra slash in the command, I edited the script to remove it and I now get 18 perfect backups…I don’t know if this is a system specific thing but it now works perfectly.

Thanks for all of the help, I am most grateful.

To expand on this, would it not be better to chdir() into the \mysql\bin directory first?

I suppose performing the chdir() first could make life a little easier but as long as the path to the utility is correct in the current script it probably wouldn’t be a lot of an advantage. Both methods would undoubtedly work.

Were you thinking more in terms of taking out an area of potential failure or misconfiguration?