MySQL: Database Dump with PHP

Purpose

Generally, you would use phpMyAdmin within the Plesk Control Panel to perform a backup of your MySQL database (Exporting and importing a MySQL database) but sometimes this process can fail or time out if you have a reasonably large database. The workaround for this is to use a simple PHP script to invoke a MySQL dump command and place the resulting backup file into a private directory, which you can access for download.

Method

Create the following PHP file either directly from the Plesk File Manager or on your local computer and then upload it to the httpdocs directory. You could call it sqldump.php

Copy the following content into your PHP file:

<?php
set_time_limit(600);
$backupFile = __DIR__ . "/DATABASE_BACKUP.sql";
$password = 'DBPASSWD';
$user = 'DBUSER';
$db = 'DATABASE';

$cmd = sprintf(
    'mysqldump -h localhost -u %s -p"%s" %s > "%s" 2>&1',
    $user,
    $password,
    $db,
    $backupFile
);
$output = [];
$return_var = 0;
// Execute and capture output
exec($cmd, $output, $return_var);
if ($return_var !== 0) {
    echo "<strong>Backup FAILED!</strong><br>";
    echo "Exit Code: $$return_var<br><br>";
    echo "<pre>" . implode("\n", $output) . "</pre>";
} else {
    echo "Backup completed successfully!<br>";
    echo "Saved to: DATABASE_BACKUP.sql";
}
?>

DBUSER = Your database username
DBPASSWD = Your database password (IMPORTANT: if your password has special characters such as $ you will have to escape those first. e.g. placing a \ infront of the special character.  e.g pass\$word)
DATABASE = Your database name
DATABASE_BACKUP.sql = The name for your Database backup file. Specify a custom backup filename if you wish.

Now run the command by calling the sqldump.php file in your browser. e.g. https://www.domain.nz/sqldump.php - Be aware that it may take a moment to backup the database, so please ensure you only visit the URL once and allow it to complete.
 

You should now have the resulting backup file ready for download via FTP in your directory. Note that any files within your private directory contribute to your overall disk quota so remember to remove the file once you have downloaded the backup. Best practice would be to also delete the sqldump.php file from your hosting account because it contains your database login credentials and also to avoid any accidental execution in future.

Get in touch

If you are experiencing any difficulty with any of these instructions, give us a call on 0800 477 333 (8AM to 10PM, 7 days a week).