Creating MySQL backup from admin panel

Question

I need to create a button in my admin panel that when clicked, would generate a DB backup.

I can't seem to find a way to do it without command line or without using phpMyAdmin Export.

The button is to be done with php, or any other alternative.

Any help or advice or at least an idea would be welcome.

**Possible duplicate:** [http://stackoverflow.com/questions/3751069/backup-a-mysql-database-and-download-as-a-file](http://stackoverflow.com/questions/3751069/backup-a-mysql-database-and-download-as-a-file) — Keep Coding, Mar 16 '15 at 19:28
[This](http://stackoverflow.com/questions/6750531/using-a-php-file-to-generate-a-mysql-dump) should work for you. — Charlie Vieillard, Mar 16 '15 at 19:28

score 0 · Answer 1 · answered Mar 16 '15 at 19:35

You could make some more or less basic php-script for it, like this:

$link = mysqli_connect($host,$user,$pass,$dbname);
$tables = array();
$result = mysqli_query('SHOW TABLES');

while($row = mysqli_fetch_row($result)){
    $tables[] = $row[0];
}

foreach($tables as $table)
{
    $result = mysqli_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);

    $return.= 'DROP TABLE '.$table.';';
    $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);

Hope this helps you! This code just goes into every table, gets every row and stores it into $return and after that it writes $return into a files, which is named just automaticly! You only have to fill in some variables, like the DB-settings and the file properties at the end.

What this does is just simply grabs all info from the DB so it can be used again on the same DB with all the fields and properties, such as charsets already set up, correct? The only thing that soomewhat confuses me is why you need to drop the table? Could you explain the code a bit more? — Alexey, Mar 17 '15 at 06:49
Yes, that is also the part of code that depends on your choice. DO you want to remove the tables, or just backup the data. And indeed, the charsets etc are not taken with it. — Genie Kort, Mar 19 '15 at 16:28

Creating MySQL backup from admin panel

1 Answers1