Create MySQL DB export via PHP

Question

I want my entire database to be exported to a string(Such as PHPmyadmin does an export) in a PHP file. I want to export the database I'm connected to at that moment of time.

I can't really find a definite answer via Google, just a lot of people who want to export to CSV and stuff. I just want the export query.

Is this even a possibility? If so, how?

This does it to a file, but you can just remove the redirection to have it write to a string instead: http://stackoverflow.com/questions/6750531/using-a-php-file-to-generate-a-mysql-dump — slugonamission, Dec 05 '13 at 10:36
I asked same kind of question long before: http://stackoverflow.com/questions/2491728/php-code-to-mysql-database-export — Kumar V, Dec 05 '13 at 10:36
Duplicate question: http://stackoverflow.com/questions/6750531/using-a-php-file-to-generate-a-mysql-dump — Benubird, Dec 05 '13 at 10:36

score 4 · Accepted Answer · answered Dec 05 '13 at 10:39

4

$dump = shell_exec('mysqldump --user=user --password=asdasd --host=localhost db_name');

alternatively you can use php implementation of mysqldump, available on https://github.com/clouddueling/mysqldump-php

answered Dec 05 '13 at 10:39

Artek Wisniewski

797
5
13

score 1 · Answer 2 · edited May 23 '17 at 12:06

You will need to write a PHP function that helps you export all your MySQL data into a .sql file which later you can gzip and save

backup_tables('localhost','username','password', 'databasename');


/* 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);

        $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] = str_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);
}

Link - http://davidwalsh.name/backup-mysql-database-php

For doing gzip, you can refer to this

// Name of the file we are compressing
$file = "test.txt";

// Name of the gz file we are creating
$gzfile = "test.gz";

// Open the gz file (w9 is the highest compression)
$fp = gzopen ($gzfile, 'w9');

// Compress the file
gzwrite ($fp, file_get_contents($file));

// Close the gz file and we are done
gzclose($fp);

Link - https://stackoverflow.com/a/6073415/2482430

Thanks for your answer. Some pointers: `if ($table == '*')`. it never is. And `ereg_replace` is deprecated! — CaptainCarl, Dec 05 '13 at 12:39
`if ($table == '*')` refers to All tables should be considered for doing backup `$row[$j] = ereg_replace("\n","\\n",$row[$j]);` can be replaced with `$row[$j] = str_replace("\n","\\n",$row[$j]);` — rsakhale, Dec 18 '13 at 09:12

score 1 · Answer 3 · answered Oct 06 '14 at 21:45

Take a look here! It is a native solution written in php. You won't need to exec mysqldump, or cope with incomplete scripts. This is a full mysqldump clone.

You can install it using composer, or just download the php file, and it is as easy as doing:

<?php

use Ifsnop\Mysqldump as IMysqldump;

try {
    $dump = new IMysqldump\Mysqldump('database', 'username', 'password');
    $dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
    echo 'mysqldump-php error: ' . $e->getMessage();
}

?>

It supports advanced users, with lots of options copied from the original mysqldump.

All the options are explained at the github page, but more or less are auto-explicative:

$dumpSettingsDefault = array(
    'include-tables' => array(),
    'exclude-tables' => array(),
    'compress' => 'None',
    'no-data' => false,
    'add-drop-table' => false,
    'single-transaction' => true,
    'lock-tables' => false,
    'add-locks' => true,
    'extended-insert' => true,
    'disable-keys' => true,
    'where' => '',
    'no-create-info' => false,
    'skip-triggers' => false,
    'add-drop-trigger' => true,
    'hex-blob' => true,
    'databases' => false,
    'add-drop-database' => false,
    'skip-tz-utc' => false
);

Create MySQL DB export via PHP

3 Answers3