Backup MYSQL database using PHP

Backup database using PHP

Sometime we need to backup MYSQL database without access to PHPMYAdmin in scenarios when need to do this through a scheduled job. Here is a code snippet I found to backup database using PHP.

This will also allow to backup complete database or single table.

{code}
backup_tables(‘localhost’,’DB_USER_NAME’,’DB_PASSWORD’,’DB_Name’);

/* 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] = ereg_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); } {/code}

I am a Technical consultant with over 16 years of experience in developing, leading, and consulting on various web and mobile applications for startups and businesses. My core competencies include open-source frameworks, PHP, Marketo, WordPress and Digital Marketing Strategy. I am also a certified Microsoft Professional and a graduate of the Executive Program in Digital and Social Media Marketing from IIM Raipur. I lead a team of talented and passionate developers, designers, and marketers who deliver innovative and impactful solutions for our clients. We work with the latest technologies and methodologies, such as AI, cloud computing, agile, and DevOps, to create user-friendly, scalable, and secure products that meet the needs and expectations of our customers.

More Posts - Twitter - LinkedIn - Google Plus