Backing Up and Restoring MySQL Databases Using PHP

For Web Hosting Linux packages with MySQL databases

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

Backup of the MySQL 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 the 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=array(),$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;
}
?>

How to back up your database using PHP script:

Step 1

Create a new text file on your PC with an editor and copy the PHP code shown above into it.

Step 2

Enter the access data to your MySQL database in the code and save the script with the file extension.php.

Show access data in 1&1 IONOS

Step 3

Upload the file to your web space in the directory associated with your domain.

Step 4

Execute the script by calling the Internet address in the Internet browser, e.g. in the format http://yourdomain.co.uk/scriptname.php.

Please note: If you use the configuration option safe_mode = on, it is necessary to deactivate the safe mode for 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 archive the file on your PC.

Restoring the MySQL Database

The following script shows you how to import existing backups as .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=array(),$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;
}
?>

Note: If the size of your database causes the script to run out and your web hosting package includes SSH access, you can also restore the database via SSH.