Best Ways to Take MYSQL Backup using PHP

Posted: February 11, 2010 in Uncategorized

There are at least three ways to backup your MySQL Database :

  1. Execute a database backup query from PHP file.
  2. Run mysqldump using system() function.
  3. Use phpMyAdmin to do the backup

Execute a database backup query from PHP file

Below is an example of using SELECT INTO OUTFILE query for creating table backup :

<?php
include ‘config.php’;
include ‘opendb.php’;

$tableName  = ‘mypet’;
$backupFile = ‘backup/mypet.sql’;
$query      = “SELECT * INTO OUTFILE ‘$backupFile’ FROM $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;

?>

<?php
include ‘config.php’;
include ‘opendb.php’;

$tableName  = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query      = “LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;
?>

<?php
include ‘config.php’;
include ‘opendb.php’;

$backupFile = $dbname . date(“Y-m-d-H-i-s”) . ‘.gz’;
$command = “mysqldump –opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile”;
system($command);

include ‘closedb.php’;

?>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s