By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,304 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,304 IT Pros & Developers. It's quick & easy.

How to copy mysql tables between two servers?

P: n/a
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("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 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_formulate_row($row); // ?????? not implemented
$qid_B = mysql_query("INSERT INTO $table $stuff") or die("");
}
}
?>
Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 password.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 17 '05 #2

P: n/a
"Chris Hope" <bl*******@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.
Jul 17 '05 #3

P: n/a
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) ";
}
// -------------------------------------------------------
?>
Jul 17 '05 #4

P: n/a
Perttu Pulkkinen wrote:
"Chris Hope" <bl*******@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.
Jul 17 '05 #5

P: n/a
"Michael Austin" <ma*****@firstdbasource.com> kirjoitti viestissä
news:lu*****************@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.

Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.