Back to Top

How to backup MySQL database in PHP

Database BackUp Script

After explaining such assured and flexible MySQL Connection which logs error in Email and SMS you want.I am going to explain how you can take a backup of the database by running a single cron or running a file from the browser in MySQL.

Backup, Backup, Backup!.Backing up a database is exceptionally important.If your server provides access of PHPMyAdmin interface or PHPMyAdmin installed on your server, you can easily take manual backups of database

But if you don’t have time for the manual process, you can use my script listed below.

Below script is just a copy and paste code, you just need to copy and paste into one of the PHP files and change host, username,password and database information match to your database.That’s it.

Your script is ready to use.

$host="localhost"; //server address
$user="username"; //username
$pass="password"; // password
$name="db_name"; //database name

// To connect with mysql
$link = mysql_connect($host,$user,$pass) or die ("Server not found");
// Database selection
mysql_select_db($name) or die("database not found");
$tables = array();
// Query to display all resulting tables from selected database 
$result = mysql_query('SHOW TABLES'); 
while($row = mysql_fetch_row($result))
{
	$tables[] = $row[0];
}
foreach($tables as $table)
{
	$result = mysql_query("SELECT * FROM $table"); 
        // fetch all entries from table
	$num_fields = mysql_num_fields($result);
	$return.= "DROP TABLE IF EXISTS $table;"; 
        // drop old table 
	$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); 
        // create table query  structure
	$return.= "\n\n".$row2[1].";\n\n";		
	for ($i = 0; $i < $num_fields; $i++) 
	{
		while($row = mysql_fetch_row($result)) 
               // create insert query for all entries
		{
		    $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";
}
$zp = gzopen("Backup/Backup".date(d_m_Y).".sql.gz", 'w9');
gzwrite($zp, $return);
gzclose($zp);

Keep in mind: This script will work only for tables If your database contains any other information like views, sequences, store procedures, then above script will not work.

Do you like this article? How would you improve the example? Share your thoughts in the comment below.

Thanks for reading and feel free to share your thoughts! Don't Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to set 301 Redirection in PHP

Posted on 4 years ago

Bhumi

How to use Flickr API with PHP

Posted on 6 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 3 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 4 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 5 years ago

Bhumi