Last active
January 20, 2018 08:52
-
-
Save JahsonKim/0f362185ebdf4e0e5ed8993867c6f4f5 to your computer and use it in GitHub Desktop.
Back up MYSQL database in PHP.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/* Some times you may wonder how you can automatically back up your Mysql database. | |
This functions helps you create a back up file for your mysql databases | |
*/ | |
define ('DATABASE_NAME','YOR DATABASE NAME'); | |
define ('DATABASE_USER','DATABASE USERNAME'); | |
define ('DATABASE_PASSWORD','DATABASE PASSWORD'); | |
define ('DATABASE_HOST','DATABASE HOST'); | |
/*call backUpDb() function and pass the above parameters appropriately. | |
To select all the tables on the database pass * for $tables parameter | |
*/ | |
backUpDb(DATABASE_HOST,DATABASE_USER,DATABASE_PASSWORD,DATABASE_NAME); | |
function backUpDb($dbHost,$dbUsername,$dbPassword,$dbName,$tables = '*'){ | |
//connect & select the database | |
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); | |
$return =''; | |
//get all of the tables | |
if($tables == '*'){ | |
$tables = array(); | |
$result = $db->query("SHOW TABLES"); | |
while($row = $result->fetch_row()){ | |
echo 'getting all tables '.$row[0].'<br>'; | |
$tables[] = $row[0]; | |
} | |
}else{ | |
$tables = is_array($tables)?$tables:explode(',',$tables); | |
} | |
echo'<br>-----------------------------------------------------------------------------------------------------<br>'; | |
//loop through the tables | |
foreach($tables as $table){ | |
$result = $db->query("SELECT * FROM $table"); | |
$numColumns = $result->field_count; | |
echo ' get table content '. $table.'<br>'; | |
$return .=''; | |
$result2 = $db->query("SHOW CREATE TABLE $table"); | |
$row2 = $result2->fetch_row(); | |
$return .= "\n\n".$row2[1].";\n\n"; | |
for($i = 0; $i < $numColumns; $i++){ | |
while($row = $result->fetch_row()){ | |
$return .= "INSERT INTO $table VALUES("; | |
for($j=0; $j < $numColumns; $j++){ | |
$row[$j] = addslashes($row[$j]); | |
$row[$j] = preg_replace("/\n/","\\n",$row[$j]); | |
if (isset($row[$j])) { $return .= '"'.$row[$j].'"' ; } else { $return .= '""'; } | |
if ($j < ($numColumns-1)) { $return.= ','; } | |
} | |
$return .= ");\n"; | |
} | |
} | |
$return .= "\n\n\n"; | |
} | |
echo'<br>-----------------------------------------------------------------------------------------------------<br>'; | |
//save database file as a .sql file. This file is saved with a time stamp | |
$handle = fopen('dbname'.time().'.sql','w+'); | |
fwrite($handle,$return); | |
fclose($handle); | |
echo 'Database backed up successfully.'; | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment