2

I am a newbie and hence need some advice.

My problem is similar to this question but I couldn't resolve the problem yet.

Problem: I am processing data internally and generating 8-10 tables. I want to replicate those tables (in 2 different schema) to remote server automatically and continuously every 15 minutes.

I went toward AWS solution using EC2 DMS RDS but got stuck there and couldn't resolve the problem after spending two days (here is my other question if it helps to understand the background).

Proposed Solution: By doing research and reading this post, this post, this post and this post, I have come up to a different solution.

  1. Automatically Dump and FTP the csv file(s) to remote web server/cPanel every 15min using PHP and Windows Task Schedular.
  2. Automatically read those csv file(s) and update records on remote DB using PHP script and with some sort of task schedular on web server? (is it possible?).

Question/Advice: Is my above approach correct or do I need to find another or better solution to do this? If this approach is correct then any kind of related help would be highly appreciated.

Please note: I couldn't find any solution after spending hours on research on and off S.O. I'm no natural born coder, I find solutions to what I need to achieve

Fahad Khan
  • 67
  • 1
  • 9
  • Have you tried something like this? https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql – Difster Aug 01 '17 at 02:10
  • @Difster I came across this but haven't tried it yet. May be in my understanding this is manual replication which can't be automated? – Fahad Khan Aug 01 '17 at 03:23
  • Once you have that set up, you shouldn't need to do anything else manually unless you have an infrastructure change. – Difster Aug 01 '17 at 03:25
  • Thanks, if this process will process DBs continuously then I will try to implement it now. – Fahad Khan Aug 01 '17 at 04:45

1 Answers1

1

I think you should do the first part as you mentioned

  1. Automatically Dump and FTP the csv file(s) to remote web server/cPanel every 15min using PHP and Windows Task Schedular.
  2. Automatically read those csv file(s) and update records on remote DB using PHP script and with some sort of task schedular on web server? (is it possible?).

After that as you mentioned that it is cpanel, you can setup cronjob to run this php file in your step 2. Setup php file to send email once the database is updated for your records. The email should be setup for 2 outcomes. One message if there was an error updating database and one if database was updated successfully.

Cronjob is quite useful tool on cpanel.

in.k
  • 102
  • 2
  • 12