Friday, July 20, 2007

Replication Syncing MASTER_POS_WAIT

  • MASTER_POS_WAIT


  • This function is useful for control of master/slave synchronization. It blocks until the slave has read and applied all updates up to the specified position in the master log. The return value is the number of log events the slave had to wait for to advance to the specified position. The function returns NULL if the slave SQL thread is not started, the slave's master information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if the timeout has been exceeded. If the slave SQL thread stops while MASTER_POS_WAIT() is waiting, the function returns NULL. If the slave is past the specified position, the function returns immediately.

    If a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.



    This is great for applications which auto promote masters, but one has to account for race conditions.


    For example, dbmaster1 and dbmaster2 are in bi-directional replication displayed below.


    dbmaster1 <----> dbmaster2


    dbmaster2 has slaves called dbslaves1 and dbslaves2. We want to replace dbmaster2 with dbslave1.

    On dbmaster1 issue:

    FLUSH LOGS;


    On dbmaster2 stop external writes to it and execute

    SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4); SLAVE STOP;


    db3>show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State:
    Master_Host: dbmaster1
    Master_User: replication
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: dbmaster1-bin.000002
    Read_Master_Log_Pos: 3696603
    Relay_Log_File: dbslave-relay.000005
    Relay_Log_Pos: 597702176
    Relay_Master_Log_File: dbmaster1-bin.000002
    Slave_IO_Running: No
    Slave_SQL_Running: No
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 805
    Relay_Log_Space: 601376092
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: NULL




    Notice Exec_Master_Log_Pos is at POS 805. This is a small window of events that passed. So, make sure to account for it.


    Now on dbmaster2 issue:

    FLUSH LOGS

    Do the same steps for dbslave1-2 with MASTER_WAIT_POS but for the position on dbmaster2 not dbmaster1.

    Make dbslave1 a master by adding

    log-bin to my.cnf

    Issue CHANGE MASTER TO on dbslave1 to dbmaster1 // this moves dbslave1 to dbmaster1

    CHANGE MASTER TO MASTER_HOST='dbmaster1', MASTER_LOG_POS=805, MASTER_LOG_FILE='dbmaster1.000002';

    Issue CHANGE MASTER TO on dbslave2 to dbslave1 // this moves dbslave2 to dbslave1

    CHANGE MASTER TO MASTER_HOST='dbslave1', MASTER_LOG_POS=4, MASTER_LOG_FILE='dbslave1-bin.000001';


    Enable writes on dbslave1

    done.

    I wrote an application that does this all for me, using IPC, fork, db connections and the "algorithm" above. If your interested in it. I'll post it here.

    5 comments:

    Alexey Kovyrin said...

    I think you definitely need to take a look at MMM.

    pakfak said...

    Thanks a lot fro this statement it helps me a lot.
    Do you have an idea how to insert the show master status returns into a table?
    I thoght that the whole information with bin-log files names and positions are kept into some system tables in mysql but i was wrong. Any idea?

    pakfak said...

    Thanks a lot fro this statement it helps me a lot.
    Do you have an idea how to insert the show master status returns into a table?
    I thoght that the whole information with bin-log files names and positions are kept into some system tables in mysql but i was wrong. Any idea?

    sexy said...

    情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,按摩棒,跳蛋,充氣娃娃,情境坊歡愉用品,情趣用品,情人節禮物,情惑用品性易購

    免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

    A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX

    情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


    情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

    av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

    情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

    sex said...

    徵信社
    情趣用品
    情趣用品
    情趣用品
    情趣
    情趣


    SM
    充氣娃娃


    SM
    性感睡衣


    免費視訊聊天室
    aio交友愛情館
    愛情公寓
    情色貼圖
    情色文學
    情色小說
    情色電影
    情色論壇
    成人論壇
    辣妹視訊
    視訊聊天室
    情色視訊
    免費視訊

    免費視訊聊天
    視訊交友網
    視訊聊天室
    視訊美女
    視訊交友
    ut聊天室
    聊天室
    豆豆聊天室
    尋夢園聊天室
    聊天室尋夢園
    080聊天室
    080苗栗人聊天室
    女同志聊天室

    上班族聊天室
    小高聊天室






    免費視訊聊天
    免費視訊聊天室
    免費視訊
    ut聊天室
    聊天室
    豆豆聊天室 聊天室尋夢園
    影音視訊聊天室


    色情遊戲
    寄情築園小遊戲
    情人視訊網
    辣妹視訊
    情色交友

    成人論壇
    情色論壇
    愛情公寓
    情色
    色情聊天室
    色情小說
    做愛
    做愛影片
    性愛


    一葉情貼圖片區
    情趣用品


    辣妹視訊
    美女視訊
    視訊交友網
    視訊聊天室
    視訊交友
    視訊美女