Backing up MySQL table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Backing up MySQL table

Post by Nay »

How can I back up an entire MySQL table (into a back.sql file)? I don't mean from phpmyadin. I want to be able to back up from the Control Panel that I'm building. I don't know how, not even a theory. Any ideas?

If possible, I also want to be able to use that back up when something is wrong or so (something like resetting). Get what I mean? lol

Thanks,

-Nay
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

have a look at this, this is code that has been taken from phpMyAdmin

Code: Select all

<?
		$target="file";
		if($target=="file")
		{
			header('Content-Type: application/octetstream');
			header('Content-Disposition: filename="backup.sql"');
			$asfile="download";
		}

		
		$crlf="\r\n";

		$link = mysql_connect("localhost", "root", "");
		$database = mysql_select_db("newobs"); 
	
		$dbname = "newobs";
	
		$dump_buffer="";
	
		$tables = mysql_query("show tables from $dbname");
		$num_tables = mysql_num_rows($tables);
	
		if($num_tables == 0)
		{
			echo "# No Tables Found";
			exit;
		}
		
		$dump_buffer.= "# DatabaseBackup $crlf";
		$dump_buffer.= "# Backup made:$crlf";
		$dump_buffer.= "# ".date("F j, Y, g:i a")."$crlf";
		$dump_buffer.= "# Database: $dbname$crlf";
		$dump_buffer.= "# Backed up tables : $dbname $crlf";
	
		$i = 0;
		while($i < $num_tables)
		{
			$table = mysql_tablename($tables, $i);
			//echo $table . "<br>";
				$dump_buffer.= "# --------------------------------------------------------$crlf";
				$dump_buffer.= "$crlf#$crlf";
				$dump_buffer.= "# Table structure for table '$table'$crlf";
				$dump_buffer.= "#$crlf$crlf";
				$db = $table;
				$dump_buffer.= get_table_def($table, $crlf,$dbname).";$crlf";
				$dump_buffer.= "$crlf#$crlf";
				$dump_buffer.= "# Dumping data for table '$table'$crlf";
				$dump_buffer.= "#$crlf$crlf";
				$tmp_buffer="";
				get_table_content($dbname, $table, 0, 0, 'my_handler', $dbname);
				$dump_buffer.=$tmp_buffer;
				
			$i++;
			$dump_buffer.= "$crlf";
		}
		echo $dump_buffer;
		exit;
	
	function get_table_def($table, $crlf,$dbname)
	{
	
		$schema_create = "DROP TABLE IF EXISTS $table;$crlf";
		$db = $table;
	
		$schema_create .= "CREATE TABLE $table ($crlf";
	
		$result = mysql_query("SHOW FIELDS FROM " .$dbname."."
		. $table) or die();
		while($row = mysql_fetch_array($result))
		{
			$schema_create .= "   $row[Field] $row[Type]";
	
			if(isset($row["Default"]) && (!empty($row["Default"]) || $row["Default"] == "0"))
				$schema_create .= " DEFAULT '$row[Default]'";
			if($row["Null"] != "YES")
				$schema_create .= " NOT NULL";
			if($row["Extra"] != "")
				$schema_create .= " $row[Extra]";
			$schema_create .= ",$crlf";
		}
		$schema_create = ereg_replace(",".$crlf."$", "", $schema_create);
		$result = mysql_query("SHOW KEYS FROM " .$dbname."." .
		$table) or die();
		while($row = mysql_fetch_array($result))
		{
			$kname=$row['Key_name'];
			$comment=(isset($row['Comment'])) ? $row['Comment'] : '';
			$sub_part=(isset($row['Sub_part'])) ? $row['Sub_part'] : '';
	
			if(($kname != "PRIMARY") && ($row['Non_unique'] == 0))
				$kname="UNIQUE|$kname";
	
			if($comment=="FULLTEXT")
				$kname="FULLTEXT|$kname";
			 if(!isset($index[$kname]))
				 $index[$kname] = array();
	
			if ($sub_part>1)
			 $index[$kname][] = $row['Column_name'] . "(" . $sub_part . ")";
			else
			 $index[$kname][] = $row['Column_name'];
		}
	
		while(list($x, $columns) = @each($index))
		{
			 $schema_create .= ",$crlf";
			 if($x == "PRIMARY")
				$schema_create .= "   PRIMARY KEY (";
			 elseif (substr($x,0,6) == "UNIQUE")
				$schema_create .= "   UNIQUE " .substr($x,7)." (";
			 elseif (substr($x,0,8) == "FULLTEXT")
				$schema_create .= "   FULLTEXT ".substr($x,9)." (";
			 else
				$schema_create .= "   KEY $x (";
	
			$schema_create .= implode($columns,", ") . ")";
		}
	
		$schema_create .= "$crlf)";
		if(get_magic_quotes_gpc()) {
		  return (stripslashes($schema_create));
		} else {
		  return ($schema_create);
		}
	}
	function get_table_content($db, $table, $limit_from = 0, $limit_to = 0,$handler)
	{
		// Defines the offsets to use
		if ($limit_from > 0) {
			$limit_from--;
		} else {
			$limit_from = 0;
		}
		if ($limit_to > 0 && $limit_from >= 0) {
			$add_query  = " LIMIT $limit_from, $limit_to";
		} else {
			$add_query  = '';
		}
	
		get_table_content_fast($db, $table, $add_query,$handler);
	
	}
	
	function get_table_content_fast($db, $table, $add_query = '',$handler)
	{
		$result = mysql_query('SELECT * FROM ' . $db . '.' . $table . $add_query) or die();
		if ($result != false) {
	
			@set_time_limit(1200); // 20 Minutes
	
			// Checks whether the field is an integer or not
			for ($j = 0; $j < mysql_num_fields($result); $j++) {
				$field_set[$j] = mysql_field_name($result, $j);
				$type          = mysql_field_type($result, $j);
				if ($type == 'tinyint' || $type == 'smallint' || $type == 'mediumint' || $type == 'int' ||
					$type == 'bigint'  ||$type == 'timestamp') {
					$field_num[$j] = true;
				} else {
					$field_num[$j] = false;
				}
			} // end for
	
			// Get the scheme
			if (isset($GLOBALS['showcolumns'])) {
				$fields        = implode(', ', $field_set);
				$schema_insert = "INSERT INTO $table ($fields) VALUES (";
			} else {
				$schema_insert = "INSERT INTO $table VALUES (";
			}
	
			$field_count = mysql_num_fields($result);
	
			$search  = array("\x0a","\x0d","\x1a"); //\x08\\x09, not required
			$replace = array("\\n","\\r","\Z");
	
	
			while ($row = mysql_fetch_row($result)) {
				for ($j = 0; $j < $field_count; $j++) {
					if (!isset($row[$j])) {
						$values[]     = 'NULL';
					} else if (!empty($row[$j])) {
						// a number
						if ($field_num[$j]) {
							$values[] = $row[$j];
						}
						// a string
						else {
							$values[] = "'" . str_replace($search, $replace, addslashes($row[$j])) . "'";
						}
					} else {
						$values[]     = "''";
					} // end if
				} // end for
	
				$insert_line = $schema_insert . implode(',', $values) . ')';
				unset($values);
	
				// Call the handler
				$handler($insert_line);
			} // end while
		} // end if ($result != false)
	
		return true;
	}
	
	
	function my_handler($sql_insert)
	{
		global $crlf, $asfile;
		global $tmp_buffer;
	
		if(empty($asfile))
			$tmp_buffer.= htmlspecialchars("$sql_insert;$crlf");
		else
			$tmp_buffer.= "$sql_insert;$crlf";
	}
	
	
	
	function faqe_db_error()
	{
		return mysql_error();
	}
	
	
	
	function faqe_db_insert_id($result)
	{
		return mysql_insert_id($result);
	}


?>
Replace following variables:

databaseserver = The machine on which your database is residing.
username = database username
password = database password
databasename = the name of database to backup.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

mMm.......I'm still a little O_o about the code. I need to read through it again.

btw, it's odd asking this but what's

Code: Select all

.=
I've been learning PHP for a few months now and I still don't know what is that..........what's that dot in front of the equal sign for? O_o

-Nay
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Code: Select all

$var = "Hello";
$var .= " World";

print $var;

// prints Hello World
It is the concatenation operator

Mark
evilMind
Forum Contributor
Posts: 145
Joined: Fri Sep 19, 2003 10:09 am
Location: Earth

Post by evilMind »

An alternative to the very useful code that was posted by Bech100 is the mysqldump command in which you can dump 1, or some, or all of your tables at the same time, seperating the contents and the structure.

Code: Select all

mysqldump -e -n -t -u YourUserName -pYourPasswd DATABASE &#1111;tables]
mysqldump -d -e -u YourUserName -pYourPasswd DATABASE
mysqldump -e -u YourUserName -pYourPasswd DATABASE

<?php // exampleDumpDb.php //
ob_start();
system( 'mysqldump -d -e -u userOne -pmyPasswd MyFirstDB' );
$mysqlDumpContents = ob_get_contents();
ob_end_clean();

// save the contents of the dump for later restoration
$fp = fopen('/some/dir/mysqlDump.sql','w+');
fwrite( $fp , $mysqlDumpContents );
fclose( $fp );

?>
First mysqldump line will dump all row information for your database and store it in "OutputFile.name"

Second Line will dump the table creation information (no row info) and store it in "OutputFile.name"

Third line will dump table creation information PLUS row information and store it in "OutputFile.name"
evilMind
Forum Contributor
Posts: 145
Joined: Fri Sep 19, 2003 10:09 am
Location: Earth

Post by evilMind »

er.. none of the mysqldumps above will actually write a file. I was using output redirection at first... ie mysqldump -e -u User -pMyPas dbName > outputFile.name will write the file outputFile.name :)
Post Reply