Friday 27 December 2013

Replication Architecture



Architecture:

 


Definition:
                   
                      Replication means that data written on a master MySQL will be send to separate server and executed there. In other words ,The process of creating and managing duplicate versions of a database. Replication not only copies a database but also synchronizes a set of replicas so that changes made to one replica are reflected in all the others.


Applications:
  • backups
  • spread read access on multiple servers for scalability
  • disaster recovery

The architecture shows the graphical representation of how a master slave replication is set up.
 It consists of  logs, threads and filters .

Logs:
         There are two types of logs in replication,

                                                (i)  Binary Log   
                                               (ii)  Relay Log



Binary Log:

                          It provides a record of data changes to be send to slave server.It contains events that describe database changes such as table creation operations or changes 
to table data.It also contains statements that might have not made changes.There are two types of binlog formats,

                                (i)    Statement-based
                                (ii)   Row-based
                                (iii)  Mixed

setting binlog,

  mysql> set global binlog_format='statement' ;

Relay Log:
         
                            The relay log is a set of log files created by a slave during replication.
It's the same format as the binary log, containing a record of events that affect the data or structure.


Threads:
        
              Threads are use to connect master to slave and do changes in slave databases.There are two types of slave threads at here,

                                                 (i)  IO Thread
                                                (ii)  SQL Thread 

IO Thread:

               It opens an ordinary client connection to the master,then starts a special binlog dump process. The binlog dump process reads events from the master  's binary log.After getting the data from the binary log it writes the events to the replica's relay log.

SQL Thread:

                It reads and replays the events from the relay log and do the changes in the slave database, thus updating the replica's data to match the master's data
.

Replication Filters:
                                                  Replication filtering option  lets us to replicate just a part of server's data.There are two kinds of replication filters:

          (i)  That filter events out of binary log on the master,
          (ii) That  filter events coming from the relay log on the replica.


Filter types,

                   --do-db


                 --ignore-db





Master,
binlog-do-db 
binlog-ignore-db

Slave,
   replicate-ignore-db
   replicate-do-db
   replicate-do-table
   replicate-ignore-table
   replicate-wild-do-table
   replicate-wild-ignore-table