I have an often offline dev db, and would like to be able to push updates to my live server in the most efficient way possible. I would prefer not to update tens of thousands of rows that have not changed, simply to update data in a few rows.
One strategy would be to have a last_updated column, and then insert, or update on duplicate key - but only if data newer than that currently in db.
Another potential solution would be to have a separate table to keep track of the irregular uploads, and then only export those rows for iodku that have changed since last upload. This uploads far less data (entire db is maybe 200-400MB), but also requires the added layer of complexity.
Set up replication, with scripts to turn off replication except when I want to update. This seems like the worst of the three from what I understand, because I am not in clear control of how long I need replication to run, and I need to open mariadb port, unlike pushing out an update via ssh.
Is one of these the typical solution for the dev box to prod workflow, or is there a better way than any of these to push data to a server? I don't understand if there is a syntax to do 1), if it is the best solution. Thanks.