Posted By

peterbelsky on 05/04/10


Tagged


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

omardixon
BrianCoyDesign
dantreacy


Execute a database backup query from PHP file


 / Published in: PHP
 

URL: http://devsnippets.com/article/10-essential-php-code-snippets.html

  1. Below is an example of using SELECT INTO OUTFILE query for creating table backup :
  2.  
  3.  
  4.  
  5. <?php
  6. include 'config.php';
  7. include 'opendb.php';
  8.  
  9. $tableName = 'mypet';
  10. $backupFile = 'backup/mypet.sql';
  11. $query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
  12. $result = mysql_query($query);
  13.  
  14. include 'closedb.php';
  15. ?>
  16.  
  17. To restore the backup you just need to run LOAD DATA INFILE query like this :
  18.  
  19.  
  20.  
  21. <?php
  22. include 'config.php';
  23. include 'opendb.php';
  24.  
  25. $tableName = 'mypet';
  26. $backupFile = 'mypet.sql';
  27. $query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
  28. $result = mysql_query($query);
  29.  
  30. include 'closedb.php';
  31. ?>
  32.  
  33. It's a good idea to name the backup file as tablename.sql so you'll know from which table the backup file is
  34.  
  35. Run mysqldump using system() function
  36.  
  37. The system() function is used to execute an external program. Because MySQL already have built in tool for creating MySQL database backup (mysqldump) let's use it from our PHP script
  38.  
  39.  
  40.  
  41. <?php
  42. include 'config.php';
  43. include 'opendb.php';
  44.  
  45. $backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
  46. $command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
  47. system($command);
  48. include 'closedb.php';
  49. ?>
  50.  
  51. Use phpMyAdmin to do the backup
  52.  
  53. This option as you may guessed doesn't involve any programming on your part. However I think i mention it anyway so you know more options to backup your database.
  54.  
  55. 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.
  56. <style type="text/css"> .style1 { width: 615px; } .style2 { text-align: right; width: 85px; } </style>

Report this snippet  

You need to login to post a comment.