Last Updated:

How do I back up my MySQL database?

Each database must be backed up. You can automatically back up your database using PHP and CRON. With this script, you can select a specific table to back up or select an entire one.

 
 

How to implement

 

The best way to use a script is to copy the code and place it in a folder called "database_backups" as shown below. Then, when you open the backup file through the browser.php it will create a backup copy of the database that is stored on your server. I recommend that you download these database backups periodically, as your copy will be stored securely if you lose your hosting.

We use CRON

 

Cron is a task scheduler that allows you to run specific scripts at specific times. For example, I have database backups created every night, at a time when traffic on my site is very small. You can configure CRON to work if your web host allows you to use CRON by running the following command. Just change the link to point to the backup.php file that you have on your server.


wget -O /dev/null http://your-site.com/database_backup/backup.php

The backup.php file.

 

function backup_database_tables($host,$user,$pass,$name,$tables)
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//Get all 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);
}
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 to file
$handle = fopen('db-backup-'.time().' -'. (md5(implode(',',$tables))).'. sql','w+');
fwrite($handle,$return);
fclose($handle);
}
> ?>