Backup Your MySQL Database Using PHP

By  on  

One of the most important tasks any developer needs to do often is back up their MySQL database. In many cases, the database is what drives most of the site. While most web hosts do a daily backup of a customer's database, relying on them to make backups and provide them at no cost is risky to say the least. That's why I've created a database backup function that I can call whenever I want -- including nightly CRONs.

The PHP & MySQL Code

backup_tables('localhost','username','password','blog');

/* 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);
		
		$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+');
	fwrite($handle,$return);
	fclose($handle);
}

Of course, you'll need to provide database credentials to the function, as well as an array of tables you'd like to backup. If you provide a "*" or no tables, a complete database backup will run. The script does the rest!

Never take chances when your website is on the line. Make frequent backups or pay the price later!

Recent Features

Incredible Demos

  • By
    Highlight Table Rows, Columns, and Cells Using MooTools 1.2.3

    Row highlighting and individual cell highlighting in tables is pretty simple in every browser that supports :hover on all elements (basically everything except IE6). Column highlighting is a bit more difficult. Luckily MooTools 1.2.3 makes the process easy. The XHTML A normal table. The cells...

  • By
    CSS :target

    One interesting CSS pseudo selector is :target.  The target pseudo selector provides styling capabilities for an element whose ID matches the window location's hash.  Let's have a quick look at how the CSS target pseudo selector works! The HTML Assume there are any number of HTML elements with...

Discussion

  1. This is something I’ve been meaning to do for a while now. Dreamhost does do regular backups they claim (have never had to use them), but I should really do this anyway.

    • david

      Thanks, very informative.

  2. Rich

    David, would you suggest doing this manually, or maybe setting up a cron to do this on a weekly basis. or maybe having mootools or jquery (hint hint)…to set up a timer to do this…that way it just overrides the last one and you always have a weekly backup of your Db at hand…just in case. any thoughts?

  3. ok, noob checking in here. yes, i’m one of those “hmmm, what does that do…” sort of self-taught posters. most of the time i learn enough get into trouble. ;)

    anyway, from what i can ascertain from parsing your code, this should be a very sweet addition to the must-haves.

    but, as a noob, i’m not sure i grok where the file is being saved “to”

    i notice the save comment at the end, but cannot decipher where it’s actually saved.

    a little insight please?

    thanks! and thanks again for your blog.

    • Robert Brooks

      Hi Micheal, It looks like he is saving the output of the file to whichever directory this php code is located at. I might be wrong but the file name is timestamped and given a diffcult to guess name so nobody will stumble upon it and get access to his entire database structure-which would be not good!. If I were to do the same I would be writing this file to an out of server root location.

  4. @Rich: I wouldn’t trust jQuery or Moo to do this — this is definitely a server-side responsibility. Maybe a nightly or weekly CRON would be best. My blog emails me a DB backup weekely.

    @Michael: I’m writing a backup to a file called “db-backup-{current time}-{tables md5}.sql” file. That way it’s (theoretically) easy to tell when the backup was last run.

  5. Rich

    Kind of figured that. Awesome idea though. I usually do it manually very month. this will make life so much easier. Thanks again David!

  6. He David,
    What happened with the good old mysqldump command? Even gzipped for saving bandwidth or disk space.
    And in PHP code:

    $output = `mysqldump --add-drop-table database-name | gzip > db.sql.gz`;
    

    Or with specific tables:

    $output = `mysqldump --add-drop-table database-name table1,table2,table3 | gzip > db.sql.gz`;
    

    Saves you a line or two ;)
    Cheers!

  7. Martin

    That wont re-create all your indexes n stuff tho will it?

    mysqldump is the tool of choice.

  8. Nice article.

    Every webmaster should really set up a Cron like that to backup their database !!

    I made up a similar script for my websites.

    The solution of sending the sql by mail is not a good solution if your site become larger and larger. Or you need to save only recent entries and not all tables. If you have a ftp for storing the backup file, you could copy the file once a week on the ftp.

    Mysqldump is not accessible on all hosting services so this script is the solution.

  9. I know mysqldump is not available on every shared hosting. But I choose a more expensive hostng package or VPS when it is not available.
    This is IMHO not a security issue, so why should it not be available…

  10. @Martin, PrimeMinister: I chose this route because as the two posts after you mentioned, many hosting providers don’t allow the mysqldump functionality.

  11. gerry22

    bug?

    for ($i = 0; $i < $num_fields; $i++)  { 
    $return.= 'DROP TABLE '.$table.';
    
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    
    $return.= "\n\n".$row2[1].";\n\n";  
    

    loops through and creates multiple Drop Table and Create table mysql statements

    me thinks this part:

             $return.= 'DROP TABLE '.$table.';';  
    
            $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));  
            $return.= "\n\n".$row2[1].";\n\n";  
    

    should be outside the for loop

  12. I agree with you primeminister. A hosting provider should make mysqldump available some way or another.

  13. ereg() is slower than preg_match, and is being depreciated in php6.

    I would recommend exchanging the ereg() function with preg_match().

    Other than that, great script! (as usual)

  14. @ gerry22:

    Yup

    $return.= 'DROP TABLE '.$table.';';
    
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";
    
    
    Should be above:
    
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
    for ($i = 0; $i < $num_fields; $i++) 
    {
    
  15. @gerry22: Good call. I’ll fix the article as soon as I can.

  16. am i the only person who groaned when I saw the DROP command being used.

    seriously, I havent looked closely through the code, but is it dropped a duplicated table or what is the DROP doing?!

  17. Johnathan

    It’s there in case you need to restore the table, must like PHPMyAdmin. What’s with the groand?

  18. ok, sorry. I didnt read the code all the way through. Its writing it out rather than actually DROPPING the table in the backup processes. Thats what I get for commenting before I read the code…. :(

  19. elias

    Hello David,

    I found this script very useful, but i have problems with blob data, the script wont get this data correctly.

    Thanks

  20. Nice looking code, i have not tested it yet but i plan on doing so, im looking to modify the code a little to suit my needs (implement it into my members control panel) but il be sure to come back to you with the new work so you can repost it here :)

    yeah yeah im one of those hosts that doesnt permit sqldump lol, infact i dont use the main stream control panels for my hosting at all.

    • Ku Hye Sun

      Hi Brian, did you find a way to fix the blob data and by using the above script, a file is created but empty.

  21. Your post is a very good. I’ll download and try.

  22. One little modification I made:

    $return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;

    Added the IF EXISTS !!

    Great script!!!, a live saver!!!

    • I did exactly the same thing as yourself. I’m also in agreement, this script is very well written and just saved me a tonne of time writing my own!

      Many thanks!

  23. Bryce

    Now we just need a restoring script :)

  24. Carl

    Hi, I think what Michael was trying to say was…where is the actual file itself being saved to…is it the same directory as the the script, do I have to define it…or is there a pre-defined saving camp?

    Ta Much.

  25. @Carl: The same directory as the script.

  26. Michael

    @Carl, & @David: thanks!

  27. Carl

    Gotcha, Thanks – I’m uploading now ;)

  28. john

    superb article, just what i had spent days looking for.

    As someone has previously posted though, how do we restore the database once it has ben backed up?

  29. Kelvin

    A straight to the point script, just what i was looking for many thanks.

    One quick question, what is the purpose of (md5(implode(‘,’,$tables))) ? ?

  30. @Kelvin: It takes a string of the desired tables and md5’s them for the file name so that files aren’t constantly being overwritten.

  31. Would you mind sharing the script you use to email the file weekly?

  32. If you’re getting an “Out of range value adjusted for column error” when trying to restore your backup, edit lines:

    if (isset($row[$j])) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘””‘; }

    with this:

    if (isset($row[$j])) {
    if ($row[$j] == null) {
    $return .= ‘null’ ;
    } else {
    $return .= ‘”‘.$row[$j].'”‘ ;
    }
    }
    else {
    $return .= ‘””‘;
    }

  33. kunal

    what is the solution for ” how to transfer the file from one ftp server to the another ftp server directly plz explainning in details if possible for anyone”

  34. nice, just what I was after, Thanks David

    @Micheal Fox – I’ve bolted on some script to send the sql, that I found here http://www.sitepoint.com/article/advanced-email-php/

    a little modified, only real difference is MIME type application/x-sql.

    just add the function below, and call it here in the original script print("fwrite($handle,$return);

    sendBackup($filename, $name);
    fclose($handle);");`
    
    print("<?php
    
    
    function sendBackup($newFileName, $name){
    // Read POST request params into global vars
    $to      = 'me@example.com;
    $from    = 'me@example.com;
    $subject = 'Your Backup for '.$name;
    $message = 'Backup for '.$name;
    // Obtain file upload vars
    $fileatt      = $newFileName;
    $fileatt_type = "application/x-sql";
    $fileatt_name = $newFileName;
    
    $headers = "From: $from";
    
    // Read the file to be attached ('rb' = read binary)
      $file = fopen($fileatt,'rb');
      $data = fread($file,filesize($fileatt));
      fclose($file);
    
    // Generate a boundary string
      $semi_rand = md5(time());
      $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
    
    // Add the headers for a file attachment
      $headers .= "\nMIME-Version: 1.0\n" .
                  "Content-Type: multipart/mixed;\n" .
                  " boundary=\"{$mime_boundary}\"";
    
    // Add a multipart boundary above the plain message
      $message = "This is a multi-part message in MIME format.\n\n" .
                 "--{$mime_boundary}\n" .
                 "Content-Type: text/plain; charset=\"iso-8859-1\"\n" .
                 "Content-Transfer-Encoding: 7bit\n\n" .
                 $message . "\n\n";
    
    // Base64 encode the file data
      $data = chunk_split(base64_encode($data));
    
    // Add file attachment to the message
      $message .= "--{$mime_boundary}\n" .
                  "Content-Type: {$fileatt_type};\n" .
                  " name=\"{$fileatt_name}\"\n" .
                  //"Content-Disposition: attachment;\n" .
                  //" filename=\"{$fileatt_name}\"\n" .
                  "Content-Transfer-Encoding: base64\n\n" .
                  $data . "\n\n" .
                  "--{$mime_boundary}--\n";
    
    // Send the message
    $ok = @mail($to, $subject, $message, $headers);
    if ($ok) {
      echo "Mail sent! ";
    } else {
      echo "Mail could not be sent. Sorry!";
    }
    }
    ?>");
    
  35. Add this code to the end to save on your local drive:

    $filename = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
    Header("Content-type: application/octet-stream");
    Header("Content-Disposition: attachment; filename=$filename");
    echo $return;
    
  36. Exporting in xml will create a huge file… am I wrong?

  37. Ahmad Alfy: It definitely would.

  38. subash

    i am very happy to find this code .it relay work fine and very few time to take a backup the database
    thanks

  39. Richei

    This script saved my bacon! i had tried a few others but kept getting errors. Had a few errors on this one, but it was with me, not the script. Only thing i need to figure out is how to get it save to a specific folder, but i think i need to use the physical path instead of the relative one.

    Thanks for the script!

  40. It is a great script, but what about VIEWS ?
    The script recognize a VIEW as a TABLE and I get this:
    “DROP TABLE vw_customerorders;

    CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW vw_customerorders AS select unl_login.FirstName…….”

    “INSERT INTO vw_customerorders VALUES(“…………..”)
    For this issue what can be done ?

    Thank you for the script and the informations i usually get from your Blog.

    • tbass

      This is old question, but might help someone. If you change your SHOW TABLES sql command to: $result = mysql_query(‘SHOW FULL TABLES WHERE Table_type = \’BASE TABLE\”); you will only get tables and not VIEWS.

    • erde

      But how I can use the script for the VIEWs, is it possible?
      Thank you

  41. Alvaro

    Great script!

    Just checked that NULL values are grabbed as “0”… perhaps It would be fixed as per below:

    if (isset($row[$j]) && ($row[$j] != NULL)) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘NULL’; }

    Again… thank you for your script

  42. I love yor filename pattern.. Thanks, scripts work and make me Understand :)

  43. Dickram

    Thank you very much. I was just googling about this stuff and found this. Not tested yet, but after reading the comments I know it will surely work!!
    Thanx and keep up the good work.

  44. This is a really cool script you’ve got Dave, thanks! I’ll be pointing a link at you as soon as I can get it working on my client’s server. It’s working like a dream on my test site, but the DB on the client site is larger and the script keeps timing out 3 tables into the process (I have 16 total). If you have a work around, I’d love to hear it. Again, thanks for the jumpstart

  45. Bob

    Thanks for sharing that. I’ll give it a try. It does look like a life saver.

    And do you have a similar script to run with a cron job to backup the web public directory in a .tar or .tgz file, prefix or suffix with the date?
    that would a great backup combo script !!!! and another interesting article.

    cheers and thanks

  46. Thanks, saved a load of time and taught a thing or two.

    For those worried about having too many backup files stored, add a simple table on your database to store the details of your backups and use this info to purge unwanted files – for example – run the below from your sql window to create the table.

    CREATE TABLE `backup` (
      `id` tinyint(10) NOT NULL auto_increment,
      `name` varchar(50) NOT NULL,
      UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    

    Where David recommends saving using the code:

    //save file
    $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
    

    If you instead use

    $now=time();
    //save file - this is using a different format to the one above to help keep the filename samaller in size (personal preference)
    $handle = fopen('db-backup-'.$now.'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
    
        $sql="INSERT INTO backup (name)
    VALUES
    ('db-backup-".$now.".sql')";if (!mysql_query($sql))
    //file saved to the database you created above
      {
      die('Error: ' . mysql_error());
      }
    //change the LIMIT 14 to be the number of backups you want to keep. For example I run this once a day so saving the 14 latest backups provides a 2 week window.
      $query="SELECT * FROM backup ORDER BY id DESC LIMIT 14,100";
      $result = mysql_query($query);
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    //removes unwanted backup files and then removes their details from the database
    $name=$row['name'];
    unlink ($name);
    $sql="DELETE FROM backup WHERE name='$name'";
      if (!mysql_query($sql))
      {
      die('Error: ' . mysql_error());
      }
    
    
      };
    

    And using this will allow you to only keep a limited amount of backups, with the oldest being deleted as a new one is created. I hope this helps keep your backup-footprint down :)

    Great White

  47. Thanks for this script! Worked like a charm.

    I would like to ask a question. Could you show us on how to then make something which the user can click on the button to download the file create?

    Thanks a lot. Please contact me here or on my email. Thanks.

  48. Tenzin

    Nice work,
    I tried it instantly and it did back up my DB. But I saw problem with a character encoding. It lost all the character informations, all I can see is lots and lots of ?????????????? marks :-(. I tried with phpmyadmin dump, it don’t show this problem. My sites language is Tibetan and German ( with german chars. no problems ). Any idea or suggestion for fix.
    Thanks a lot
    Tenzin

    • Mohi

      Same Problem…
      did u found how to fix it?

    • you just need to set the charset to utf8,
      here is how I solved it,

      $conn = mysqli_connect($host,$user,$pass,$db);
      if (!$conn->set_charset("utf8")) {
          printf("Error loading character set utf8: %s\n", $conn->error);
      } else {
      	
      }
      
  49. Giedrius

    Not so bad. But I tried to save all db tables with *.
    And theses tables without records, were dropped and after that not created.
    bug?

  50. I tried a script that ran mysqldump through the PHP system() and it did nothing but create a blank file. This one works like a bloody charm and is better than relying on using a system command, which can vary if moved to a different server. Though I chopped all the login stuff out since each page is always logged into the database that needs backing up
    THANKS!

  51. Philip

    It doesn’t seem to be exhaustiv but Thanks!

  52. Lorand

    What is the goal of the first loop on line 36?

      for ($i = 0; $i < $num_fields; $i++)
    

    On line 41 you have again the same loop:

      for($j=0; $j<$num_fields; $j++)
    

    Is it necessary to loop two time through the table fields? May be there is something I don’t understand. Thank you.

  53. Dave

    I have been using this script and hit a glitch when I tried restoring a hacked Joomla site tonight. I noticed some of my articles were getting cut off occasionally when a single or double quote was used in the article. I looked at the backup created by this script and noticed some of the quotes in the SQL backup didn’t get escaped with a “\”, so now I have to manually go through the backup and add them where they were missed so the restore goes through properly. Has anyone run into this problem? I really love this script but now I’m worried about running into a similar issue on a more robust site which would be a nightmare to manually go through and add slashes that were skipped!

  54. Hi I am facing this error please help me

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 128921702 bytes) in /home/webmaste/public_html/dewshare.com/forums/backup.php on line 46

  55. dan

    first of all thanks david for these awsome tutorials,you helped a lot in understanding mootools ;).

    Right now i’m doing kind of a toolbox where i collect all kinds of useful classes or write them myself,in the last days i did a login with php/mysqli, because i wanted to use the mysqli-prepared-statement functionality.
    as i browsed through your tuts today i found the ‘create-a-zip’ and this tut,so i went on and combined these with my mysqli_wrapper_class ( i had to rewrite your backup-script as well :> ).
    Right now i’m searching for a way to make an backup-file for every table in the selected database,just a little more fixing.

    greetz from germany (yeah,we can code too)

  56. oluwaseun

    i must commend your effort in displaying thsi code.this i have been looking for for my many weeks now.God bless you.thanks so much.

  57. Actualy there’s a little mistake in line 41. Where it says:

    for($j=0; $j<$num_fields; $j++) 
    

    It should say

    $columns = count($row);
    for($j=0; $j<$columns; $j++) 
    

    I hope this helps everybody and specially @Lorand.

    For those how use mysqli here’s my modified version:

            foreach($tables as $table){
                /* Drop previous tables */
                $return.= 'DROP TABLE IF EXISTS '.$table.';';
    
                /* Get 'how to' create the table */
                $result = $this->link->query('SHOW CREATE TABLE '.$table);
                $row = $result->fetch_array(MYSQL_NUM);
                $return.= "\n\n".$row[1].";\n\n";
    
                $result = $this->link->query('SELECT * FROM '.$table);
                $num_fields = $result->num_rows;
    
                for ($i = 0; $i fetch_array(MYSQL_NUM)) {
                        $columns = count($row);
                        $return.= 'INSERT INTO '.$table.' VALUES(';
                        for($j=0; $j<$columns; $j++) {
                            $row[$j] = addslashes($row[$j]);
                            $row[$j] = preg_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";
            }    
    
  58. chidi

    Wow!! it worked like magic. Could you please give a tutorial for exporting tables in a database to excel? will be glad if you would.

    thanks

  59. Add these lines at the line 4 and it will can backup UTF-8 databases:

    mysql_query('SET NAMES utf8');
    mysql_query('SET CHARACTER SET utf8');
    

    Byez!

  60. charan

    great script… will try it out

  61. MrTuTu

    Thank you ! It works perfect and i spare time…

  62. thanks, myself and a mate just used this script. very handy. you’ve saved us a lot of effort :)

  63. David,
    Nice script! I added a little something I’d like to share. When naming the file, I put the date and table as the name.

    //save file
    if ($tables = '*') {
       $table="all";
    }
    // if your not backing up all(*) the tables, it appends that table name to the end of the file.
    $handle = fopen('db-backup-'. date("Ymd") .'-'. $name .'-'. $table. ' .sql','w+');
    

    Gives you something like: db-backup-20091216-mydatabase-all.sql

    Hope that helps someone, thanks again David

  64. razvan

    beautiful.. thanks

  65. juanantonio

    great script.. can some one give me a script for restoring the DB, i tried a script but only reads the first 3000 lines, my DB its big (141200 lines) ..please i need some help

    thanks

  66. Amazing!
    My former (free) host had connections issues for phpMyAdmin, so I wasn’t able to export my database. Thanks to you now I saved it!

    Thank you for this amazing script.

  67. Aw I just forgot to add that you should modify a line in the script :
    $return.= ‘DROP TABLE ‘.$table.’;’;

    should be :
    $return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;
    in order to avoid any “inexistant talbe” warnings.

    Cheers,

  68. vasu

    The script is not fetching value…. in insert query.

  69. First off nice script give me a starting point for a good backup system.
    There should be a $return = '' ; just after mysql_select_db($name,$link); this way you don’t get a notice of undefined return
    I also did a little mod to you system and added to My $db class
    and added a choice of rather to do just the structure or just the data

    function backup_tables($host='',$user='',$pass='',$name='',$tables = '*',$struc=false,$data=false)
    {
            global $db;
    	$return = '';
    	
    	//get all of the tables
    	if($tables == '*')
    	{
    		$tables = array();
    		$result = $db->sql_query('SHOW TABLES');
    		while($row = $db->sql_fetchrow($result))
    		{
    			$tables[] = $row[0];
    		}
    	}
    	else
    	{
    		$tables = is_array($tables) ? $tables : explode(',',$tables);
    	}
    	
    	//cycle through
    	foreach($tables as $table)
    	{
    		$result = $db->sql_query('SELECT * FROM '.$table);
    		$num_fields = $db->sql_numfields($result);
    		
    		if($struc)$return.= 'DROP TABLE '.$table.';';
    		$row2 = $db->sql_fetchrow($db->sql_query('SHOW CREATE TABLE '.$table));
    		if($struc)$return.= "\n\n".$row2[1].";\n\n";
    		
    		if($data){
    		for ($i = 0; $i sql_fetchrow($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('./backups/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    	fwrite($handle,$return);
    	fclose($handle);
    }
    backup_tables($host,$user,$pass,$db,$tables = '*',false,true);
    returns only data
    backup_tables($host,$user,$pass,$db,$tables = '*',true,false);
    returns only structure 
    backup_tables($host,$user,$pass,$db,$tables = '*',true,true);
    returns all
    
  70. jamus

    Great script! I have to add the tables for it to run though. Leaving * results in the ‘mysql_num_fields(): supplied argument is not a valid MySQL’ error. Is this because of my host?

  71. jamus

    Also, I’ve just compared a out from this script to one from phpmyadmin.

    Text content that had a line break included is being output incorrectly and actually creating a line break in the .sql.

    ” Cras pulvinar\r\ndui in urna sagittis”

    ” Cras pulvinar
    \ndui in urna sagittis”

    Any ideas why this might be?

  72. Arnold

    guys i have problem with utf-8 , i dont know where to add

    mysql_query('SET NAMES utf8');
    mysql_query('SET CHARACTER SET utf8');
    

    someone says in line 4 but i test that and still my records are in ? marks

    plz tell exact place of code

  73. Your text editor is UFT-8 compilant?

  74. Arnold

    thanx guys problem was in importing db file in SSH

    but still i can not compress the sql file

    i wonder how i can create gz file to save bandwidth

  75. Simple as:
    gzip yourfile.sql

  76. What would be the solution to let’s say delete 3 days old backups, as they are in this form:

    hostname.com-db-backup-‘.date(‘Y-m-d–H-i’).zip

  77. @Lorand: I don’t understand it either. The $i loop seems useless to me, rows are only fetched on the first iteration, the rest do nothing . Am I missing something?

    But thanks for the script btw. :)

  78. Aleksandar

    Another useful thing in the script would be to add:
    $return = “SET FOREIGN_KEY_CHECKS=0;\n\n”;
    on line 25, and
    $return .= “SET FOREIGN_KEY_CHECKS=1;\n”;
    on line 53.

    So the script manages to update the database, without causing the #1217 – “Cannot delete or update a parent row: a foreign key constraint fails” error.

    Regards,
    Aleksandar

  79. Excellent article!

    I must say this was a lot easier than I thought that it would be.

    I am curious, how would I prompt the user to save the file locally?

    Keep on truckin’!

    64Pacific

  80. Thanks Davids…
    It really help me to create a daily backup on my dbase server…

  81. Zoran

    This is very useful, thanks for the time you took to create this function.

  82. Dejan

    Great piece of code, although the UTF-8 characters stored in db are converted into question marks.
    Anyone with a solution on this?

  83. Zoran

    @Dejan
    Try and store your text fields with utf_general_ci encoding when creating tables, it works for me for Macedonian characters.

  84. Thanks for the script…. It saved my time….

  85. Ilshat

    Thank you Dave, you’ve saved my fat a*se!

  86. Very helpful to add database backup functionality..
    I got restore functionality
    http://dan.cx/blog/2006/12/restore-mysql-dump-using-php/

  87. Alvaro

    Hi,

    What about innodb tables? How do you back them up?

    Thanks

  88. Yavor Marinov

    Hi, great work!

    I have a table named: ‘pears-1_00-1_49’.
    The script bugs because of the special characters in name of the table.
    So I fixed it by using sprintf() for the query on three places:

    sprintf("SELECT * FROM `%s`;", $table);
    
    sprintf("SHOW CREATE TABLE `%s`", $table);
    
    sprintf("INSERT INTO `%s` VALUES(", $table);
    

    It works for me!

    Thanks to all!

  89. sheetal

    Hello David ,

    Nice script.I tested it.
    Now it is creating sql files backend in that folder.We have to go to that folder to check that it is there or not.
    I want option to download that sql file on click of button.How to do that?

    One more thing I don’t understand how u use $return.= “\n\n”.$row2[1].”;\n\n”; to get attributes of field.Can u explain me it little bit?

    Thanks & Regards,
    Sheetal

  90. Erick Alcala

    Hey David,

    great info you are providing. I am trying to do a php component for joomla with your code, but I need to specify a suffix on the name for it to only backup the ones that have that suffix.

    I want to backup tables that start with
    jos_vm

    Do you have any idea how can I do this?. or anyone? =).. thanks

  91. Tejas Sali

    The script runs without any problems locally. But when I try to do the same on the web server, I get warnings on

    16 while($row = mysql_fetch_row($result))
    
    30 $num_fields = mysql_num_fields($result)
    
    38   while($row = mysql_fetch_row($result)
    
    
    Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource
    
    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource
    

    And the back up file is created with just the ‘drop statement’ sometimes or an empty file is returned.

    Any clue?

  92. shafi Ahamed

    Hey,

    Thanks man.your code working perfectly

    Regards,
    Shafi.SD

  93. very useful stuff, thanks for sharing!

  94. Hi David,

    You did well, Its working fine but the sql file have
    DROP TABLE ‘table name’
    if I was drop the table after import your extract database files its throw error
    I was correction that as follows

    DROP TABLE IF EXISTS ‘table name’
    otherwise excellent

    Thank You
    Thulasidharan.PT

  95. sudeep

    @primeminister: i tried the code (mysqldump) but nothing happened, can u suggest anything?

  96. Hey!

    I am a website developer and I make MLM websites which has so much importance of database.

    This is very useful code. Yesterday my client’s website database corrupted somehow and I wasn’t having backup. So If I would have applied this code and given him the module to backup the database himself then I would not be in mess in which I am right now.I had talked to the server support and they have told me to gave me backup file but it will also take time so it is bit unwanted situation.I am in it though I haven’t done anything wrong but one thing is I also haven’t done the right thing.

    So from now on I’ll give this functionality to backup to all my new clients and also the existing ones.

    I have also modified the code which will give you file to save when you run it. This may be useful to others who has been in situation that I am right now.

    backup_tables('hostname','username','password','database_name');
    
    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {
    	global $file;
    	
    	$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 if exists '.$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.= '""'; }
    
    • Fatso84

      Hie, the code is giving me syntax error on window.close();

  97. Kenny

    How to prompt to save the file instead into server?

  98. allos

    … the problem is that when an entry is NULL i get at the backup ”
    Is there a way to get the word NULL instead of ” ??

  99. megamanx

    Hi David..

    couldnt thank you enough..so heres an infinite loop for you..cheers!!

    while(0<1) {
         echo "Thank you David";
    }
  100. allos

    anynone know a solution for my problem ?

  101. megamanx

    @allos

    follow this if you want NULL to appear in your field instead of ” when the data field is set to NULL

    if (isset($row[$j])) {
    if($row[$j] == NULL) {
    $return .= ‘NULL’ ;
    }
    else {
    $return .= ‘”‘.$row[$j].'”‘ ;
    }
    }
    else {
    $return.= ‘””‘;
    }

    but also remember that if the particular column attribute of your table is set to NOT NULL, then your import function will fail coz it will try to insert a NULL. so make sure that the column allows to insert NULL, in other words it should not have the NOT NULL attribute set when you are creating the table..it should rather have DEFAULT NULL attribute set.

  102. Awsome, worked like a charm, thanks

  103. Viren

    Simply great.

    U made my day david.!!!
    Gr8 work. Awsome.

  104. Anton

    To make it working with PHP 5.3+

    replace: $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    with: $row[$j] = preg_replace("#\n#", "\\n", $row[$j]);

    Another thing to mention, the script is not working if you have a table name with a dash in.

    Thanks David for the script!

  105. ahmed zain

    Thank you very very very very very very very very very much .. :)

  106. hi every one,

    When i am tried to save backup file on local drive with below codes. Then file was saved successfully with all the database struction but in the bottom this file also contain my html and php codes. Can any one tell me how i remove these my html page content?

  107. Dear gavshouse,
    When i am tried to save backup file on my local drive with given function by adding in bottom of the given backup function. Then file was saved successfully with all the database struction but in the bottom this file also contain my html and php codes. Can you tell me how i remove these my html page content?

  108. When i am tried to save backup file on my local drive with given function by adding in bottom of the given backup function. Then file was saved successfully with all the database struction but in the bottom this file also contain my html and php codes. Can you tell me how i remove these my html page content?

  109. Blaine

    Hey David,

    I do see a problem with this. If the data is int like an “id” column it would really screw up your tables. You could check if it’s int in PHP is_int() so you don’t put quotes around it.

  110. ole peter smith

    As they say, real me do not do backups…

  111. jose ciacciarelli

    hello i hope you can help me.
    i create a database call ‘operativa’ using wamp and need to backup it and then restore. user root no pass.

    if you could instanciate the script with an example so i copy paste it to eclipse php.

    i dont now so much english so sorry about that.

  112. Thanks David! Works like a Charm!! :)

  113. Paul

    Hi David,

    Just used your script – worked a treat and saved me from a *big* problem.

    Best wishes, Paul

  114. Ruffo

    if you have problems to import with the ” (quotes) in the SHOW TABLES query, you can replace it with `

    $needle = '"';
    $haystack = "`";
    
    //replace " with `
    while(in_array($needle,$row2));
    {
     $row2 = str_replace($needle,$haystack,$row2);
    }
    
  115. vesela houba

    Hi,
    thanks for cool script. My problem was “memory limit exceeded” because of dumping really big DB. I’ve changed few lines to make it append to file after each table, not a whole DB in one time and now it works great. Do You have any similar scipt which would do the “load” function?

    • Yasir Anwar

      Hi Vesela,

      could you please help me in what change you made to dump really big DB. I have the same problem and could anyone also help me to create its log file to see what’s happening behind the scenes.

      Though, It is great script, was looking for years.

      Thanks!

  116. Rodrigo Gregorio

    php dont reinvent whell

    thks

  117. This worked perfectly for me. Thank you very much!

  118. It’s Good@!
    thank you for sharing this script

  119. Charles

    Love the script, works great with single tables. However, when I attempt * the script kind of stops, no file is written and when I echo out the $table it appears to only attempt processing 50% of the table in my DB.

    In PHPAdmin the file comes out at around 4 mg in gzipped uncompressed it’s 25mg. Is there a size of data limitation which can be overcome ?

  120. Jokerius

    Thanks, nice script)

    Now it’s good to see some easy script to restore the data

    Just changed

    $handle = fopen('path/to/file/db-backup-'.date("Y-m-d-His").'-'.rand(0,999).'.sql','w+');
    
  121. Jokerius

    Created easy script to load dump.

    At first modified original script so the create table statement fit it 1 line

    $row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
    $row2 = str_replace(“\n”,””,$row2);

    function load_dump($host,$user,$pass,$name,$filename){
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    $lines = file($filename);
    foreach($lines as $line){
    if(strlen($line)>1){ // to avoid \n lines
    mysql_query($line);
    }
    }

    Is it OK? On 6 Mb table was working ok.

  122. Charles

    Hi Andrew,

    Could you help?

    Saw you emailing script, gave it a try, got it to work, only the email sending part however I was not able to get the attachment element to function because when adding extract below as in your script the page crashes. If I rem it out the pages runs ok but with the backup file being attached and re-code to include your script in the backup script and not calling it as a function.

    print("fwrite($handle,$return);
    sendBackup($filename, $name);
    fclose($handle);");
  123. Hi David,

    you ROCK! This script is exactly what I was looking for. I’ve used it in my latest WordPress Plugin called Snapshot Backup: it reads out the database, then TAR’s the entire installation and FTP’s it to an off-site location.

    I gave you a credit in the admin menu – hope that’s OK?

    All the best from London—

    JAY

  124. henri

    thank you david.

    henri

    indonesia

  125. it’s great!!!!!!!!!!!!1

  126. neno

    Hi
    I need help with this line
    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*')

    I want to backup specific table but through the form where i can choose table to backup.
    Example:

    ALL
    TABLE 1
    TABLE 2

    // php code for db backup
    $table_tobackup=$_GET['table'];

    backup_tables('localhost','root','****','katalog');
    function backup_tables($host,$user,$pass,$name,$tables = '$table_tobackup')
    .
    .
    .
    etc

    I’m getting this error: Table ‘katalog.$table_tobackup’ doesn’t exist
    Any idea how to load that variable $table_tobackup into $tables.
    Thanks

  127. Thank you, this script worked for me.

  128. Jamie

    Well, i noticed this script is a bit outdated. Please post a more up-to-date one, oh! Lets hope i don’t have to debug it again. :)

  129. AM

    There is a small nonsense in the script.

        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++) 
            {
              ...
            }
            $return.= ");\n";
          }
        }
    

    the outmost for here is pointless. It actually works because it iterates doing something only once, in following iterations mysql_fetch_row always returns false.
    But this for is really confusing, it took me a while to find out why it is in the script (or why the script is actually working correct).
    I think it should be removed from the script here.

  130. Nick Gelashvili

    Who has this error:

    Notice: Undefined variable: return in *BLABLABLA* on line *BLA*

    have to change this:

    $return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;

    to:

    @($return.= “DROP TABLE IF EXISTS “.$table.”;”);

    • It gives error while replacing the line with @($return.= “DROP TABLE IF EXISTS “.$table.”;”);

  131. dip
    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";
          }
        }<
    

    please remove first for loop from the script, its do nothing but just creating confusions to other

  132. Mack

    I have a question regarding the above script

    What is the use of this loop

    for ($i = 0; $i < $num_fields; $i++)

    i had made some editing in code and checked that this loop will work only 1st time and from next time it's inner part doesn't execute, so anyone can explain me its use !

  133. Looks great script.
    Will modify it a little so I can store it in another mysql database instead of in a output file.

  134. Mikko

    Hi!

    Is there any easy script to return backup?
    This script is fantastic and creates file, but I need script to run it.

    Thanks

  135. yil

    I got an error like this? Any idea

    Warning: fopen(db-backup-1306427001-fe4047d04bbd7e3e2ca225a30121879c.sql) [function.fopen]: failed to open stream: Permission denied in /XXXX/mysqlyedek.php on line 56

    Warning: fwrite() expects parameter 1 to be resource, boolean given in /XXXX/mysqlyedek.php on line 57

    Warning: fclose() expects parameter 1 to be resource, boolean given in /XXXX/mysqlyedek.php on line 58

  136. Steve

    Great script, David, thanks for sharing.

  137. Extremely great script.
    I had used it but have problem
    Deprecated: Function ereg_replace() is deprecated in \www\db_backup\tmp\bckup.php on line 44
    For a solution i had updated the code adding additional function

    <?php
    
    backup_tables('localhost','root','','new_painting');
    /* backup the db OR just a table */
    	function clean($str) {
    		if(@isset($str)){
    			$str = @trim($str);
    			if(get_magic_quotes_gpc()) {
    				$str = stripslashes($str);
    			}
    			return mysql_real_escape_string($str);
    		}
    		else{
    			return 'NULL';	
    		}
    	}
    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);
        
        $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]);
              //$row[$j] = preg_replace("\n","\\n",$row[$j]);
    		  $row[$j] = clean($row[$j]);
    		  
              if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
              if ($j
    
  138. Borek Hanzl

    Actually i managed once to delete from table 6000 products by misstake (incomplete query) and even i had backup (week old ) my boss werent really pleased. So i decided to make backup script instead of using phpmyadmin export function. Thank you very much for this, you saved me lots of work :)

  139. Ariel Fernández

    I developed in a few minutes a Visual Basic App that uses mysqldump.

    This script is brilliant though, but it keeps the backup in the server. If you someday need a backup really fast and, for example, your ISP or the server are down, it is more convenient to have the .sql file next to you in your PC.

  140. I’ve improved this routine into something faster by removing unneeded cycles, changing a couple of workarounds into native and faster functions, and adding bzip2 compression and mail attachment of the file, it runs 2.5times faster than what this one does for me.
    This is the code explained:
    http://www.ephestione.it/backup-zip-mysql-database-php-email-attachment/

  141. BMR777

    Thanks for posting this! It’s a lifesaver. I made a heavily modified copy to back up a database with over 2GB of data and 100,000 tables.

    I’ve got two sections of a PHP file, one that determines what the next table will be and one that actually does the backup. By using redirects and backing up one table at a time bouncing between the two sections of code and passing status / table info in get variables I can back up my database without increased server load, something that both mysqldump and phpmyadmin failed to do!

  142. Mariano

    If you add these lines make copy of Stored Procedures too

    //------------ /* Backup Procedure structure*/
      $result = mysql_query('SHOW PROCEDURE STATUS');
      while($row = mysql_fetch_row($result)) { $procedures[] = $row[1]; }
      foreach($procedures as $proc)
      {
        $return.= "DELIMITER $$\n\n";
        $return.= "DROP PROCEDURE IF EXISTS `$name`.`$proc`$$\n";
        $row2 = mysql_fetch_row(mysql_query("SHOW CREATE PROCEDURE `$proc`"));
        $return.= "\n".$row2[2]."$$\n\n";
        $return.= "DELIMITER ;\n\n";
      }
    //------------ /* Backup Procedure structure*/
  143. Danny Martin

    Hi David, Great script that has served me well for a while – but since the upgrade to php 5.3 the ereg_replace() has been deprecated. You probably know this, but thought I’d drop it in as this post still seems to be getting plenty of hits after three years on air! Way to go…

    Changing ereg_replace() to preg_replace() should cut the mustard, just don’t use ereg_replace() to do it!

    Danny

  144. Saša

    Thank you for the great codeĐ

    I add this line in order to save file to sub-folder

    $path='/home/httpd/vhosts/xxxxx.com/httpdocs/rucno/back_up/files/';

    $handle = fopen($path.'/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    if (fwrite($handle, $return) === FALSE) {
    echo "cannot write to file ";
    exit;
    }

    echo "Done! ";

    fclose($handle);

  145. Hey David, That’s a very specific and small code.

    Thanks man !!!

  146. Hans

    Just one thing: when copy the text it might convert > to <
    It took me a while to figure it out.

  147. Hans

    What I ment:
    it might convert
    < to <
    It took me a while to figure it out.

  148. From looking at this code alone, I can see it is very useful.

    I have bookmarked this webpage so I can implement it when I get some free time (hopefully later today).

    Rather than saving it as:

    'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql'

    I prefer to save it as:

    'db-backup-'.date(d-m-y-H-i-s).sql'

    Since I can’t convert timestamps in my head (not that good at maths yet hehe)

    That’s just down to personal preference though :p

    Thanks Again!

  149. Oops, forgot my quotes in the date() function in the above comment. Never mind ^.^

  150. Alejandro Arauz

    This is a good script, however if you manage several databases you may want to get a notification when the backup task fails rather than check this manually. In addition, sending the backup files to a remote server is usually necessary and for this you would need to add another script.
    At work I used to run several scripts for my backups but looking for backup tools I found MySQLBackupFTP (http://mysqlbackupftp.com/), the only disadvantage with the free version is that it only allows you to schedule 2 databases but other than that it is very helpful. It can ftp your backups, compress the files, send notifications and you can even connect to a PhpMyAdmin instance.

  151. mrlonganh

    Hi !
    if i have 200 tables , and each table have 1000 records .
    error : max_execution_time

    how i will fix this error ?
    pls , help me .

  152. any one have completed backup code now in running form , i am unable to collect all piceses and run fine,

    please complete script

    thanks in advance

  153. Thnx it works great!!!!!!!

  154. Jerry Smith

    There are just too many mods and errors mentioned, and corrections, and with this page changing all the quotes and apostrophies(sp?) to other codes, I give up, going elsewhere… :)
    AND you cant read the code in those darn BLACK BOXES… damn!

  155. Kalpesh Lodha

    Thanx it works fine………

  156. Hi! Thanks! I am using it and it works great.
    I have wrapped this code with a nice PHP config file, and published it on my website as open source
    http://softov.org/webdevtools
    Your credits are there of course. But what is the licence for this peace of code? GPL? GPLv3?

    Thanks
    Ori a

  157. Many thanks for this great little script!

  158. Straw Hat

    how to make the download option to download the backup file appear when the php script has run? please help me

  159. Straw Hat

    please anyone help me…. how to make download option for downloading the backup file appear after the php script run?

  160. If you use the wrapper I wrote for this script you’ll need to put a www-visible folder in $backupmysql[‘folder’]
    and select any file name you need and put it in $backupmysql[‘filename’]
    A direct link for this wrapper (might not work in far future)
    http://softov.org/webdevtools/?a=backup-mysql.php

  161. Straw Hat

    thank you so muchhh!! :DD…

  162. Pulkit Gupta

    thanks a lot :)

  163. Flor

    Thank you! You saved me some time!

  164. Paul Riley

    Hi – great work – works like a charm although I am having trouble with larger db’s…?

    I have one that is showing at 300MB – is there an easy fix to get this working?

    Cheers

  165. Paul Riley

    Using your blog Dave, I found a solution…

    I set the script its own limits and presto!

  166. Thank you for the nice and simple code. But does this also support large mysql databases???

  167. Nice quick little script David, While there is a bunch of ways of addressing this I like to have as much control as possible and this is a good base. I have ammended/hacked it into line with my intentions, would advise anyone with bigger db’s to use this script, but put limited dumping in, e.g. when $return hits x number of characters dump to the file. This is easy to do and will be useful two fold, low memory environments won’t fall over and for higher efficiency.

    Anyhow cheers David.

  168. Also….I had to replace “” with NULL on the insert of blank fields, by way of how I setup things.

  169. Hi David!

    Congrats for the great script.
    Most probably you can see the trackback of this article in one of mines. I just added it.
    I hope you don’t mind that I used some parts of your script to make a PHP helper. If you want me to remove it, please let me know.

    Basically it’s a PHP file that anyone can upload to their website, access it, fill the form with the database connection credentials and hit the backup button. At this moment only a full database backup is available.

    If anyone is interested, it can be download from here: http://mihaifrentiu.com/mysql-backup-php-helper

    Thank you again for sharing this script.

  170. Hi PHEARIN,

    If you are talking about the helper I mentioned above, then most probably you didn’t unpack the archive.

  171. Great script. Here’s a few mods to get it working on my local apache setup and bring it up to date a bit and make PhpMyAdmin happier importing it:

    (replacing depreciated ereg_replace)
    change:

    $row[$j] = ereg_replace("\n","\\n",$row[$j]);

    to:

    $row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);

    (easier importing to PhpMyAdmin)
    remove:

    $return.= 'DROP TABLE '.$table.';';

    (zipity zip it and simplify date format)
    replace:

    $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);

    with:

    $gzdata = gzencode($return, 9);
    $handle = fopen('db-'.date("ymd").'.sql.gz','w+');
    fwrite($handle, $gzdata);
    fclose($handle);
  172. Yuri

    Very usefull script!! thank you!!
    Here is a modified script that backup your databaseS. That would backup all of your databases, if you have more than one! It also creates directory like “backup_2012-02-23” (so that you can see them in alphabetic/time order) in which every databases is saved in its own .sql file like “dbname.sql”!

    http://pastebin.com/NshY4qA2

    This is for a daily backup!

  173. Arvin Yorro

    For those who are having trouble with binary or blob (images and other non-text data).

    Covert the binary to hex using

    $binary_data = hex2bin($binary_data);

    Then append “_binary ox” before inserting it to the query (without the double quotes!)

    $return.= '_binary 0x'.$row[$j] . ',' ;

    goo.gl/z4DCq

    To David: “goo.gl/z4DCq” This is a legit URL. You may want to update your site.

    • Kwaku

      How do I convert to binary in the.
      Cos I don’t know where to put the variable binary_dat

  174. sagar

    It could be a security hole. because any one can download that database backup file.
    is int it ? if not i need some explanation please!

  175. Shivoharsh

    Hello all,

    This script is not working for the table with plenty of rows eg. my table have 74000+ rows….. any suggestions….

  176. Kathy Newbie

    Sorry for a probably stupid question.
    I made a backup .sql file with the script.
    Now let’s say my original database was totally wiped out, doesn’t exist anymore.
    How do I restore it from this backup??
    If I create a new totally empty database and try to import this file it gives error messages because it seems to expect that all the tables already exist in the DB and it can just enter the data. But of course they don’t since the DB was wiped out.
    Is there a command to tell restore all tables and fields from the backup file?
    Kathy

  177. nivahada

    can u please tell me how to save it to local drive

  178. korean

    Good! Thanks david~!

  179. Mauro

    You should use DROP TABLE IF EXISTS. Will work fine when the table exists and when it doesn’t!

  180. Ross Martin

    Great script, thanks a bunch.

    For those that have tables with dashes/hyphens in their name (ex. Customer-Payment), all you need to do is add back ticks (“) around the table name in the MySQL code to get the script to work 100%.

    So instead of

    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    

    You would need this below instead if you have dashes/hyphens in your databases table names. It won’t affect results if you don’t have dashes either.

    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));
    

    And you will need to replace the other lines in the script using the above technique with the back ticks (“) –

     //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]);
    		  //$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
    		  $row[$j] = preg_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);
    
  181. Hi!

    Is there any easy script to return backup?
    This script is fantastic and creates file, but I need script to run it.

    Thanks

  182. How about using mysql_real_escape_string instead of addslashes ?

  183. Hi David,

    Is this applicable to huge database? Say, like 8gb?

    Thanks,

  184. Yariv de Botton

    Hello,
    I suggest using
    mysql_query("SHOW FULL TABLES WHERE `Table_type` = 'BASE TABLE';");

    And another go with
    mysql_query("SHOW FULL TABLES WHERE `Table_type` = 'VIEW';");
    And "SHOW CREATE VIEW"; (without selecting data from the views);

  185. Hi,

    Is this applicable in large database like more than 1gb?

    Thanks,

  186. Kenneth Purtell

    Great stuff David. You got me out of a tight spot. Thanks.

  187. blackhoot

    you’re missing a bracket, and some of this doesn’t make sense. Ah, shame… if only I could just use shell_exec()… blah.

  188. Great time saver! Will definitely set this up as a cron and sleep easier at night!

  189. Great script, thanks for creating/posting this!

  190. Thanks David

    Your script is great.
    I just tested to transform your script to PDO:
    It works, however SHOW CREATE TABLE seems not to accept a PDO-Parameter for TABLE

    Thank you
    Marcus

  191. This code is very much helpful for developers and users as wel. It helps me a lot to get backups of my mysql databases. Thanks a lot.

  192. Thanks a lot, Great Article.

  193. Sharing my improvements:
    (sorry – my linefeeds got smashed-up, I am trying to improve)
    I programmed a small extension to handle numeric values explicitly.
    This leads to:
    – better standard conformity (no quotes for numeric values)
    – less file space required (removing quotes)
    – speedup (skip escaping for numeric values)

    HowTo:
    1. Create an array with all numeric types.
    2. Get attribute-types for actual table, strip redundant information (in brackets).
    3. Setup an array $numflag indicating numeric datatype for each attribute
    4. Assign numeric values without quotes.

    CODE Snippets:

    
    //1..at the top of function
    $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real');
    
    //2.. at the top of table-loop
    $typesarr=mysql_fetch_array(mysql_query('SHOW COLUMNS FROM '.$table));
    
    //3.. below 2
    for($i=0; $i<$num_fields; $i++) {
    	$acttype=trim(preg_replace('/\s*\([^)]*\)/', '', $typesarr[$i]['Type']));// strip brackets from type
    	if (is_numeric(array_search($acttype,$numtypes))){//numeric type false cannot be checked correctly
    	$numflag[$i]=1;}else{$numflag[$i]=0;}
    }
    
    //4.. in the fields loop (innermost)
    if ($numflag[$j]==1){$return.= $row[$j] ; } else{
    	$row[$j] = addslashes($row[$j]);
    	$row[$j]  = preg_replace("/\r\n/","\\r\\n",$row[$j]);
    	$return.= '"'.$row[$j].'"' ; 
    }
    

    Thanks to David and all other Contributors
    Marcus

  194. Joshua

    Hi,
    i’m just afraid of this line,

    $return .= ‘DROP TABLE ‘.$table.’;’;

    what if i gets problem with the code after dropping a table. . .

  195. I was having trouble with the whole NULL, NOT NULL situation and here is my solution:

                            for($j=0; $j<$num_fields; $j++) {
    							
    							if(is_null($row[$j])) { $return .= 'NULL' ;} else {
    								$row[$j] = addslashes($row[$j]);
    								$row[$j] = preg_replace("/\n/","\\n",$row[$j]);
    								if(isset($row[$j])) {$return .= '"'.$row[$j].'"' ;}else {$return .= '""';}
    							}
    
                                if ($j<($num_fields-1)) { $return.= ','; }
                            }
    
  196. Also if you want to encode UTF-8 characters properly instead of the question marks add this line:


    mysql_set_charset('utf8',$link);

    Right after:


    $link = mysql_connect($host,$user,$pass,true);

    So it becomes:


    $link = mysql_connect($host,$user,$pass,true);
    mysql_set_charset('utf8',$link);

  197. Hi, thanks for the script with little modifications it works perfect for me :) Perfect stylized and soft :)

  198. Gopi

    Can’t we use MySqlAdmin to export the database to our system from the website

  199. Thomas Yau

    Thanks for you code, it’s really make me headache since there is no system() command can be used in Synology NAS. Your code is work perfectly for me.

    GREAT JOB

  200. Great script, I’ve found it quite handy when I don’t have access to the DB via SSH or phpMyAdmin. One issue though, when exporting large databases (in my case, it was 98 MB of data), it gives a “memory exhausted” error.

    It can be fixed by simply creating the file handle before the table loop, and writing to it for every new string, instead of writing the whole thing at the end. This kept the script from building up a 98 MB string in memory. Also, as several other people have mentioned, I needed “IF EXISTS” on the DROP statement because I was importing it into an empty schema.

  201. thank bro, its work fine on my project..

  202. Thank you for great script. A timesaver!
    David my question is when did you update the code last time?
    There exists some comments that gives tips about refactoring lines.
    Did you have opportunity to check and edit code.
    Keep up the good work!

  203. Archana

    Hi!

    Is there any script for postgresql database backup & restore?
    what is query in postgresql for “Show create table” like mysql?
    Please help me.

    Thanks

  204. hossein

    i just confused why no body answer the errors,
    for tese errors
    Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in C:\alpstanda\xampp\htdocs\training\Eswari\backup.php on line 33

    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\alpstanda\xampp\htdocs\training\Eswari\backup.php on line 37

    just add
    if (empty($result)) continue;
    after $result in foreach and for this error

    Warning: fopen(jegga/dbbackup_19.08.2011_06:03:22_.sql) [function.fopen]: failed to open stream: Invalid argument in C:\alpstanda\xampp\htdocs\training\Eswari\backup.php on line 63
    just create the jegga folder becuse fopen can create files but not folders

    • David

      HOSSEIN, you have some warning messages. First check and double-check the lines that give warnings – backup.php line 33, 37 and 63. If you still want help, please post the lines you get warnings for.

      You seem to have colons “:” in the file name. I think colons are forbidden, try replacing them with dots “.” or dashes “-“.

  205. mcmc

    sir how can i automaticaly delete the previous backup?

    • David

      MCMC, change the line near the end of the function

      $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

      to

      $handle = fopen('db-backup.sql','w+');

      and try

      unlink('db-backup.sql');
  206. George

    Do you have any idea why it produces CREATE TABLE `cards` instead of CREATE TABLE 'cards' ?

    Also I get Greek letters as questionmarks. Instead of ατομικός I get ????????.

    I tried text-mode translation flag (‘t’), and ‘b’ to force binary mode in fopen with same results.

    Is this working only in English?

  207. Thank you so much. I use it with a little change. it works great :X

  208. mahdi J

    it’s very small and very good. best backup php script
    thanks

  209. Omarz

    im a php noob. can somebody tell me how can i work this code like trigger a button in a php page? thx

  210. Quite old post yet still useful. Works like a charm however the code has a bug. It doesn’t close the mysql connection. In my case, I used this code in a Joomla site, backup went fine however Joomla failed to execute any queries from thereon. After closing the connection, it started working fine. Code below.


    mysql_close($link);

    Cheers!

  211. Nathan

    Thank you for this. I altered the drop statement to be: drop if exists so I could drop the backup into an empty database for debugging.

    Great code, great work. It works great, even with HTML Text in my exports. Just as good as the PHPMyAdmin export feature, but now I can automate it!

    Thank you again.

  212. TWITP MARTIN

    Dear David thank you for great script.
    Manually I made this modifications:
    * Added
    $return = '' ;
    before
    $return.= ‘DROP TABLE ‘.$table.’;’;
    * Changed this $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    to this: $row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
    * Added this to last line:
    mysql_close($link);
    * Also there exists comments about the tables that have dashes inside its name.

    Do you plan to edit the original script?
    Regards

  213. joelifer

    what if I only want to backup a specific table or tables from the database and not the whole database?

  214. $return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;

    IF EXISTS would be a great addition, otherwise will generate error when importing the sql.

  215. Good article, I can also suggest to use XCloner ‘s great tool for MySQL / Full site backup management.

  216. Roger

    Can you please help me with an issue. I am having a table with around 1.7GB data. I used your function(with out the DROP functionality), but as the size is large its creating error. I have access to phpmyadmin and FTP of the hosting server. So where should I modify to extract this huge amount of data?

  217. Abdelaziz

    Great script, got me started, and I needed as well the utf8 encoding.

    I also disabled foreign key constraint checks, and wrapped the sql dump into a transaction, by adding before the main foreach loop:

    $return = 'SET FOREIGN_KEY_CHECKS=0;' . "\r\n";
    $return.= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' . "\r\n";
    $return.= 'SET AUTOCOMMIT=0;' . "\r\n";
    $return.= 'START TRANSACTION;' . "\r\n";

    And after the same loop:

    $return .= 'SET FOREIGN_KEY_CHECKS=1;' . "\r\n";
    $return.= 'COMMIT;';
  218. After I initially commented I appear to have clicked on the -Notify me when new comments are added- checkbox and now each time a comment is added
    I recieve four emails with the same comment. Perhaps there is a way
    you are able to remove me from that service? Thanks a
    lot!http://www.ign.com/boards/threads/partners-season-1-episode-5-online-2-broke-guys.
    452738729/

  219. You could definitely see your expertise in the article you write.
    The sector hopes for more passionate writers like you who aren’t afraid to mention how they believe. Always follow your heart.http://www.ign.com/boards/threads/revolution-season-1-episode-7-online-the-childrens-crusade.452738745/

  220. Thanks a lot for this script! It helps me a lot.

    Danny

  221. thanks a lot for the script!
    This will really help with a personal project I’m working on where regular DB backups are a must
    :D

  222. thanks for sharing this function. i am appreciated :)

  223. Hi, I wish for to subscribe for this webpage to take
    latest updates, therefore where can i do it please help.

  224. A highly configurable version of this tool at
    http://tablefield.com/backup-mysql

  225. David, you are a life saver, thank you very much for this script!

    I have taken most people’s suggestions into consideration (still using mysql rather than mysqli) and below you can find the complete script:

    <?php
    // Specify the values below to make the script work.
    backup_tables('localhost','username','password','db_name');
    
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {
      $link = mysql_connect($host,$user,$pass);
      // Check via PhpMyAdmin to see what character your database is using
      mysql_query('SET NAMES utf8');
      mysql_query('SET CHARACTER SET utf8');
      mysql_select_db($name,$link);
      $return = '' ;
      
      //get all of the tables
      if($tables == '*')
      {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
        {
          $tables[] = $row[0];
    	  //if (empty($result)) continue;
        }
      }
      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 IF EXISTS '.$table.';';
        
    	// If you are getting errors related to fetching, uncomment the next row:
    	//if (empty($result)) continue;
    	
    	$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";
        
    	// If you are getting errors related to fetching, uncomment the next row:
    	//if (empty($result)) continue;
    	
        for ($i = 0; $i < $num_fields; $i++) 
        {
          while($row = mysql_fetch_row($result))
          {
            $return.= 'INSERT INTO '.$table.' VALUES(';
            // If you are getting errors related to fetching, uncomment the next row:
    		//if (empty($result)) continue;
    		$columns = count($row);
    		for($j=0; $j<$columns; $j++)
            {
              $row[$j] = addslashes($row[$j]);
              //$row[$j] = ereg_replace("\n","\\n",$row[$j]);
    		  $row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
              if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
              if ($j
    

    This works fine with my web hosting provider.

    Suggestions for improvement:
    1. Can anyone rewrite the script with using mysqli (not just a row or two but post the whole thing)? This is because future PHP versions may end up not supporting the older mysql command.

    2. This is related to OpenCart CMS only: Three tables from OpenCart was not possible to export (fetching errors). This was due to the tables’ name being ‘option’, ‘order’ and ‘return’. MySQL language is viewing that as boolean arguments. The only solution was to rename those tables (e.g. via PHPMyAdmin) to for example option_admin etc. and then re-linking those in the website (by searching all web files for: DB_PREFIX . “option` , DB_PREFIX . “option` , DB_PREFIX . “option` and replacing that with DB_PREFIX . “option_admin` , etc.

    Thank you again, Dave and others for the numerous feedbacks!

  226. Hmm, it was cut of cut off. So here is the rest (starting with the line that was cut off):

    if ($j

  227. Hmm, it was cut of cut off. So here is the rest (starting with the line that was cut off):


    if ($j

  228. I have been browsing online more than 2 hours today, yet I
    never found any interesting article like yours. It is pretty
    worth enough for me. Personally, if all web owners and bloggers made good content as you did, the
    internet will be much more useful than ever before.
    |
    I couldn’t resist commenting. Perfectly written!|
    But what can you do other than grin and bare it!

  229. thanx alot for this choco-pie…

  230. Mark Quinn

    Thank you. Thank you Thank you.

    I bow down to you over and over.

    It works. WOOT!

  231. Mark Quinn

    one correction put an if exists in your drop statements!

    but other than that great script!!!!

  232. This design is wicked! You definitely know how to keep
    a reader entertained. Between your wit and your videos, I was
    almost moved to start my own blog (well, almost.
    ..HaHa!) Fantastic job. I really enjoyed what you had to say, and
    more than that, how you presented it. Too cool!

  233. Gerard

    Added some compression to reduce the size of the backups. This works well when importing using phpMyAdmin.

    The code changes form:

        //save file
        $handle = fopen(BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
        fwrite($handle,$return);
        fclose($handle);
    

    To the following where I created a boolean flag to which I pass as a parameter in the function:

    if($compression)
      {  
        $zp = gzopen(BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql.gz', "w9");
        gzwrite($zp, $return);
        gzclose($zp);
      }
      else
      {
        //save file
        $handle = fopen(BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
        fwrite($handle,$return);
        fclose($handle);
      }  
    
  234. Fully updated script using MYSQLI, added compression in the SQL insert statements, added Column names to Insert Statement by Table, added ‘IF EXISTS’ to DROP TABLE lines in case you are moving servers or inserting on a clean slate to remove any possible errors. Added the Compression as supplied by ‘Gerard’ above.

    Does anyone know if you can add a password to the GZip through PHP so it can be safely emailed? I have had bit of a search around but have not been able to find much on this topic, or if you know of an alternative method to GZip that would be great as well if I can add encryption, it’s not one of those areas I’ve delved into much.

    //You should test your script, once confirmed all working, add '@' in front of all 'mysqli' commands to stop any errors being displayed.
    
    //Script Variables
    $compression = true;
    $BACKUP_PATH = "";
    
    //I have this in a file elsewhere, makes it easier to keep consistent across pages.
    $user = "";
    $pass = "";
    $host = "localhost";
    $db   = "";
    $conn = mysqli_connect($host,$user,$pass,$db);
    
    backup_tables($conn);
    
    /* backup the whole db by default ('*') OR a single table ('tableName') */
    function backup_tables($conn,$tables = '*') {
    	//get all of the tables
    	if($tables == '*') {
    		$tables = array();
    		$result = mysqli_query($conn,'SHOW TABLES');
    		while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
    			$tables[] = $row[0];
    		}
    	} else {
    		$tables = is_array($tables) ? $tables : explode(',',$tables);
    	}
    
    	//cycle through data
    	$return = "";
    	foreach($tables as $table) {
    		$result = mysqli_query($conn,'SELECT * FROM '.$table);
    		$num_fields = mysqli_num_fields($result);
    
    		$return.= 'DROP TABLE IF EXISTS '.$table.';';
    		$row2 = mysqli_fetch_row(mysqli_query($conn,'SHOW CREATE TABLE '.$table));
    		$return.= "\n\n".$row2[1].";\n\n";
    
    		$return.= 'INSERT INTO '.$table." (";
    		$cols = mysqli_query($conn,"SHOW COLUMNS FROM company");
    		$count = 0;
    		while ($rows = mysqli_fetch_array($cols, MYSQLI_NUM)) {
    			$return.= $rows[0];
    			$count++;
    			if ($count < mysqli_num_rows($cols)) {
    				$return.= ",";
    			}
    		}
    		$return.= ")".' VALUES';
    		for ($i = 0; $i < $num_fields; $i++) {
    			$count = 0;
    			while($row = mysqli_fetch_row($result)) {
    				$return.= "\n\t(";
    				for($j=0; $j<$num_fields; $j++) {
    					$row[$j] = addslashes($row[$j]);
    					//$row[$j] = preg_replace("\n","\\n",$row[$j]);
    					if (isset($row[$j])) {
    						$return.= '"'.$row[$j].'"' ;
    					} else {
    						$return.= '""';
    					}
    					if ($j<($num_fields-1)) {
    						$return.= ',';
    					}
    				}
    				$count++;
    				if ($count < mysqli_num_rows($result)) {
    					$return.= "),";
    				} else {
    				$return.= ");";
    				}
    			}
    		}
    		$return.="\n\n\n";
    	}
    
    	//save file
    	if ($compression) {
    		$zp = gzopen($BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql.gz', "w9");
    		gzwrite($zp, $return);
    		gzclose($zp);
    	} else {
    		$handle = fopen($BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    		fwrite($handle,$return);
    		fclose($handle);
    	}
    }
    
  235. Thanks David, and Todd Wiggins. I merged David’s original and Todd’s mysqli copy, removing compression and unique db specific info, but keeping mysqli, IF EXISTS, and download option. Also like to start with an include for db connection, but inline $con is still there, just commented out…

    /*
    $user = "yourusername";
    $pass = "yourpassword";
    $host = "localhost";
    $db = "yourdatabase";
    $con = mysqli_connect($host,$user,$pass,$db);
    // let's get the variable $con from our included config file instead... 
    */
    include '../inc/config-db.php';
    
    backup_tables($con);
    
    /* backup the whole db (‘*’) OR specific table(s) (‘tableName’ or 'tableOne, tableTwo')  */
    function backup_tables($con,$tables = 'categories, customers, dispatch, freight, location_rates, notes, quotes, text_boxes, variables')
    {
      
    //  $link = mysql_connect($host,$user,$pass);
    //  mysql_select_db($name,$link);
      
      //get all of the tables
      if($tables == '*')
      {
        $tables = array();
        $result = mysqli_query($con,'SHOW TABLES');
        while($row = mysqli_fetch_row($result, MYSQLI_NUM))
        {
          $tables[] = $row[0];
        }
      }
      else
      {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
      }
      
      //cycle through
      foreach($tables as $table)
      {
        $result = mysqli_query($con,'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);
        
        $return.= 'DROP TABLE IF EXISTS '.$table.';';
        $row2 = mysqli_fetch_row(mysqli_query($con,'SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";
        
        for ($i = 0; $i < $num_fields; $i++) 
        {
          while($row = mysqli_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('rvg-db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
      fwrite($handle,$return);
      fclose($handle);
      
         $filename = 'rvg-db-backup-'.time().'.sql';
         Header("Content-type: application/octet-stream");
         Header("Content-Disposition: attachment; filename=$filename");
         echo $return;
    
    }
    mysqli_close($con);
    
    • damian

      my lifesaver!!!!!!!! tnx tnx tnx!!!!!!!!!

      how about with restore? with upload file-style?

    • damian

      how do you make those save file prompts? the clients want to be able to save the backup file to any directory he wants through those download/save prompts.
      and how to rename the sql file with dates only? not with those different characters..

  236. Piotr

    Hi, I’ve read this code and comments and compiled it for simple web page,
    which I can put on server and use for regular backup, ie by wget.
    I removed unnecesary `for` loop by $i, and added filtering by table prefix.
    I tested it with SMF forum database and it works. Of course there is no VIEW
    etc. support.

    Usage with wget:

    wget --content-disposition --post-data="auth=password" http://server.org/backup_db.php
    
    db_server='localhost';
    $db_bkp->db_user='user';
    $db_bkp->db_pass='pass';
    $db_bkp->db_name='base';
    $db_bkp->db_prefix='prefix_';
    /* cfg --end*/
    
    if( isset( $_POST['auth'] ) && $_POST['auth']==$auth ) {
      $db_bkp->sql();
    } else {
      echo( '
        
        
        
        
        
        
        
        db_server.'-'.$db_bkp->db_user.'-'.$db_bkp->db_name
        .'.sql">
        
        
        ');
    }
    
    
    class backup_tables {
      public $db_server;
      public $db_user;
      public $db_pass;
      public $db_name;
      public $db_prefix;
    
      function sql( $filename=NULL )
      {
        $len = strlen( $this->db_prefix );
    
        $return='-- Backup from `backup_tables` class.'."\n"
          .'-- db server: `'.$this->db_server.'`'."\n"
          .'-- db user: `'.$this->db_user.'`'."\n"
          .'-- db name: `'.$this->db_name.'`'."\n";
        if( $len > 0 ) {
          $return.='-- tables: `'.$this->db_prefix.'*`'."\n";
        }
        $return.='-- Date: '.date( DATE_RFC822 )."\n\n\n";
    
        $link = mysql_connect( $this->db_server, $this->db_user, $this->db_pass );
        mysql_query( "SET NAMES 'utf8'" );
        mysql_select_db( $this->db_name, $link );
    
        /*get all of the tables*/
        $tables = array();
        $result = mysql_query( 'SHOW TABLES' );
        while( $row = mysql_fetch_row( $result ) )
        {
          if( $this->db_prefix == substr( $row[0], 0, $len ) ) {
            $tables[] = $row[0];
          }
        }
    
        /*cycle through*/
        foreach( $tables as $table )
        {
          $result = mysql_query( 'SELECT * FROM '.$table );
          $num_fields = mysql_num_fields( $result );
    
          $return.= 'DROP TABLE IF EXISTS '.$table.';';
          $row2 = mysql_fetch_row( mysql_query( 'SHOW CREATE TABLE '.$table ) );
          $return.= "\n\n".$row2[1].";\n\n";
    
          while( $row = mysql_fetch_row( $result ) )
          {
            $return.= 'INSERT INTO '.$table.' VALUES( ';
            for( $j=0; $j<$num_fields; $j++ )
            {
              $row[$j] = mysql_real_escape_string( $row[$j] );
              if( isset( $row[$j] ) ) {
                $return.= '"'.$row[$j].'"';
              } else { 
                $return.= '""';
              }
              if( $jdb_server.'-'.$this->db_user.'-'.$this->db_name;
          if( $len > 0 ) {
            $filename.='-'.$this->db_prefix;
          }
        }
    
        header( 'Content-Type: application/octet-stream' );
        header( "Pragma: public" );
        header( "Expires: 0" );
        header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
        header( "Content-Type: text/x-sql" );
        header( 'Content-Disposition: attachment; filename="'.$filename.'.sql"' );
    
        echo( $return );
      }
    }
    
    ?>
    
    • Hello Piotr,
      Does it open a prompt window for downloading?

  237. Hi

    Thanks for your valuable code.
    This is working fine.

    Thanks again.

  238. Navaneeth

    Thank you for the valuable code share.

  239. /*
    
        Version with VIEW
    
    */
    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {
        
        $first_table_veriable=$tables;
        
        
            $return=null;
        
    	$link = mysql_connect($host,$user,$pass);
    	mysql_select_db($name,$link);
    	
            
            
            
            // BASE TABLE SAVE
            
    	//get all of the tables
    	if($tables == '*')
    	{
    		$tables = array();
    		$result = mysql_query("SHOW FULL TABLES WHERE Table_type =  'BASE TABLE'");
    		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 IF EXISTS '.$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";
    	}
            
            
            
            // WIEW SAVE
            
            
    	//get all of the tables
        $tables = $first_table_veriable;
            
    	if($tables == '*')
    	{
    		$tables = array();
    		$result = mysql_query("SHOW FULL TABLES WHERE Table_type =  'VIEW'");
    		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);
    
    		$return.= 'DROP TABLE IF EXISTS '.$table.';';
    		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE VIEW '.$table));
    		$return.= "\n\n".$row2[1].";\n\n";
    		
    		$return.="\n\n\n";
    	}
            
    
            
    	//save file
            
            $file_name='db-backup.sql';
            
    	$handle = fopen($file_name,'w+');
    	fwrite($handle,$return);
    	fclose($handle);
            
            return $file_name;
    }
    
  240. ghasem

    i see some error when try to restore backup.

    [Err] 1292 – Incorrect datetime value: ” for column ‘PaymentDate’ at row 1
    [Err] 1366 – Incorrect integer value: ” for column ‘Priority’ at row 1

    i think that the order of the fields is incorrect

    • ghasem

      i find the solution:
      where a filed value is null you should put NULL instead of “”

  241. ghasem

    there was another problem
    this script doesn’t work correctly with unicode text!

  242. Thanks a lot man, i have set it up for my own blog and my server using crone job, though my host already provide me functionality of doing it from server but after setting it in crone job im free…, :)

  243. I have used automysqlbackup and Dropbox. It is very simple to install and configure: http://webdevnote.com/article/create-a-backup-for-your-ubuntu-server-with-automysql-backup-and-dropbox-6.html

  244. Hello,

    There is a problem, can you help?

    If the DEFAULT is NULL !!!
    Sample:
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `user_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `real_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `password` varchar(128) COLLATE utf8_unicode_ci NOT NULL,

    AND
    If the data record is like the following
    Email the field blank !!!
    INSERT INTO user VALUES(‘1′,’Adem’,’Adem’,”,’$2a$12$YQ8O6YBynJPwJXmHdtArp’);

    BACKUP,
    Add to null, If the DEFAULT is NULL !!!
    INSERT INTO user VALUES(‘1′,’Adem’,’Adem’, null,’$2a$12$YQ8O6YBynJPwJXmHdtArp’);

    I’m sorry, my English is bad

    Best regards

  245. sathish

    Thanks for your valuable code.

    This is working.

  246. if foreign constraint fail, just add line

    …..
    $return = ‘SET FOREIGN_KEY_CHECKS=0; ‘;

    //cycle through
    foreach($tables as $table)
    {

    …….

  247. Hello There. I found your blog using msn. This is an extremely well written article.

    I’ll make sure to bookmark it and come back to read more of your useful information. Thanks for the post. I’ll certainly return.

  248. Francesco

    Hi,

    very good script, I’m just wondering how to avoid the “INSERT INTO” statements when I’ve to backup a database with federated tables.

  249. is not backup database of utf-8

  250. Garnish with a mini-candy cane hanging along the rim of glass or prior to pouring the drink
    into glass, crush the candy cane and wet down the glass
    rim. In addition to that, here is something else you wouldn’t think of. Just as in other addictions, conquering the beast takes information, a well-developed plan, support and a whole lot of tenacity.

  251. Hi there!

    I was testing this in a large database ( the one that has more it has 600.000+ records) and it fails…

    any workaround in this script to limit the query count? and maybe add more loops?

    Thanks!

  252. Lan

    //here it is in code display

        require 'login.php';
        $DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password);
        
        
        
        
        //put table names you want backed up in this array.
        //leave empty to do all
        $tables = array();
        
        backup_tables($DBH, $tables);
        
        
        
        function backup_tables($DBH, $tables) {
        
        $DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_TO_STRING );
        
        //Script Variables
        $compression = false;
        $BACKUP_PATH = "";
        $nowtimename = time();
        
        
        //create/open files
        if ($compression) {
        $zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "w9");
        } else {
        $handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+');
        }
        
        
        //array of all database field types which just take numbers 
        $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real');
        
        //get all of the tables
        if(empty($tables)) {
        $pstm1 = $DBH->query('SHOW TABLES');
        while ($row = $pstm1->fetch(PDO::FETCH_NUM)) {
        $tables[] = $row[0];
        }
        } else {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
        }
        
        //cycle through the table(s)
        
        foreach($tables as $table) {
        $result = $DBH->query('SELECT * FROM '.$table);
        $num_fields = $result->columnCount();
        $num_rows = $result->rowCount();
        
        $return="";
        //uncomment below if you want 'DROP TABLE IF EXISTS' displayed
        //$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; 
        
        
        //table structure
        $pstm2 = $DBH->query('SHOW CREATE TABLE '.$table);
        $row2 = $pstm2->fetch(PDO::FETCH_NUM);
        $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
        $return.= "\n\n".$ifnotexists.";\n\n";
        
        
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        
        //insert values
        if ($num_rows){
        $return= 'INSERT INTO `'.$table."` (";
        $pstm3 = $DBH->query('SHOW COLUMNS FROM '.$table);
        $count = 0;
        $type = array();
        
        while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) {
        
        if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true);
        } else $type[$table][] = $rows[1];
         
        $return.= $rows[0];
        $count++;
        if ($count rowCount())) {
        $return.= ", ";
        }
        }
        
        $return.= ")".' VALUES';
        
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        }
        
        while($row = $result->fetch(PDO::FETCH_NUM)) {
        $return= "\n\t(";
        for($j=0; $j<$num_fields; $j++) {
        $row[$j] = addslashes($row[$j]);
        //$row[$j] = preg_replace("\n","\\n",$row[$j]);
        
        
        if (isset($row[$j])) {
        //if number, take away "". else leave as string
        if (in_array($type[$table][$j], $numtypes)) $return.= $row[$j] ; else $return.= '"'.$row[$j].'"' ;
        } else {
        $return.= '""';
        }
        if ($j<($num_fields-1)) {
        $return.= ',';
        }
        }
        $count++;
        if ($count rowCount())) {
        $return.= "),";
        } else {
        $return.= ");";
        
        }
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        }
        $return="\n\n-- ------------------------------------------------ \n\n";
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        }
        
        
        
        $error1= $pstm2->errorInfo();
        $error2= $pstm3->errorInfo();
        $error3= $result->errorInfo();
        echo $error1[2];
        echo $error2[2];
        echo $error3[2];
        
        if ($compression) {
        gzclose($zp);
        } else {
        fclose($handle);
        }
        }
    
    
  253. Lan

    sorry, here is the final version with bugs ironed out and made a bit better. tested and works perfectly. enjoy.

    require_once 'login.php';
        $DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password);
        
        
        
        //put table names you want backed up in this array.
        //leave empty to do all
     
        
           
        
        function backup_tables($DBH, $BACKUP_PATH, $tables) {
        
        $DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL );
        
        //Script Variables
        $compression = false;
        $nowtimename = time();
        
        
        //create/open files
        if ($compression) {
        $zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "a9");
        } else {
        $handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+');
        }
        
        
        //array of all database field types which just take numbers 
        $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real');
        
        //get all of the tables
        if(empty($tables)) {
        $pstm1 = $DBH->query('SHOW TABLES');
        while ($row = $pstm1->fetch(PDO::FETCH_NUM)) {
        $tables[] = $row[0];
        }
        } else {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
        }
        
        //cycle through the table(s)
        
        foreach($tables as $table) {
        $result = $DBH->query("SELECT * FROM $table");
        $num_fields = $result->columnCount();
        $num_rows = $result->rowCount();
        
        $return="";
        //uncomment below if you want 'DROP TABLE IF EXISTS' displayed
        //$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; 
        
        
        //table structure
        $pstm2 = $DBH->query("SHOW CREATE TABLE $table");
        $row2 = $pstm2->fetch(PDO::FETCH_NUM);
        $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
        $return.= "\n\n".$ifnotexists.";\n\n";
        
        
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        
        //insert values
        if ($num_rows){
        $return= 'INSERT INTO `'."$table"."` (";
        $pstm3 = $DBH->query("SHOW COLUMNS FROM $table");
        $count = 0;
        $type = array();
        
        while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) {
        
        if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true);
        } else $type[$table][] = $rows[1];
         
        $return.= "`".$rows[0]."`";
        $count++;
        if ($count rowCount())) {
        $return.= ", ";
        }
        }
        
        $return.= ")".' VALUES';
        
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        }
        $count =0;
        while($row = $result->fetch(PDO::FETCH_NUM)) {
        $return= "\n\t(";
        
        for($j=0; $jquote($row[$j]); 
        
        } else {
        $return.= 'NULL';
        }
        if ($j<($num_fields-1)) {
        $return.= ',';
        }
        }
        $count++;
        if ($count rowCount())) {
        $return.= "),";
        } else {
        $return.= ");";
        
        }
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        }
        $return="\n\n-- ------------------------------------------------ \n\n";
        if ($compression) {
        gzwrite($zp, $return);
        } else {
        fwrite($handle,$return);
        }
        $return = "";
        }
        
        
        
        $error1= $pstm2->errorInfo();
        $error2= $pstm3->errorInfo();
        $error3= $result->errorInfo();
        echo $error1[2];
        echo $error2[2];
        echo $error3[2];
        
        if ($compression) {
        gzclose($zp);
        } else {
        fclose($handle);
        }
        }
        
    
  254. Thank you for script. It is great for shared hosting.
    But if you have root access you can use mysqldump. If you want best root option you can use Percona xtra backup for free.

  255. Sergio

    Great! thanks a lot. It helped me a lot. May I suggest a compression after?

  256. Alexsandra

    I found this on the net for compressing and adjusted it according to my needs. Hope this helps.

    if (!(substr($file,-4) != ".sql")) {
    $fp = fopen( $file, "r" );
    	$data = fread ( $fp, filesize( $file ) );
    	fclose( $fp );
    		
    	$dstFileName=$file.'.gz';
    	$zp = gzopen( $dstFileName, "w9" );
    	gzwrite( $zp, $data );
    	gzclose( $zp ); 
    				
    				// !!!!!!!!!!!!
    	unlink ($file);
    }
    
  257. Hello,
    What is the reason for this error?

    Parse error: syntax error, unexpected ‘rowCount’ (T_STRING) in D:\EasyPHP\data\localweb\dbyedekle.php on line 86

    Line: if ($count rowCount())) {

  258. Hello,

    Such an option is it possible?
    combine all tables into one backup file: Yes/No

    Regards

  259. Lukas

    Hi , thanks for this , I needed a way to backup my BD on a shared hosting so I couldn´t use MySQLDump .

    I extended your solution to backup the tables AND the triggers for those tables.I´ll share it so hopefully it will come handy for someone :)

    function backupDB($backup_dir) {
    	$con = mysqli_connect ( DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DBNAME );
    	
    	// get all of the tables
    	$tables = array ();
    	$result = mysqli_query ( $con, 'SHOW TABLES' );
    	while ( $row = mysqli_fetch_array ( $result ) ) {
    		$tables [] = $row [0];
    	}
    	
    	// cycle through the tables
    	foreach ( $tables as $table ) {
    		$result = mysqli_query ( $con, "SELECT * FROM `$table`");
    		$num_fields = mysqli_num_fields ( $result );
    		$num_rows = mysqli_num_rows( $result );
    		$return .= "--\n-- Structure for the table $table\n--\n\n";
    		$return .= "DROP TABLE IF EXISTS `$table`;";
    		$row2 = mysqli_fetch_array ( mysqli_query ( $con, "SHOW CREATE TABLE `$table`" ) );
    		$return .= "\n\n" . $row2 [1] . ";\n\n";
    		if ($num_rows > 0) {
    			$return .= "--\n-- Data dump for the table $table\n--\n\n";
    		}
    		$i = 0;
    		while ( $row = mysqli_fetch_array ( $result ) ) {
    			if ($i == 0) {
    				$return .= "INSERT INTO `$table` VALUES\n";
    			}
    			$i++;
    			for($j = 0; $j < $num_fields; $j ++) {
    				if ($j == 0) {
    					$return .= '(';
    				}
    				$row [$j] = addslashes ( $row [$j] );
    				$row [$j] = mysqli_real_escape_string ( $con, $row [$j] );
    				if (isset ( $row [$j] )) {
    					$return .= '"' . $row [$j] . '"';
    				} else {
    					$return .= '""';
    				}
    				if ($j < ($num_fields - 1)) {
    					$return .= ',';
    				}
    			}
    			if ($i  0) {
    			$return .= "--\n-- Triggers for the table `$table`\n--\n\n";
    			// cycle through the triggers of the table
    			foreach ( $triggers as $trigger ) {
    				$result = mysqli_fetch_array ( mysqli_query ( $con, "SHOW CREATE TRIGGER $trigger" ) );
    				$return .= "\nDROP TRIGGER IF EXISTS `$trigger`;\n";
    				$return .= $result [2] . ";\n";
    			}
    			$return .= "\n\n";
    		}	
    	}
    	
    	// save file
    	$handle = fopen ( $backup_dir, 'w+' );
    	fwrite ( $handle, $return );
    	fclose ( $handle );
    }
    
  260. Rajesh M. Kanojia

    Thank’s David it works..

  261. Good blog post. I absolutely love this website.

    Continue the good work!

  262. ereg_replac is now deprecated how do I overcome this error for this line of script from the above code.

    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    
  263. It may not work anymore. Default package is php 5.5.5 , mysql_* functions are removed.

  264. Thank you for posting the script its very handy!

    what would be useful is if the script in the post was updated with the bug fixes suggested in the comments

    also putting an updated on X would help us know its been updated,

    thanks again for the helpful script

  265. Hi there ,

    All some script :) .. I don’t know much and the coding but my hosting has stopped the mysqldump way of backup .. I used this to email ..

    Could someone recode for me .. so it will be able to send the back up files to a email address ??

    Thanks

  266. Great script. Today I discovered the hard way that my ISP runs only weekly backups :-(. In few minutes I had my own backup running daily.

  267. WOW just what I was looking for. Came here by searching for membatalkannya

  268. @dave the script work perfectly, can’t it be possible to save to external device instead of saving to the server. anybody with nice suggestion?

  269. Singgih Pranowo

    100% Work!

    very good.. this is what I searching for.

    thankyou, David :)

  270. toma

    The script does correctly export the file with UTF-8 data? it seems that no..

  271. taz

    Here is my solution:

    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = '';
    $dbname = 'a_wp_test';
    
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {
    	
    	$link = mysql_connect($host,$user,$pass);
    	mysql_select_db($name,$link);
    	mysql_query("SET NAMES 'utf8'");
    	
    	//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);
    	}
    	$return='';
    	//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] = str_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);
    }
    
    backup_tables($dbhost,$dbuser,$dbpass,$dbname);
    
    • eren

      hello sir!!! your script really saved me!! i’ve been searching for backup and restore scripts for weeks… and yours worked flawlessly.. i’ve made some modifications though, to make it work with mysqli..

      can you help me with the restore script sir? our panelists insisted on a file upload-style restore database and tried various scripts i found on the internet, but so far not successful.

  272. Alberto

    Thanks for the script! this works fine. First of all, is better backup also the structure of db (tables, field, and type and size for each field)

  273. noors

    when i run this code i received an error on these lines

    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result));
  274. kazi Arif

    Thanks Good script!!!

    But it generate an error when i run the srcipt with large database which contain may be 2 lac data or more.this error is shown

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 133693567 bytes) in C:\wamp\www\Backup\index.php on line 62

    what can i do…????

  275. Iqlas Uddin

    Thanks so much buddy.. you saved my day

  276. Huong

    The method above but very dark indeed. I have a problem when my database size greater than 800M An error notification:
      Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 133 693 567 bytes)
    I can not use mysqldump for database through ADOdb connected to a different server.
    I have a solution using phpMyadmin but also not be too large because space.
    Who artillery appropriate solution please just help!

  277. Hello,

    How do I change replace? "CREATE TABLE" ===> "CREATE TABLE IF NOT EXISTS"

    query('SHOW CREATE TABLE '.$table)->fetch_row();
    $return .= "\n".$row2[1].";\n";
    $return .= "\n--\n" ;
    $return .= '-- Tablonun veri dökümü `' . $table . '`' . "\n" ;
    $return .= "--\n\n" ;
    ?>
    
  278. Hello to everyone,

    How do I use this? “ORDER BY TABLE_NAME ASC”

    Thanks

  279. Thom

    Nice script but I’m having some problems with it…
    I’ve got a database with 11 tables, the backup-script only saves the last of the tables to the backup-file.

    Why is this?

  280. There is an error on line 45

    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    

    The ereg_replace is deprecated in PHP5.4

    Just fyi.. fixed that and the code works great! Many Thanks to you for the nice work!

  281. Here is the MYSQLI solution:

    $dbName = 'testdb';
    
    $link = mysqli_connect('localhost', 'root', 'root', $dbName, (int)'3306', null);
    
    if (!$link) {
        echo mysqli_connect_error();
        exit(1);
    }
    
    $query = 'SHOW TABLES';
    $result = mysqli_query($link, $query);
    $tables = array();
    $buffer = '';
    
    while ($row = mysqli_fetch_row($result)) {
        $tables[] = $row[0];
    }
    
    foreach ($tables as $table) {
    
        $howManyColumns = "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$dbName' AND table_name='$table';";
        $executeHowManyColumns = mysqli_query($link, $howManyColumns);
        $numberOfColumns = mysqli_fetch_row($executeHowManyColumns)[0];
    
        $creationQuery = "SHOW CREATE TABLE $table";
        $executeCreationQuery = mysqli_query($link, $creationQuery);
        $createTableQuery = mysqli_fetch_row($executeCreationQuery)[1] . ';';
    
        $buffer .= "\n$createTableQuery\n";
    
        $tableContent = "SELECT * FROM $table";
        $tableContentResult =  mysqli_query($link, $tableContent);
    
        $rows = mysqli_data_seek($tableContentResult, 0);
        if (false !== $rows) {
            $buffer .= "\nINSERT INTO $table VALUES\n";
        }
    
        for ($i = 0; $i < $numberOfColumns; $i++) {
            while ($entry = mysqli_fetch_row($tableContentResult)) {
                $buffer .= '(';
                for ($j = 0; $j  0) {
                        $buffer .= ',';
                    }
                    $buffer .= "'$element'";
                }
                $buffer .= ");\n";
            }
        }
    }
    
    $buffer = trim($buffer) . "\n";
    
    • Sorry, I forgot to write the if statement:

      if ($j > 0) {
                          $buffer .= ',';
                      }
                      $buffer .= "'$element'";
      
  282. Hi all, if you are looking for a more advanced backup scheme, we have just developed web3backup; it’s a PHP script that backs up MySQL, SVN, local files with binary incremental backup, and rotates daily, weekly, monthly and yearly backups. You can get it at:
    http://www.exteon.ro/en/products/programming-tools/web3backup

  283. Keshav Gyawali

    It gives error
    Undefined variable: return in E:\web\xampp\htdocs\libmgmt\scripts\backupdb-Copy.php on line 37
    while running the script

  284. Raj

    This script is unable to add foreign key constraints while importing in mysql.
    Hence is not importing all the tables.

  285. Cody

    Awesome thanks for the script. For anyone having issues importing this sql after its saved. If your using foreign keys relationships you will want to list out the tables in this function:

    backup_tables($host,$user,$pass,$name,$tables = '*')
    

    Instead of using the star, otherwise they will write to the sql file in alphabetical order and when imported tables which rely on other tables will error out. If you list them out like ‘questions, answers’ it will create them in order fixing any missing foreign key errors.

  286. Onkar

    This script may not work if database is relational database.
    For Example:

    database:my_db
    tables:tbl_city(id(pk),name,county_id(fk)),tbl_country(id(pk),name)
    

    I Used Following Script For Backup:

    $mysqldump_path = "E:\wamp\bin\mysql\mysql5.1.53\bin\mysqldump";//
    $user ="root";
    $pwd ="***";
    $db_name = "my_db";
    $sql_file_name = "C:my_db_backup.sql";
    
    $command = $mysqldump_path.' -u'.$user.' -p'.$pwd.' '.$db_name.' > '.$sql_file_name;
    
    exec($command); //command line call
    
  287. To fix the problem of encoding I used this:

    fwrite($handle,utf8_encode($return));
    

    This worked for me , I hope it will be useful for you guys

  288. kevin

    Hello, will this be also applicable if my database has Foreign Key? I encounter an error while restoring it. Thanks :)

  289. I modified this code to created a script that can be run via cron, and can schedule different time for backing up diffrent databases.

    http://anasthecoder.blogspot.ae/2015/10/phpautomateddbbackup.html

  290. Ramon

    hello sir!!! your script really saved me!! i’ve been searching for backup and restore scripts for weeks… and yours worked flawlessly.. i’ve made some modifications though, to make it work with mysqli..

    can you help me with the restore script sir? our panelists insisted on a file upload-style restore database and tried various scripts i found on the internet, but so far not successful.

    http://www.mi-web.org/miembros/92256-tarotist/perfil

  291. karan
    $mysqldump_path = "E:\wamp\bin\mysql\mysql5.1.53\bin\mysqldump";//
    $user ="root";
    $pwd ="***";
    $db_name = "my_db";
    $sql_file_name = "C:my_db_backup.sql";
    
    $command = $mysqldump_path.' -u'.$user.' -p'.$pwd.' '.$db_name.' > '.$sql_file_name;
    
    exec($command); //command line call
    

    What about restore????

  292. Slawern

    Saved my life :)

  293. I made and tried out an up-to-date mysqli version of David scripts, with an HTML form to configure it.

    https://github.com/ninuzzo/mysqlphpdump

  294. JonShipman

    Replace

    $row[$j] = ereg_replace("\n","\\n",$row[$j]);

    with

    $row[$j] = str_replace(array("\n","\r"),array('\\n','\\r'),$row[$j]);

    A) to catch \r returns from Windows. And B) str_replace is slightly faster.

  295. Ian Haney

    Hi

    I am looking for a database backup script that backups multiple databases with different credentials, if possible to do using PDO or mysqli, has anyone got a script or know of a php script that does this please

    Thank you in advance

    Ian

  296. Sunil Kumar

    Thank you for such a nice script. I would suggest if we can change DROP TABLE to DROP TABLE IF EXIST to avoid error while uploading backedup database into blank database.

  297. Sunil Kumar

    Also could you please refresh code with replacing depricated functions like mysql and preg_replace?
    Thanks again

  298. AZ

    Can you show KEY & PRIMARY key Backup code.

  299. deadlly

    replace line 33 with this :

    $return.= 'DROP TABLE IF EXISTS '.$table.';';
    
  300. I know this is an old post, but it was very helpful to me. I did get the ereg_replace deprecated error.

    I replaced this:

    $row[$j] = ereg_replace("\n","\\n",$row[$j]);

    with this:

    $row[$j] = preg_replace("/\n/","\\n",$row[$j]);
  301. It works for me:) thank you very much. I modified end of file to gzip results:

    //save file
    $filename = "db-backup.sql";
    $gzfilename = $filename.".gz";
    $handle = fopen($filename,'w+');
    fwrite($handle,$return);
    fclose($handle);
    
    $fp = gzopen ($gzfilename, 'w9');
    gzwrite ($fp, file_get_contents($filename));
    gzclose($fp);	
    
  302. Ace

    this code works like a charm. More kudos to you david.

  303. Rana

    Why there is a dot with $return.

    Can someone comment the complete and working update form of this code as soon as possible. Thanks so much in advance.

  304. Chris

    Updated for php7

    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = '*'){
    	
    	$link = mysqli_connect($host,$user,$pass,$name);
    	
    	//get all of the tables
    	if($tables == '*'){
    		$tables = array();
    		$result = mysqli_query($link,'SHOW TABLES');
    		while($row = mysqli_fetch_row($result)){
    			$tables[] = $row[0];
    		}
    	}
    	else{
    		$tables = is_array($tables) ? $tables : explode(',',$tables);
    	}
    	
    	//cycle through
    	foreach($tables as $table){
    		$result = mysqli_query($link,'SELECT * FROM '.$table);
    		$num_fields = mysqli_num_fields($result);
    		$return.= 'DROP TABLE '.$table.';';
    		$row2 = mysqli_fetch_row(mysqli_query($link,'SHOW CREATE TABLE '.$table));
    		$return.= "\n\n".$row2[1].";\n\n";
    		
    		for ($i = 0; $i < $num_fields; $i++) {
    			while($row = mysqli_fetch_row($result)){
    				$return.= 'INSERT INTO '.$table.' VALUES(';
    				for($j=0; $j < $num_fields; $j++) {
    					$jfromdb = $row[$j];
    					
    					$jfromdb = addslashes($jfromdb);
    					$jfromdb = preg_replace("/\n/","\\n",$jfromdb);//'/looking for/', 'replace with', $in this text
    					if (isset($jfromdb)) { $return.= '"'.$jfromdb.'"' ; } else { $return.= '""'; }
    					if ($j < ($num_fields-1)) { $return.= ','; }
    				}
    				$return.= ");\n";
    			}
    		}
    		$return.="\n\n\n";
    	}
    	
    	//save file
    	$handle = fopen('dbbackups/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    	fwrite($handle,$return);
    	fclose($handle);
    }
    
  305. tovi

    hi, how can execute the back up code?..is it a javascript to be added in a file or 2 seperate php file to back up

  306. Kwaku

    Having troubles with the blob data… Any help?.
    The blob data doesn’t come in plain text.

  307. Blak Dronzer

    Well.. this seems to be good piece of code but this will fail when the database size is good enough. Under such situation – the code tries to put the data in memory – wont be able to hold it long .. it will fail with out of memory exception.

  308. Here is the code of mysqli supported:

    $db_host = 'localhost';
    $db_user = 'root';
    $db_pass = '';
    $db_name = 'your_database_name';
    
    $file_path = 'backup/'.$db_name.'.sql';
    
    backup_database($db_host, $db_user, $db_pass, $db_name, $file_path);
    
    function backup_database($host, $user, $pass, $name, $file_path)
    {
    	$tables = '*';
    	
    	$mysqli = new mysqli($host,$user,$pass,$name);
    			
    	if ($mysqli->connect_errno)
    	{
    		printf("Connect failed: %s\n", $mysqli->connect_error);					
    	}
    	else
    	{
    		//get all of the tables
    		if($tables == '*')
    		{
    			$tables = array();
    			$result = $mysqli->query('SHOW TABLES', MYSQLI_USE_RESULT);			
    			while($row = $result->fetch_array(MYSQLI_ASSOC))
    			{
    				$key = @key($row); 												
    				$tables[] = $row[$key];
    			}	
    			$result->close();					
    		}
    		else
    		{
    			$tables = is_array($tables) ? $tables : explode(',',$tables);
    		}
    		
    		//cycle through
    		$output = '';
    		foreach($tables as $table)
    		{
    			$insert_sql = '';
    			$result = $mysqli->query('SELECT * FROM '.$table, MYSQLI_USE_RESULT);			
    			$num_fields = $result->field_count;
    			for ($i = 0; $i fetch_row())
    				{					
    					$insert_sql.= 'INSERT INTO '.$table.' VALUES(';
    					for($j=0; $j < $num_fields; $j++) 
    					{
    						$row[$j] = addslashes($row[$j]);
    						$row[$j] = str_replace("\n","\\n",$row[$j]);
    						if (isset($row[$j])) { $insert_sql.= '"'.$row[$j].'"' ; } else { $insert_sql.= '""'; }
    						if ($j close();
    							
    			$output .= 'DROP TABLE IF EXISTS '.$table.';';
    			$result = $mysqli->query('SHOW CREATE TABLE '.$table, MYSQLI_USE_RESULT);			
    			$row = $result->fetch_row();		
    			$result->close();						
    			$output .= "\n\n".$row[1].";\n\n";
    			$output.= $insert_sql;
    			$output.="\n\n\n";				
    		}
    		
    		
    		
    		//save file
    		$handle = fopen($file_path, 'w+');
    		fwrite($handle, $output);
    		fclose($handle);
    		
    	}
    	
    	$mysqli->close();
    }
    
  309. Adem

    Hello,
    What needs to be added to the code to back up all data structures of audit_trail?
    Thank you

  310. Adem

    Hello,
    How to include PDO and triggers in database backup

  311. Hi! Thanks for the great post, very informative.

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!