MySQL Until_log_file and Until_log_pos

How to use until option in MySQL Replication.
 
Some times we need updates in replication for until binlog file and position, during that time we can use this option in Slave server.

Here I gave how to do it.



 
The above fig. shows there is no until condition in slave status.

1.Give the change master query.

Change master to
     master_user='user name',
     master_host='host name',
     master_password='******',
     master_log_file='mysql-bin.000142',
     master_log_pos='562483'
     master_port='3306';

2.Dont use START SLAVE;
   first start the IO thread. START SLAVE IO_THREAD;

3.Now give the until condition while start the SQL thread,

   START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='mysql-bin.000146',
   MASTER_LOG_POS=178741931;

   or


   START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE='mysql-bin.******',
   RELAY_LOG_POS=*****;


4.Now See, SLAVE STATUS\G



 Here we can see the until condition is MASTER and see the until_log_file and until_log_pos that what are the values we gave when we started the SQL thread.The replication will be happen until the given values.

Until_Condition has these values:

• None if no UNTIL clause was specified
• Master if the slave is reading until a given position in the master's binary log
• Relay if the slave is reading until a given position in its relay log

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/start-slave.html

DIAGRAMMATIC REPRESENTATION OF MySQL REPLICATION PROCESS

All of them know how does the MySQL replication works.But here I gave a diagrammatic representation of MySQL Replication It looks something different to you at first sight.

Replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database.In replication two logs are used to read the events in master server and in slave server.

Binlog :

 

In master server the changes happen in every database will be recorded in binlog file if it is enabled and by default it is disable.The binary log file stores the data that replication slave will read when its get connected to master server.

Relay log :
 

The relay log is like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.It records the events from master using a slave thread called IO thread.

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 use to read the binlog events from master to slave relay log when it gets connected to master server.We can see that which file is reading from master to slave by seeing variables like Master_Log_File and Read_Master_Log_Pos.

SQL Thread :

It reads the events from relay log to do the changes in slave databases and keep it up to date the databases like master databases.Once the event processed by the SQL thread the relay log will delete the processed event.We can read the currents process in sql thread by seeing Relay_Master_Log_File and Exec_Master_Log_Pos.

Master_Slave :

Here In the above figure the arrows indicates the process step by step in replication process.

Thanks...