pt-table-sync over bad connections

  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-table-sync over bad connections

    Dear All,
    I'm working for an NGO in Nepal (INF www.inf.org) and we are using pt-table-sync to synchronize a MySQL database across our centers in different parts of the country. We do not need live syncing, once a day is enough.
    So I wrote a wrapper around pt-table-sync that runs on the main server and syncs (bidirectional) table by table on all the servers in the small offices around the country. We are using VPN to connect to every office.
    It works fine as long the Internet connection is OK. But here in Nepal we are suffering often from bad internet connections. So 10-25% packet loss is nothing unusual. And then we run into problems like "DBD::mysql::db commit failed: Lost connection to MySQL server during query at /usr/bin/pt-table-sync line 10925"

    Could somebody give me advise how to tune pt-table-sync and the MySQL server so the connection becomes more robust and could cope with more packet loss?

  • #2
    You may be interested in this article:
    In short, you may need to increase net_read_timeout and net_write_timeout.

    Btw, why do you need so regular pt-table-sync? Isn't MySQL replication doing what is supposed to do?


    • #3
      Thank for the link, I will have a look.
      It might be the MySQL replication is a better solution but when I had to deicide I looked into it and picked pt-table-sync. But I'm not an expert so any advice will be appreciated.
      So the background:
      We have a database that holds all records of our staff members. The data must be read and written in different parts of the country.
      One central server does not work for Nepal because the internet connection often breaks. But we would like to keep every offices working even if they don't have an internet/VPN.
      So that mean I need a master-master replication and that sounded complicated in MySQL and I read some post that did discourage a master-master set-up.
      The two other sync solutions I found was the bidirectional sync of pt-table-sync and SQLyog Job Agent (SJA) from http://www.webyog.com/en/ but the two-way sync of SQLyog was just a 2 x one-way-sync. Does not help me because in a conflict the last server will win. And the pt-table-sync is much easier to script. so I wrote a Perl script around it that does the two-way-sync quite stable till now, detects conflicts, decides who wins and sends Emails to every party of the conflict and it also syncs files (images and documents like CVs are stored as files).
      This script runs once a day and syncs every server that is available at that moment. If one is missing it just skips it and syncs the other.

      some time ago I wrote a post about the https://individualit.wordpress.com/2...pt-table-sync/
      I have to update the post because my script does more nowadays that at that time.

      If there is better, more elegant, easier or saver way to solve our problem I would appreciate a tip.