Various Topics on PHP

..rt.. ..lkkin..

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";
$p***_A = "xxx";
$base_A = "xxx";

$host_B = "yyy";
$user_B = "yyy";
$p***_B = "yyy";
$base_B = "yyy";

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

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

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

foreach($tables as $table)
{
$qid_A = mysql_query("SELECT * FROM $table", $link_A)
or die("Haku tietokantaan A epäonnistui! : " . mysql_error());
$qid_DEL_B = mysql_query("DELETE FROM $table", $link_B):
or die("Taulun tyhjennys tietokann***a B epäonnistui! : " .
mysql_error());

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

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

..r.. ....

Perttu Pulkkinen wrote:

> 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.

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 p***word.

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

..rt.. ..lkkin..

"Chris Hope" <blackhole@electrictoolbox.com> wrote

> But the fastest way to dump and load a database is using the mysqldump

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.

..rt.. ..lkkin..

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";
$p***_A = "x";
$base_A = "x";

$host_B = "y";
$user_B = "y";
$p***_B = "y";
$base_B = "y";

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

// -------------------------------------------------------
$table_prefix_B = trim($table_prefix_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, $p***_A)
or die("Yhteys tietokantaan A epäonnistui! : " . mysql_error());
$link_B = mysql_connect($host_B, $user_B, $p***_B)
or die("Yhteys tietokantaan B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
mysql_select_db($base_A, $link_A) or die("Tietokannan A valinta
epäonnistui!");
mysql_select_db($base_B, $link_B) or die("Tietokannan B valinta
epäonnistui!");
// -------------------------------------------------------
foreach($tables as $table)
{
$table_B = $table_prefix_B.$table;

$show_create = "SHOW CREATE TABLE $table";
// echo "<p>".$show_create."</p>";
$qid_SHOW_A = mysql_query($show_create, $link_A)
or die("Taulukuvauksen haku tietokannasta A epäonnistui! : " .
mysql_error());
// -------------------------------------------------------
$drop = "DROP TABLE IF EXISTS $table_B";
// echo "<p>".$drop."</p>";
$qid_DROP_B = mysql_query($drop, $link_B)
or die("Taulun poisto tietokannasta B epäonnistui! : " . mysql_error());
// -------------------------------------------------------
$create_row = mysql_fetch_row($qid_SHOW_A)
or die("Kuvausrivin haku epäonnistui! : " . mysql_error());
$create = $create_row[1];
$create = str_replace("CREATE TABLE `$table`", "CREATE TABLE `$table_B`",
$create)
or die("Taulunimen muodostus epäonnistui!");
// echo "<p>".$create."</p>";
$qid_CREATE_B = mysql_query($create, $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($select, $link_A)
or die("Taulun datan haku tietokannasta A epäonnistui! : " .
mysql_error());
// -------------------------------------------------------
while($row = mysql_fetch_***oc($qid_SELECT_A))
{
$insert = formulate_insert_row($table_B, $row, $link_B);
// echo "<p>".$insert,"</p>";
$qid_INSERT_B = mysql_query($insert, $link_B)
or die("Rivin kopiointi tauluun B epäonnistui! : " . mysql_error());
}
}
// -------------------------------------------------------
function formulate_insert_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) ";
}
// -------------------------------------------------------
?>

..cha.. ..st..

Perttu Pulkkinen wrote:

> "Chris Hope" <blackhole@electrictoolbox.com> wrote
>
>
>>But the fastest way to dump and load a database is using the mysqldump
>
>
> 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.
>
>

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.

..rt.. ..lkkin..

"Michael Austin" <maustin@firstdbasource.com> kirjoitti viestissä
news:luGAc.6821$3q1.6349@newssvr24.news.prodigy.co m...
> 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...

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.