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

Question

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.

Thanks

score 60 · Answer 1 · answered Jun 20 '13 at 10:47

60

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.

answered Jun 20 '13 at 10:47

Radu Damian

1,051
10
8

4

This should be the accepted answer. Much easier than mysqldump command-line. – Patrick Moore Nov 07 '17 at 19:26

score 37 · Accepted Answer · answered Oct 14 '10 at 11:28

37

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

answered Oct 14 '10 at 11:28

Pekka

442,112
142
972
1,088

8

Since I want to transfer only one table, I guess with a little bit mod this will work mysqldump -u user1 -ppassword1 --add-drop-table databasename tablename > dump.sql thank you – Ergec Oct 14 '10 at 11:35
If you use phpmyadmin, then it will be better. – gautamlakum Oct 14 '10 at 13:22
8

You can even pipe it through: `mysqldump --user=root --password=remote_password the_database_name | mysql --user=root --password=local_password the_database_name`; usually this may be done from server to server using SSH-port-forwarding: `ssh -f -N -L 3307:localhost:3306 nmmn` (nmmn is one of my SSH-shortcuts, use a server-name+port instead); @Ergec for multiple tables use the param `--tables table1 table2` – feeela Aug 30 '11 at 21:50
Personally, I would cat the file back into mysql – Daryl Gill May 06 '13 at 15:29
also noteworthy to add that there is no space between `-p` and the password. Some tend to think it is a typo as well as if your password contains any special characters you will need to accent them with `\\` – Cesar Bielich Aug 07 '20 at 22:30

score 36 · Answer 3 · answered Nov 11 '10 at 10:29

36

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

It all can be done in a single command.

answered Nov 11 '10 at 10:29

shantanuo

31,689
78
245
403

score 7 · Answer 4 · answered May 14 '14 at 14:07

$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

score 7 · Answer 5 · answered Mar 06 '17 at 11:18

7

One liner with different servers

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

answered Mar 06 '17 at 11:18

Juergen Schulze

1,515
21
29

1

Optional you can also add an alternative port, e.q. `-P 3309` – phse Feb 25 '20 at 11:41

score 5 · Answer 6 · answered Oct 15 '13 at 11:24

5

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

answered Oct 15 '13 at 11:24

Fury

4,643
5
50
80

Simple and straight. I wonder why no one found this useful as I'm the first one to upvote it. – Faraz Ahmad Apr 01 '20 at 15:13
This doesn't copy the keys over. – arnoldbird Apr 08 '20 at 03:46
OP says each DB has different login+password, so can't use this. – Marki555 Oct 20 '22 at 13:23

score 5 · Answer 7 · answered Oct 14 '10 at 13:22

5

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

answered Oct 14 '10 at 13:22

gautamlakum

11,815
23
67
90

1

IIRC, the inbuilt functionality will work only if the same user has access to both databases. – Pekka Oct 14 '10 at 13:52

score 3 · Answer 8 · answered Oct 05 '13 at 14:23

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:

browse to the table you want to move
Click the Operations tab
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.

score 1 · Answer 9 · answered Nov 14 '20 at 16:11

1

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

answered Nov 14 '20 at 16:11

D.Y.

149
2
7

score 0 · Answer 10 · edited May 06 '13 at 15:25

0

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

edited May 06 '13 at 15:25

unexpectedvalue

6,079
3
38
62

answered May 06 '13 at 15:04

Peter Eskandar

9
2

score 0 · Answer 11 · edited Jan 31 '15 at 10:30

0

use <from database>

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

edited Jan 31 '15 at 10:30

e-sushi

13,786
10
38
57

answered Jul 18 '13 at 21:46

user2597353

1

1

Welcome to stackoverflow. I think your answer got messed up. Please try to reformat it marking it as code. – Simon H Jul 18 '13 at 22:16
1

As it stands this answer makes little sense. Please add some explanaation, and correct the snippet you've posted – Jul 18 '13 at 22:18

Kostiantyn Ivashchenko · Answer 12 · 2019-02-25T02:25:51.257

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.

It's been deprecated for more than 10 years. It hasn't even existed since PHP 5, last version of that was released more than 5 years ago. — miken32, Jan 28 '20 at 20:56
mysql_ functions have been removed in PHP 7, released Dec. 2015. There is no excuse for using them in 2020. — ericek111, Feb 10 '20 at 13:31
@ericek111, if a very old intranet web application needs only minor improvements -- should it be completely rewritten? — Kostiantyn Ivashchenko, Feb 19 '20 at 13:21
Do your applications still support Windows 95? Why would someone post a piece of code that has been defunct for half a decade? Even if for very old intranet web application, there should be a disclaimer. — ericek111, Feb 19 '20 at 13:51
@ericek111, nobody forces you to read any solution for any problem, if the problem does not exist in your opinion. While these solutions might be useful for those developers whose customers ask for the support of old applications. — Kostiantyn Ivashchenko, Feb 19 '20 at 16:13

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

12 Answers12

Linked

Related