How can I backup my MySQL database?

Backup using PHP

The following PHP Script will backup the content of a MySQL database in a GZip file:


            <?php
  // Enter Database access details
  $host= 'dbxx.oneandone.co.uk';
  $user= 'dbxxxxxx';
  $pass= 'xxxxxxxx';
  $db=   'dbxxxxxxx';

  // Instructing the system to zip and store the database
  system(sprintf(

    'mysqldump --opt -h%s -u%s -p%s %s | gzip > %s/dumpDB.sql.gz',
    $host,
    $user,
    $pass,
    $db,
    getenv('DOCUMENT_ROOT')
  ));
  echo '+DONE';
?>

Afer you have run the script you can download the dump file by FTP or using the WebSpace Explorer in your 1&1 Control Panel

If your database is too big and the script times out, here is another PHP script that gives you the opportunity to backup separate tables.

	Please Note:
	You will need to create the folder called "DB_backup".


            <?php

$host = "dbXX.oneandone.co.uk";
$db = "dbXXXXXXXX";
$dbuser = "pXXXXXXX";
$dbpw = "XXXXXXX";

MYSQL_CONNECT($host, $dbuser, $dbpw) or die ( "<H3>Database Server could not be reached</H3>");
MYSQL_SELECT_DB($db) or die ( "<H3>Database not Available</H3>");

$path = getenv('DOCUMENT_ROOT')."/DB_backup";
$result = MYSQL_QUERY("SHOW TABLES");
$numrow = MYSQL_NUM_ROWS($result);

for($i = 0;$i < $numrow;$i++) {
  $table = MYSQL_RESULT($result,$i);
  echo "$table ... ";
  system(sprintf("mysqldump --opt -h $host -u $dbuser -p$dbpw $db $table | gzip > %s/$table.sql.gz",$path));
  echo "DONE\n\n";
}

MYSQL_CLOSE();

?>

Backup by SSH

If you have a Business Pro Package, Professional Package or a Managed Server then you can also do this using the Secure Shell (SSH) interface. Log in and run the following commands.


            

pXXXXXXX@kundenserver:~ > mysqldump -hdbXX.oneandone.co.uk -upXXXXXXX -p******** dbXXXXXXX > dbXXXXXXXX.sql

(******** = your password)

For more information please see http://dev.mysql.com/doc/

Print Article

Feedback

Overview	Search
Sitemap	Glossary