Re: How to copy mysql tables between two servers?

From: Perttu Pulkkinen (perttu.pulkkinen_at_co.jyu.fi)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 11:39:43 GMT

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_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, $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("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_assoc($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) ";
}
// -------------------------------------------------------
?>