CODEWITHSUNDEEP
phpmysqlphpmyadmindatabase-migrationdatabase-mirroring
Fahad Khan
Fahad Khan

Reputation: 67

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

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

Upvotes: 2

Views: 1374

Answers (1)

in.k
in.k

Reputation: 102

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.

Upvotes: 1

Related Questions