back up mysql database using php

Question

I'am using the code below to make a backup to mysql database . but when I import the backup file to new database the file imported successfuly but the new database is empty(no tables).

this is the code which I use:

<?php
  $dbhost = 'localhost:3036';
  $dbuser = 'root';
  $dbpass = 'rootpassword';

   $backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
   $command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ".
       "test_db | gzip > $backup_file";

   system($command);
   ?>

Answer 1

I think this script will be helpful to you for taking dabase backup

<?php
backup_tables(‘hostaddress’,'dbusername’,'dbpassword’,'dbname’);

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = ‘*’)
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == ‘*’)
{
$tables = array();
$result = mysql_query(‘SHOW TABLES’);
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(‘,’,$tables);
}

//cycle through
foreach($tables as $table)
{
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);

$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$return.= “\n\n”.$row2[1].”;\n\n”;

for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= ‘INSERT INTO ‘.$table.’ VALUES(‘;
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace(“\n”,”\\n”,$row[$j]);
if (isset($row[$j])) { $return.= ‘”‘.$row[$j].’”‘ ; } else { $return.= ‘”"‘; }
if ($j<($num_fields-1)) { $return.= ‘,’; }
}
$return.= “);\n”;
}
}
$return.=”\n\n\n”;
}

//save file
$handle = fopen(‘db-backup-’.time().’-’.(md5(implode(‘,’,$tables))).’.sql’,'w+’);
fwrite($handle,$return);
fclose($handle);
}
?>

Answer 2

First of all, the .sql extension is not so correct, what you get with SELECT ... INTO OUTFILE is a comma (or other delimiter) delimited text file, not an SQL statements file. It's more a code readability issue but better name your file ad file.txt or file.csv (if delimiters are CSV-compliant).

That said, if you import back your files with

LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE your-table-name;`

the backup/import should work but PROVIDED you create the table at first with a CREATE TABLE. So this leaves you the burden to recreate the schema code-wise, very painful. It is ok if you use already have the schema (f. ex. exporting/importing into the very same database).

---

Another try if you have exec permissions is to use mysqldump. For Linux systems it should be:

exec("/usr/bin/mysqldump -uusername-ppassword databasename | gzip -9 > /path/to/backupfile.gz");

to backup the whole db, with additional benefit of GZIP compression. To backup a specific table just add its name next to databasename.

Answer 3

This code doesn't actually store the schema for table so when you try to import it you're only importing the data.

You can either create the table by hand or use the cli utility mysqldump which will help.

It is possible to dump the data and schema via php but would require a bit more work.