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: stackoverflow.com/questions/6750531/… — slugonamission
– slugonamission, Commented Dec 5, 2013 at 10:36
I asked same kind of question long before: stackoverflow.com/questions/2491728/… — Kumar V
– Kumar V, Commented Dec 5, 2013 at 10:36

Artek Wisniewski · Accepted Answer · 2013-12-05 10:39:26Z

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 5, 2013 at 10:39

Artek Wisniewski

7975 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:06:49Z

1

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

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Dec 5, 2013 at 11:04

rsakhale

1,03613 silver badges27 bronze badges

2 Comments

CaptainCarl Over a year ago

Thanks for your answer. Some pointers: if ($table == '*'). it never is. And ereg_replace is deprecated!

rsakhale Over a year ago

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]);

diego · Accepted Answer · 2014-10-06 21:45:09Z

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

Collectives™ on Stack Overflow

Create MySQL DB export via PHP

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related