Copy Data To Another MySQL Database

[dvation]

I'm using MySQL v5 and have two databases on different servers that have the same exact table structure, but require different credentials.

 

My goal is to be able to copy entire rows of data from a table on one database to the same table on the other database. I'd like to try to code this myself but I need some guidance on how I should be approaching this.

[Maq]

msyqldump

 

Could you be a bit more specific of what you're trying to do?  There are multiple ways to handle this depending on your goal.

[dvation]

Sure.

The specifics are that my boss wants me to build an "offline" version of our live website, which is PHP5/MySQL5 driven and hosted externally. I have a local web server that also has PHP5/MySQL 5 installed and I have an exact replica of the site and the databases.

 

The data in the database is specific to a customer and gets built out as the client needs it. What my boss wants me to be able to do is to copy a "live" client's data down to the offline version. This means copying a row from a table from the "live" database and put it in the "offline" database. This needs to be done "on-demand" by our employees.

 

As I said above, the credentials are different for each of the two databases. Is this enough information to give me an idea on how to accomplish this?

[dvation]

Well, I got close to doing what I want to do. The problem I have now is that the code only copies one row of data from the table. Sometimes the table has more than one row of data and I want to be able to copy that too.

	
$acctidtbls = array("account","division","knowledgebase","user");
while ($tblrow = current($acctidtbls)) {
	$result = mysql_query("SELECT * FROM $tblrow WHERE accountid = 24", $livelink) or die("Error: ".mysql_error());
		$row = mysql_fetch_assoc($result);
		$qkeys = array();
		$qvals = array();
		for($i = 0; $i < count($row); $i++)	{
			$qkeys[] = key($row);
			$qvals[] = $row[key($row)];
			next($row);
		}	
		$keys = implode(",", $qkeys);
		$vals = implode("','", $qvals);
		$query = "REPLACE INTO $tblrow ($keys) values ('$vals')";
		next($acctidtbls);
		$result = mysql_query($query, $locallink)or die();			
}	

[dvation]

Well I'm not sure if nobody had the answer or if I wasn't clear enough in my request, but I eventually figured it out on my own; even if it took longer than I would have liked. For anyone else looking at this thread in the future, this is my final code:

$acctidtbls = array("account","division","knowledgebase","user");
foreach ($acctidtbls as $tblrow) { 
	$result = mysql_query("SELECT * FROM $tblrow WHERE accountid = 24", $livelink) or die("Error: ".mysql_error());
	$numrows = mysql_num_rows($result);
	while ($row = mysql_fetch_assoc($result)) { 		
		$qkeys = array();
		$qvals = array();
		for($i = 0; $i < count($row); $i++)	{ 
			$qkeys[] = key($row);
			$qvals[] = $row[key($row)];
			next($row);
		} 
		$keys = implode(",", $qkeys);
		$vals = implode("','", $qvals);
		$query = "REPLACE INTO $tblrow ($keys) values ('$vals')";
		$copyresult = mysql_query($query, $locallink) or die();
	} 
} 

[Mchl]

Ehmmm... try replication?

[FezEvils]

use command prompt to dump your mysql database.. then you will get all the data structure and data

[corbin]

use command prompt to dump your mysql database.. then you will get all the data structure and data

 

 

That could potentially lock tables on the live database and cause high CPU consumption and memory consumption.  Know every xx:00 time when PHPFreaks crawls to a slow for 3 minutes?  It would be like that.