How to copy a table from one mysql database to another mysql database

I need to copy a table from one database to another. This will be a cronjob. Which one is the best way to do it? PHP script or Shell Script. The problem with PHP, both databases has different usernames and passwords so I can't do it like this.

CREATE TABLE db1.table1 SELECT * FROM db2.table1

Should I just connect first DB get all r开发者_运维问答ecords and insert all to new database using WHILE loop or there is a better way?

I prefer a shell script to do this instead of PHP script.

Thanks

If you need to copy the table on the same server you can use this code:

USE db2;

CREATE TABLE table2 LIKE db1.table1;

INSERT INTO table2  
    SELECT * FROM db1.table1;

It's copy+pasted from here: codingforums.com

It's not my solution, but I find it useful.

I'd dump it. Much less complicated than anything PHP based.

mysqldump -u user1 -ppassword1 databasename > dump.sql
mysql -u user2 -ppassword2 databasename < dump.sql

MySQL reference: 4.5.4. mysqldump — A Database Backup Program

mysqldump -u user1 -ppassword1 databasename TblName | mysql -u user2 -ppassword2 anotherDatabase

It all can be done in a single command.

$L1 = mysql_connect('localhost', 'user1', 'pass1');
$DB1 = mysql_select_db('database1', $L1);   

$L2 = mysql_connect('localhost', 'user2', 'pass2');
$DB2 = mysql_select_db('database2', $L2);   

$re=mysql_query("SELECT * FROM table1",$L1);
while($i=mysql_fetch_assoc($re))
{
    $u=array();
    foreach($i as $k=>$v) if($k!=$keyfield) $u[]="$k='$v'";
    mysql_query("INSERT INTO table2 (".implode(',',array_keys($i)).") VALUES ('".implode("','",$i)."') ON DUPLICATE KEY UPDATE ".implode(',',$u),$L2) or die(mysql_error());
}

user1, pass1, database1, table1 reffers to initial table user2, pass2, database2, table2 reffers to copied table $keyfield is the primary key of table

One liner with different servers

mysqldump -h host1 -u user1 -ppassword1 databasename TblName | mysql -h host2 -u user2 -ppassword2 anotherDatabase

Phpmyadmin has inbuilt functionality to copy tables from one database to another. Otherwise you can go with Pekka or export table then import table.

CREATE TABLE db_target.cloned_table 
SELECT * 
FROM db_source.source_table;

I'll put this answer up for anyone else looking for help.

If you don't have access to SSH then you can use PhpMyAdmin.

Simply:

1. browse to the table you want to move
2. Click the Operations tab
3. Use the MOVE or COPY to database function

If you come across privilege problems, you can temp grant a user Global permissions or add the same user to both databases.

exec('mysqldump --user=username --password="password" --host=hostname --database=database table1 table2 > databasedump.sql');

exec('mysql --user=username --password="password" --host=hostname --database=database < databasedump.sql');

insert into dest.table select * from orginal.table;

use <from database>

create table <to database.new name> as (select * from <table to copy>);

As it seems that nobody answered the initial question actually, here is my PHP script to backup a table from a remote MySQL server to a local MySQL server:

function backup_remote_table (
    $remote_db_host, $remote_login, $remote_password, $remote_db_name, $remote_table_name,
    $local_db_host, $local_login, $local_password, $local_db_name, $local_table_name
) {
    // Generating names with time stamps for local database and/or local table, if not available
    if ($local_table_name) {
        $applied_local_table_name = $local_table_name;
    } else {
        $applied_local_table_name = $remote_table_name;
    }
    if ($local_db_name) {
        $applied_local_db_name = $local_db_name;
        if (!$local_table_name) {
          $applied_local_table_name .= date_format(date_create(), '_Y_m_d_H_i_s');
        }
    } else {
        $applied_local_db_name = $remote_db_name . date_format(date_create(), '_Y_m_d_H_i_s');
    }

    // Local server connection
    $local_db_server = mysql_connect($local_db_host, $local_login, $local_password);
    $local_db_server = mysql_query("CREATE DATABASE IF NOT EXISTS " . $applied_local_db_name, $local_db_server);
    mysql_select_db($applied_local_db_name, $local_db_server);

    // Remote server connection
    $remote_db_server = mysql_connect($remote_db_host, $remote_login, $remote_password);
    mysql_select_db($remote_db_name, $remote_db_server);

    // Getting remote table data
    $result_remote_table_info = mysql_query("SHOW CREATE TABLE " . $remote_table_name, $remote_db_server);
    $remote_table_info = mysql_fetch_array($result_remote_table_info);
    $remote_table_description = substr($remote_table_info[1], 13);

    // Creating local table
    $sql_new_table = "CREATE TABLE IF NOT EXISTS " . $applied_local_db_name . "." . $remote_table_description;
    mysql_query($sql_new_table, $local_db_server);

    // Getting all records of remote table
    $result_remote_table_data = mysql_query("SELECT * FROM " . $table_name, $remote_db_server);
    while ($remote_table_row = mysql_fetch_array($result_remote_table_data, MYSQL_ASSOC)){
        // Browsing records of remote table
        $sql_new_row = "INSERT INTO $applied_local_db_name.$applied_local_table_name (".implode(", ",array_keys($remote_table_row)).") VALUES (";
        $extra_sql = "";
        foreach (array_values($remote_table_row) as $value) {
            if ($extra_sql != "") {
                $extra_sql .= ",";
            }
            $extra_sql .= "'";
            $extra_sql .= mysql_real_escape_string($value);
            $extra_sql .= "'";
        }
        $sql_new_row .= $extra_sql . ")";
        // Adding record to local table
        $result_new_table_row = mysql_query($sql_new_row, $local_db_server);
    }
    mysql_free_result($result_remote_table_data);

    return;
}

The solution above is not mine, I got it here, with minor changes.