如题所述
ããå¤å¶ä¹æ以工ä½å¾çäºMySQLæ对æ°æ®åºçåæ´é½è®°å½å¨ binlogä¸ï¼ç¶å主åºæå®è¯»åºæ¥ï¼æ¾å°ä»åºä¸å»åºç¨ãå½ç¶binlog çç¨éä¸ä»
éäºæ¤ï¼æ¯å¦ PITRç
ããå¨5.1.4çæ¬ä»¥åï¼binlogæ ¼å¼åªè½æ¯ statement -based replication ,å¨ä»¥åççæ¬ä¸å¼å ¥äº row-based replication 以å mixed-based replicationã
ããä¸é¢æä¼ç®åçä»ç»ä¸ä¸SBRãRBRãMBR è¿ä¸ç§æ ¼å¼ä¸binlogæ¯å¦ä½ç»ç»çï¼æ´éè¦çæ¯å¨è¿ä¸ç§æ ¼å¼ä¸ï¼replicationæ¯å¦ä½è¿è¡å·¥ä½çãå½ç¶æ主è¦ä»ç» RBR模å¼ä¸çå¤å¶ï¼å 为RBR模å¼ä¸æ们çå¤å¶æ¯è®¤ä¸ºæ¯æå®å ¨çæ¹å¼ï¼å³ä½¿æ¯ä½¿ç¨MBRä¹ä¼æå¯è½è¸©å°åã
ããå¨MySQLä¸ï¼Binlogæ两类æ件ï¼ä¸ç±»æ件ç¨äºè®°å½æ°æ®åæ´ï¼ä¸ç±»æ件ç¨äºè®°å½binlog listã
ããBinlog listæä»¶å°±æ¯ ${HOSTNAME}-bin.index ï¼ç¨äºè®°å½å½åæåªäºbinlog
ããbinlogçæ°æ®æ件ååç±»ä¼¼äº ${HOSTNAME}-bin.00001,è¿ç±»æ件æ¯æ们éç¹å ³æ³¨ç对象ã
ããå æ¥å¤§æ¦çççbinlog çæ件å 容ï¼
ããsvan-mac:mydata xiean$ mysqlbinlog -vvvv mysql-bin.000010
ãã/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
ãã/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
ããDELIMITER /*!*/;
ãã# at 4
ãã#151218 15:19:30 server id 5331 end_log_pos 123 CRC32 0xd483743a Start: binlog v 4, server v 5.7.9-log created 151218 15:19:30
ãã# Warning: this binlog is either in use or was not closed properly.
ããBINLOG '
ããgrNzVg/TFAAAdwAAAHsAAAABAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ããAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ããATp0g9Q=
ãã'/*!*/;
ãã# at 123
ãã#151218 15:19:30 server id 5331 end_log_pos 154 CRC32 0x622c3733 Previous-GTIDs
ãã# [empty]
ããSET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
ããDELIMITER ;
ãã# End of log file
ãã/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
ãã/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
ããæ½ä¸æ®µbinlogåºæ¥ççï¼
ãã# at 141
ãã#151218 15:19:30 server id 5331 end_log_pos 245
ããQuery thread_id=3350 exec_time=11 error_code=0
ããat 141
ããå½åäºä»¶å¨æ件ä¸çèµ·å§ä½ç½®ï¼åä½bytes
ãã#151218 15:19:30
ããå½åäºä»¶å¼å§æ¶é´
ããServer id
ããå½åäºä»¶å¨åªä¸ªServer æ§è¡ç
ããEnd_log_pos
ããä¸ä¸ä¸ªäºä»¶å¨æ件ä¸çå¼å§ä½ç½®ï¼å³å½åäºä»¶å 容为 4~end_log_pos-1èå´
ããexec_time
ããæ§è¡æè±è´¹çæ¶é´(å¦ææ¯Master )ï¼
ããerror_code
ããæ§è¡è¯¥äºä»¶æ¶çç»æ
ããæ们æBinlog æ件æåæ3个é¨ä»½
ããç®åæè¿° ï¼ï¼>
ãã{
ãã"header": "desc â¦",
ãã"event": "xxxxxxx",
ãã"footer": "log rotat"
ãã}
ãã第ä¸é¨ä»½ï¼ ç± 4-bytes å¼å§ï¼è¿4-bytes 表æå®æ¯ä¸ä¸ªMySQL binlog æ件ï¼ç±log_event.h è¿ä¸ªæ件常éï¼BINLOG_MAGIC (0xfe 0x62 0x69 0x6e = 0xfe 'b''i''n') 表示ï¼ï¼è¿ä¹å°±è½è§£éæ们ç第ä¸ä¸ªevent 为ä»ä¹ èµ·å§ä½ç½®æ¯ 4 äºåå äºã
ãã第äºé¨ä»½ï¼ ä¸ä¸ªä¸ä¸ªç event ï¼æ¯ä¸ªevent æ ¹æ®binlogçæ¬ä¸åï¼è§£éåºæ¥çå«ä¹ææä¸å(å¨MySQL 5.0ï¼ binlogçæ¬é½ä½¿ç¨çæ¯ V4çæ¬)
ãã第ä¸é¨ä»½ï¼ä¹å°±æ¯æ件æåeventç¨äºè®°å½log-rotation äºä»¶ï¼è¡¨æäºæ¥ä¸æ¥çæ¥å¿å°åå ¥åªä¸ªæ件ã
ããEvent æ¯binlog è®°å½çæå°åä½ï¼event çä¸ä¸çº§æ¯event group ï¼å¤å¶æè æ¢å¤çæ¶ååºäº event group æ¥éæ¾æ¥å¿ã对äºä¸ä¸ªç»æ¥è¯´ï¼è¦ä¹é½æ§è¡ï¼ è¦ä¹é½ä¸æ§è¡ãè对äºDMLæ¥è¯´ä¸ä¸ª event å å«å¤ä¸ªeventï¼è对äºDDL æ¥è¯´ï¼ä¸ä¸ªevent å°±æ¯ä¸ä¸ª event groupã对äºæ¯ä¸ªEventéè¾¹æåªäºä¸è¥¿ï¼ä»¥åevent group æ¯å¦ä½ååçæ们å°å¨ä¸é¢è®¨è®ºã
ãã触åå¨ï¼åå¨è¿ç¨ï¼å½æ°å¨MySQLéè¾¹æ¯å¦ä½å¤ççå¢ï¼
ãã触åå¨ï¼åå¨è¿ç¨ï¼å½æ° å¨å建çæ¶åé½ä¼è®°å½Binlog
ãã触åå¨å¨æ§è¡çæ¶å MasterãSlave é½ä¼è¢«è°ç¨ï¼Masterä¸ç触åå¨å°ä¼å¨Masterä¸è¢«è°ç¨ï¼Slaveä¸ç触åå¨å°ä¼å¨Slaveä¸è¢«è°ç¨ã
ããåå¨è¿ç¨å¨æ§è¡çæ¶åï¼Masterä¸ä¼è¢«è½¬åæå ·ä½çSQLè¯å¥åå¨å¨Binlogéè¾¹ï¼å æ¤å¨binlogéè¾¹æ¯çä¸å°ä»»ä½ call æ¥è°ç¨åå¨è¿ç¨çevent ã
ããå½æ°å¨æ§è¡çæ¶å并ä¸ä¼è¢«è§£ææä¸ºå ·ä½çè¯å¥ï¼ç¸åå½æ°æ§è¡æ¶å触åå¨æ¯è¾ç¸åï¼å¨Masterä¸åSlaveé½ä¼è¢«è°ç¨ã
ããä¸ç§æ¨¡å¼ä¸Binlog æ¯å¦ä½è®°å½ç
ããåºäºè¯å¥çå¤å¶ ï¼ æ¯MySQL 5.7.7 çæ¬ä»¥åé»è®¤é ç½®ï¼å®å°SQLè¯å¥ï¼èä¸æ¯å®é çæ°æ®ååï¼ä»ä¸»æå¡å¨å¤å¶å°ä»æå¡å¨ã
ããä¼ç¹ ï¼å¨æäºæ åµä¸ï¼æç»åå ¥æ¥å¿æ件çæ°æ®æ´å°ï¼ä¾å¦æ´æ°æè å é¤è®¸å¤è¡æ¶ã对äºåªå½±åå è¡æ°æ®çç®åè¯å¥ï¼åºäºè¡çå¤å¶å ç¨ç空é´æ´å°ã
ããç¼ºç¹ ï¼ æææ¾çæ¯å®ä¸æ¯æä¸ç¡®å®æ§çè¯å¥ï¼ä¾å¦å½åæ¶é´å½æ°ã
ããåºäºè¡çå¤å¶ ï¼ ä½¿ç¨å个ç表è¡è®°å½ååï¼èä¸æ¯è¯å¥ã主æå¡å¨å°æ¶æ¯ï¼ä¹å°±æ¯äºä»¶åå ¥äºè¿å¶æ¥å¿ï¼è¡¨ç¤ºå个表è¡çååãè¿ä¸å ¶ä»RDBMSä¸æ´å ä¼ ç»çå¤å¶æ¹å¼ç±»ä¼¼ã
ããä¼ç¹ï¼éè¦æ´å°çéå®ï¼æå³çè½å¤è¾¾å°æ´é«ç并åã
ãã缺ç¹ï¼å®ä¼äº§çæ´å¤éè¦è®°å½çæ°æ®ï¼å ç¨ç空é´å¤§ã
ããæ··å模å¼æ¥å¿ ï¼ å¯ä»¥æ ¹æ®è¦è®°å½çäºä»¶å®æ¶æ¹åäºè¿å¶æ¥å¿æ ¼å¼ã使ç¨æ··å模å¼å¤å¶æ¶ï¼é»è®¤éç¨åºäºè¯å¥çå¤å¶ï¼ä½æ¯å¨æäºæ åµä¸èªå¨åæ¢å°åºäºè¡çå¤å¶
ããå¨5.1.4çæ¬ä»¥åï¼binlogæ ¼å¼åªè½æ¯ statement -based replication ,å¨ä»¥åççæ¬ä¸å¼å ¥äº row-based replication 以å mixed-based replicationã
ããä¸é¢æä¼ç®åçä»ç»ä¸ä¸SBRãRBRãMBR è¿ä¸ç§æ ¼å¼ä¸binlogæ¯å¦ä½ç»ç»çï¼æ´éè¦çæ¯å¨è¿ä¸ç§æ ¼å¼ä¸ï¼replicationæ¯å¦ä½è¿è¡å·¥ä½çãå½ç¶æ主è¦ä»ç» RBR模å¼ä¸çå¤å¶ï¼å 为RBR模å¼ä¸æ们çå¤å¶æ¯è®¤ä¸ºæ¯æå®å ¨çæ¹å¼ï¼å³ä½¿æ¯ä½¿ç¨MBRä¹ä¼æå¯è½è¸©å°åã
ããå¨MySQLä¸ï¼Binlogæ两类æ件ï¼ä¸ç±»æ件ç¨äºè®°å½æ°æ®åæ´ï¼ä¸ç±»æ件ç¨äºè®°å½binlog listã
ããBinlog listæä»¶å°±æ¯ ${HOSTNAME}-bin.index ï¼ç¨äºè®°å½å½åæåªäºbinlog
ããbinlogçæ°æ®æ件ååç±»ä¼¼äº ${HOSTNAME}-bin.00001,è¿ç±»æ件æ¯æ们éç¹å ³æ³¨ç对象ã
ããå æ¥å¤§æ¦çççbinlog çæ件å 容ï¼
ããsvan-mac:mydata xiean$ mysqlbinlog -vvvv mysql-bin.000010
ãã/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
ãã/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
ããDELIMITER /*!*/;
ãã# at 4
ãã#151218 15:19:30 server id 5331 end_log_pos 123 CRC32 0xd483743a Start: binlog v 4, server v 5.7.9-log created 151218 15:19:30
ãã# Warning: this binlog is either in use or was not closed properly.
ããBINLOG '
ããgrNzVg/TFAAAdwAAAHsAAAABAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ããAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ããATp0g9Q=
ãã'/*!*/;
ãã# at 123
ãã#151218 15:19:30 server id 5331 end_log_pos 154 CRC32 0x622c3733 Previous-GTIDs
ãã# [empty]
ããSET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
ããDELIMITER ;
ãã# End of log file
ãã/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
ãã/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
ããæ½ä¸æ®µbinlogåºæ¥ççï¼
ãã# at 141
ãã#151218 15:19:30 server id 5331 end_log_pos 245
ããQuery thread_id=3350 exec_time=11 error_code=0
ããat 141
ããå½åäºä»¶å¨æ件ä¸çèµ·å§ä½ç½®ï¼åä½bytes
ãã#151218 15:19:30
ããå½åäºä»¶å¼å§æ¶é´
ããServer id
ããå½åäºä»¶å¨åªä¸ªServer æ§è¡ç
ããEnd_log_pos
ããä¸ä¸ä¸ªäºä»¶å¨æ件ä¸çå¼å§ä½ç½®ï¼å³å½åäºä»¶å 容为 4~end_log_pos-1èå´
ããexec_time
ããæ§è¡æè±è´¹çæ¶é´(å¦ææ¯Master )ï¼
ããerror_code
ããæ§è¡è¯¥äºä»¶æ¶çç»æ
ããæ们æBinlog æ件æåæ3个é¨ä»½
ããç®åæè¿° ï¼ï¼>
ãã{
ãã"header": "desc â¦",
ãã"event": "xxxxxxx",
ãã"footer": "log rotat"
ãã}
ãã第ä¸é¨ä»½ï¼ ç± 4-bytes å¼å§ï¼è¿4-bytes 表æå®æ¯ä¸ä¸ªMySQL binlog æ件ï¼ç±log_event.h è¿ä¸ªæ件常éï¼BINLOG_MAGIC (0xfe 0x62 0x69 0x6e = 0xfe 'b''i''n') 表示ï¼ï¼è¿ä¹å°±è½è§£éæ们ç第ä¸ä¸ªevent 为ä»ä¹ èµ·å§ä½ç½®æ¯ 4 äºåå äºã
ãã第äºé¨ä»½ï¼ ä¸ä¸ªä¸ä¸ªç event ï¼æ¯ä¸ªevent æ ¹æ®binlogçæ¬ä¸åï¼è§£éåºæ¥çå«ä¹ææä¸å(å¨MySQL 5.0ï¼ binlogçæ¬é½ä½¿ç¨çæ¯ V4çæ¬)
ãã第ä¸é¨ä»½ï¼ä¹å°±æ¯æ件æåeventç¨äºè®°å½log-rotation äºä»¶ï¼è¡¨æäºæ¥ä¸æ¥çæ¥å¿å°åå ¥åªä¸ªæ件ã
ããEvent æ¯binlog è®°å½çæå°åä½ï¼event çä¸ä¸çº§æ¯event group ï¼å¤å¶æè æ¢å¤çæ¶ååºäº event group æ¥éæ¾æ¥å¿ã对äºä¸ä¸ªç»æ¥è¯´ï¼è¦ä¹é½æ§è¡ï¼ è¦ä¹é½ä¸æ§è¡ãè对äºDMLæ¥è¯´ä¸ä¸ª event å å«å¤ä¸ªeventï¼è对äºDDL æ¥è¯´ï¼ä¸ä¸ªevent å°±æ¯ä¸ä¸ª event groupã对äºæ¯ä¸ªEventéè¾¹æåªäºä¸è¥¿ï¼ä»¥åevent group æ¯å¦ä½ååçæ们å°å¨ä¸é¢è®¨è®ºã
ãã触åå¨ï¼åå¨è¿ç¨ï¼å½æ°å¨MySQLéè¾¹æ¯å¦ä½å¤ççå¢ï¼
ãã触åå¨ï¼åå¨è¿ç¨ï¼å½æ° å¨å建çæ¶åé½ä¼è®°å½Binlog
ãã触åå¨å¨æ§è¡çæ¶å MasterãSlave é½ä¼è¢«è°ç¨ï¼Masterä¸ç触åå¨å°ä¼å¨Masterä¸è¢«è°ç¨ï¼Slaveä¸ç触åå¨å°ä¼å¨Slaveä¸è¢«è°ç¨ã
ããåå¨è¿ç¨å¨æ§è¡çæ¶åï¼Masterä¸ä¼è¢«è½¬åæå ·ä½çSQLè¯å¥åå¨å¨Binlogéè¾¹ï¼å æ¤å¨binlogéè¾¹æ¯çä¸å°ä»»ä½ call æ¥è°ç¨åå¨è¿ç¨çevent ã
ããå½æ°å¨æ§è¡çæ¶å并ä¸ä¼è¢«è§£ææä¸ºå ·ä½çè¯å¥ï¼ç¸åå½æ°æ§è¡æ¶å触åå¨æ¯è¾ç¸åï¼å¨Masterä¸åSlaveé½ä¼è¢«è°ç¨ã
ããä¸ç§æ¨¡å¼ä¸Binlog æ¯å¦ä½è®°å½ç
ããåºäºè¯å¥çå¤å¶ ï¼ æ¯MySQL 5.7.7 çæ¬ä»¥åé»è®¤é ç½®ï¼å®å°SQLè¯å¥ï¼èä¸æ¯å®é çæ°æ®ååï¼ä»ä¸»æå¡å¨å¤å¶å°ä»æå¡å¨ã
ããä¼ç¹ ï¼å¨æäºæ åµä¸ï¼æç»åå ¥æ¥å¿æ件çæ°æ®æ´å°ï¼ä¾å¦æ´æ°æè å é¤è®¸å¤è¡æ¶ã对äºåªå½±åå è¡æ°æ®çç®åè¯å¥ï¼åºäºè¡çå¤å¶å ç¨ç空é´æ´å°ã
ããç¼ºç¹ ï¼ æææ¾çæ¯å®ä¸æ¯æä¸ç¡®å®æ§çè¯å¥ï¼ä¾å¦å½åæ¶é´å½æ°ã
ããåºäºè¡çå¤å¶ ï¼ ä½¿ç¨å个ç表è¡è®°å½ååï¼èä¸æ¯è¯å¥ã主æå¡å¨å°æ¶æ¯ï¼ä¹å°±æ¯äºä»¶åå ¥äºè¿å¶æ¥å¿ï¼è¡¨ç¤ºå个表è¡çååãè¿ä¸å ¶ä»RDBMSä¸æ´å ä¼ ç»çå¤å¶æ¹å¼ç±»ä¼¼ã
ããä¼ç¹ï¼éè¦æ´å°çéå®ï¼æå³çè½å¤è¾¾å°æ´é«ç并åã
ãã缺ç¹ï¼å®ä¼äº§çæ´å¤éè¦è®°å½çæ°æ®ï¼å ç¨ç空é´å¤§ã
ããæ··å模å¼æ¥å¿ ï¼ å¯ä»¥æ ¹æ®è¦è®°å½çäºä»¶å®æ¶æ¹åäºè¿å¶æ¥å¿æ ¼å¼ã使ç¨æ··å模å¼å¤å¶æ¶ï¼é»è®¤éç¨åºäºè¯å¥çå¤å¶ï¼ä½æ¯å¨æäºæ åµä¸èªå¨åæ¢å°åºäºè¡çå¤å¶
温馨提示:答案为网友推荐,仅供参考
第1个回答 2021-01-29
GTID 对于单源复制还是很方便,但是对于多源复制,这里就需要特别注意:
要先停止所有的从库 stop slave;
然后清理本机所有的 GTID,reset master;
再进行 SET @@GLOBAL.GTID_PURGED='xxxxx' gtid 设置
这里就会引入一个问题,如果是级联复制的情况下,reset master 的时候,会把本机的所有 binlog 清理掉。如果下一级的从库存在延迟,没有及时的把 binlog 传过去,就会造成主从中断,这里我们该怎么避免呢?看这里:做 reset master 的时候,先看看下游的从库是否存在很大的延迟。如果存在,把当前的 binlog 和后面未同步的 binlog 全部备份下;
待添加好从库的 channel 后,再把未同步的 binlog 文件手动拷贝到 binlog 目录;
更新下 mysql-bin.index 文件;
注意,binlog 不能同名,需要手动更新下文件。