Page 1 of 1

Trouble exporting database

Posted: Sun Mar 05, 2006 10:58 pm
by bwv2
I need to export my database so I can install it on a different machine. I am having trouble because my computer, for whatever reason, will not do the whole thing at once. I am using phpMyAdmin, and I am using the "export" tool. I can export the database if I exclude one of my tables, which is very large at 660MB. The rest of the database is about 4 MB all together.

When I try to export the last table, it writes a little bit and then thinks it's done. I usually end up with a file that's between 10 and 36MB. It should be around 660MB. Try as I might, I can't get it to finish the export.

In light of this problem, I have tried to write my own script that will take the contents of my table and write them to a dump file, which I can then use to load the table onto the other computer. However, the script that I have written does not put out files that will load correctly into my new database. I think that there's some error in my table content. I have included this script below.

I know there must be some other way to do this. The normal way seems to lock up and not finish, as described above. Does anyone know another way of doing this? If not, can you see anything wrong in my self-made table export script?

Code: Select all

$tableName='mytable';
$dbName='mydb';
$comment='my comment'
$conn = mysql_connect("localhost", "", "");
$db = mysql_select_db($dbName, $conn)or die("Could not Select DB: " . mysql_error());

$fileOpen = fopen( "c:/Documents and Settings/Me/Desktop/".$tableName.".sql" , "w" );
if(!$fileOpen){
    echo "Couldn't open the data file. Try again later.";
    exit;
}
$fileStart= "
-- phpMyAdmin SQL Dump
-- version 2.6.1-pl3
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Server version: 4.1.10
-- PHP Version: 5.0.4
-- 
-- Database: `".$dbName."`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `".$tableName."`
-- 

CREATE TABLE `".$tableName."` (
  `longitude` float NOT NULL default '0',
  `latitude` float NOT NULL default '0',
  `winWeibC` float NOT NULL default '0',
  `winWeibK` float NOT NULL default '0',
  `sprWeibC` float NOT NULL default '0',
  `sprWeibK` float NOT NULL default '0',
  `sumWeibC` float NOT NULL default '0',
  `sumWeibK` float NOT NULL default '0',
  `falWeibC` float NOT NULL default '0',
  `falWeibK` float NOT NULL default '0',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `".$tableName."`
-- 
";

fwrite( $fileOpen, $fileStart );

$sql = "SELECT * FROM ".$tableName;
$result = mysql_query($sql, $conn) or die("mysql query didn't work!");
$numCols=mysql_num_fields($result);
while($row = mysql_fetch_assoc($result)){
	$i=$numCols;
	$inputString= "INSERT INTO `".$tableName."` VALUES (";
	foreach ($row as $col=>$val){
			$inputString= $inputString.$val;
			if($i!=1){
				$inputString= $inputString." , ";
			}
			$i--;
	}
	$i=0;
	$inputString= $inputString.");\n";
	fwrite( $fileOpen, $inputString );
}

fclose( $fileOpen );

Posted: Sun Mar 05, 2006 11:23 pm
by josh
I've had problems where the SQL file being generated will not fit in memory, the solution was to push the data from one machine to the other in chunks of 100,000 rows using a PHP script (grant permissions in mysql from one machine to allow remote connections)

Posted: Sun Mar 05, 2006 11:25 pm
by Benjamin
I think in a situation like this it would be best to login to MySQL directly via a shell and dump the data from the command line. I'm pretty sure phpMyAdmin and PHP aren't designed for such tasks.

Posted: Mon Mar 06, 2006 12:10 am
by RobertGonzalez
If you have access to the mysql database as an administrator, download the MySQL Administrator application from MySQL. This thing has dumped my 40MB(ish) db in a few seconds into a dump file. It is blazing fast. You can also use this tool to restore your datbase from the dump (backup) file.

Which version of phpMyAdmin are you running? Also, does the gzip compression option help it at all?

And lastly, BigDump installer is an app that loads larger than acceptable MySQL dumps when you do not have admin rights to change the server variables. Maybe you can tear this app apart and see if there is way to code a dump in the same way so you don't have to push your systems memory limits.

Posted: Mon Mar 06, 2006 7:55 am
by bwv2
I used mySQL Administrator and it worked. Thanks a lot for the help, that was a lot easier than the way I was trying to do it :)