Member Avatar for ElegantElephant

Is it possible to use PHP and a mysql query combined to allow a whole database backup file to be downloaded?

For example, I'm currently backup up my database by using the export tool in phpmyadmin, but what if I wanted to add a button or link on my site which would essentially allow me to do the same thing?

The backup has to appear as an SQL file btw

  • 3 Contributors
  • 2 Replies
  • 192 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

A one-click SQL export is perfectly doable, but pick the method that fits your hosting environment and security requirements. ’s idea to call the system mysqldump is the usual fast solution, however is correct that hosts often disable system/exec and similar functions — check the disable_functions setting in php.ini (and whether your host allows program execution) before relying on that route. php.ini disable_functions. (php.net)

Avoid the old mysql_* API shown in the thread: those functions were deprecated and later removed. Any new exporter or connection code should use mysqli or PDO so it runs on modern PHP versions. mysql_connect manual / migration note. (php.net)

If you use mysqldump from PHP, don’t pass the DB password on the command line (it’s visible to other users). Use an option file or mysql_config_editor / --defaults-extra-file (or a properly secured temporary config file) so credentials aren’t exposed, and restrict permissions on any backup file. Also protect the download endpoint with authentication and keep server-side backups outside the webroot or push them to remote storage. mysqldump / password guidelines. (dev.mysql.com)

If shell exec is unavailable, implement a streamed exporter in PHP: iterate SHOW TABLES, emit each table’s SHOW CREATE TABLE, then stream rows as INSERTs while using unbuffered queries (MYSQLI_USE_RESULT or PDO with buffered queries disabled) so you don’t fill PHP memory. Send proper download headers and flush as you go. Example header skeleton to force a download and stream output:

<?php
header('Content-Type: application/sql');
header('Content-Disposition: attachment; filename="backup_'.date('Ymd_His').'.sql"');
header('Pragma: no-cache');
flush();
// stream CREATE / INSERT SQL lines here (use unbuffered queries)
?>

See notes on unbuffered queries and streaming in the PHP docs. mysqli_use_result / unbuffered queries, header / Content-Disposition examples. (php.net)

Recommended Answers

All 2 Replies

Member Avatar for rch1231

Is it possible to use PHP and a mysql query combined to allow a whole database backup file to be downloaded?

For example, I'm currently backup up my database by using the export tool in phpmyadmin, but what if I wanted to add a button or link on my site which would essentially allow me to do the same thing?

The backup has to appear as an SQL file btw

Hello,

If you follow the normal standards for config files for php something like this should work.

Source code : config.php , opendb.php, backitup.php

<?php
// This is an example of config.php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'phpcake';
?>
<?php
// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);
?>
<?php
// This is an example of backitup.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';
?>
Member Avatar for Member #120589

system() may be disabled on your webserver for security reasons. Worth a try though.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.