Java (10) MySQL (6) linux (6) INNODB_LOCKS (2) INNODB_TRX (2) J2EE (2) bash (2) exclusive (2) innoDB (2) mountrakis (2) processlist (2) transactions (2) x-locks (2) xlocks (2) ERROR 1045 (28000) (1) Jboss (1) Jboss5.0 (1) Jconsole (1) Master (1) Slave (1) Synchronization (1) VPN (1) binlogs (1) command line (1) cook-book (1) cookbook (1) date (1) for (1) handy commands (1) loop (1) michael mountrakis (1) replication (1) sh (1) shell (1) unix epoch (1) unix timestamp (1) while (1)
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts

Monday, 11 November 2013

MySQL Replication implementation details

The previous and current week I am dealing with MySQL replication.

Currently, I have a master-slave setup, with slave operating in readonly mode.

According to MySQL manual page, replication in slave is implemented using two threads:
  • Thread I/O is polling the Master for binlog updates: this thread polls master for any updates written to its binlog. If master has updates, I/O thread puts them in a queue.
  • Thread SQL reads the updates queue and executes them in the slave.
To assist my study, I created the following pictures illustrating the MySQL replication threads operations. Here we have to state that those operations are for the simple NON-Clustered replication as described above.  
This picture describes Slave I/O replication thread:

And the following one describes the operations of the Slave SQL replication thread:

The basic parameters for replication can be viewed from the MySQL manual here.