'', 'html' => ''); // Get general backup settings $weekday = $cfg['weekday']; $monthday = $cfg['monthday']; $keep_days = $cfg['keep_days']; $keep_weeks = $cfg['keep_weeks']; $keep_months = $cfg['keep_months']; // Get archiving and compression options $archive_tar = $cfg['archive_tar']; $archive_prefix = $cfg['archive_prefix']; $archive_gzip = $cfg['archive_gzip']; $archive_del_sql = $cfg['archive_del_sql']; // Get email options $email_report = $cfg['email_report']; $email_archive = $cfg['email_archive']; $email_from = $cfg['email_from']; $email_to = $cfg['email_to']; $email_agent = $cfg['email_agent']; $email_smtp_host = $cfg['email_smtp_host']; $email_smtp_port = $cfg['email_smtp_port']; // Get FTP server upload options $ftp_upload = $cfg['ftp_upload']; $ftp_host = $cfg['ftp_host']; $ftp_user = $cfg['ftp_user']; $ftp_password = $cfg['ftp_password']; $ftp_path = $cfg['ftp_path']; // HTML header for webpage and HTML/Mime version of mail $log_header = "\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "MySQL database backup\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "\n"; $log_header .= "
";

// HTML footer for webpage and HTML/Mime version of mail
$log_footer = "
\n"; $log_footer .= "\n"; $log_footer .= ""; // Error Counters $db_error_count = 0; $total_error_count = 0; // Define Separator string in reports $sep = "----------------------------------------------------------------------\n"; // No HTML Output when running from command-line if (php_sapi_name() != 'cli') { print($log_header); } // Output program information log_msg('MySQL database backup $Revision: 1.22 $ ' . "\n"); log_msg("Copyright (c) 2003, 2004 by Alain Wolf, Zurich - Switzerland\n"); log_msg("Website: http://restkultur.ch/personal/wolf/scripts/db_backup/\n"); log_msg($sep); log_msg("PHP " . phpversion() . " - SAPI: " . php_sapi_name() . " - OS: " . PHP_OS . " - PHP Safe-Mode: "); if (ini_get('safe_mode') == 1) { log_msg("On\n"); } else { log_msg("Off\n"); } log_msg($sep); log_msg("Starting backup on " . DB_BACKUP_HOST . "\n" . date("l j. F Y H:i T") . "\n"); log_msg($sep); // Configuration Check if (DB_BACKUP_PATH == "") { log_msg('Configuration Error! Backup Aborted! ' . "\n" . $sep . ' Without some customization, the script will not work. Most important are the MySQL server settings and the directory where to store the backup files. Open the file "config.inc.php" in your favourite text- editor (e.g. vim or notepad). ' . "\n", 40); log_msg($sep); $total_error_count++; if (php_sapi_name() != "cli") { // No HTML Output when running from command-line print($log_footer); } // Mail the contents of the output buffer if ($email_report) { $success = mail_report($log); log_msg(date("H:i:s") . ": $success"); } die(); } // Display Current Backup Settings log_msg("Configuration Settings:\n"); log_msg("Backup to: " . DB_BACKUP_PATH . "\n"); log_msg("Weekly Backup: "); if (date("w") == $weekday) { log_msg("Yes\n"); } else { log_msg("No\n"); } log_msg("Monthly Backup: "); if ((date("j") == date("t") or (date("j") == $monthday))) { log_msg("Yes\n"); } else { log_msg("No\n"); } log_msg("Keeping daily backups for $keep_days days \n"); log_msg("Keeping weekly backups for $keep_weeks weeks \n"); log_msg("Keeping monthly backups for $keep_months months \n"); log_msg("Archiving SQL files: "); if ($archive_tar) { log_msg("Yes\n"); log_msg("Archive filename prefix: $archive_prefix \n"); log_msg("Remove SQL-files after archiving: "); if ($archive_del_sql) { log_msg("Yes\n"); } else { log_msg("No\n"); } log_msg("Compress archive-file: "); if ($archive_gzip) { log_msg("Yes\n"); } else { log_msg("No\n"); } } else { log_msg("No\n"); } log_msg("Send reports by email: "); if ($email_report) { log_msg("Yes\n"); log_msg("Send backup archive by email: "); if ($email_archive) { log_msg("Yes\n"); log_msg("Email address to send backup archives: $email_to \n"); } else { log_msg("No\n"); } log_msg("Send email message to: " . $email_to . "\n"); } else { log_msg("No\n"); } log_msg("Upload backup archive to FTP server: "); if ($ftp_upload) { log_msg("Yes"); log_msg("\n"); log_msg("FTP server address: " . $ftp_host . "\n"); log_msg("FTP server username: " . $ftp_user . "\n"); log_msg("FTP upload pathname: " . $ftp_path . "\n"); } else { log_msg("No\n"); } log_msg($sep); // If the directory to backup to doesn't exist, create it if (!file_exists(DB_BACKUP_PATH)) { mkdir(DB_BACKUP_PATH, 0777); chmod(DB_BACKUP_PATH, 0777); } // Show diskspace used by backup log_msg(date("H:i:s") . ": Diskspace used by MySQL database backup files: "); log_msg(format_size(dirsize(DB_BACKUP_PATH))); log_msg("\n"); log_msg($sep); // Start working on the databases // Ignore 'STOP' button in users browser. ignore_user_abort(TRUE); foreach ($cfg['db'] as $db_i => $db_name) { if ($cfg['db'][$db_i]['db_name'] != '') { // Get configuration options for this database $db_name = $cfg['db'][$db_i]['db_name']; $db_host = $cfg['db'][$db_i]['host']; $db_port = $cfg['db'][$db_i]['port']; $db_user = $cfg['db'][$db_i]['user']; $db_pass = $cfg['db'][$db_i]['password']; $exclude_tables = $cfg['db'][$db_i]['exclude_tables']; $sql_drop_table = $cfg['db'][$db_i]['sql_drop_table']; $sql_single_file = $cfg['db'][$db_i]['sql_single_file']; // Reset error counter for this database $db_error_count = 0; log_msg(date("H:i:s") . ": Beginning backup of MySQL database ". $db_name . " ... \n"); log_msg($sep); log_msg("Current database settings: \n"); log_msg("MySQL Server: " . $db_host . "\n"); log_msg("MySQL User: " . $db_user . "\n"); log_msg("Excluded tables: "); foreach($exclude_tables as $str) { log_msg("$str "); } log_msg("\nAdd \"drop table\" statements: "); if ($sql_drop_table) { log_msg("Yes\n"); } else { log_msg("No\n"); } log_msg("All Tables in single SQL file: "); if ($sql_single_file) { log_msg("Yes\n"); } else { log_msg("No\n"); } log_msg($sep); // Establish MySQL server connection log_msg(date("H:i:s") . ": Connecting to database server ...\n"); if (!$db_connect = @mysql_pconnect($db_host, $db_user, $db_pass)) { log_msg(date("H:i:s") . ": Could not connect to MySQL server!\n " . mysql_error() . "\n" . $sep, 30); $db_error_count++; // continue; } else { // Get MySQL Server version $mysql_server_info = mysql_get_host_info(); $mysql_server_version = mysql_get_server_info(); $mysql_client_version = mysql_get_client_info(); log_msg(" MySQL Server: " . $mysql_server_info . "\n"); log_msg(" MySQL Server Version: " . $mysql_server_version . "\n"); log_msg($sep); // Select database on MySQL server if (!$success = mysql_select_db($db_name, $GLOBALS["db_connect"])) { log_msg(date("H:i:s") . ": Could not select database $db_name:\n " . mysql_error(), 30); log_msg("\n"); $db_error_count++; } else { // Get all tables in the database $tables = mysql_list_tables($db_name); // Check for wildcard exclusions $excluded_tables = array(); while ($all_tables = mysql_fetch_array($tables)) { $curr_table = $all_tables[0]; foreach($exclude_tables as $str) { if (fnmatch($str, $curr_table)) { if (!in_array ($curr_table, $excluded_tables)) { $excluded_tables[] = $curr_table; break; } } } } log_msg(date("H:i:s") . ": Database storage size is " . format_size(db_size($db_name)) . "\n"); log_msg(date("H:i:s") . ": " . mysql_num_rows($tables) . " tables found.\n"); log_msg(date("H:i:s") . ": " . count($excluded_tables) . " tables excluded from backup.\n"); log_msg($sep); // Set the archive filename $db_backup_fname = $archive_prefix . $db_name . ".tar"; // If the directory for backups of this database doesn't exist create it if (!file_exists(DB_BACKUP_PATH . "/" . $db_name)) { mkdir(DB_BACKUP_PATH . "/" . $db_name, 0777); chmod(DB_BACKUP_PATH . "/" . $db_name, 0777); } // Change to the directory to backup to chdir(DB_BACKUP_PATH . "/" . $db_name); // If the directory for daily backups doesn't exist create it if (!file_exists("days")) { mkdir("days", 0777); chmod("days", 0777); } // If the directory for weekly backup doesn't exist create it if (!file_exists("weeks")) { mkdir("weeks", 0777); chmod("weeks", 0777); } // If the directory for monthly backup doesn't exist create it if (!file_exists("months")) { mkdir("months", 0777); chmod("months", 0777); } // Extract SQL files from archive if found if ($archive_tar) { // Increase script execution time-limit to 15 min for decompression. if ( !ini_get('safe_mode')) @set_time_limit(15*60); require_once(dirname(__FILE__) . '/Tar.php'); if ($archive_gzip) { if (file_exists(DB_BACKUP_PATH . "/" . $db_backup_fname . ".gz")) { log_msg(date("H:i:s") . ": Extracting previous archive ". $db_backup_fname . ".gz ...\n"); $tar = new Archive_Tar(DB_BACKUP_PATH . "/" . $db_backup_fname . ".gz", "gz"); if ($tar->extract()) { log_msg(date("H:i:s") . ": Archive extraction successfull.\n"); if (unlink(DB_BACKUP_PATH . "/" . $db_backup_fname . ".gz")) { log_msg(date("H:i:s") . ": Previous archive file deleted.\n"); } else { log_msg(date("H:i:s") . ": Error: Could not delete previous archive db_backup_fname!\n", 20); $db_error_count++; } } else { log_msg(date("H:i:s") . ": Error: Archive extraction from $db_backup_fname failed!\n", 20); $db_error_count++; } } else { log_msg(date("H:i:s") . ": No archive from a previous backup was found.\n"); } // if (file_exists($db_backup_fname . ".gz")) } else { if (file_exists(DB_BACKUP_PATH . "/" . $db_backup_fname)) { log_msg(date("H:i:s") . ": Extracting previous archive ". $db_backup_fname . " ...\n"); $tar = new Archive_Tar(DB_BACKUP_PATH . "/" . $db_backup_fname); if ($tar->extract()) { log_msg(date("H:i:s") . ": Archive extraction successfull.\n"); if (unlink(DB_BACKUP_PATH . "/" . $db_backup_fname)) { log_msg(date("H:i:s") . ": Previous archive file deleted.\n"); } else { log_msg(date("H:i:s") . ": Error: Could not delete archive file $db_backup_fname!\n", 20); $db_error_count++; } } else { log_msg(date("H:i:s") . ": Error: Archive extraction from $db_backup_fname failed!\n", 20); $db_error_count++; } } else { log_msg(date("H:i:s") . ": No archive from previous backup was found.\n"); } // if (file_exists($db_backup_fname)) } // if ($db_backup_gzip) log_msg($sep); } // ($db_backup_tar) // Header information in SQL-file $file_header = "# MySQL database backup\n"; $file_header .= "# " . '$Revision: 1.22 $' . "\n"; $file_header .= "# Copyright (c) 2003, 2004 by Alain Wolf - Zurich, Switzerland.\n"; $file_header .= "#\n"; $file_header .= "# Generated: " . date("l j. F Y H:i T") . "\n"; $file_header .= "# Hostname: " . DB_BACKUP_HOST . "\n"; $file_header .= "# MySQL Server: " . $mysql_server_info . "\n"; $file_header .= "# MySQL Server version: " . $mysql_server_version . "\n"; $file_header .= "# Database: " . backquote($db_name) . "\n"; // Do we use only one sql-file for all tables? if ($sql_single_file) { $fname = $db_name; $sql_file = $file_header; $sql_file .= "# --------------------------------------------------------\n"; } // if ($db_backup_sql_single_file) // Process the tables to backup for ($i = 0; $i < mysql_num_rows($tables); $i++) { $curr_table = mysql_tablename($tables, $i); if (!in_array($curr_table, $excluded_tables)) { log_msg(date("H:i:s") . ": Starting backup of table $db_name.$curr_table ...\n"); // Increase script execution time-limit to 15 min for every table. if ( !ini_get('safe_mode')) @set_time_limit(15*60); // Create the SQL statements $sql_statements = make_sql($curr_table); // Do we use separate sql-files for every table? if ($sql_single_file == FALSE) { $fname = $curr_table; if ($fp = open_daily($fname)) { $sql_file = $file_header; $sql_file .= "# --------------------------------------------------------\n"; $sql_file .= "# Table: " . backquote($curr_table) . "\n"; $sql_file .= "# --------------------------------------------------------\n"; $sql_file .= $sql_statements; // Write the SQL statements to file (separate sql-file for every table) write_daily($fp, $sql_file); close_daily($fp, $keep_days); // Do we have to do a weekly backup? (separate sql-file for every table) if (date("w") == $weekday) { weekly_backup($fname, $keep_weeks); } // if (date("w") ... // Do we have to do a monthly backup? (separate sql-file for every table) if ((date("j") == date("t") or (date("j") == $monthday))) { monthly_backup($fname, $keep_months); } // if ((date("j") ... } // if ($fp = open_daily($fname)) } else { $sql_file .= "# --------------------------------------------------------\n"; $sql_file .= "# Table: " . backquote($curr_table) . "\n"; $sql_file .= "# --------------------------------------------------------\n"; $sql_file .= $sql_statements; } // if ($db_backup_sql_single_file == FALSE) log_msg(date("H:i:s") . ": Backup of table $db_name.$curr_table finished.\n"); } else { log_msg(date("H:i:s") . ": Table $curr_table is excluded from backup.\n"); } // if (!in_array($curr_table, $exclude_tables)) log_msg($sep); } // for ($i = 0; $i < mysql_num_rows($tables); $i++) // Do we use only one single sql-file for all tables? if ($sql_single_file) { $fp = open_daily($fname); // Write the SQL statements to file (single SQL file for all tables) write_daily($fp, $sql_file); // Close the file for writing (single SQL file for all tables) close_daily($fp, $keep_days); // Do we have to do a weekly backup? (single SQL file for all tables) if (date("w") == $weekday) { weekly_backup($fname, $keep_weeks); } // if (date("w") ... // Do we have to do a monthly backup? (single SQL file for all tables) if ((date("j") == date("t") or (date("j") == $monthday))) { monthly_backup($fname, $keep_months); } // if ((date("j") ... } // if ($db_backup_sql_single_file) log_msg(date("H:i:s") . ": Backup of database $db_name finished.\n"); // Look for and remove outated SQL files log_msg(date("H:i:s") . ": Looking for outdated SQL files ...\n"); clean_up(DB_BACKUP_PATH . "/" . $db_name . "/days", (UNIX_DAY * $keep_days)); clean_up(DB_BACKUP_PATH . "/" . $db_name . "/weeks", (UNIX_WEEK * $keep_weeks)); clean_up(DB_BACKUP_PATH . "/" . $db_name . "/months", (UNIX_MONTH * $keep_months)); log_msg($sep); // Archive all the SQL files if ($archive_tar) { // Increase script execution time-limit to 15 min for archiving. if ( !ini_get('safe_mode')) @set_time_limit(15*60); // create Archive_Tar() object require_once(dirname(__FILE__) . '/Tar.php'); // Compress the archive file if ($archive_gzip) { log_msg(date("H:i:s") . ": Archiving SQL files in " . $db_backup_fname . ".gz ...\n"); $tar = new Archive_Tar(DB_BACKUP_PATH . "/" . $db_backup_fname . ".gz", "gz"); } else { log_msg(date("H:i:s") . ": Archiving SQL files in " . $db_backup_fname . " ...\n"); $tar = new Archive_Tar(DB_BACKUP_PATH . "/" . $db_backup_fname); } // Build archive if ($tar->add("days weeks months")) { log_msg(date("H:i:s") . ": Archiving succesfull.\n"); log_msg($sep); // Remove the SQL files if ($archive_del_sql) { log_msg(date("H:i:s") . ": Removing SQL-files ...\n"); del_sql_files("days"); del_sql_files("weeks"); del_sql_files("months"); chdir(DB_BACKUP_PATH); if (rmdir($db_name)) { log_msg(date("H:i:s") . ": Directory \"$db_name\" removed.\n"); } else { log_msg(date("H:i:s") . ": Error removing directory \"$db_name\"!\n", 20); $db_error_count++; } log_msg($sep); } // if (($db_backup_del_sql) } else { log_msg(date("H:i:s") . ": Error while archiving!\n", 30); $db_error_count++; log_msg($sep); } } // if ($db_backup_tar) } // if (!$success = mysql_select_db($db_name, $GLOBALS["db_connect"])) } //if (!$db_connect = mysql_pconnect(DB_HOST, DB_USER, DB_PASS)) log_msg(date("H:i:s") . ": Backup for database $db_name completed with $db_error_count errors.\n"); $total_error_count = $total_error_count + $db_error_count; log_msg($sep); } // if ($cfg['db'][$db_i]['db_name'] != '';) } // foreach ($cfg['db'] as $db_i) // Upload archive file to remote server by FTP if (($ftp_upload == "yes") && ($archive_tar)) { ftp_upload(); } // if($use_ftp == "yes") log_msg(date("H:i:s") . ": Diskspace used by MySQL database backup files: "); log_msg(format_size(dirsize(DB_BACKUP_PATH))); log_msg("\n"); log_msg($sep); log_msg(date("H:i:s") . ": Backup completed with $total_error_count errors.\n Have a nice day!\n"); // Mail the contents of the output buffer if (($email_report) or ($email_archive)) { $success = mail_report($log); log_msg(date("H:i:s") . ": $success"); log_msg("\n"); } log_msg($sep); // No HTML Output when running from command-line if (php_sapi_name() != "cli") { print($log_footer); } // +----------------------------------------------------------------------+ // | History Log: // +----------------------------------------------------------------------+ // | $Log: db_backup.php,v $ // | Revision 1.22 2004/02/04 07:40:48 wolf // | Fixed: Error message on failed ftp connection. // | Fixed: Addded absolute path for include files. // | Fixed: Settings for Mail Report were not honoured. // | Changed: Simplified some loops. // | Changed: Updated documentation in README // | // | Revision 1.21 2004/01/23 07:12:43 wolf // | changed: more possible error-levels. // | changed: log actions accroding to error-level. // | changed: using different logs for text and html. // | changed: output error messages with when diks log-file. // | added: calculate and display database storeage size. // | changed: minor changes in some error messages // | fixed: fixed some old variable names, removed obosolete ones. // | added: added error-levels to many messages, but still not all yet. // | changed: moved html.mail.class file-inclusion to mailing function. // | changed: errors while sending mail are now logged and not just displayed. // | changed: php errors are logged to disk-log-file. // | added: configuration options for colors in html reports. // | added: error messages are color-highlighted in html. // | fixed: exeption handling logic while connecting to database server. // | changed: minor changes in comments for the config-file. // | added: log-level options in config-file. // | changed: minor documentation updates. more to come. // | // | Revision 1.20 2004/01/11 11:45:52 wolf // | Added: Check for empty database names in cfg array. // | // | Revision 1.19 2004/01/09 07:58:12 wolf // | Changed: Moved functions to separate file. // | Changed: Format of configuration settings as in PHPMyAdmin. // | Changed: Copyright Statement. // | Added: Reports and program outpout are now handled by a function. // | Removed: Configuration check error message. // | Changed: Display of current backup settings. // | Added: Multiple Databases can be handled. // | Added: Option to put all tables of a DB in one SQL-file. // | Changed: SQL creation moved to a function. // | Changed: SQL file handling moved to separate functions. // | Changed: Tar-Archiving and gzip-compression now handled by PEAR object Archive.Tar // | Changed: FTP upload handles multiple files. // | Changed: Mailing handles multiple files. // | // | Revision 1.18 2003/12/10 04:01:12 wolf // | Changed: Background and color of mail reports // | Added: errors are now counted. // | Added: Expiry date in email reports. // | Added: Priority in email reports, according to errors enountered. // | Changed: Current settings displayed/logged before connection to MySQL-Server // | Changed: Minor reporting changes. // | // | Revision 1.17 2003/10/29 15:11:24 wolf // | Changed: Rewrite FTP upload of archive files // | // | Revision 1.16 2003/05/19 11:06:20 wolf // | Fixed: Error defining already defined constant DB_BAKUP_HOST in CLI-Mode. // | Fixed: Hostname must be defined manually in config-file when running from CRON in CLI-Mode. // | Fixed: All subdirs are created by default, supressing errors in tar-commands. // | // | Revision 1.15 2003/05/18 12:47:34 wolf // | Added: PHP error loggin options. // | Changed: Moved include files to the top of script. // | // | Revision 1.14 2003/05/18 01:37:07 wolf // | Changed: Improved Hostname detection. // | // | Revision 1.13 2003/05/17 23:22:54 wolf // | Added: Configuration check and error message. // | Added: Hostname is set (UNIX and WIN) // | Added: Sending Archive file by mail. // | Added: Clean up backup dirs from old SQL files. // | Added: Server hostname (not MySQL) to reports and SQL files. // | Added: Number of Records to reports and SQL files. // | Fixed: HTTP-server vars are set in CLI-mode. // | Changed: Subject-line in mail. // | Changed: SMTP params are now GLOBAL. // | Changed: removed RK_ from global vars. // | Changed: Output HTML only when not in CLI-mode. // | // | Revision 1.12 2003/05/17 02:03:15 wolf // | Changed: Renamed config file to "config.inc.php". // | // | Revision 1.11 2003/05/15 02:51:07 wolf // | Added: Expires header in mail-reports. // | Fixed: Some minor HTML glitches and report layout. // | Fixed: mysql_pconnect parameters are honoured. // | // | Revision : 1.10 2003/5/15 2:40:40 wolf // | Added: Mailing of reports using SMTP and html-mime-mail-class. // | Added: Copyright statement in Reports. // | Changed: Moved configuration options to separate file. // | Changed: Function fnmatch defined conditionally, if it doesn't exist. // | Changed: Progress saved in buffer, for maliing and display. // | Changed: Minor changes in report output format. Replaced
tags. // | // | Revision 1.9 2003/04/24 22:24:37 wolf // | Fixed: Renamed function fnmatch to fn_match. // | Fixed: Cosmetic changes in report. // | // | Revision 1.8 2003/04/24 10:12:32 wolf // | Changed: All archived files are extracted before backup and archived // | together after backup. Avoiding archive updates. // | // | Revision 1.7 2003/04/24 08:00:28 wolf // | Fixed: Format of SQL dump. Better escaped special chars. // | // | Revision 1.6 2003/04/24 05:50:29 wolf // | Fixed: Some variable spelling errors // | Added: DROP TABLE in SQL file // | // | Revision 1.5 2003/04/17 22:44:32 wolf // | Changed: Diskspace measured by PHP functions (less system() calls). // | Changed: Version number in protocol header. // | Changed: Removed all
tags from protocol. // | Fixed: Array $exclude_tables properly initialized // | Fixed: ';' missed in SQL fiels after table structure. // | Added: Footer comment in SQL file. // | // | Revision 1.4 2003/04/17 20:12:46 wolf // | Added: Tables can be excluded from backup // | Changed: File and directory permissions // | // | Revision 1.2 2003/04/17 01:41:16 wolf // | Added. Browser STOP button is ignored. // | Added. MySQL table structure included in SQL-files. // | Added. Comment header in SQL-files. // | Changed. Compression done by Gzip. // | Added. update and delete on tar-files. // | // | Revision 1.1 2003/04/05 02:10:53 wolf // | New version of MySQL Database Backup. Now with gzip-compression. // | // +----------------------------------------------------------------------+ ?>