What would be a piece of PHP code for exporting a MySQL database to a .sql file?
I don't have shell access so I can't use exec. I've found some code but I looked through the code and this code is old and not well written. Most instructions I've found require shell access … the rest is outdated.
Thanks in advance.
If you dont have phpMyAdmin, you can write in php CLI commands such as login to mysql and perform db dump. In this case you would use shell_exec function.
In *nix systems, use the WHICH command to show the location of the mysqldump, try this :
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'test';
$mysqldump=exec('which mysqldump');
$command = "$mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname > $dbname.sql";
exec($command);
?>
Try the following.
Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:
<?php
$DB_HOST = "localhost";
$DB_USER = "xxx";
$DB_PASS = "xxx";
$DB_NAME = "xxx";
$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
die('Connection failed [' . $con->connect_error . ']');
}
$tableName = 'yourtable';
$backupFile = 'backup/yourtable.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysqli_query($con,$query);
?>
To restore the backup you just need to run LOAD DATA INFILE query like this:
<?php
$DB_HOST = "localhost";
$DB_USER = "xxx";
$DB_PASS = "xxx";
$DB_NAME = "xxx";
$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
die('Connection failed [' . $con->connect_error . ']');
}
$tableName = 'yourtable';
$backupFile = 'yourtable.sql';
$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysqli_query($con,$query);
?>
This tool might be useful, it's a pure PHP based export utility: https://github.com/2createStudio/shuttle-export
You can use this command it works or me 100%
exec('C:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump.exe -uroot DatabaseName> c:\\database_backup.sql');
note:C:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump.exe is the path for mysqldump app , check on your pc.
-uroot is -u{UserName}
If your database is protected with password then add after -uroot this sentense -p{YourPassword}
Best way to export database using php script.
Or add 5th parameter(array) of specific tables: array("mytable1","mytable2","mytable3") for multiple tables
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlUserName = "Your Username";
$mysqlPassword = "Your Password";
$mysqlHostName = "Your Host";
$DbName = "Your Database Name here";
$backup_name = "mybackup.sql";
$tables = "Your tables";
//or add 5th parameter(array) of specific tables: array("mytable1","mytable2","mytable3") for multiple tables
Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName, $tables=false, $backup_name=false );
function Export_Database($host,$user,$pass,$name, $tables=false, $backup_name=false )
{
$mysqli = new mysqli($host,$user,$pass,$name);
$mysqli->select_db($name);
$mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES');
while($row = $queryTables->fetch_row())
{
$target_tables[] = $row[0];
}
if($tables !== false)
{
$target_tables = array_intersect( $target_tables, $tables);
}
foreach($target_tables as $table)
{
$result = $mysqli->query('SELECT * FROM '.$table);
$fields_amount = $result->field_count;
$rows_num=$mysqli->affected_rows;
$res = $mysqli->query('SHOW CREATE TABLE '.$table);
$TableMLine = $res->fetch_row();
$content = (!isset($content) ? '' : $content) . "
".$TableMLine[1].";
";
for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0)
{
while($row = $result->fetch_row())
{ //when started (and every after 100 command cycle):
if ($st_counter%100 == 0 || $st_counter == 0 )
{
$content .= "
INSERT INTO ".$table." VALUES";
}
$content .= "
(";
for($j=0; $j<$fields_amount; $j++)
{
$row[$j] = str_replace("
","\
", addslashes($row[$j]) );
if (isset($row[$j]))
{
$content .= '"'.$row[$j].'"' ;
}
else
{
$content .= '""';
}
if ($j<($fields_amount-1))
{
$content.= ',';
}
}
$content .=")";
//every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num)
{
$content .= ";";
}
else
{
$content .= ",";
}
$st_counter=$st_counter+1;
}
} $content .="
";
}
//$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
$backup_name = $backup_name ? $backup_name : $name.".sql";
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"".$backup_name."\"");
echo $content; exit;
}
?>
I would Suggest that you do the folllowing,
<?php
function EXPORT_TABLES($host, $user, $pass, $name, $tables = false, $backup_name = false)
{
$mysqli = new mysqli($host, $user, $pass, $name);
$mysqli->select_db($name);
$mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES');
while ($row = $queryTables->fetch_row())
{
$target_tables[] = $row[0];
}
if ($tables !== false)
{
$target_tables = array_intersect($target_tables, $tables);
}
$content = "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";
SET time_zone = \"+00:00\";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--Database: `" . $name . "`
";
foreach ($target_tables as $table)
{
$result = $mysqli->query('SELECT * FROM ' . $table);
$fields_amount = $result->field_count;
$rows_num = $mysqli->affected_rows;
$res = $mysqli->query('SHOW CREATE TABLE ' . $table);
$TableMLine = $res->fetch_row();
$content .= "
" . $TableMLine[1] . ";
";
for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter = 0)
{
while ($row = $result->fetch_row())
{ //when started (and every after 100 command cycle):
if ($st_counter % 100 == 0 || $st_counter == 0)
{
$content .= "
INSERT INTO " . $table . " VALUES";
}
$content .= "
(";
for ($j = 0; $j < $fields_amount; $j++)
{
$row[$j] = str_replace("
", "\
", addslashes($row[$j]));
if (isset($row[$j]))
{
$content .= '"' . $row[$j] . '"';
}
else
{
$content .= '""';
} if ($j < ($fields_amount - 1))
{
$content.= ',';
}
}
$content .=")";
//every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
if ((($st_counter + 1) % 100 == 0 && $st_counter != 0) || $st_counter + 1 == $rows_num)
{
$content .= ";";
}
else
{
$content .= ",";
} $st_counter = $st_counter + 1;
}
} $content .="
";
}
$content .= "
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";
$backup_name = $backup_name ? $backup_name : $name . "___(" . date('H-i-s') . "_" . date('d-m-Y') . ")__rand" . rand(1, 11111111) . ".sql";
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"" . $backup_name . "\"");
echo $content;
exit;
}
?>
The enitre project for export and import can be found at https://github.com/tazotodua/useful-php-scripts.
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$table_name = "employee";
$backup_file = "/tmp/employee.sql";
$sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";
mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not take data backup: ' . mysql_error());
}
echo "Backedup data successfully
";
mysql_close($conn);
?>
Here is my code, This will backup MySQL database and store it in the specified path.
<?php
function backup_mysql_database($options){
$mtables = array(); $contents = "-- Database: `".$options['db_to_backup']."` --
";
$mysqli = new mysqli($options['db_host'], $options['db_uname'], $options['db_password'], $options['db_to_backup']);
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
$results = $mysqli->query("SHOW TABLES");
while($row = $results->fetch_array()){
if (!in_array($row[0], $options['db_exclude_tables'])){
$mtables[] = $row[0];
}
}
foreach($mtables as $table){
$contents .= "-- Table `".$table."` --
";
$results = $mysqli->query("SHOW CREATE TABLE ".$table);
while($row = $results->fetch_array()){
$contents .= $row[1].";
";
}
$results = $mysqli->query("SELECT * FROM ".$table);
$row_count = $results->num_rows;
$fields = $results->fetch_fields();
$fields_count = count($fields);
$insert_head = "INSERT INTO `".$table."` (";
for($i=0; $i < $fields_count; $i++){
$insert_head .= "`".$fields[$i]->name."`";
if($i < $fields_count-1){
$insert_head .= ', ';
}
}
$insert_head .= ")";
$insert_head .= " VALUES
";
if($row_count>0){
$r = 0;
while($row = $results->fetch_array()){
if(($r % 400) == 0){
$contents .= $insert_head;
}
$contents .= "(";
for($i=0; $i < $fields_count; $i++){
$row_content = str_replace("
","\
",$mysqli->real_escape_string($row[$i]));
switch($fields[$i]->type){
case 8: case 3:
$contents .= $row_content;
break;
default:
$contents .= "'". $row_content ."'";
}
if($i < $fields_count-1){
$contents .= ', ';
}
}
if(($r+1) == $row_count || ($r % 400) == 399){
$contents .= ");
";
}else{
$contents .= "),
";
}
$r++;
}
}
}
if (!is_dir ( $options['db_backup_path'] )) {
mkdir ( $options['db_backup_path'], 0777, true );
}
$backup_file_name = $options['db_to_backup'] . " sql-backup- " . date( "d-m-Y--h-i-s").".sql";
$fp = fopen($options['db_backup_path'] . '/' . $backup_file_name ,'w+');
if (($result = fwrite($fp, $contents))) {
echo "Backup file created '--$backup_file_name' ($result)";
}
fclose($fp);
return $backup_file_name;
}
$options = array(
'db_host'=> 'localhost', //mysql host
'db_uname' => 'root', //user
'db_password' => '', //pass
'db_to_backup' => 'attendance', //database name
'db_backup_path' => '/htdocs', //where to backup
'db_exclude_tables' => array() //tables to exclude
);
$backup_file_name=backup_mysql_database($options);
I would Suggest that you do the folllowing,
<?php
$con = mysqli_connect('HostName', 'UserName', 'Password', 'DatabaseName');
$tables = array();
$result = mysqli_query($con,"SHOW TABLES");
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
$return = '';
foreach ($tables as $table) {
$result = mysqli_query($con, "SELECT * FROM ".$table);
$num_fields = mysqli_num_fields($result);
$return .= 'DROP TABLE '.$table.';';
$row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE '.$table));
$return .= "
".$row2[1].";
";
for ($i=0; $i < $num_fields; $i++) {
while ($row = mysqli_fetch_row($result)) {
$return .= 'INSERT INTO '.$table.'VALUES(';
for ($j=0; $j < $num_fields; $j++) {
$row[$j] = addslashes($row[$j]);
if (isset($row[$j])) {
$return .= '"'.$row[$j].'"';} else { $return .= '""';}
if($j<$num_fields-1){ $return .= ','; }
}
$return .= ");
";
}
}
$return .= "
";
}
$handle = fopen('backup.sql', 'w+');
fwrite($handle, $return);
fclose($handle);
echo "success";
?></div>