Cross-server database copy

[dlindsey]

I have developed a relatively simple script to copy a database located at server1 over to server2. The easiest and most effective way I could think of to do this was to first copy the structure using a "SHOW CREATE TABLES FROM server1.db" and then cutting up the string a bit to reproduce the table on server2. This part worked beautifully, the problem comes when trying to transfer data over. First I query server1.db (let's use table options as an example):

 

 

STEP 1:

$db_connect_source = mysql_connect($source['host'], $source['user'], $source['pw'], true);

mysql_select_db($source['db'], $db_connect_source);

$options = mysql_query("select * from options", $db_connect_source) or die(mysql_error());

mysql_close($db_connect_source);

 

 

Now I have all of the data from server1.db.options in the $options variable (I have confirmed that the data is there).

 

 

 

STEP 2:

$db_connect_dest = mysql_connect($dest['host'], $dest['user'], $dest['pw'], true);

mysql_select_db($dest['db'], $db_connect_dest);

$options_cols = "id, title, subtitle, value, value2, value3, value4, value5";

print_tables($options, "options", $options_cols); //Custom function to write to server2.db

The function is relatively simple, but effective. What it does is reads in one row at a time from the source table, and builds an "INSERT" statement that mirrors the data from server1.db.table to server2.db.table.

 

 

function print_tables($table_name, $name, $cols){
$sqlcols = explode(",", $cols);
for($i = 0; $i<count($sqlcols); $i++){
	$sqlcols[$i] = trim($sqlcols[$i]);
}
while($nt=mysql_fetch_row($table_name)){
	$data = null;
	$x = 0;
	$sql = "insert into " . $name . " ";
	$scols = "(";
	$svals = "(";

	foreach($nt as $info){
		if ($info){
			if (is_numeric($info)){
				$scols .= $sqlcols[$x] . ", ";
				$svals .= $info . ", ";
			}
			else{
				$data = addslashes($info);
				$scols .= $sqlcols[$x] . ", ";
				$svals .= "'" . $data . "', ";
			}
		}
		$x++;
	}
	$scols = substr_replace($scols, ")", strlen($scols) - 2, 2);
	$svals = substr_replace($svals, ")", strlen($svals) - 2, 2);
	$sql .= $scols . " values " . $svals;
	//print $sql . "<br />";
	$result=mysql_query($sql, $db_connect_dest) or die(mysql_error());
}
}

The "INSERT" strings come out fine, but I seem to keep getting an error:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource

 

The error occurs on this line:

$result=mysql_query($sql, $db_connect_dest) or die(mysql_error());

 

I am not sure where to go with this, I have checked and it is possible to write data to server2.db.table from server2. Maybe it is a communication error between server1 and server2? But if that were the case, how did the tables manage to get built?

 

I'm stuck on ideas here. Any help would be much appreciated.

 

 

UPDATE: I have checked and it is possible to write the data across servers, too. The following statement worked fine:

mysql_query("insert into options (id, title, subtitle, value) values (2, 'rewards', 'last_payday', '2009-08-19')", $db_connect_dest) or die(mysql_error());

[fenway]

Use code tags next time.

 

Echo the offending queries.

[dlindsey]

//print $sql . "<br />";
$result=mysql_query($sql, $db_connect_dest) or die(mysql_error());

 

That's what the commented line is for, I just comment out the query and uncomment the print statement to see my queries. They all look perfectly fine. Here's a few example outputs:

 

insert into testvpslist.options (id, title, subtitle, value) values (1, 'rewards', 'next_payday', '2009-08-26')

insert into testvpslist.options (id, title, subtitle, value) values (2, 'rewards', 'last_payday', '2009-08-19')

insert into testvpslist.options (id, title, subtitle, value) values (3, 'rewards', 'next_calcdate', '2009-08-23')

insert into testvpslist.options (id, title, subtitle, value) values (4, 'rewards', 'last_calcdate', '2009-08-16')

[fenway]

Hmmm... those seem reasonable.

 

Have you confirmed that you have a valid connection to each DB?

[dlindsey]

Yes, the connection is there, otherwise the plain-text sql queries would not work, correct? I have decided to go another way with the project and just build the whole thing from scratch, but this is still aggravating me because there is no reason why it shouldn't work. Also the script doesn't die() until it reaches the insert queries, so yes, the connection is there and the database is selected.

[markwillis82]

your db connection isn't "visible" from inside the function.

 

Change

function print_tables($table_name, $name, $cols){
   $sqlcols = explode(",", $cols);
   for($i = 0; $i<count($sqlcols); $i++){
      $sqlcols[$i] = trim($sqlcols[$i]);
   }
   while($nt=mysql_fetch_row($table_name)){
      $data = null;
      $x = 0;
      $sql = "insert into " . $name . " ";
      $scols = "(";
      $svals = "(";
      
      foreach($nt as $info){
         if ($info){
            if (is_numeric($info)){
               $scols .= $sqlcols[$x] . ", ";
               $svals .= $info . ", ";
            }
            else{
               $data = addslashes($info);
               $scols .= $sqlcols[$x] . ", ";
               $svals .= "'" . $data . "', ";
            }
         }
         $x++;
      }
      $scols = substr_replace($scols, ")", strlen($scols) - 2, 2);
      $svals = substr_replace($svals, ")", strlen($svals) - 2, 2);
      $sql .= $scols . " values " . $svals;
      //print $sql . "<br />";
      $result=mysql_query($sql, $db_connect_dest) or die(mysql_error());
   }
}

ro adding a global tag in

function print_tables($table_name, $name, $cols){
global $db_connect_source;
   $sqlcols = explode(",", $cols);
   for($i = 0; $i<count($sqlcols); $i++){
      $sqlcols[$i] = trim($sqlcols[$i]);
   }
   while($nt=mysql_fetch_row($table_name)){
      $data = null;
      $x = 0;
      $sql = "insert into " . $name . " ";
      $scols = "(";
      $svals = "(";
      
      foreach($nt as $info){
         if ($info){
            if (is_numeric($info)){
               $scols .= $sqlcols[$x] . ", ";
               $svals .= $info . ", ";
            }
            else{
               $data = addslashes($info);
               $scols .= $sqlcols[$x] . ", ";
               $svals .= "'" . $data . "', ";
            }
         }
         $x++;
      }
      $scols = substr_replace($scols, ")", strlen($scols) - 2, 2);
      $svals = substr_replace($svals, ")", strlen($svals) - 2, 2);
      $sql .= $scols . " values " . $svals;
      //print $sql . "<br />";
      $result=mysql_query($sql, $db_connect_dest) or die(mysql_error());
   }
}

alternativly if your on 2 linux boxes would it not be easier to use a BASH script and SCP (file transfer)

 

and do:

mysqldump #DATABASE# #TABLE# > dump.sql
scp dump.sql user@server2.co.uk:~/dump.sql
ssh user@server2.co.uk -C "mysql < dump.sql"

 

the last SSH command may not be correct (running of memory here)

 

hope this helps

Mark willis

[dlindsey]

Unfortunately I'm running a windows client on a windows server, I'll check out the visibility problem you mentioned in a little bit, that actually is the only thing I've heard that makes sense so far.

[markwillis82]

mysql dump is on windows too (look in bin directory of mysql installation) - if both servers are on a network then just use COPY through a network share (instead of BASH use a batch file)

 

It will still be 3 commands

[dlindsey]

Both servers are on completely different networks, otherwise I'd have just done a dump, haha. Thanks anyway