Tutorials Point


  Learning PHP
  Advanced PHP
  PHP Function Reference
  PHP Useful Resources
  Selected Reading

Copyright © 2014 by tutorialspoint



  Home     References     Discussion Forums     About TP  

Perform MySQL backup using PHP


previous next AddThis Social Bookmark Button


Advertisements

It is always good practice to take a regular backup of your database. There are three ways you can use to take backup of your MySQL database.

  • Using SQL Command through PHP.

  • Using MySQL binary mysqldump through PHP.

  • Using phpMyAdmin user interface.

Using SQL Command through PHP

You can execute SQL SELECT command to take a backup of any table. To take a complete database dump you will need to write separate query for separate table. Each table will be stored into separate text file.

Example:

Try out following example of using SELECT INTO OUTFILE query for creating table backup :

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$table_name = "employee";
$backup_file  = "/tmp/employee.sql";
$sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not take data backup: ' . mysql_error());
}
echo "Backedup  data successfully\n";
mysql_close($conn);
?>

There may be instances when you would need to restore data which you have backed up some time ago. To restore the backup you just need to run LOAD DATA INFILE query like this :

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$table_name = "employee";
$backup_file  = "/tmp/employee.sql";
$sql = "LOAD DATA INFILE '$backup_file' INTO TABLE $table_name";

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not load data : ' . mysql_error());
}
echo "Loaded  data successfully\n";
mysql_close($conn);
?>

Using MySQL binary mysqldump through PHP:

MySQL provides one utility mysqldump to perform database backup. Using this binary you can take complete database dump in a single command.

Example:

Try out following example to take your complete database dump:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';

$backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ".
           "test_db | gzip > $backup_file";

system($command);
?>

Using phpMyAdmin user interface:

If you have phpMyAdmin user interface available then its very easy for your to take backup of your database.

To backup your MySQL database using phpMyAdmin click on the "export" link on phpMyAdmin main page. Choose the database you wish to backup, check the appropriate SQL options and enter the name for the backup file.


previous next Printer Friendly

Advertisements


  

Advertisements