Main menu
WalkswithMeMySQLhow to backup MySQL Database tables using php

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

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

  1. 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?

        1. 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+');

  2. 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.

    1. 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..

  3. 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 ..

    1. 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.

  4. 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!

      1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

 

FacebookTwitterGoogle+RSS