How can I backup my MySQL database?
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