Automate MySQL DB to update tables on remote server from localhost continuously

Question

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.

Automatically Dump and FTP the csv file(s) to remote web server/cPanel every 15min using PHP and Windows Task Schedular.
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

Have you tried something like this? digitalocean.com/community/tutorials/… — Difster
– Difster, Commented Aug 1, 2017 at 2: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
– Fahad Khan, Commented Aug 1, 2017 at 3:23
Once you have that set up, you shouldn't need to do anything else manually unless you have an infrastructure change. — Difster
– Difster, Commented Aug 1, 2017 at 3:25
Thanks, if this process will process DBs continuously then I will try to implement it now. — Fahad Khan
– Fahad Khan, Commented Aug 1, 2017 at 4:45

in.k · Accepted Answer · 2017-08-01 06:53:07Z

1

I think you should do the first part as you mentioned

Automatically Dump and FTP the csv file(s) to remote web server/cPanel every 15min using PHP and Windows Task Schedular.
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.

answered Aug 1, 2017 at 6:53

in.k

1022 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automate MySQL DB to update tables on remote server from localhost continuously

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related