backup_dbs_config.php

<?php

######################################################################
## MySQL Backup Script v3 - Jan, 2017  - Now supports PHP7
######################################################################
## For more documentation and new versions, please visit:
## http://web3us.com/drupal6/content/how-setup-mysql#Backup_and_Restore
## -------------------------------------------------------------------
## Updated for PHP7 by Jeff Sly (www.web3us.com).
## Origonally Created by Dagon Design (www.dagondesign.com).
## Much credit goes to Oliver Mueller (oliver@teqneers.de)
## for contributing additional features, fixes, and testing.
######################################################################

######################################################################
## Usage Instructions
######################################################################
## This script requires two files to run:
##     backup_dbs.php        - Main script file
##     backup_dbs_config.php - Configuration file
## Be sure they are in the same directory.
## -------------------------------------------------------------------
## Do not edit the variables in the main file. Use the configuration
## file to change your settings. The settings are explained there.
## -------------------------------------------------------------------
## A few methods to run this script:
## - php /PATH/backup_dbs.php
## - BROWSER: http://domain/PATH/backup_dbs.php
## - ApacheBench: ab "http://domain/PATH/backup_dbs.php"
## - lynx http://domain/PATH/backup_dbs.php
## - wget http://domain/PATH/backup_dbs.php
## - crontab: 0 3  * * *     root  php /PATH/backup_dbs.php
## -------------------------------------------------------------------
## For more information, visit the website given above.
######################################################################

error_reporting( E_ALL );

// Initialize default settings
$MYSQL_PATH = '/usr/bin';
$MYSQL_HOST = 'localhost';
$MYSQL_USER = 'root';
$MYSQL_PASSWD = 'visitor1';
$BACKUP_DEST = '/db_backups';
$BACKUP_TEMP = '/tmp/backup_temp';
$VERBOSE = true;
$BACKUP_NAME = 'mysql_backup_' . date('Y-m-d');
$LOG_FILE = $BACKUP_NAME . '.log';
$ERR_FILE = $BACKUP_NAME . '.err';
$COMPRESSOR = 'bzip2';
$EMAIL_BACKUP = false;
$DEL_AFTER = false;
$EMAIL_FROM = 'Backup Script';
$EMAIL_SUBJECT = 'SQL Backup for ' . date('Y-m-d') . ' at ' . date('H:i');
$EMAIL_ADDR = 'root@localhost.localdomain';
$ERROR_EMAIL = 'user@domain.com';
$ERROR_SUBJECT = 'ERROR: ' . $EMAIL_SUBJECT;
$EXCLUDE_DB = 'information_schema';
$MAX_EXECUTION_TIME = 18000;
$USE_NICE = 'nice -n 19';
$FLUSH = false;
$OPTIMIZE = false;

// Load configuration file
$current_path = dirname(__FILE__);
if( file_exists( $current_path.'/backup_dbs_config.php' ) ) {
    require( $current_path.'/backup_dbs_config.php' );
} else {
    echo 'No configuration file [backup_dbs_config.php] found. Please check your installation.';
    exit;
}

################################
# functions
################################
/**
 * Write normal/error log to a file and output if $VERBOSE is active
 * @param string    $msg
 * @param boolean    $error
 */
function writeLog( $msg, $error = false ) {

    // add current time and linebreak to message
    $fileMsg = date( 'Y-m-d H:i:s: ') . trim($msg) . "\n";

    // switch between normal or error log
    $log = ($error) ? $GLOBALS['f_err'] : $GLOBALS['f_log'];

    if ( !empty( $log ) ) {
        // write message to log
        fwrite($log, $fileMsg);
    }

    if ( $GLOBALS['VERBOSE'] ) {
        // output to screen
        echo $msg . "\n";
        flush();
    }
} // function

/**
 * Checks the $error and writes output to normal and error log.
 * If critical flag is set, execution will be terminated immediately
 * on error.
 * @param boolean    $error
 * @param string    $msg
 * @param boolean    $critical
 */
function error( $error, $msg, $critical = false ) {

    if ( $error ) {
        // write error to both log files
        writeLog( $msg );
        writeLog( $msg, true );

        // terminate script if this error is critical
        if ( $critical ) {
            die( $msg );
        }

        $GLOBALS['error']    = true;
    }
} // function

 

################################
# main
################################

// set header to text/plain in order to see result correctly in a browser
header( 'Content-Type: text/plain; charset="UTF-8"' );
header( 'Content-disposition: inline' );

// set execution time limit
if( ini_get( 'max_execution_time' ) < $MAX_EXECUTION_TIME ) {
    set_time_limit( $MAX_EXECUTION_TIME );
}

// initialize error control
$error = false;

// guess and set host operating system
if( strtoupper(substr(PHP_OS, 0, 3)) !== 'WIN' ) {
    $os            = 'unix';
    $backup_mime    = 'application/x-tar';
    $BACKUP_NAME    .= '.tar';
} else {
    $os            = 'windows';
    $backup_mime    = 'application/zip';
    $BACKUP_NAME    .= '.zip';
}

// create directories if they do not exist
if( !is_dir( $BACKUP_DEST ) ) {
    $success = mkdir( $BACKUP_DEST );
    error( !$success, 'Backup directory could not be created in ' . $BACKUP_DEST, true );
}
if( !is_dir( $BACKUP_TEMP ) ) {
    $success = mkdir( $BACKUP_TEMP );
    error( !$success, 'Backup temp directory could not be created in ' . $BACKUP_TEMP, true );
}

// prepare standard log file
$log_path = $BACKUP_DEST . '/' . $LOG_FILE;
($f_log = fopen($log_path, 'w')) || error( true, 'Cannot create log file: ' . $log_path, true );

// prepare error log file
$err_path = $BACKUP_DEST . '/' . $ERR_FILE;
($f_err = fopen($err_path, 'w')) || error( true, 'Cannot create error log file: ' . $err_path, true );

// Start logging
writeLog( "Executing MySQL Backup Script v1.4" );
writeLog( "Processing Databases.." );

################################
# DB dumps
################################
$excludes    = array();
if( trim($EXCLUDE_DB) != '' ) {
    $excludes    = array_map( 'trim', explode( ',', $EXCLUDE_DB ) );
}

// Loop through databases
$db_conn    = @mysqli_connect( $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWD, 'drupal22' ) or error( true, mysqli_connect_error(), true );
#$db_result    = mysql_list_dbs($db_conn);

$sql="SHOW DATABASES";
if (!($db_result=mysqli_query($db_conn,$sql))) {
        printf("Error: %s\n", mysqli_error($db_conn));
    }

$db_auth    = " --host=\"$MYSQL_HOST\" --user=\"$MYSQL_USER\" --password=\"$MYSQL_PASSWD\"";
while ($db_row = mysqli_fetch_object($db_result)) {
    $db = $db_row->Database;

    if( in_array( $db, $excludes ) ) {
        // excluded DB, go to next one
        continue;
    }

    // dump db
    unset( $output );
    exec( "$MYSQL_PATH/mysqldump $db_auth --opt $db 2>&1 >$BACKUP_TEMP/$db.sql", $output, $res);
    if( $res > 0 ) {
        error( true, "DUMP FAILED\n".implode( "\n", $output) );
    } else {
        writeLog( "Dumped DB: " . $db );

        if( $OPTIMIZE ) {
            unset( $output );
            exec( "$MYSQL_PATH/mysqlcheck $db_auth --optimize $db 2>&1", $output, $res);
            if( $res > 0 ) {
                error( true, "OPTIMIZATION FAILED\n".implode( "\n", $output) );
            } else {
                writeLog( "Optimized DB: " . $db );
            }
        } // if
    } // if

    // compress db
    unset( $output );
    if( $os == 'unix' ) {
        exec( "$USE_NICE $COMPRESSOR $BACKUP_TEMP/$db.sql 2>&1" , $output, $res );
    } else {
        exec( "zip -mj $BACKUP_TEMP/$db.sql.zip $BACKUP_TEMP/$db.sql 2>&1" , $output, $res );
    }
    if( $res > 0 ) {
        error( true, "COMPRESSION FAILED\n".implode( "\n", $output) );
    } else {
        writeLog( "Compressed DB: " . $db );
    }

    if( $FLUSH ) {
        unset( $output );
        exec("$MYSQL_PATH/mysqladmin $db_auth flush-tables 2>&1", $output, $res );
        if( $res > 0 ) {
            error( true, "Flushing tables failed\n".implode( "\n", $output) );
        } else {
            writeLog( "Flushed Tables" );
        }
    } // if

} // while

mysql_free_result($db_result);
mysql_close($db_conn);

################################
# Archiving
################################

// TAR the files
writeLog( "Archiving files.. " );
chdir( $BACKUP_TEMP );
unset( $output );
if( $os    == 'unix' ) {
    exec("cd $BACKUP_TEMP ; $USE_NICE tar cf $BACKUP_DEST/$BACKUP_NAME * 2>&1", $output, $res);
} else {
    exec("zip -j -0 $BACKUP_DEST/$BACKUP_NAME * 2>&1", $output, $res);
}
if ( $res > 0 ) {
    error( true, "FAILED\n".implode( "\n", $output) );
} else {
    writeLog( "Backup complete!" );
}

// first error check, so we can add a message to the backup email in case of error
if ( $error ) {
    $msg    = "\n*** ERRORS DETECTED! ***";
    if( $ERROR_EMAIL ) {
        $msg    .= "\nCheck your email account $ERROR_EMAIL for more information!\n\n";
    } else {
        $msg    .= "\nCheck the error log {$err_path} for more information!\n\n";
    }

    writeLog( $msg );
}

################################
# post processing
################################

// do we email the backup file?
if ($EMAIL_BACKUP) {
    writeLog( "Emailing backup to " . $EMAIL_ADDR . " .. " );

    $headers = "From: " . $EMAIL_FROM . " <root@localhost>";
    // Generate a boundary string
    $rnd_str = md5(time());
    $mime_boundary = "==Multipart_Boundary_x{$rnd_str}x";

    // Add headers for file attachment
    $headers .= "\nMIME-Version: 1.0\n" .
        "Content-Type: multipart/mixed;\n" .
        " boundary=\"{$mime_boundary}\"";

    // Add a multipart boundary above the plain message
    $body = "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" .
        file_get_contents($log_path) . "\n\n";

    // make Base64 encoding for file data
    $data = chunk_split(base64_encode(file_get_contents($BACKUP_DEST.'/'.$BACKUP_NAME)));

    // Add file attachment to the message
    $body .= "--{$mime_boundary}\n" .
        "Content-Type: {$backup_mime};\n" .
        " name=\"{$BACKUP_NAME}\"\n" .
        "Content-Disposition: attachment;\n" .
        " filename=\"{$BACKUP_NAME}\"\n" .
        "Content-Transfer-Encoding: base64\n\n" .
        $data . "\n\n" .
        "--{$mime_boundary}--\n";

    $res = mail( $EMAIL_ADDR, $EMAIL_SUBJECT, $body, $headers );
    if ( !$res ) {
        error( true, 'FAILED to email mysql dumps.' );
    }
}

// do we delete the backup file?
if ( $DEL_AFTER && $EMAIL_BACKUP ) {
    writeLog( "Deleting file.. " );

    if ( file_exists( $BACKUP_DEST.'/'.$BACKUP_NAME ) ) {
        $success = unlink( $BACKUP_DEST.'/'.$BACKUP_NAME );
        error( !$success, "FAILED\nUnable to delete backup file" );
    }
}

// see if there were any errors to email
if ( ($ERROR_EMAIL) && ($error) ) {
    writeLog( "\nThere were errors!" );
    writeLog( "Emailing error log to " . $ERROR_EMAIL . " .. " );

    $headers = "From: " . $EMAIL_FROM . " <root@localhost>";
    $headers .= "MIME-Version: 1.0\n";
    $headers .= "Content-Type: text/plain; charset=\"iso-8859-1\";\n";
    $body = "\n".file_get_contents($err_path)."\n";

    $res = mail( $ERROR_EMAIL, $ERROR_SUBJECT, $body, $headers );
    if( !$res ) {
        error( true, 'FAILED to email error log.' );
    }
}

################################
# cleanup / mr proper
################################

// close log files
fclose($f_log);
fclose($f_err);

// if error log is empty, delete it
if( !$error ) {
    unlink( $err_path );
}

// delete the log files if they have been emailed (and del_after is on)
if ( $DEL_AFTER && $EMAIL_BACKUP ) {
        if ( file_exists( $log_path ) ) {
                $success = unlink( $log_path );
                error( !$success, "FAILED\nUnable to delete log file: ".$log_path );
        }
        if ( file_exists( $err_path ) ) {
                $success = unlink( $err_path );
                error( !$success, "FAILED\nUnable to delete error log file: ".$err_path );
        }
}

// remove files in temp dir
if ($dir = @opendir($BACKUP_TEMP)) {
    while (($file = readdir($dir)) !== false) {
        if (!is_dir($file)) {
            unlink($BACKUP_TEMP.'/'.$file);
        }
    }
}
closedir($dir);

// remove temp dir
rmdir($BACKUP_TEMP);

?>