Home>

There are database A and database B on the same server.
I want to make the contents of database B exactly the same as database A on a regular basis (here every other week). (A → B synchronization)
B → A synchronization is not required.
There may be changes in B only, but there is no problem even if all changes disappear at the timing of synchronization.

Thoughts

I thought it would be possible to use cron every other week, mysqldump with the –add-drop-table option, and then restore.
However, I feel that this method is too primitive. In addition, since other than the difference is also synchronized, I think that the CPU will be used quite wastefully.

I think it's a common requirement, but I couldn't find any information that would be a good idea even if I looked it up. You can use tools and scripts.

  • Answer # 1

    As long as you see, the difference is acceptable for a week, right?
    If it is such a loose operating condition, I think that you can dump and restore.
    (Although it can be accepted because the writing frequency is low and the difference is small even in one week, that is, it can be regarded that there are many updates equivalent to the same data as a dump)

    Separately, as a requirement ↓ I am interested here.

      

    There may be changes only in B, but there is no problem even if all changes disappear at the timing of synchronization.

    Would you like to synchronize (same) or allow different? Is there a guarantee that a row in a table that has been changed only on B is also changed on the A side at the timing of synchronization? If not, applying only the difference on the A side will leave the change unattended. When will it be changed, which one will come? Is it possible to allow the difference so long term? If it is unacceptable, the B side rollback work is also required. If you think about it, I think that dumping and restoring are all right, so I answered the previous question.

  • Answer # 2

    If you timestamp each record with a modified column
    I think it ’s not difficult to get the difference.