Knowledgebase
MySQL: Database Dump with PHP
Posted by zz-James Moir on 06 July 2017 07:12 PM

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);
$user = get_current_user();
system("mysqldump -h localhost -u DBUSER -pDBPASSWD DATABASE > ~$user/httpdocs/sqldump/DATABASE_BACKUP.sql");
?>

DBUSER = Your database username
DBPASSWD =
 Your database password (NOTE: DO NOT put a space between -p and your password)
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. http://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 private 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.