MySQL is an open source relational database management system (DBMS) which is frequently deployed in a wide assortment of contexts. Most frequently it is deployed as part of the LAMP Stack. The database system is also easy to use and highly portable and is, in the context of many applications, extremely efficient. As MySQL is often a centralized data store for large amounts of mission critical data, making regular backups of your MySQL database is one of the most important disaster recovery tasks a system administrator can perform. This guide addresses a number of distinct methods for creating back ups of your database as well as restoring databases from backups.
Dynamically creating database backup via program will be hand in many a situations. We can schedule the backup process periodically by using CRON jobs. Taking the database backup using PHP programming is easy. In this tutorial, we are going to learn how to backup MySQL database with a simple core PHP code.
Backup Methodology
Most backups of MySQL databases in this guide are performed using the mysqldump tool, which is distributed with the default MySQL server installation. We recommend that you use mysqldump whenever possible because it is often the easiest and most efficient way to take database backups. Other methods detailed in this guide are provided for situations when you do not have access to the mysqldump tool, as in a recovery environment like Finnix or in situations where the local instance of the MySQL server will not start.
Nevertheless, this guide provides a mere overview of the mysqldump tool, as there are many options for and uses of mysqldump that fall beyond the scope of this document. We encourage you to become familiar with all of the procedures covered in this document, and to continue your exploration of mysqldump beyond the cases described here. Be sure to note the following:
- The
*.sqlfiles created withmysqldumpcan be restored at any time. You can even edit the database.sqlfiles manually (with great care!) using your favorite text editor. - If your databases only make use of the MyISAM storage engine, you can substitute the
mysqldumpcommand with the fastermysqlhotcopy.
PHP Code To Make Backup File Of a Database
<?php
/**
* This file contains the Backup_Database class wich performs
* a partial or complete backup of any given MySQL database
* @author Daniel López Azaña <daniloaz@gmail.com>
* @version 1.0
*/
/**
* Define database parameters here
*/
define("DB_USER", 'root');
define("DB_PASSWORD", '');
define("DB_NAME", 'fana');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", 'Fana_Backup'); // Comment this line to use same script's directory ('.')
define("TABLES", '*'); // Full backup
//define("TABLES", 'table1, table2, table3'); // Partial backup
define("CHARSET", 'utf8');
define("GZIP_BACKUP_FILE", true); // Set to false if you want plain SQL backup files (not gzipped)
define("DISABLE_FOREIGN_KEY_CHECKS", true); // Set to true if you are having foreign key constraint fails
define("BATCH_SIZE", 100000); // Batch size when selecting rows from database in order to not exhaust system memory
// Also number of rows per INSERT statement in backup file
/**
* The Backup_Database class
*/
class Backup_Database {
/**
* Host where the database is located
*/
var $host;
/**
* Username used to connect to database
*/
var $username;
/**
* Password used to connect to database
*/
var $passwd;
/**
* Database to backup
*/
var $dbName;
/**
* Database charset
*/
var $charset;
/**
* Database connection
*/
var $conn;
/**
* Backup directory where backup files are stored
*/
var $backupDir;
/**
* Output backup file
*/
var $backupFile;
/**
* Use gzip compression on backup file
*/
var $gzipBackupFile;
/**
* Content of standard output
*/
var $output;
/**
* Disable foreign key checks
*/
var $disableForeignKeyChecks;
/**
* Batch size, number of rows to process per iteration
*/
var $batchSize;
/**
* Constructor initializes database
*/
public function __construct($host, $username, $passwd, $dbName, $charset = 'utf8') {
$this->host = $host;
$this->username = $username;
$this->passwd = $passwd;
$this->dbName = $dbName;
$this->charset = $charset;
$this->conn = $this->initializeDatabase();
$this->backupDir = BACKUP_DIR ? BACKUP_DIR : '.';
$this->backupFile = 'ፋና Backup ፋይል- ዕለት'.date("d-m-Y ሰዓት H-i-s", time()).'.sql';
$this->gzipBackupFile = defined('GZIP_BACKUP_FILE') ? GZIP_BACKUP_FILE : true;
$this->disableForeignKeyChecks = defined('DISABLE_FOREIGN_KEY_CHECKS') ? DISABLE_FOREIGN_KEY_CHECKS : true;
$this->batchSize = defined('BATCH_SIZE') ? BATCH_SIZE : 1000; // default 1000 rows
$this->output = '';
}
protected function initializeDatabase() {
try {
$conn = mysqli_connect($this->host, $this->username, $this->passwd, $this->dbName);
if (mysqli_connect_errno()) {
throw new Exception('ERROR connecting database: ' . mysqli_connect_error());
die();
}
if (!mysqli_set_charset($conn, $this->charset)) {
mysqli_query($conn, 'SET NAMES '.$this->charset);
}
} catch (Exception $e) {
print_r($e->getMessage());
die();
}
return $conn;
}
/**
* Backup the whole database or just some tables
* Use '*' for whole database or 'table1 table2 table3...'
* @param string $tables
*/
public function backupTables($tables = '*') {
try {
/**
* Tables to export
*/
if($tables == '*') {
$tables = array();
$result = mysqli_query($this->conn, 'SHOW TABLES');
while($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',', str_replace(' ', '', $tables));
}
$sql = 'CREATE DATABASE IF NOT EXISTS `'.$this->dbName."`;\n\n";
$sql .= 'USE `'.$this->dbName."`;\n\n";
/**
* Disable foreign key checks
*/
if ($this->disableForeignKeyChecks === true) {
$sql .= "SET foreign_key_checks = 0;\n\n";
}
/**
* Iterate tables
*/
foreach($tables as $table) {
$this->obfPrint("Backup ፋይል ይስራሕ ኣሎ `".$table."` ...".str_repeat('.', 50-strlen($table)), 1, 0);
/**
* CREATE TABLE
*/
$sql .= 'DROP TABLE IF EXISTS `'.$table.'`;';
$row = mysqli_fetch_row(mysqli_query($this->conn, 'SHOW CREATE TABLE `'.$table.'`'));
$sql .= "\n\n".$row[1].";\n\n";
/**
* INSERT INTO
*/
$row = mysqli_fetch_row(mysqli_query($this->conn, 'SELECT COUNT(*) FROM `'.$table.'`'));
$numRows = $row[0];
// Split table in batches in order to not exhaust system memory
$numBatches = intval($numRows / $this->batchSize) + 1; // Number of while-loop calls to perform
for ($b = 1; $b <= $numBatches; $b++) {
$query = 'SELECT * FROM `' . $table . '` LIMIT ' . ($b * $this->batchSize - $this->batchSize) . ',' . $this->batchSize;
$result = mysqli_query($this->conn, $query);
$realBatchSize = mysqli_num_rows ($result); // Last batch size can be different from $this->batchSize
$numFields = mysqli_num_fields($result);
if ($realBatchSize !== 0) {
$sql .= 'INSERT INTO `'.$table.'` VALUES ';
for ($i = 0; $i < $numFields; $i++) {
$rowCount = 1;
while($row = mysqli_fetch_row($result)) {
$sql.='(';
for($j=0; $j<$numFields; $j++) {
if (isset($row[$j])) {
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n","\\n",$row[$j]);
$row[$j] = str_replace("\r","\\r",$row[$j]);
$row[$j] = str_replace("\f","\\f",$row[$j]);
$row[$j] = str_replace("\t","\\t",$row[$j]);
$row[$j] = str_replace("\v","\\v",$row[$j]);
$row[$j] = str_replace("\a","\\a",$row[$j]);
$row[$j] = str_replace("\b","\\b",$row[$j]);
if ($row[$j] == 'true' or $row[$j] == 'false' or preg_match('/^-?[0-9]+$/', $row[$j]) or $row[$j] == 'NULL' or $row[$j] == 'null') {
$sql .= $row[$j];
} else {
$sql .= '"'.$row[$j].'"' ;
}
} else {
$sql.= 'NULL';
}
if ($j < ($numFields-1)) {
$sql .= ',';
}
}
if ($rowCount == $realBatchSize) {
$rowCount = 0;
$sql.= ");\n"; //close the insert statement
} else {
$sql.= "),\n"; //close the row
}
$rowCount++;
}
}
$this->saveFile($sql);
$sql = '';
}
}
/**
* CREATE TRIGGER
*/
// Check if there are some TRIGGERS associated to the table
/*$query = "SHOW TRIGGERS LIKE '" . $table . "%'";
$result = mysqli_query ($this->conn, $query);
if ($result) {
$triggers = array();
while ($trigger = mysqli_fetch_row ($result)) {
$triggers[] = $trigger[0];
}
// Iterate through triggers of the table
foreach ( $triggers as $trigger ) {
$query= 'SHOW CREATE TRIGGER `' . $trigger . '`';
$result = mysqli_fetch_array (mysqli_query ($this->conn, $query));
$sql.= "\nDROP TRIGGER IF EXISTS `" . $trigger . "`;\n";
$sql.= "DELIMITER $$\n" . $result[2] . "$$\n\nDELIMITER ;\n";
}
$sql.= "\n";
$this->saveFile($sql);
$sql = '';
}*/
$sql.="\n\n";
$this->obfPrint('<span style="color:red">100% ተወዲኡ</span>');
}
/**
* Re-enable foreign key checks
*/
if ($this->disableForeignKeyChecks === true) {
$sql .= "SET foreign_key_checks = 1;\n";
}
$this->saveFile($sql);
if ($this->gzipBackupFile) {
$this->gzipBackupFile();
} else {
$this->obfPrint('Backup file succesfully saved to ' . $this->backupDir.'/'.$this->backupFile, 1, 1);
}
} catch (Exception $e) {
print_r($e->getMessage());
return false;
}
return true;
}
/**
* Save SQL to file
* @param string $sql
*/
protected function saveFile(&$sql) {
if (!$sql) return false;
try {
if (!file_exists($this->backupDir)) {
mkdir($this->backupDir, 0777, true);
}
file_put_contents($this->backupDir.'/'.$this->backupFile, $sql, FILE_APPEND | LOCK_EX);
} catch (Exception $e) {
print_r($e->getMessage());
return false;
}
return true;
}
/*
* Gzip backup file
*
* @param integer $level GZIP compression level (default: 9)
* @return string New filename (with .gz appended) if success, or false if operation fails
*/
protected function gzipBackupFile($level = 9) {
if (!$this->gzipBackupFile) {
return true;
}
$source = $this->backupDir . '/' . $this->backupFile;
$dest = $source . '.gz';
$this->obfPrint('Backup ፋይል ብ GZ መልክዕ ተዳልዩ ' . $dest . '... ', 1, 0);
$mode = 'wb' . $level;
if ($fpOut = gzopen($dest, $mode)) {
if ($fpIn = fopen($source,'rb')) {
while (!feof($fpIn)) {
gzwrite($fpOut, fread($fpIn, 1024 * 256));
}
fclose($fpIn);
} else {
return false;
}
gzclose($fpOut);
if(!unlink($source)) {
return false;
}
} else {
return false;
}
$this->obfPrint('<span style="color:red">100% ተወዲኡ</span>');
return $dest;
}
/**
* Prints message forcing output buffer flush
*
*/
public function obfPrint ($msg = '', $lineBreaksBefore = 0, $lineBreaksAfter = 1) {
if (!$msg) {
return false;
}
if ($msg != 'OK' and $msg != 'KO') {
$msg = date("Y-m-d H:i:s") . ' - ' . $msg;
}
$output = '';
if (php_sapi_name() != "cli") {
$lineBreak = "<br />";
} else {
$lineBreak = "\n";
}
if ($lineBreaksBefore > 0) {
for ($i = 1; $i <= $lineBreaksBefore; $i++) {
$output .= $lineBreak;
}
}
$output .= $msg;
if ($lineBreaksAfter > 0) {
for ($i = 1; $i <= $lineBreaksAfter; $i++) {
$output .= $lineBreak;
}
}
// Save output for later use
$this->output .= str_replace('<br />', '\n', $output);
echo $output;
if (php_sapi_name() != "cli") {
if( ob_get_level() > 0 ) {
ob_flush();
}
}
$this->output .= " ";
flush();
}
/**
* Returns full execution output
*
*/
public function getOutput() {
return $this->output;
}
}
/**
* Instantiate Backup_Database and perform backup
*/
// Report all errors
error_reporting(E_ALL);
// Set script max execution time
set_time_limit(900); // 15 minutes
if (php_sapi_name() != "cli") {
echo '<div style="font-family: monospace;">';
}
$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, CHARSET);
$result = $backupDatabase->backupTables(TABLES, BACKUP_DIR) ? '<span style="color:red">100% ተወዲኡ</span>' : 'KO';
$backupDatabase->obfPrint('Backup ውፅኢት: ' . $result, 1);
// Use $output variable for further processing, for example to send it by email
$output = $backupDatabase->getOutput();
if (php_sapi_name() != "cli") {
echo '</div>';
}
?>NB: Change the name of the database in the code. the database name i have used here is Fana.
deamatsoftwareplc.code.blog 
Leave a comment