Copy table from different server using cron

Question

I have a live server and a test server right now, and I need to copy a table from live to test using cron because it needs to run every hour. I've created a php script and tested it using localhost, and it works fine, but when I test at live server, it doesn't work.

I suspect because when at localhost, although it between different database but still using the same server connection. and I'm using phpmyadmin.

Edit: Here the code

<?php
/* fill in your source database name */
$database = "xxx";
$dbhost1 = "xxx";
$dbusr1 = "xxx";
$dbpas1 = "xxx";
/* fill in your target database name */
$database2 = "yyy";
$dbhost2 = "yyy";
$dbusr2 = "yyy";
$dbpas2 = "yyy";

if ($connect = mysqli_connect($dbhost1, $dbusr1, $dbpas1)) 
{
    mysql_select_db($database, $connect);
    echo "connected to xxx<br/>";
}
else { die("Source database fail to connect: Please try again" . mysql_error());}

if ($connect2 = mysqli_connect($dbhost2, $dbusr2, $dbpas2)) 
{
    mysql_select_db($database2, $connect2);
    echo "connected to yyy<br/>";
}
else { die("Target database fail to connect 1: " . mysql_error());}
set_time_limit(0);

$tables = array("coupon");
$tables_no = count($tables); 

for ($i=0; $i < $tables_no ; $i++){
    $tab = $tables[$i];
    $query_table1 = "SELECT * FROM $database.$tab";
    $query_table2 = "SELECT * FROM $database2.$tab";

    $source= mysql_query($query_table1);
    $destination= mysql_query($query_table2);

    if ($source <= $destination){
    echo 'aaa';
        while($row_table1 = mysql_fetch_array($source)){
            // var_dump ($row_table1);
            // if statement
        }
    }
    else {echo 'dalam else';}
} 
?>

showing some script could help, you can mask out the confidential details... — Aaron Gong, Aug 27 '13 at 02:07
Sorry. I put the script at test server and when I run it, it seems that it can't read the query statement. I put the script for help. Thanks — Kiki, Aug 27 '13 at 02:08
Please see my answer here (And also try to use mysqli instead!) Connect To Multiple Database [1]: http://stackoverflow.com/questions/18393200/is-it-possible-to-connect-to-multiple-database-e-g-database1-database2/18393755#18393755 — Aaron Gong, Aug 27 '13 at 02:16
Why go to the trouble of writing a script when there is a replication mechanism (see http://dev.mysql.com/doc/refman/5.0/en/replication.html) — Ed Heal, Aug 27 '13 at 21:56
@EdHeal I need to only copy one or two table every hour and not the whole table in the database. Is the link given also can be used on that? Sorry to ask because I'm not familiar with that script. — Kiki, Aug 28 '13 at 02:46
@kiki - Replication will mean that if somebody updates the live database (delete/insert/update a row) that will be filtered down to the test one. There is no script required. Just need to configure things. You can even use it for backup etc.. just follow the links from the link. (you can use it for load balancing!) — Ed Heal, Aug 28 '13 at 03:05

Aaron Gong · Accepted Answer · 2013-08-27T05:27:18.800

0

Your second connect is killing your 1st one. if using mysql_connect, please check the manual for variable to pass in to avoid this...

if ($connect = mysql_connect($dbhost1, $dbusr1, $dbpas1)) 
{
    mysql_select_db($database, $connect);
    echo "connected to xxx<br/>";
}
else { die("Source database fail to connect: Please try again" . mysql_error());}

if ($connect2 = mysql_connect($dbhost2, $dbusr2, $dbpas2, true)) 
{
    mysql_select_db($database2, $connect2);
    echo "connected to yyy<br/>";
}

And to retrieve results from each DB, use their respective resource...

$result1 = mysql_query($connect1, $sql_query1);

$result2 = mysql_query($connect2, $sql_query2);

edited Aug 27 '13 at 05:27

answered Aug 27 '13 at 02:17

Aaron Gong

977
7
18

I've try using your code but still the same error happens. It seems like it stop to read the code at ** while($row_table1 = mysql_fetch_array($source)){ ** line. the ** echo 'aaa'; ** line is printed and stop at while statement after that – Kiki Aug 27 '13 at 03:18
Hi, editing above what else went wrong, I think u need to specify resource for your query – Aaron Gong Aug 27 '13 at 05:26
Great! yes, need to specify the resource of query. Thank you for your immediate help. – Kiki Aug 27 '13 at 06:34

Copy table from different server using cron

1 Answers1