0

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.

3
  • Maybe a TRIGGER would help with capturing the changes.
    – Rick James
    Aug 17, 2022 at 4:45
  • Yes, the plan would be to have last_update to trigger on update, but the problem I am having is the strategy to push that info to the server.
    – sgc
    Aug 17, 2022 at 5:07
  • Replication could run over a port forwarded ssh tunnel which mitigates some aspects there. However like all multi-write enabled servers, a conflict resolution strategy may/may not be suited to plain replication.
    – danblack
    Aug 22, 2022 at 1:49

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.