create/copy table from remote server to local server

Discussion in 'MySQL' started by aayybb, Feb 10, 2010.

0
  1. #1
    Hi,

    I need to copy a table 'customer' from remote server to local server as 'customer_copy'. I tried the following and everything is fine but the copy part failed. Not sure why? Thanks for any help in advance.


    $con_remore = mysql_connect("xx.xx.xx.xx","aaa","bbb");
    if (!$con_remote)
    {
    die('Could not connect: ' . mysql_error());
    }
    else
    {
    print 'connect remote ';
    }
    $db_selected_remote = mysql_select_db("remoteDB", $con_remote);
    if (!$db_selected_remote)
    {
    die ("Can\'t use test_db : " . mysql_error());
    }

    $con_local = mysql_connect("yy.yy.yy.yy","ccc","ddd");
    if (!$con_local)
    {
    die('Could not connect: ' . mysql_error());
    }
    else
    {
    print ' connect bottle ';
    }
    $db_selected_local = mysql_select_db("localDB", $con_local);
    if (!$db_selected_local)
    {
    die ("Can\'t use test_db : " . mysql_error());
    }
    $check_table_existing = mysql_query("SHOW TABLES from localDB like 'customer_copy'",$con_local);
    if (mysql_num_rows($check_table_existing) == 1)
    {
    mysql_query("DROP TABLE customer_copy",$con_local);
    }
    elseif (mysql_num_rows($check_table_existing) == 0)
    {
    print ' not exist ';
    }

    $copy_table = mysql_query("CREATE TABLE localDB.customer_copy SELECT remoteDB.customer.ID, remoteDB.customer.Name FROM remote.customer_order");
    if ($copy_table)
    print 'copy';
    else
    print ' fail copy';
    ?>
     
    Last edited: Feb 10, 2010
    aayybb, Feb 10, 2010 IP
  2. systematical

    systematical Peon

    Messages:
    81
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Your best bet may be writing a shell script that performs a mysql dump, then sends the dump via FTP to the other server. A cronjob on the remote server would continually check a directory (where you would FTP the mysql dump too) and run the sql source.

    Make sense?
     
    systematical, Feb 12, 2010 IP
  3. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    systematical, you have lost me. I am not familiar with shell script or mysql dump or even send anything vis FTP to another server. I thought the syntax of my mysql query to create the table is correct. Just don't know why it failed to create. I will need to read about shell script and mysql dump first to understand your suggestion. Thanks for your response.
     
    aayybb, Feb 12, 2010 IP