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:
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:
This makes it run a php script at that url at 3am everyday.
A) back it up to a directory
B) e-mail the backup to you
C) FTP to another location
Here is the script:
PHP Code:
<?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...
*/
?>
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
Hour 3
Day *
Month *
Weekday *
Command php /home/username/public_html/script.php