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?
$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
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); 
if ($table == '*'). it never is. And ereg_replace is deprecated!
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]);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 ); 