MySQL database

This is how to backup and restore a MySQL table using a PHP script. Please note: this is an old post and the scripts include deprecated PHP functions.

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';

To restore the backup you just need to run LOAD DATA INFILE query like this :

<?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';

You could use the result of SHOW TABLES; to loop through all the tables thereby backing them all up.

http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx

The following post offers an alternative to this strategy: Backing up a MySQL database using PHP’s system() & MySQL’s mysqldump

By foxbeefly

PHP / MySQL Developer

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.