I need to synchronize database on server with database of local server upon active internet connection.

For its implementation I execute mysqlbinlog utility to read transaction logs and write the transaction queries into a sql file

Thereafter I execute sql file on database server to apply transaction updates on database on server

But I need to fetch only latest transaction logs within specific time interval across multiple binary log files . Please assist me in its implementation

  • "fetch only latest transaction logs within specific time interval " How fine this is? Are you saying you need to filter transactions and apply only some of them? – mysql_user Nov 7 '16 at 6:19
  • @mysql_user Yes I need to apply transactions within a specific time interval using mysqlbinlog utility – Rubin Porwal Nov 7 '16 at 6:36
  • sorry, but what is Your problem? You are download few files for example 1-10 apply them. on next time You fetch files 11-20 and apply them – a_vlad Nov 7 '16 at 8:02
  • @a_vlad I just need to fetch latest transaction logs within specific time interval and execute those transaction statements across database – Rubin Porwal Nov 7 '16 at 9:22
  • What are You mean - latest? Normally for Syncronysation - You need all from last apply, or You for unclear reason want to download Single Latest file? – a_vlad Nov 7 '16 at 9:30

As per your requirement you're suggesting leads to this solution:

mysqlbinlog --start-datetime=<START_DATETIME> --stop-datetime=<STOP_DATETIME> binlog.xxx > binlog.xxx.sql

Though this is very much prone to corrupt your data and/or introduce inconsistency! Not recommended!

There can be multiple solutions to one problem, if you could share the scenario you would get a better solution.

For eg:

  • If you're planning to make application specific changes on development (local) server and want to push that to live, use version control to manage the SQLs and exec to live.

  • If it is only specific database, you can setup replication and only move replicate changes to that db (or even tables).

|improve this answer|||||
  • Thanks to answer my question .But I need to perform two way synchronization of mysql database -(a) online to offline (b) offline to online. – Rubin Porwal Nov 9 '16 at 7:02
  • Well you can use the same method both sides. Consider the risks. – mysql_user Nov 9 '16 at 13:16
  • How do I read logs from server .Logs can be easily read from local server – Rubin Porwal Nov 9 '16 at 13:30
  • Again this two way sink has multiple approaches. You may setup process to ship binlogs to local. Generate dml on prod and ship. Setup replication. Restore from fresh backup or use incremental. All in all there are risks to consider. – mysql_user Nov 9 '16 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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