Ads

Creating your own automatic database backup script

This tutorial is more suggested for interm. programmers. Sorry, if your new to PHP, this tutorial is NOT reccomended. But hey give it a try.

Lets get started. First off we need to define our folder that the database backups are going to go into. You are going to change database_backups to whatever the folder you have created for the db backups.

CODE
 $backup_dir = dirname( __FILE__ ) . '/database_backups/';




Next we need information to connect to the database that the script is going to backup. Just replace the example information with your correct working information.

CODE
$u = 'user_user';
 $p = 'password'; 
 $db = 'user_dbname'; 




Okay, so we've defined our backup folder, as well as our login information, Now to connect to the server, and if we cannot display an error message.

CODE
$db_link = mysql_connect($h,$u,$p);
 $res = mysql_db_query($db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
 echo 'Found '. mysql_num_rows($res) . ' databases' . "n";




So now we have to get all of the information, then store it in a .sql/.txt file and name it. The name will be the database-name.year-month-day.sql. Very simple.

You can change it to display whatever you want tho. But basically what the following code below does is it connects to the server, grabs all of the database information (the db from the info that we've entered at the start of the tut) then does a bit of cleaning, gives you the total time it took to compress the db backup.

CODE
while ($rec = mysql_fetch_array($res)){
  $time = microtime();
  $time = explode(' ', $time);
  $time = $time[1] + $time[0];
  $start = $time;
  
  echo $rec[0] . "n";
  shell_exec( 'mysqldump --result-file='.$backup_dir.$rec[0].'.'.date('Y-m-d').'.sql --password='.$p.' '.$rec[0] );
  //Parse time :
  $time = microtime();
  $time = explode(' ', $time);
  $time = $time[1] + $time[0];
  $finish = $time;
  $total_time = round(($finish - $start), 6);
  echo 'Parsed in ' . $total_time . ' secs' . "nStarting with compression..n";
 }




Now what we're going to do it pretty neat. We are going to take the .sql file, and use PHP's function shell_exec() to put the already back'uped database information and put it into a .tar file.

CODE
shell_exec( 'tar cvf '.$backup_dir.date('Y-m-d').'.tar '.$backup_dir.'*.sql' );




Here is our final code.

CODE
<?php
 
 $backup_dir = dirname( __FILE__ ) . '/database_backups/';
 
 $u = 'user_user'; //This is just the username of the database
 $p = 'password'; //This is just the password of the database
 $db = 'user_dbname'; //This is just the name of a database just to make the query work. The script will backup all the databases that your use has access to.
 
 $db_link = mysql_connect($h,$u,$p);
 $res = mysql_db_query($db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
 echo 'Found '. mysql_num_rows($res) . ' databases' . "n";
 
 while ($rec = mysql_fetch_array($res)){

  //Parse time :
  $time = microtime();
  $time = explode(' ', $time);
  $time = $time[1] + $time[0];
  $start = $time;
  
  echo $rec[0] . "n";
  shell_exec( 'mysqldump --result-file='.$backup_dir.$rec[0].'.'.date('Y-m-d').'.sql --password='.$p.' '.$rec[0] );
  //Parse time :
  $time = microtime();
  $time = explode(' ', $time);
  $time = $time[1] + $time[0];
  $finish = $time;
  $total_time = round(($finish - $start), 6);
  echo 'Parsed in ' . $total_time . ' secs' . "nStarting with compression..n";
 }
 //Let's tar those backups :
 shell_exec( 'tar cvf '.$backup_dir.date('Y-m-d').'.tar '.$backup_dir.'*.sql' );
?>




Now you have your own basic database backup script! Now what you could do is set an cron job for every xx minutes/hours/days/months for the script to run.

Thursday September 13, 2007 - 1647 reads