How to copy mysql tables between two servers?

[Perttu Pulkkinen]

I want to copy certain tables from server to server b.
How do I make it fastly and efficiently with PHP?
I wrote below my current start, but it may not be the best approach.


Perttu Pulkkinen, FINLAND
----------------------------
<?
$host_A = "xxx";
$user_A = "xxx";
$pass_A = "xxx";
$base_A = "xxx";

$host_B = "yyy";
$user_B = "yyy";
$pass_B = "yyy";
$base_B = "yyy";

$tables = array(""this", that", "those", "them");

$link_A = mysql_connect($ host_A, $user_A, $pass_A);
or die("Yhteys tietokantaan A epäonnistui! : " . mysql_error());
$link_B = mysql_connect($ host_B, $user_B, $pass_B);
or die("Yhteys tietokantaan B epäonnistui! : " . mysql_error());

mysql_select_db ($base_A, $link_A) or die("Tietokanna n A valinta
epäonnistui!");
mysql_select_db ($base_B, $link_B) or die("Tietokanna n B valinta
epäonnistui!");

foreach($tables as $table)
{
$qid_A = mysql_query("SE LECT * FROM $table", $link_A)
or die("Haku tietokantaan A epäonnistui! : " . mysql_error());
$qid_DEL_B = mysql_query("DE LETE FROM $table", $link_B):
or die("Taulun tyhjennys tietokannassa B epäonnistui! : " .
mysql_error());

// THIS PART IS MAYBE STUPID, PAINFUL-TO-DO AND UNEEFICIENT

while($row = mysql_fetch_row ($qid_A))
{
$stuff = somehow_formula te_row($row); // ?????? not implemented
$qid_B = mysql_query("IN SERT INTO $table $stuff") or die("");
}
}
?>

[Chris Hope]

Re: How to copy mysql tables between two servers?

Perttu Pulkkinen wrote:
[color=blue]
> I want to copy certain tables from server to server b.
> How do I make it fastly and efficiently with PHP?
> I wrote below my current start, but it may not be the best approach.[/color]

The following query shows you the query required to create the table:
SHOW CREATE TABLE tablename

But the fastest way to dump and load a database is using the mysqldump
command line utility eg

mysqldump -u username -p databasename1 > filename.txt

will dump all tables database structure and data into a file containing all
the insert queries. There are additional flags that can speed this up.

mysql -u username -p databasename2 < filename.txt

will then load this into the other database. After hitting the enter key in
both cases you'll be prompted for your password.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Perttu Pulkkinen]

Re: How to copy mysql tables between two servers?

"Chris Hope" <blackhole@elec trictoolbox.com > wrote
[color=blue]
> But the fastest way to dump and load a database is using the mysqldump[/color]

I would like to do with php the best I can plus I dont want to copy all
tables.
Of course
1) if with flags I could select the tables I want to dump and
2) if manage to use shell correctly through php and
3) if manage to pick up that dump file to php
this approach would work.

[Perttu Pulkkinen]

Re: How to copy mysql tables between two servers?

Original problem: How to copy ONLY CERTAIN mysql tables between TWO servers
USING PHP? This approach is working, but its is slow and it can easily
exceed php maximum executution time. (Could maximum time extended only for
current page request?)

-perttu pulkkinen, jkl


<?php

$host_A = "x";
$user_A = "x";
$pass_A = "x";
$base_A = "x";

$host_B = "y";
$user_B = "y";
$pass_B = "y";
$base_B = "y";

$tables = array("qwer", "wer", "ert", "rty");
$table_prefix_B = "copy_of_";

// -------------------------------------------------------
$table_prefix_B = trim($table_pre fix_B);
if($host_A == $host_B && $base_A == $base_B && $table_prefix_B =="")
die("Samaa taulua ei voi kopioida samannimiseksi saman tietokannan
sisällä!");
// -------------------------------------------------------
$link_A = mysql_connect($ host_A, $user_A, $pass_A)
or die("Yhteys tietokantaan A epäonnistui! : " . mysql_error());
$link_B = mysql_connect($ host_B, $user_B, $pass_B)
or die("Yhteys tietokantaan B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
mysql_select_db ($base_A, $link_A) or die("Tietokanna n A valinta
epäonnistui!");
mysql_select_db ($base_B, $link_B) or die("Tietokanna n B valinta
epäonnistui!");
// -------------------------------------------------------
foreach($tables as $table)
{
$table_B = $table_prefix_B .$table;

$show_create = "SHOW CREATE TABLE $table";
// echo "<p>".$show_cre ate."</p>";
$qid_SHOW_A = mysql_query($sh ow_create, $link_A)
or die("Taulukuvau ksen haku tietokannasta A epäonnistui! : " .
mysql_error());
// -------------------------------------------------------
$drop = "DROP TABLE IF EXISTS $table_B";
// echo "<p>".$drop ."</p>";
$qid_DROP_B = mysql_query($dr op, $link_B)
or die("Taulun poisto tietokannasta B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
$create_row = mysql_fetch_row ($qid_SHOW_A)
or die("Kuvausrivi n haku epäonnistui! : " . mysql_error());
$create = $create_row[1];
$create = str_replace("CR EATE TABLE `$table`", "CREATE TABLE `$table_B`",
$create)
or die("Taulunimen muodostus epäonnistui!");
// echo "<p>".$create." </p>";
$qid_CREATE_B = mysql_query($cr eate, $link_B)
or die("Taulun luonti tietokantaan B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
$select = "SELECT * FROM $table";
// echo "<p>".$select." </p>";
$qid_SELECT_A = mysql_query($se lect, $link_A)
or die("Taulun datan haku tietokannasta A epäonnistui! : " .
mysql_error());
// -------------------------------------------------------
while($row = mysql_fetch_ass oc($qid_SELECT_ A))
{
$insert = formulate_inser t_row($table_B, $row, $link_B);
// echo "<p>".$insert," </p>";
$qid_INSERT_B = mysql_query($in sert, $link_B)
or die("Rivin kopiointi tauluun B epäonnistui! : " . mysql_error());
}
}
// -------------------------------------------------------
function formulate_inser t_row($table, $row, $link)
{
$fields=""; $values = "";
$flag=true;
foreach($row as $key=>$value)
{
if($flag) $flag=false; else { $values.= ","; $fields.= ","; }
$values.= "'".mysql_real_ escape_string($ value, $link)."'";
$fields.= $key;
}
return " INSERT INTO $table($fields) VALUES($values) ";
}
// -------------------------------------------------------
?>

[Michael Austin]

Re: How to copy mysql tables between two servers?

Perttu Pulkkinen wrote:
[color=blue]
> "Chris Hope" <blackhole@elec trictoolbox.com > wrote
>
>[color=green]
>>But the fastest way to dump and load a database is using the mysqldump[/color]
>
>
> I would like to do with php the best I can plus I dont want to copy all
> tables.
> Of course
> 1) if with flags I could select the tables I want to dump and
> 2) if manage to use shell correctly through php and
> 3) if manage to pick up that dump file to php
> this approach would work.
>
>[/color]

Can you give us an idea of why you want to do it this way? Doing this
will eventually have the databases out of sync with each other. So, why
not back up the originating database and restore it to the remote
location on some pre-determined interval... now all tables will be
identical... and probably much faster than moving table data from one
database to another...

Michael Austin
Sr DBA.

[Perttu Pulkkinen]

Re: How to copy mysql tables between two servers?

"Michael Austin" <maustin@firstd basource.com> kirjoitti viestissä
news:luGAc.6821 $3q1.6349@newss vr24.news.prodi gy.com...[color=blue]
> Can you give us an idea of why you want to do it this way? Doing this
> will eventually have the databases out of sync with each other. So, why
> not back up the originating database and restore it to the remote
> location on some pre-determined interval... now all tables will be
> identical... and probably much faster than moving table data from one
> database to another...[/color]

I understand your point. But in our system this "database" is yet a
collection of
tables in one real "master database". Thats why I need only dump those
related tables.