How to back up MYSQL database with PHP.

Have you ever wondered how you can back up your MYSQL database in PHP?

This function helps you create a back up file for your MYSQL database. Define the following constants in your config file.

define ('DATABASE_NAME','YOUR DATABASE NAME'); define ('DATABASE_USER','DATABASE USERNAME'); define ('DATABASE_PASSWORD','DATABASE PASSWORD'); define ('DATABASE_HOST','DATABASE HOST');

Call the backUpDb() function and pass the above parameters appropriately. To select all the tables in the database just pass the *  value for the $tables parameter.

backUpDb(DATABASE_HOST,DATABASE_USER,DATABASE_PASSWORD,DATABASE_NAME);

After executing the above method your database will be backed up and saved on the project folder with the current time stamp.

Below is the full git hosted in Github.

	<?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.';
	}
	?>

view raw back_up_db_mysql.php hosted with ❤ by GitHub