1

I have two lamp stacks that are remote to one another. I have to send the results of a query from stack one to a table on stack 2. I'm not sure what the best method is to use.

I have considered setting up an API but am not sure if this is the right application for an API. I have considered, having stack one export a sql dump and the other server download then import, but this feels very insecure. Any advice would be greatly appreciated.

Travis
  • 36
  • 6
  • Same DB systems? Maybe replicate the table(s) to the other server? – user3783243 Sep 03 '19 at 03:23
  • Yes, both servers are running MySQL. I will do some research on replicating a table between two MySQL servers, but for clarification on your comment, is there a particular method you are recommending? – Travis Sep 03 '19 at 03:28
  • I've used full DB replication, you can do specific tables though. https://dev.mysql.com/doc/refman/5.7/en/replication.html https://stackoverflow.com/questions/2962721/replicate-a-single-table – user3783243 Sep 03 '19 at 03:32
  • This is a great suggestion, I just wish there was a way to only replicate the results of a query, as the table I am pulling the data from is huge. The data I need is about 5-10% of the data that exists on the table. – Travis Sep 03 '19 at 03:39
  • if not limited with lamp stack, shared redis or memcached service would be nice. – James Li Sep 03 '19 at 06:22
  • maybe you can store the query result in a separate table as cache , than replicate this cache table only. – James Li Sep 03 '19 at 06:24

1 Answers1

0

I ended up solving this problem with rsync. I have the first lamp stack dump the data to a file then open an rsync connection to the second server and have a cron running the import 2 hours after the rsync is set to connect. The cron also unlinks the file once the import is complete. Not perfect but the job is complete and I feel I didn't open up any security issues.

Travis
  • 36
  • 6