0

Hi i'm planning to use Mysql replication for a distributed system, the topology will be:

Main DB (Master) --> Bridge DB (Master for Target DB, Slave of Main DB) --> Target DB (Slave of Bridge DB)

I will use ssh tunnels for connect the databases.

I know that replication could be asynchronously, by my concern is i want to know if Mysql replicate incrementally, the main db will be in a site where the internet connection is limited and probably will connect via mobile network only some days of the week, so i want to be sure that mysql does not send ALL data every time the databases synchronize.

Thanks in advance.

0

i want to be sure that mysql does not send ALL data every time the databases synchronize

In that case, you are in luck. MySQL native replication always, only, ever sends "incremental" updates. In fact, it doesn't have a way to automatically send the whole thing. To initially configure replication, you have to manually copy a snapshot of the data from master to slave. After that, only the changes are transferred, in an ordered stream of change events referred to as the binary log, sometimes also called the "binlog."

Replication is based on the master server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log. The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically, SELECT statements are not recorded because they modify neither database structure nor content.

Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the master.

http://dev.mysql.com/doc/refman/5.7/en/replication-implementation.html

  • One more note: The Slave will timeout. In your situation, raise slave_net_timeout to something more than 3600 seconds and/or net_retry_count. 604800 would give you a week. Read the manual to decide what makes sense for you. (Maybe try every 4 hours for 2 weeks?) – Rick James Oct 18 '16 at 18:33

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.