Select Category
Sitemap Help Contact
print article

Importing and Exporting MySQL Databases Using PHP

For Linux packages.

Follow the steps below to import or export your MySQL database using a PHP script as opposed to using the phpMyAdmin web interface or performing the import/export via command line.

MySQL databases are only available for Linux packages. If you have a Windows Hosting package, you will want to reference the MS SQL section of the Help Center.

If your database is too large to import using phpMyAdmin or you do not have a package that allows SSH access, you can perform import/export actions using the PHP language. The process is the same whether you are importing or exporting. The only difference will be in the actual PHP script.

PHP Import Script for Linux Hosting
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='db123456789';
$mysqlUserName ='dbo123456789';
$mysqlPassword ='yourPassword';
$mysqlHostName ='db1234.perfora.net';
$mysqlImportFilename ='yourMysqlBackupFile.sql';

//DO NOT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysql -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<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;
}
?>

PHP Import Script for Managed Servers
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='db123456789';
$mysqlUserName ='dbo123456789';
$mysqlPassword ='yourPassword';
$mysqlImportFilename ='yourMysqlBackupFile.sql';

//DO NOT EDIT BELOW THIS LINE
$mysqlHostName ='localhost';
//Export the database and output the status to the page
$command='mysql -u' .$mysqlUserName .' -S /kunden/tmp/mysql5.sock -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' < ' .$mysqlImportFilename;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Import file <b>' .$mysqlImportFilename .'</b> successfully imported to database <b>' .$mysqlDatabaseName .'</b>';
break;
case 1:
echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<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;
}
?>

PHP Export Script for Linux Hosting
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='db123456789';
$mysqlUserName ='dbo123456789';
$mysqlPassword ='myPassword';
$mysqlHostName ='db1234.perfora.net';
$mysqlExportPath ='chooseFilenameForBackup.sql';

//DO NOT EDIT BELOW THIS LINE
//Export the database and output the status to the page
$command='mysqldump --opt -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ~/' .$mysqlExportPath;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Database <b>' .$mysqlDatabaseName .'</b> successfully exported to <b>~/' .$mysqlExportPath .'</b>';
break;
case 1:
echo 'There was a warning during the export of <b>' .$mysqlDatabaseName .'</b> to <b>~/' .$mysqlExportPath .'</b>';
break;
case 2:
echo 'There was an error during export. Please check your values:<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;
}
?>

PHP Export Script for Managed Servers

<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='db123456789';
$mysqlUserName ='dbo123456789';
$mysqlPassword ='myPassword';
$mysqlExportPath ='chooseFilenameForBackup.sql';

//DO NOT EDIT BELOW THIS LINE
$mysqlHostName ='localhost';
//Export the database and output the status to the page
$command='mysqldump -u' .$mysqlUserName .' -S /kunden/tmp/mysql5.sock -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ~/' .$mysqlExportPath;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Database <b>' .$mysqlDatabaseName .'</b> successfully exported to <b>~/' .$mysqlExportPath .'</b>';
break;
case 1:
echo 'There was a warning during the export of <b>' .$mysqlDatabaseName .'</b> to <b>~/' .$mysqlExportPath .'</b>';
break;
case 2:
echo 'There was an error during export. Please check your values:<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;
}
?>

Step 1
Open a basic text editor on your computer such as WordPad or Notepad. Copy the import or export script above into the text file.
Step 2
Replace the database info in single quotes at the top of the script with your database information. For more help on how to find your database info, please reference MySQL Database Connection Credentials

The backup script will export the database to the root of your webspace. To export to a subfolder in your webspace named "sqlBackups" simply add the folder name before your filename such as

$mysqlExportPath ='sqlBackups/chooseFilenameForBackup.sql';

To export to a subfolder in your webspace, the subfolder must already have been created.

The import script will look for the backup file in the same folder where the script is saved.
Step 3
Save the file as phpImport.php or phpExport.php. It is suggested to save the file to your Desktop so that it can be easily found.
Step 4
Upload the phpImport.php or phpExport.php page using FTP to a location in your webspace that can be accessed via a browser.
Please note:
If using the Import script, make sure to also upload the .sql backup file to the same location.

For more help on FTP transfers, reference the FTP section of the 1&1 Help Center.
Step 5
Once uploaded, access the PHP page using the correct URL (such as http://1and1help.com/phpImport.php) to execute the script. It may take a few seconds/minutes for the export or import process to complete before the status page is loaded.

If there was an error with the import or export, check the database info used in the script to make sure that it is correct. If necessary, edit the PHP page again in a basic text editor to change values, then save the file, upload the file via FTP again and the reload the page to execute the script with the new values.

Step 6
Once the import/export has completed successfully, make sure to delete the PHP script! This is important as the PHP script contains all of your database information and if using the import script, you do not want someone to accidentally access the script later on down the road which will run the import function again. This could potentially overwrite your entire database with the data from an old backup.