How can I backup my MySQL database?

[-Oz-]

One possible way is to login to phpmyadmin via cpanel and export the whole database including all data. If you want an easier backup system Andy posted a PHP script that will take the database and either:
A) back it up to a directory
B) e-mail the backup to you
C) FTP to another location

Here is the script:

<?php
  /* HMS_BACKUP.PHP
     Holomarcus MySQL Database Backup   
    
     Version 1.0 - August 15th, 2003
     Changelog: see bottom of script.
     
     (c)2003 Holomarcus ([url]http://www.holomarcus.nl[/url])
     You can distribute this script and use it freely as
     long as this header is not edited in the script.
     
     With HMS_BACKUP you can make a backup of your MySQL-database.
     This backup can be sent by e-mail or uploaded through FTP.
     
     This script doesn't need privileges to execute *nix commands.
     It's 100% pure PHP.
     
     The script needs write-privileges on the directory it resides in!
     
     Change the necessary settings below...
  */

  $dbhost        = 'localhost';  // Server address of your MySQL Server
  $dbuser        = 'xxx_xxx';      // Username to access MySQL database
  $dbpass        = 'xxxx';    // Password to access MySQL database
  $dbname        = 'xxx_xxx';      // Database Name

  $use_gzip      = 'yes';        // Set to No if you don't want the files sent in .gz format

  $remove_file   = 'no';        // Set this to yes if you want to remove the file after sending. Yes is recommended.

  $use_email     = 'yes';          // Set to 'yes' if you want the backup to be sent throug email. Fill out next 3 lines.
  $send_to       = 'xxx@xxxx.xxx';   // E-mail to send the mail to
  $send_from     = 'DB_BAckp'; // E-mail the mail comes from
  $subject       = "MySQL Database ($dbname) Backup - " . date("j F Y"); // Subject in the email to be sent.

  $use_ftp       = 'no'; // Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines
  $ftp_server    = '';   // FTP hostname
  $ftp_user_name = '';   // FTP username
  $ftp_user_pass = '';   // FTP password
  $ftp_path      = "/";  // This is the path to upload on your ftp server!

  $echo_status = 'no';   // Set to 'no' if the script should work silently (no output will be sent to the screen)


# You probably don't need to edit below this line....
#-------------------------------------------------------------------------------

  $db = mysql_connect("$dbhost","$dbuser","$dbpass");
    mysql_select_db("$dbname",$db);

  $path = make_dir();
  
  if ($echo_status == 'yes') {
    print "Dumpfile will be written to $path<br>";
  }

  $result = mysql_query("show tables from $dbname");
  while (list($table) = mysql_fetch_row($result)) {
    $newfile .= get_def($table);
    $newfile .= "\n\n";
    $newfile .= get_content($table);
    $newfile .= "\n\n";
    $i++;
    if ($echo_status == 'yes') {
      print "Dumped table $table<br>";
    }
  }

    $file_name = $dbname . "-" . date("Ymd-Hi") . ".sql";
    $file_path = $path . $file_name;

  if ($use_gzip == "yes") {
    $file_name .= ".gz";
    $file_path .= ".gz";
    $zp = gzopen($file_path, "wb9");
    gzwrite($zp,$newfile);
    gzclose($zp);

    if ($echo_status == 'yes') {
      print "<br>Gzip-file is created...<br>";
    }
  } else {
    $fp = fopen($file_path, "w");
    fwrite($fp, $newfile);
    fclose($fp);

    if ($echo_status == 'yes') {
      print "<br>SQL-file is created...<br>";
    }
  }

  if ($use_email == 'yes') {
    $fileatt_type = filetype($file_path);
  
    $headers = "From: $send_from";
  
    // Read the file to be attached ('rb' = read binary)
    $fp = fopen($file_path,'rb');
    $data = fread($fp,filesize($file_path));
    fclose($fp);
  
    // Generate a boundary string
    $semi_rand = md5(time());
    $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
  
    // Add the headers for a file attachment
    $headers .= "\nMIME-Version: 1.0\n" ."Content-Type: multipart/mixed;\n" ." boundary=\"{$mime_boundary}\"";
  
    // Add a multipart boundary above the plain message
    $message = "This is a multi-part message in MIME format.\n\n" ."--{$mime_boundary}\n" ."Content-Type: text/plain; charset=\"iso-8859-1\"\n" ."Content-Transfer-Encoding: 7bit\n\n" .
    $message . "\n\n";
  
    // Base64 encode the file data
    $data = chunk_split(base64_encode($data));
  
    // Add file attachment to the message
    $message .= "--{$mime_boundary}\n" ."Content-Type: {$fileatt_type};\n" ." name=\"{$file_name}\"\n" ."Content-Disposition: attachment;\n" ." filename=\"{$file_name}\"\n" ."Content-Transfer-Encoding: base64\n\n" .
    $data . "\n\n" ."--{$mime_boundary}--\n";
  
    // Send the message
    $ok = @mail($send_to, $subject, $message, $headers);
    
    if ($echo_status == 'yes') {
      print "<br>Mail is sent...<br>";
    }
  }
  
  if ($use_ftp == 'yes') {
    if ($use_gzip == 'yes') {
      $mode = FTP_BINARY;
    } else {
      $mode = FTP_ASCII;
    }
    $ftp_id       = ftp_connect($ftp_server);
    $login_result = ftp_login($ftp_id, $ftp_user_name, $ftp_user_pass);
    $upload       = ftp_put($ftp_id, $ftp_path . $file_name, $file_path, $mode);
    ftp_close($ftp_id);

    if ($echo_status == 'yes') {
      print "<br>Backup is uploaded to $ftp_user_name@$ftp_server...<br>";
    }
  }

  if ($remove_file == "yes") {
    unlink($file_name);
    if ($echo_status == 'yes') {
      print "<br>File is deleted...<br>";
    }
  }

  if ($echo_status == 'yes') {
    print "<br>I am done!<br>";
  }


  function make_dir() {
    $page = split("/", getenv('SCRIPT_NAME'));
    $n = count($page)-1;
    $page = $page[$n];
    $page = split("\.", $page, 2);
    $extension = $page[1];
    $page = $page[0];
    $script     = "$page.$extension";
    $base_url     = "http://".$_SERVER['SERVER_NAME'];
    $directory     = $_SERVER['PHP_SELF'];
    $url_base = "$base_url$directory";
    $url_base = ereg_replace("$script", '', "$_SERVER[PATH_TRANSLATED]");

    $path = $url_base;

    return $path;
  }

  function get_def($table) {
    $def = "";
    $def .= "DROP TABLE IF EXISTS $table;\n";
    $def .= "CREATE TABLE $table (\n";
    $result = mysql_query("SHOW FIELDS FROM $table") or die("Table $table not existing in database");
    while($row = mysql_fetch_array($result)) {
      $def .= "    $row[Field] $row[Type]";
      if ($row["Default"] != "") $def .= " DEFAULT '$row[Default]'";
      if ($row["Null"] != "YES") $def .= " NOT NULL";
      if ($row[Extra] != "") $def .= " $row[Extra]";
      $def .= ",\n";
    }
    $def = ereg_replace(",\n$","", $def);
    $result = mysql_query("SHOW KEYS FROM $table");
    while($row = mysql_fetch_array($result)) {
      $kname=$row[Key_name];
      if(($kname != "PRIMARY") && ($row[Non_unique] == 0)) $kname="UNIQUE|$kname";
      if(!isset($index[$kname])) $index[$kname] = array();
      $index[$kname][] = $row[Column_name];
    }
    while(list($x, $columns) = @each($index)) {
      $def .= ",\n";
      if($x == "PRIMARY") $def .= "   PRIMARY KEY (" . implode($columns, ", ") . ")";
      else if (substr($x,0,6) == "UNIQUE") $def .= "   UNIQUE ".substr($x,7)." (" . implode($columns, ", ") . ")";
      else $def .= "   KEY $x (" . implode($columns, ", ") . ")";
    }
    $def .= "\n);";
    return (stripslashes($def));
  }

  function get_content($table) {
    $content="";
    $result = mysql_query("SELECT * FROM $table");
    while($row = mysql_fetch_row($result)) {
      $insert = "INSERT INTO $table VALUES (";
      for($j=0; $j<mysql_num_fields($result);$j++) {
        if(!isset($row[$j])) $insert .= "NULL,";
        else if($row[$j] != "") $insert .= "'".addslashes($row[$j])."',";
        else $insert .= "'',";
      }
      $insert = ereg_replace(",$","",$insert);
      $insert .= ");\n";
      $content .= $insert;
    }
    return $content;
  }

  /* Changelog
  
     Version 1.0 - August 15th, 2003
     ===============================
     Created this beautiful script...

  */
 ?>

You can even create a cron job to have this script executed daily, weekly, etc.

Courtesy Of Andy

Here is a setup for the cron job:

In the advanced cron editor:
Set the minutes day, etc as you want. mine run:

Minute 0
Hour 3
Day *
Month *
Weekday *
Command php /home/username/public_html/script.php

This makes it run a php script at that url at 3am everyday.