Backup Your MySQL Database Using PHP

Comment: 0 Comment | Hit: 3835 | Date: 20 Jul 2010 02:07 | Category: PHP and MySQL

Backup MySQLOne 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.

 

 

 

 

 

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!

 

Original:

http://davidwalsh.name/backup-mysql-database-php

http://digg.com/submit?phase=2&url=http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&title=Backup Your MySQL Database Using PHPhttp://delicious.com/post?url=http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&title=Backup Your MySQL Database Using PHPhttp://www.facebook.com/sharer.php?u=http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&t=Backup Your MySQL Database Using PHPhttp://twitter.com/home?status=Backup Your MySQL Database Using PHP via @nvitou http://bit.ly/coooHZhttp://www.google.com/bookmarks/mark?op=edit&bkmk=http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&title=Backup Your MySQL Database Using PHPhttp://www.stumbleupon.com/submit?url=http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&title=Backup Your MySQL Database Using PHPhttp://www.myspace.com/Modules/PostTo/Pages/?u=http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&t=Backup Your MySQL Database Using PHPhttp://www.squidoo.com/lensmaster/bookmark?http://www.nvitou.com/en/article/backup-your-mysql-database-using-php/&title=Backup Your MySQL Database Using PHP

Comments

Name:

Email/Website:

Your comment:

nVitou.com in brief

nVitou.com was founded in July 2010 by a Cambodian, NOUV Vithou, located in Phnom Penh. The mission is to exchange knowledge and experiences on code and design.