Restoring a large MySQL Database with cPanel - help?

[RFiend]

This is actually a VERY good thread to keep handy somewhere. Others in a similar situation WILL have problems due to a couple of limitations here at HG that I haven't encountered with other hosts.

I went nuts trying to figure out how to back up our SimpleMachines forums. We have 3 of 'em... one with an 8meg database, one with a 30meg database, and one with an 87meg database. The little forum backed up through the SMF Admin panel just fine. The 30meg forum wouldn't back up until I un-checked "Compress the file with gzip" for the backup, then it worked. The largest forum wouldn't back up no matter what I tried. Several aftermarket scripts and solutions all failed to save the 87meg SQL database. That was an intolerable situation.

I finally found a script that did a raw dump of the database into a BZ2-compressed file. To get it to work, I had to remove the code relating to GZIP, FTPing or e-mailing the backup, and the part at the end where it deleted the temp files. In the config file, I set the TMP location to be a directory called 'backups' in the same dir as the script. It would be a bit cleaner if I prepended the date onto the backups, as I'm doing that manually at the moment. If you use your root user and password, it saves ALL of your SQL databases at once, although you can specify a list of DBs to exclude.

The BIGDUMP script mentioned earlier will restore these BZ2 compressed databases, and it only throws an error after the whole database has been restored. One of the last lines in the SQL file is attempting to set the time zone on the database, and it errors and aborts at that point. Here's what gets missed on the import:

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


It works fine for me. If you have huge SQL databases, this is the only method I've found thus far of saving them here at HG. In case you missed it earlier, here's BIGDUMP once again:
http://www.hotscripts.com/jump.php?listing_id=20922

Here's the backup script that almost works (you'll need the backup_dbs_config.php file):
http://www.dagondesign.com/articles/...backup-script/

and here's the modified script that I got to work (there are 2 required scripts in the distribution files, this replaces backup_dbs.php):

PHP Code:

<?php

######################################################################
## MySQL Backup Script v2.1 - May 3, 2007
######################################################################
## For more documentation and new versions, please visit:
## http://www.dagondesign.com/articles/automatic-mysql-backup-script/
## -------------------------------------------------------------------
## 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 = 'password';
$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 = 'user@domain.com';
$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    = @mysql_connect( $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWD ) or error( true, mysql_error(), true );
$db_result    = mysql_list_dbs($db_conn);
$db_auth    = " --host=\"$MYSQL_HOST\" --user=\"$MYSQL_USER\" --password=\"$MYSQL_PASSWD\"";
while ($db_row = mysql_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 );


// 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 );
}


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

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

?>