how to backup MySQL Database tables using php
In any situation we required a MySQL database tables backup with php scripting. Sometimes its helpful and its very simple to take backup MySQL database tables using php. Easy faster and very simple and clean script for getting your MySQL database backup.
You must have the following details for using the backup MySQL database table script. you should have host name , username ,password and the database name . You can easily customize the script for your requirement and add or remove DROP commands etc.
backup MySQL database tables using php
Just create a file name as backupmysql.php and have the following codes.
error_reporting(0);
backup_tables('localhost','root','','');//host-name,user-name,password,DB name
echo "<br/>Done";
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
$return = "";
$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);
$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('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
// echo $return;
fwrite($handle,$return);
fclose($handle);
}
After setting the required details you will get a file in the current location (where this page running ) its format is sql and name something like “db-backup-1376317010-85f31415a609ccab9491d22d36b8b7d9.sql”
Yes you have done with backup MySQL database table using php. try it today?
🙂 🙂 🙂
28 thoughts on “how to backup MySQL Database tables using php”
How to restore database.?
better to use CLI check this post http://www.walkswithme.net/import-mysql-db-using-command-line
for smaller DB only this method recomanded
timeout with large database, support zip?
set time limit using php init functions 🙂
the data base sql file is downloaded is empty is there any solution.
is that a large DB then make sure your code never get timeout .
It may create partial or empty files.
The backed up DB file is being downloaded into the server “htdocs” folder only. How can the DB file be downloaded into the client PC?
You can simply set the path for saving file on server using fopen().
Then simply type the url it will download the sql file.
Please provide an example
By default the file gets saved on the server where the page is running.
like where you running this script inside a folder or in root, on the same path it will save the sql file.
if you need custom path cab be set like
fopen('yourpath/myfilename.sql','w+');
Sorry. I was asking for downloading the DB to local machine from server!
Did you know the file saved path on server ?
Where the script executed on server ?
just type the same path on browser.
like below.
http://www.yourdomain.com/yourpath/myfilename.sql It will download the files through browser.!
Thanks. But it would be nice if the user can click and download it rather than typing the URL manually.
How to save filename as Db name -current time stamp
just replace the following section
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
to
$handle = fopen('DB-Name-'.time().'.sql','w+');
Thanks for Sharing your knowledge !!!
I created the backup of my database through this code and i deleted my database but when I import the file in database it give the error of “DROP TABLE xxx”.Could you just tell me the solution.
You do not need to remove the DB when its import back it will remove the old tables and create new with data,
Once you removed the DB then try to replace “DROP TABLE ” with “DROP TABLE IF EXISTS” in your backup file.
Hope it works..
i used the script but it give me error when i import it in database like “Unknow table table name”..
PLease tell me what is wrong ..
make sure your export works completely. means for large DB it may take long time to export entire table with data , so maximum execution time may exceed and incomplete file may generate,
so in that case increase maximum execution time.
You can export/ download your mysql database with hassel free manner with the single file you can download from following url https://github.com/vkt005/php-mysql-db-backup
What a great code. Working perfect. I had no other way of doing an auto backup since my websites are on shared hosting and can’t use the backup applications or system() methods.
For anyone who has UTF-8 characters in the database, you will need to add:
mysql_query(“set names utf8”);
Thanks!
Thanks Abdulraheem for the nice tips.
Where to put this line ????
mysql_query(“set names utf8″);
Try just above this line
mysql_query(‘SELECT * FROM ‘.$table);
something like,
mysql_query(“set names utf8″);
$result = mysql_query(‘SELECT * FROM ‘.$table);
Hope its works
Thanks for this great blog.
After so many days search, I got the solution from your blog.
hi this shailendra i need how to import in database….
thanks it worked properly