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 records 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.



Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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:

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

Method 2

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

Method 3

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

It all can be done in a single command.

Method 4

$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);
    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

Method 5

One liner with different servers

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

Method 6

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

Method 7

CREATE TABLE db_target.cloned_table 
FROM db_source.source_table;

Method 8

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.


  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.

Method 9

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');

Method 10

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

Method 11

use <from database>

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

Method 12

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);


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

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x