Backing Up and Restoring MySQL/MariaDB Databases Using PHP

For Linux Web Hosting packages

This article introduces two PHP scripts for backing up and restoring your databases.

Backing Up a Database

The following PHP script creates a so-called SQL dump. This is a text file containing all the data and instructions required to restore a database in SQL format.

<?php
//Enter your database information here and the name of the backup file
$mysqlDatabaseName ='Database name';
$mysqlUserName ='User name';
$mysqlPassword ='Password';
$mysqlHostName ='dbxxx.hosting-data.io';
$mysqlExportPath ='Your-desired-filename.sql';

//Please do not change the following points
//Export of the database and output of the status
$command='mysqldump --opt -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ' .$mysqlExportPath;
exec($command,$output,$worked);
switch($worked){
case 0:
echo 'The database <b>' .$mysqlDatabaseName .'</b> was successfully stored in the following path '.getcwd().'/' .$mysqlExportPath .'</b>';
break;
case 1:
echo 'An error occurred when exporting <b>' .$mysqlDatabaseName .'</b> zu '.getcwd().'/' .$mysqlExportPath .'</b>';
break;
case 2:
echo 'An export error has occurred, please check the following information: <br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr></table>';
break;
}
?>

Guided Steps

• Create a new text file and copy the PHP code shown above into it.
• Enter the connection data for your database in the code and save the script with the file extension .php.
• Upload the file to your webspace in the directory associated with your domain.
• Execute the script by visiting the URL in your browser, such as with the following format: https://yourdomain.com/scriptname.php

Please note: If you use the configuration option safe_mode=on, you will need to deactivate safe mode before executing the script. Older databases can also be called dbxx.puretec.co.uk.

An SQL dump is now created and stored in the script directory with the name specified in the script. You can then store the file wherever you choose.

Restoring a Database

The following script shows you how to import existing backups (.sql files) back into your database:

<?php
//Enter your database information here and the name of the backup file
$mysqlDatabaseName ='Database name';
$mysqlUserName ='User name';
$mysqlPassword ='Password';
$mysqlHostName ='dbxxx.hosting-data.io';
$mysqlImportFilename ='Filename-of-backup.sql';

//Please do not change the following points
//Import of the database and output of the status
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output,$worked);
switch($worked){
case 0:
echo 'The data from the file <b>' .$mysqlImportFilename .'</b> were successfully imported into the database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'An error occurred during the import. Please check if the file is in the same folder as this script. Also check the following data again:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr><tr><td>MySQL Import Dateiname:</td><td><b>' .$mysqlImportFilename .'</b></td></tr></table>';
break;
}
?>