What is Database backup ??
Database backup is a process of creating the duplicate copy of your database with architecture and it’s stored data. A backup is a duplicate copy of your database that is use to reconstruct or restore your database structure and data.
Basically database is a structured form of data that is stored into a computer. it is a collection of schemas, tables and queries. Database is a very important part of your system. It is use to store all necessary and important data. We use this stored data in various ways.
Some Popular Database management sytems are as given below
1: Mysql
2: Microsoft SQL Server
3: Postgre SQL
4: Oracle Database
5: Microsoft Access
6: SQLite
7: Maria DB
Why Database backup is important ??
It is clear that if you want to recover any disaster, you should take the proper backup of your databases. As well as Database backup will also help you while you are upgrading your database.
You should take your database backup as regualr basis.
How to take backup of you Mysql Database using PHP ??
Step 1: Create a connection file
connection.php
<?php
define("HOST", "localhost"); // The host you want to connect to.
define("USER", "root"); // The database username.
define("PASSWORD", "DB PASSWORD"); // The database password.
define("DATABASE", "happy_db"); // The database name.
$con = mysql_connect(HOST, USER, PASSWORD) or die ("Not Connected");
mysql_select_db(DATABASE, $con) or die ("Database not found");
?>
In the above php code i declare 4 constant variables with the names HOST, USER, PASSWORD, DATABASE . These variables will be use in following code
Step 2: I will create a function backup_mydb() as follow which take five parameters. Here you will pass your host, database username, database password and the name of database.
db_backup.php
<?php
include 'connection.php';
//backup_mydb('localhost','root','vertrigo','mydb');
backup_mydb(HOST,USER,PASSWORD,DATABASE);
mysql_close($con);
/* backup the db OR just a table */
function backup_mydb($host,$user,$pass,$name,$tables = '*')
{
//echo $host." ".$user." ".$pass." ".$name;
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
///echo $row[0];
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
//cycle through
foreach($tables as $table)
{
//echo 'SELECT * FROM '.$table;
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
$return.= 'DROP TABLE '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n","\\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n\n\n";
}
//save file
$handle = fopen('Database_backup'.date('d-m-Y').'.sql','w+');
///echo $handle;
fwrite($handle,$return);
header("Content-Type: application/sql");
$fname="Database_backup".date('d-m-Y').".sql";
header("Content-Disposition: attachment; filename='$fname'");
header("Pragma: no-cache");
header("Expires: 0");
readfile("$fname");
//fclose($handle);
}
?>
in the above php code i created a function with the name backup_mydb() in which i passed 5 parameters respectively 4 constant variables HOST, USER, PASSWORD, DATABASE, which are defined in step 1 and last is $table that describes all tables of database . Try to re-establish database connection and store it on $link and then select database.
if $table ==”*”, it means you want to take the backup of all tables. i declare an empty array $tables and make a query with SHOW TABLES command of MYSQL which return the list of all existed tables of database. Hold this list of tables in $result variable. Now start a while loop to store these tables into an array $tables that has defined previously.
After storing all tables ino an array $tables pass it into a foreach loop and fetch all stored data of tables with following command .
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
mysql_num_fields() is used to get the total number of fields into a result resource.
$return.= 'DROP TABLE '.$table.';';
The above code will insert a DROP QUERY before the table into backup data which is use to drop the table if it is already exist when you import the database. All data of database will be stored into $return .
fopen() function with w+ mode is use to open a file to read and write.
fwrite() function is use to write an open file. It accept file name as first parameter and the value to the write as second parameter.
Finally after writing the data into file you will redirect to download.
Thanks to read this post and if you feel any problem, guys can leave it on comment. I will try to solve it.
