Creating Automatic MySQL Database backups

Sharing is caring!

Each database ought to be went down and assuming that you’re presently completing it physically you can stop on the grounds that there’s a basic result utilizing Php. With this script you can either pass a particular table to reinforcement or decide to reinforcement the entire database.

The most effective method to enable

The most ideal route to utilize the script underneath is to duplicate the code and put it in an organizer called “database_backups” as appeared. At that point whenever you call the backup.php index it will make a database reinforcement which is archived on your server. I’d propose downloading these database reinforcements occasionally so you have a duplicate saved securely somewhere else incase you lose your hosting.

Using CRON

Cron is a time-based job scheduler which allows you to run certain scripts at specific times. For example I have my database backups running every night at a time when traffic levels are low. You could setup a CRON job if your web host permits by running the following command. Just change the link to point to the backup file in your server.
wget -O /dev/null http://your-site.com/database_backup/backup.php

The Code

<?php
backup_database_tables(‘HOST’,’USERNAME’,’PASSWORD’,’DATABASE’, ‘*’);

// backup the db function
function backup_database_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 each table and format the data
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.= “nn”.$row2[1].”;nn”;

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.=”nnn”;
}

//save the file
$handle = fopen(‘db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’);
fwrite($handle,$return);
fclose($handle);
}
?>