Backup and Restore MySQL database in PHP

Question

I am trying to use PHP to backup and restore a MySQL database:

Backup:

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'dbpass';
$dbname = 'test';

$output = "D:/backup/test.sql";
exec("D:/xampp/mysql/bin/mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname > $output");
echo "Backup complete!";

Restore:

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'dbpass';
$dbname = 'test';

$output = "D:/restore/test.sql";
exec("D:/xampp/mysql/bin/mysql --opt -h $dbhost -u $dbuser -p $dbpass $dbname < $output");
echo "Restore complete!";

But both are not working. When Backup is complete then I check test.sql file that is blank. When the restore is complete, the database is still blank.

How can I fix this?

You need to use backward slashes(not forward slashes) inside paths as you are on windows — rakeshjain
– rakeshjain, Commented Mar 6, 2014 at 7:05
replace exec with echo; run the generated command in your command line; read the error message. — Gerald Schneider
– Gerald Schneider, Commented Mar 6, 2014 at 7:06

Abdul Manaf · Accepted Answer · 2014-03-06 07:11:49Z

12

Script to backup using Php

<?php
define("BACKUP_PATH", "/home/abdul/");

$server_name   = "localhost";
$username      = "root";
$password      = "root";
$database_name = "world_copy";
$date_string   = date("Ymd");

$cmd = "mysqldump --routines -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";

exec($cmd);
?>

Script to restore

<?php

$restore_file  = "/home/abdul/20140306_world_copy.sql";
$server_name   = "localhost";
$username      = "root";
$password      = "root";
$database_name = "test_world_copy";

$cmd = "mysql -h {$server_name} -u {$username} -p{$password} {$database_name} < $restore_file";
exec($cmd);

?>

edited Mar 6, 2014 at 7:11

answered Mar 6, 2014 at 7:03

Abdul Manaf

4,8883 gold badges29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DeLe Over a year ago

when i backup then 20140306_test.sql file is 0 byte, empty

Abdul Manaf Over a year ago

Its working for me , pass full path of mysqldump instead of mysqldump. full path means "D:/xampp/mysql/bin/mysqldump"

always-a-learner Over a year ago

i have saved the database backup in my download table and as per answer I have given full directory path of my SQL file but it still shows the string(0) ""

Smit Patel Over a year ago

If it's multiple databases then how it works? We have tried multiple databases using the loop but it restores the first file of database every time.

Collectives™ on Stack Overflow

Backup and Restore MySQL database in PHP

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related