Some time its necessary to take backup of the database but generally this kind of script is not available easily on the internet.
Fortunately I found one script for that and much credit goes to Oliver Mueller ([email protected]) for writing the script.
This Script will take all the database backup from the specified domain.
It will give output in .tar formate as a single file and in that file you will get all the database backup as .sql files.
####################################################
## MySQL Backup Script
####################################################
## 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 ([email protected])
## 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
####################################################
####################################################
Main part of backup_dbs.php
####################################################
############# Start #############
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’;
// 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;
}
################################
# 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);
############ End #############
Create a cron job and be tension free about backing up your database.
Download the full script from here.