Copy records between 2 DBs w/ same table names 1

[southbeach]

Working on a script to backup all tables w/in one DB to another DB - They have different names but their tables' name are identical.

This is what I have:

<?
$conn = mysql_connect('localhost', 'userID', 'PassWord');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('MyDB_Name');

$tables = mysql_query("show tables");

echo '<table width="100%" border="1"><tr><th>File Processed</th><th>Time Processed</th></tr>';
while ($tlist = mysql_fetch_array($tables)) {

	echo '<tr><td>' . $tlist[0] . '</td><td>' . date() . '</td></tr>';
	$qstr = 'select * from ' . $tlist[0];
	$result = mysql_query($qstr);
	if (!$result) {
		die('Query failed: ' . mysql_error());
	}

	while ($rows = mysql_fetch_assoc($result)) {
		
		$i = 0; 
		$insert = 'INSERT INTO `' . $tlist[0] . '` ( ';
		$lastf = mysql_num_fields($result) - 1;
		
		/* get column metadata */
		while ($i < mysql_num_fields($result)) {
			// echo "Information for column $i:<br />\n";
			$meta = mysql_fetch_field($result, $i);
			if (!$meta) {
				echo "No information available<br />\n";
			}
			echo 'i: ' . $i . '  lastf: ' . $lastf . '<br />';
			if ($i != $lastf) { $cols .= '`' . $meta->name . '`,'; }
			else { $cols .= '`' . $meta->name . '`'; }

			if ($i != $lastf) { $vals .= '"' . $rows[$meta->name] . '",'; }
			else { $vals .= '"' . $rows[$meta->name] . '"'; }

			$i++;
		}
		$insert .= $cols . ') VALUES (' . $vals . ')';
		//echo $insert;

	}

}
echo '</table>';
mysql_free_result($result);
?>

The [$insert] string is built with correct syntax. All I need to add is
(a) Open 2nd DB
(b) Execute query to insert the records

Not having done this before forced me to stop and think if this would be wise since the tables share the same names. I am working within a loop and I am afraid that the script will crash since it will not know which DB to access once they are both opened.

My idea to solve this is to store all the [$insert] strings within an array and then once the construct loop is finished, loop through the [$insert] array and run all the insert queries.

What do you guys think?

Thanks!

 

[jpadie]

why not use mysqldump instead?

 

[southbeach]

Well, my boss suggested we keep a 2nd server in a remote location (fail over or redundancy). He also wants me to backup our DB to this server. He also wants it done upon request, not just scheduled.

Our DB is not too large (yet) so I figure this is a good way to start. The truth is that I did not even know about mysqldump until you mentioned it; having read a sample on how to use it

http://www.theprojects.org/tutorials/mysqldump.php

I have made a note of it and will look into it further.

Thanks,

 

[southbeach]

Well, I completed the PHP script and it works pretty well.

Here is the final draft:

<?
$conn = mysql_connect('localhost', 'My_UserID', 'My_Password');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('Source_DB');

$tables = mysql_query("show tables"); 
$count=0; 
$command = array(); $name = array();

echo '<table width="100%" border="1"><tr><th>Reading File</th><th>Time Processed</th></tr>';
while ($tlist = mysql_fetch_array($tables)) {

	echo '<tr><td>' . $tlist[0] . '</td><td>' . time() . '</td></tr>';
	$qstr = 'select * from ' . $tlist[0];
	$result = mysql_query($qstr);
	if (!$result) {
		die('Query failed: ' . mysql_error());
	}

	while ($rows = mysql_fetch_assoc($result)) {
		
		$i = 0; 
		$insert = 'INSERT INTO `' . $tlist[0] . '` ( ';
		$lastf = mysql_num_fields($result) - 1;
		
		/* get column metadata */
		while ($i < mysql_num_fields($result)) {
			// echo "Information for column $i:<br />\n";
			$meta = mysql_fetch_field($result, $i);
			if (!$meta) {
				echo "No information available<br />\n";
			}
			//echo 'i: ' . $i . '  lastf: ' . $lastf . '<br />';
			if ($i != $lastf) { $cols .= '`' . $meta->name . '`,'; }
			else { $cols .= '`' . $meta->name . '`'; }

			if ($i != $lastf) { $vals .= '"' . $rows[$meta->name] . '",'; }
			else { $vals .= '"' . $rows[$meta->name] . '"'; }

			$i++;
		}
		$insert .= $cols . ') VALUES (' . $vals . ')';
		$command[$count] = $insert; $count++;
		$name[$count]=$tlist[0];
		$cols=''; $vals=''; $insert='';

	}

}
echo '</table>';
mysql_free_result($result);

$local = mysql_connect('RemoteHost', 'My_UserID', 'My_Password');
if (!$local) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('Target_DB');

for ($i=0; $i < $count; $i++) {
	$insert = $command[$i];  echo 'Writing Data For ' . $name[$i] . '<br />';
	$addrec = mysql_query($insert) or die (mysql_error());
}

mysql_free_result($addrec);
?>

 

[jpadie]

if you want a proper backup and hot swap you should consider setting a master-slave replication up, and then taking backups from the least used one. the mysql forum can help you with this. it's not at all difficult to set up.

personally i cache each data manipulation statement to a sqlite database (or sometimes a flat file). in this way i can recover the database to any particular state at the press of a button).