I have been Googling my problem but haven’t been able to come up with anything; here’s the situation:
- I have a PHP / MySQL website hosted with 1and1.com.
- I also have a Python program that runs on a desktop computer I own.
- I want users to be able to submit files and modify personal information on the website and then have that information transferred to the Python program on my desktop computer (so the desktop will basically have a copy of data being stored in the MySQL database).
- I want to minimize the number of SQL queries, but I also want changes to be reflected in the desktop Python program within a minute or so.
I understand Python can query the MySQL database, but what is an efficient way to have it update its copy of the info stored on the MySQL database?
What I’m thinking is that I can have an “updates_to_db” table that records every change made to the main db, and then have the Python program use that to decide which files / db rows it needs to update.
Or is there a better way?
I’d suggest that you look up a Master-Slave mirroring between your offsite MySQL database and one that you can host locally(its not that hard to host one on your desktop).
Why?
Instead of asking python to ask your offsite database every now and then if there are new changes, why not have that database update itself in a local database that you own. That should minimize the number of queries running around and if your python program is going to do something with the data you’re pulling, you can safely just query from your local database without worries of accidentally borking something or going over your monthly bandwidth allocation
You can checkout a guide of that here: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
if you have more questions about master-slave database mirroring, you should checkout https://dba.stackexchange.com/