Help Centre
Home Hosting MySQL & MariaDB Databases

Backing up and restoring MySQL and MariaDB databases using PHP

Valid for: Web Hosting

To protect your web content, we recommend backing up your data regularly. This article introduces two PHP scripts that you can use to back up and restore your databases.

Please Note

Databases larger than 1 GB (gigabyte) must be backed up and restored using SSH access. Otherwise, the process fails because the script is cancelled due to a timeout.

For instructions on backing up and restoring a database using SSH, see the article Backing up and restoring MySQL and MariaDB databases using SSH.

Backing up a database

The following PHP script creates an SQL dump. This is a text file that contains 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,$worked);
switch($worked){
  case0:
    echo'The database <b>' .$mysqlDatabaseName .'</b> was successfully stored in the following path '.getcwd().'/' .$mysqlExportPath .'</b>';
    break;
  case1:
    echo'An error occurred when exporting <b>' .$mysqlDatabaseName .'</b> to '.getcwd().'/' .$mysqlExportPath .'</b>';
    break;
  case2:
    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;
}
?>

To back up your database using the PHP script, complete the following steps:

  • On your computer, create a new text file in a text editor and copy the PHP code shown above into it.

  • In the code, enter the access data for your database in the places provided and save the script with the file extension .php.

    To find the access data, see the article Displaying MySQL/MariaDB Database Details in Your IONOS Account.

  • Upload the file to your webspace, into the directory that is connected to your domain.

  • Run the script on your webspace. To do this, open your domain in your browser using the following pattern: https://domain.tld/scriptname.php

    An SQL file is now saved in the directory of the script, under the name specified in the script.

  • Download the SQL file to your computer and keep it in a safe place.

Restoring a database

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

To restore your database, create a .php file with the code below. Enter the missing information about the SQL file and your database in the code and upload the script file to the directory of your domain. You can then start the restore by opening the script with your domain in your browser.

<?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){
  case0:
    echo'The data from the file <b>' .$mysqlImportFilename .'</b> were successfully imported into the database <b>' .$mysqlDatabaseName .'</b>';
    break;
  case1:
    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 Filename:</td><td><b>' .$mysqlImportFilename .'</b></td></tr></table>';
    break;
}
?>

Additional information

IONOS automatically creates daily backups of your MySQL and MariaDB databases. To reset a database to an earlier state without your own backup file, or to have a deleted database restored, see the following article:

Content