Hello Friends !!
Generally we take backup of our MySQL database using import/Export option in phpMyAdmin.
But Suppose, you have to take backup every day or every hour, this technique is time consuming. It takes more time.
There is one way to take backup which take less time. We can take backup using PHP code. When you run this code, This code create one backup file automatically which ia easily import in database when you want.
I write that PHP code below. Simply you have to copy this code and paste in you PHP file.
When you run this script, it crates backup file saved at where your this PHP file stored.
Look at this code :
<?php
backup_tables(‘hostaddress’,'dbusername’,'dbpassword’,'dbname’);
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = ‘*’)
{
$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))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(‘,’,$tables);
}
//cycle through
foreach($tables as $table)
{
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);
$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(‘db-backup-’.time().’-’.(md5(implode(‘,’,$tables))).’.sql’,'w+’);
fwrite($handle,$return);
fclose($handle);
}
?>
I think this post is become very useful to you..
If you like this or any query then please comment on this.
Thanks…
Why not have your PHP exec a shell script that does the backup for you?
for example…
[code]
#!/bin/bash
date=`date "+%Y-%m-%d"`
mysqldump -u dbUsername -pPassWord databaseName > /var/backup/databaseName-backup$date.SQL
[/code]
By using this script, you can easily take backup of your database.The generated backup file is .SQL file which is easily import in your database. It is the easiest way.
Probably not the most practical or scalable method here. If you didn’t save everything into $return and just write on each row or buffered it and write to the file when the buffer is full, then you won’t run out of memory
Ya Matth, In any programming language, if you want to write in file , you have to put that data in one variable.
Here $return is a variable.And i taste this script with that database which has 9 to 10MB data and have above 130 tables. And it works fine.
Its just that you are concatenating to $return for every row instead of appending to the backup file. So instead of creating a gigantic variable, you could make a smaller one, write to disk and repeat. Simply append with fwrite (a) or do a
file_put_contents(’db-backup-’.time().’-’.(md5(implode(’,’,$tables))).’.sql’, $return, FILE_APPEND);
after the last two $return assignments ( $return.= “);\n” and $return.=”\n\n\n”; )
Thanks for your suggestion.
Take Backup of MySQL database using PHP…
Backup your mySQL database using PHP….
[...] Take Backup of MySQL database using PHP – PHP Genious [...]
BUT………………….
How can we restore database using this .sql file? it is impossible i tried. pls help me……
Hi, Thanx a lot..This is a good script .This is very helpful for me.. thanx a lot once again..
i had button to call this function….how to pass values to this function