MariaDB - IODKU, but only if condition met

Question

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.

1. 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.

2. 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.

3. 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.

Answer 1

Since nobody proposed an answer after a week, I went with #2 since it is the lightest on the server and can be fully controlled manually.

I created a simple db uploads with a table with only 2 columns: db_name and last_update. All relevant tables in all dbs now have a last_update column that uses DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

My upload script exports all rows modified later than the appropriate db's last_update in uploads.uploads, and then sets a new last_update in uploads.uploads with NOW().

If one were doing table based updating, this could just be a table in the relevant db.

As long as my production queries are properly optimized with indices on the correct columns and avoiding SELECT * queries, it should not have much of a performance impact. My sql uploads are generally measured in kbs, so very light.