How can I insert MySQL remote database table into local database table?

Question

Remote Server Database (IP: 192.168.1.19)

Database Name : RemoteDB

Table Name : RemoteTable

Local Server Database (IP: 192.168.1.35)

Database Name : LocalDB

Table Name : LocalTable

MySQL Query

INSERT INTO LocalDB.LocalTable SELECT * FROM RemoteDB.RemoteTable

But, This query is not executing, Because of different database connection.

How can I execute this query from local server (IP: 192.168.1.35)? Or any linux shell command?

Use `mysqldump` to dump a schema (create new schema and copy your table to that schema if you dont want to dump everything). Then you can work on the same database. — SOFe, Nov 03 '16 at 09:17
why would you do this? just create a dump, and insert this on your local system — Ann-Sophie Angermüller, Nov 03 '16 at 09:18
Because, After every 30 minutes I need `RemoteDB.RemoteTable` data into `LocalDB.LocalTable` @LuciaAngermüller — Sumon Sarker, Nov 03 '16 at 09:20
@SumonSarker i still got no idea why you need to do this? there should be no reason to do sth like this — Ann-Sophie Angermüller, Nov 03 '16 at 09:23
Since you tagged this question [php], you might just want to download SELECT * and upload them with INSERT directly using PHP, since you know the database structure. — SOFe, Nov 03 '16 at 09:23
Because 2 different projects using same table on different host. @LuciaAngermüller — Sumon Sarker, Nov 03 '16 at 09:27
I don't want to fetch result from Remote Table into a PHP variable and Save it to Local Table. I just want to Copy it directly via SQL Query. Because `RemoteDB.RemoteTable` has huge number of rows. Minimum `200MB`. Your solution is not efficient. @Aniket — Sumon Sarker, Nov 03 '16 at 09:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/127259/discussion-between-sumon-sarker-and-aniket). — Sumon Sarker, Nov 03 '16 at 10:08

score 3 · Accepted Answer · answered Nov 03 '16 at 09:24

3

mysqldump -uRemoteusername -pRemotepassword -h192.168.1.19 RemoteDB RemoteTable  --set-gtid-purged=OFF  | mysql -h192.168.1.35 -uLocalname -pLocalpassword   LocalDB

answered Nov 03 '16 at 09:24

chenyb999

139
4

I fixed the remote server configuration. And now Your answer is working for me. :) Thankyou! – Sumon Sarker Nov 03 '16 at 11:23

score 3 · Answer 2 · answered Nov 04 '16 at 17:50

I converted the approved answer for CronJob to my PHP script as below -

Configurations :

$remoteDbUser   = '***';
$remoteDbPass   = '***';
$remoteDbHost   = '192.168.1.19';
$remoteDb       = 'RemoteDB';
#$remoteDbTable = '';
$localDbHost    = '192.168.1.35';
$localDbUser    = '***';
$localDbPass    = '***';
$localDb        = 'LocalDB';

Synchronize function :

/*Passing `$remoteDbTable` name as function argument, Because I have many Remote DB table to synchronize with my Local DB table*/
function RemoteDbSynchronze($remoteDbTable='RemoteTable'){
  global $remoteDbUser,
    $remoteDbPass,
    $remoteDbHost,
    $remoteDb,
    $localDbHost,
    $localDbUser,
    $localDbPass,
    $localDb;
  $sql = sprintf(
    "mysqldump -u%s -p%s -h%s %s %s  --set-gtid-purged=OFF  | mysql -h%s -u%s -p%s %s",
    $remoteDbUser,
    $remoteDbPass,
    $remoteDbHost,
    $remoteDb,
    $remoteDbTable,
    $localDbHost,
    $localDbUser,
    $localDbPass,
    $localDb
  );
  shell_exec($sql);
}

Calling function from Cron script:

RemoteDbSynchronze('RemoteTable');
RemoteDbSynchronze('AnotherRemoteTable1');
RemoteDbSynchronze('AnotherRemoteTable2');
..........................................
RemoteDbSynchronze('AnotherRemoteTableN');

More details about sprintf , shell_exec and Cron

thank bro for sharing. it might be helfull for me or others sometimes later — plonknimbuzz, Nov 05 '16 at 14:33

score 2 · Answer 3 · edited May 23 '17 at 11:54

2

your question is to insert data from different database using php,isnt?

if you want to synconize them you can read this How to keep two MySQL databases in sync?

insert db using php from multiple db is simple

$conn1= mysqli_connect($host1,$user1,$pass1);
mysqli_select_db($db1,$conn1);
$conn2= mysqli_connect($host2,$user2,$pass2);
mysqli_select_db($db2,$conn2);

$query=mysql_query("SELECT id,name from tbl_user", $conn1)
while ($row = mysqli_fetch_row($query))
 {
    mysqli_query("insert into tbl_user (id,name) values ('".$row[0]."', '".$row[1]."',$conn2);
 }

edited May 23 '17 at 11:54

Community

1
1

answered Nov 03 '16 at 10:55

plonknimbuzz

2,594
2
19
31

I don't want 2 different connection instances. Because remote table have huge number of data. Minimum `6,000,000 rows X 120 columns` :( – Sumon Sarker Nov 03 '16 at 11:02
you want to dump or insert? dump (target table must be dropped firstif exists). insert(target table must be exist) – plonknimbuzz Nov 03 '16 at 11:08
1

if your data as big as you said. you must use desktop apps. dont use php. but we still can hack this with limitation select than refresh it with javascript. or maybe using cronjob. (i just brainstorming, because inever doing that using web apps) – plonknimbuzz Nov 03 '16 at 11:15
i just found this. maybe can help you http://wesbos.com/import-large-mysql-database-bigdump-php/ – plonknimbuzz Nov 03 '16 at 11:16
Thank you! But I don't want like this. My problem is now solved! – Sumon Sarker Nov 03 '16 at 16:55
Can you anwer by answer yourself thread question please? Just share with us. It can be help aomeone else who have same problem with you – plonknimbuzz Nov 04 '16 at 08:30
Ok, I will post my solution. Thank you @plonknimbuzz – Sumon Sarker Nov 04 '16 at 16:10
I posted my solution. :) You can review it @plonknimbuzz – Sumon Sarker Nov 04 '16 at 17:51
What about if $row[0] contains the single quote character. This will not work. – user2818066 Apr 25 '23 at 02:35

How can I insert MySQL remote database table into local database table?

3 Answers3