PHP function to import data

Backup PHP script mysqldump

[tomda]

Reference (close topic) [webmasterworld.com...]

This is a php function to upload .sql to the server

system("/usr/bin/MySQL -u%%USERNAME%% -p%%PASSWORD%% -h%%HOST%% %%DATABASE%% < %%DOCUMENT_ROOT%%upload_dir/dump.sql", $fp); if ($fp==0) echo "imported data"; else echo "error importing data";

Is there a way to import my sever-database to my local server dump file using PHP script. I know that I can use the PHPMyAdmin transmit command but I would prefer to have a PHP script (easier and safer - knowing I protect the file with .htaccess and .htpasswd).

I am sure about the system at the beginning of the script? Also, does such script will open a save/open dialog box?

?system? ("/webserverpath/bin/mysqldumb -u%%USERNAME%% -p%%PASSWORD%% -h%%HOST%% %%DATABASE%% < %%DOCUMENT_ROOT%%import_dir/dump.sql", $fp); if ($fp==0) echo "backup done"; else echo "error backup not done";

Any though? Or a better way to do backups?

Thanks

[sabai]

Any though? Or a better way to do backups?

Install the mysql binaries on your local system and use the program 'mysqldump' to create a database dump on your local machine...

mysqldump -p -r mydatabase.sql -h hostname databasename

then load it into your local db with

mysql localdatabasename < mydatabase.sql

[tomda]

Thansk Sabai for your response.

On the local machine - no problem...

But can do the same on the server side using PHP script
What I would like is to request the server to send me the sql.file in an email attachment for example and periodically).

Tommy

[BitBanger]

Appologies for the code dump, but here is a function I use to email a backup of a MySQL database. It doesn't attempt to handle BLOB data, and isn't tested for anything other than my specific needs. Use it as you will as a starting point.

function email_db($dbname,$to) { 
global $dbh; 
$results = mysql_query("SHOW TABLE STATUS",$dbh); 
while ($myrow = mysql_fetch_assoc($results)) {; 
$tables[] = $myrow; 
} 
$sql = ''; 
$sql .= "#SQL Dump by Dean Brown\n" 
. "#Version 0.1\n" 
. "#\n" 
. "#Generation Time: " . date("M j, Y \a\\t g:i A") . "\n" 
. "#\n" 
. "\n" 
. "USE {$dbname}\n" 
. "\n"; 
foreach ($tables as $table) { 
$query = "SHOW COLUMNS FROM `{$table['Name']}`"; 
//echo $query; 
$results = mysql_query($query,$dbh); 
$sql .= "\n# --------------------------------------------------------\n" 
. "\n" 
. "#\n" 
. "#\n" 
. "# Table structure for table `{$table['Name']}`\n" 
. "#\n" 
. "# Creation: {$table['Create_time']}\n" 
. "# Last Update: {$table['Update_time']}\n" 
. "#\n" 
. "#\n" 
. "\n" 
. "CREATE TABLE `{$table['Name']}` {\n"; 
while ($myrow = mysql_fetch_assoc($results)) { 
$sql .= " `{$myrow['Field']}` " 
. "{$myrow['Type']} " 
. ((strcmp($myrow['Null'],'YES'))? 'NOT NULL ' : '')  
. ((isset($myrow['Default']))? "default '{$myrow['Default']}'" : '') 
. "{$myrow['Extra']} " 
. ",\n"; 
} 
$query = "SHOW INDEX FROM {$table['Name']}"; 
$results = mysql_query ($query,$dbh); 
while ($myrow = mysql_fetch_assoc($results)) { 
$sql .= " UNIQUE KEY `{$myrow['Key_name']}` (`{$myrow['Column_name']}`)\n"; 
} 
$sql .= "} TYPE={$table['Type']} " 
. "AUTO_INCREMENT={$table['Auto_increment']} "  
. ((strlen($table['Comment']) == 0)? '' : "COMMENT='{$table['Comment']}'") 
. ";\n"; 
$sql .= "\n" 
. "#\n" 
. "# Dumping data for table `{$table['Name']}`\n" 
. "#\n" 
. "\n"; 
$results = mysql_query ("SELECT * FROM `{$table['Name']}` WHERE 1",$dbh); 
if (!mysql_num_rows($results) == 0) { 
$sql .= "INSERT INTO `{$table['Name']}` VALUES"; 
while ($myrow = mysql_fetch_row($results)) { 
$sql .= "\n("; 
foreach ($myrow as $value) { 
$sql .= output_data($value) . ", "; 
} 
$sql = substr($sql,0,-2) . "),"; 
} 
$sql = substr($sql,0,-1) . ";\n"; 
} 
$sql .= "\n"; 
} 
$boundary = md5(uniqid(time(),1))."_xmail"; 
$backupname = "{$dbname}_" . date("Ymd"); 
$attachment = "--{$boundary}\r\n" 
. "Content-Type: application/octet-stream; name=\"{$backupname}.sql\"\r\n" 
. "Content-Transfer-Encoding: base64\r\n" 
. "Content-Disposition: attachment; filename=\"{$backupname}.sql\"\r\n\r\n" 
. chunk_split(base64_encode($sql))."\r\n" 
. "--{$boundary} \r\n"; 
$subject = "<put your subject here>"; 
$msg = "<put your message here>\n" 
. "Generated on: " . date("M j, Y \a\\t g:i A") . "\n"; 
$headers = "FROM: Web Server <put your server ID here>\r\n" 
. "MIME-Version: 1.0\r\n" 
. "Content-Type: multipart/mixed; boundary=\"{$boundary}\""; 
$body = "--{$boundary}\r\n" 
. "Content-Type: text/plain; charset=iso-8859-1; format=flowed\r\n" 
. "Content-Transfer-Encoding: 8bit\r\n\r\n" 
. "{$msg}\r\n\r\n{$attachment}"; 
mail($to,$subject,$body,$headers); 
} 

[tomda]

Thanks BitBanger,

I will give it a try...

Tommy