Backup/restore mySQL database

1

Hello. I have been looking for a while now for a way to backup and restore my entire mySQL database. Googling it led nowhere (I found a bunch of tutorials for using phpmyadmin). I found some ressources on how to do it, but I still have a problem:
I have this:


$backup_file = $target_folder."/backup-".date("[Y-m-d]-[H-i-s]").".sql";
$query  = "SELECT * INTO OUTFILE '".$backup_file."' FROM ".$table;
$result = mysql_query($query);

which works fine, but outputs the .sql file in a place I can’t access (mySQL working folder). I need to control the output folder and I am completely unable to figure out how.

As a little contribution for all the help I got on these forums, and completely off subject, here is a little file I put together that enables an XML or

output for complete mySQL tables. Hopefully it can be of some use to someone: 

<?php
/*
FILE USAGE:
this file exports mySQL data as XML or table data;
usage is easy;
either you call the file with:

?name=[your table name]
this will ouput an XML file
or you call it with:

?name=[your table name]&output=table
which will ouput a table

alternatively, you might want to use:
?name=[your table name]&output=table&class=[class name]
which will enable you to format your table

you also must change the line below so it connects to your database
and selects it:
or you might uncomment the lines that follow and comment the include;
as you prefer. if you do, also uncomment the mysql_close in the end;
*/
$connection_file = "connect.php";
include_once($connection_file);

/*
$hostname = "xxx.xxx.xxx.xx or localhost";
$username = "your database username";
$password = "your database pass";
$database = "your database name";
$link = mysql_connect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR); 
mysql_select_db($database, $link);
*/
/*
GETTING POSTs and GETs
GETs precedes POSTs; if no GET is found,
the file will search for a POST variable;
*/
//ouput type: by default, this is 'XML'
$output = "xml";
if(isset($_GET['output'])){
    $output=$_GET['output'];
    }
else if(isset($_POST['output'])){
    $output=$_POST['output'];
    };
    
//table properties; by default, this is just border=0;
$table_class = "BORDER='0'";
if(isset($_GET['class'])){
    $table_class=$_GET['class'];
    }
else if(isset($_POST['class'])){
    $table_class=$_POST['class'];
    };
    
//table name: this is a requirement
$table_name = "items";
if(isset($_GET['name'])){
    $table_name=$_GET['name'];
    }
else if(isset($_POST['name'])){
    $table_name=$_POST['name'];
    };
    
$query = "SELECT * FROM `".$table_name."`";
$result = mysql_query($query,$link) or die("Data not found.");
//mysql_close();
$results_num = mysql_num_rows($result);

$fields_num = mysql_num_fields($result);
$fields = array();
for($i = 0; $i<$fields_num ; $i++){
    $field[$i] = mysql_field_name($result, $i);
    }

if($output=="xml"){
    $answer = "<Content-type: text/xml>";
    $answer = "<?xml version=\"1.0\"?>
";
    $answer .= "<ANSWERS>
";}
else{
    $answer = "<TABLE BORDER='1' CLASS='".$table_class."'>";
    $answer .= "	<TR>";
    for($i = 0; $i<$fields_num ; $i++){
        $answer .= "<TH>".$field[$i]."</TH>";
        };
    $answer .="	</TR>
";
    };
    
for($i = 0 ; $i < $results_num; $i++){
    $row = mysql_fetch_assoc($result);
    //opening item:
    if($output=="xml"){
        $answer .= "	<".$table_name.">
";
        }
    else{
        $answer .= "	<TR>
";
        };
    //fillin item:
    for($n = 0; $n <$fields_num; $n++){
        if($output=="xml"){
            $answer .= "		<".$field[$n].">".$row[$field[$n]]."</".$field[$n].">
";
            }
        else{
            //checking if field is empty
            if($row[$field[$n]]==""){
                //if it is, put an &nbsp; tag inside
                $answer .= "		<TD>&nbsp;</TD>
";
                }
            else{
                $answer .= "		<TD>".$row[$field[$n]]."</TD>
";
                };
            }
        }
    //closing item:
    if($output=="xml"){
            $answer .= "	</".$table_name.">
";
        }
    else{
        $answer .= "	</TR>
";
        };
}

$answer .= "</ANSWERS>";

echo $answer;
?>

edit: attached a more advanced version of the file, which can list all tables in a given database