http://www.mysqlkorea.co.kr
Çѱ۸Ŵº¾ó 5.0 , Çѱ۸Ŵº¾ó 5.1 , MySQL 5.1 HA , »ç¿ëÀڸŴº¾ó
Advanced Knowle...  
¿£Áö´Ï¾î ³ëÆ®  
ºí·Î±×Á¸  
±Û·Î¹ú MySQL  
MySQL 5.5 GA  
MySQL 5.6 Developer  
ÃֽűÛ
foreign key ¼³Á¤¡¦
ÀÎÅØ½º Ãß°¡ µî¿¡¡¦
mysql master - s¡¦
´Ù´ë´Ù °ü°è¿¡¼­ ¡¦
mysql myÆÄÀϰú ¡¦
 
MySQL 5.5 Semi-Synchronous Replication
±Û¾´ÀÌ : royster   ³¯Â¥ : 10-12-21 21:11   Á¶È¸¼ö : 9122
MySQL 5.5 Semi-Synchronous Replication¿¡ ´ëÇÏ¿© ¾Ë¾Æº¸±âÀü¿¡ ¸ÕÀú
 
MySQL 5.1¹öÀü±îÁ÷ÀÇ Replication ±¸Á¶´Â ºñµ¿±â¹æ½Ä À̾ú½À´Ï´Ù.
 
master¼­¹ö´Â ÀÚ½ÅÀÇ bin-log¿¡ update³»¿ëÀ» ¾²°í³ª¸é slave¿¡ ´ëÇÏ¿© ¾Æ¹«·± ½Å°æµµ ¾²Áö ¾Ê¾Ò½À´Ï´Ù.
 
slave°¡ masterÃøÀÇ bin-log¸¦ Àо ÀÚ½ÅÀÇ relay-log¿¡ ¾´´ÙÀ½
ÀÚ½ÅÀÇ storage engine¿¡ ¹Ý¿µÇÏ´Â ±¸Á¶¿´½À´Ï´Ù.
 
ºñµ¿±â¹æ½ÄÀº master¼­¹öÀÇ update°¡ ºó¹øÇÒ Å¸Àֿ̹¡ master¼­¹ö¿¡ ÀåÇØ°¡ ¹ß»ýÇϸé
master¿Íslave°£ÀÇ µ¿±âÈ­°¡ °ðÀß Æ²¾îÁö°í´Â Çß½À´Ï´Ù.
 
Semi-Synchronous Replication´Â ¿ø·¡ GoogleÀÇ Mark Callaghan¿¡ ÀÇÇØ °³¹ßµÇ¾ú½À´Ï´Ù.
MySQL 5.5¹öÀü¿¡¼­ ±× ±â´ÉÀ» plug-inÇüÅ·ΠÁ¦°øÇÕ´Ï´Ù.
Á»´õ ½¬¿î ¼³¸íÀ» À§Çؼ­ ¾Æ·¡ ±×¸²À» º¸µµ·Ï ÇϰڽÀ´Ï´Ù.
±×¸²Àº  Semi-Synchronous ReplicationÀÇ µ¿ÀÛÀ» ±×¸²À¸·Î ³ªÅ¸³½ °ÍÀÔ´Ï´Ù.
 
¨ç  ¾îÇø®ÄÉÀ̼Ç(Ŭ¶óÀÌ¾ðÆ®£©·ÎºÎÅÍ transactionÀ» COMMIT ÇÑ´Ù.
¨è  ¹ÙÀ̳ʸ® ·Î±×¸¦ °»½ÅÇÑ´Ù.
¨é  storage engine(Å×ÀÌºí£©À» °»½ÅÇÑ´Ù.
¨ê  ¨é°ú µ¿½Ã¿¡ ¹ÙÀ̳ʸ® ·Î±×ÀÇ ³»¿ëÀÌ ½½·¹À̺꿡 ¼Û½ÅµÈ´Ù.
¨ë  ½½·¹À̺갡 ·Î±×ÀÇ °»½ÅÀ» ¹ÞÀ¸¸é ¸¶½ºÅÍ¿¡ack¸¦ µ¹·ÁÁØ´Ù.
¨ì  ½ºÅ丮Áö ¿£ÁøÀÇ °»½Å°ú ½½·¹À̺ê·ÎºÎÅÍÀÇack¸¦ È®ÀÎÇϸé Ŭ¶óÀÌ¾ðÆ®¿¡ ÀÀ´äÀ» µ¹·ÁÁØ´Ù.
¨í  ½½·¹À̺êÃø¿¡¼­ ·Î±×¸¦ Àû¿ëÇÑ´Ù.(ºñµ¿±â)
 
Áö±Ý±îÁöÀÇ Replication(MySQL 5.1)¿¡¼­´Â SlaveÃø ÀÔÀå¿¡¼­´Â ºñµ¿±â¿´ÁÒ.
MasterÃø¿¡¼­ Slave¸¦ ÅÍÄ¡ ÇÏ´Â ¿¢¼ÇÀº ÀüÇô ¾ø¾ú½À´Ï´Ù.
 
ÇÏÁö¸¸ Semi-Synchronous Replication(¹Ýµ¿±â)¿¡¼­´Â commitÀ» ¿Ï·áÇÑ ½ÃÁ¡¿¡¼­ slave¿¡µµ
¹ÙÀ̳ʸ® ·Î±×°¡ ¹Ý¿µ µÈ °ÍÀ» Master°¡ º¸ÁõÇÑ´Ù´Â °ÍÀÌ´Ù.
 
SlaveÃø¿¡¼­ relay logÀÇ ³»¿ëÀ» Storage Engine¿¡ ¹Ý¿µµÇ´Â°ÍÀº ºñµ¿±â¿¡ ÇàÇØÁø´Ù.
¨é °ú¨ê´Â º´·Ä󸮵ȴÙ.
±× ¶§¹®¿¡ ³×Æ®¿÷ÀÌ °úºÎÇÏ »óŰ¡ ¾Æ´Ï¶ó¸é commit¼º´É¿¡ ´ëÇÑ ¿À¹öÇìµå´Â ¸Å¿ì ÀÛ°Ô µÈ´Ù.
slave·ÎºÎÅÍ ÃÖÃÊÀÇ ack¸¦ ¹Þ´Â ŸÀֿ̹¡¼­ Ŭ¶óÀÌ¾ðÆ®¿¡ send_ok() µÇ¾î Áø´Ù.
 
¸¸ÀÏ Master°¡ Å©·¡½¬ 됬À» °æ¿ì¿¡µµ commitÇÏÁö¸¸ ¿Ï·áÇÑ ¸ðµç update´Â slaveÃø¿¡ Á¸ÀçÇÑ´Ù.
slaveÃø¿¡¼­ fail over ÇØµµ updateµÈ Á¤º¸°¡ ¾ø¾îÁö´Â ÀÏÀº ¹ß»ý µÇÁö ¾Ê´Â´Ù.
 
Å©·¡½¬°¡ ¹ß»ýÇÏ¸é ´ÙÀ½°ú °°Àº ¼ø¼­·Î fail over°¡ ½ÇÇàµÈ´Ù.
 
¨ç Slave°¡ ÆÄÀϽýºÅÛÀ» ¸¶¿îÆ®ÇÑ´Ù.
¨è MySQL¼­¹ö¸¦ °¡µ¿½ÃŲ´Ù.
¨é InnoDBÀÇ ·Î±×¸¦ ½ºÄµÇØ Å©·¡½¬ recovery(Redo/Undo)¸¦ ½ÇÇàÇÑ´Ù.
 
 Æ¯È÷ Å©·¡½¬ recovery´Â ¸Í½ÅÇÒ¼ö ¾ø´Â ³à¼®À̹ǷÎ, InnDB log file size°¡ Å«°æ¿ì¿¡´Â Àå½Ã°£ ¼Ò¿äµÈ´Ù.
¶Ç InnoDBÀÇ ·Î±×»çÀÌÁ ÀÛ°Ô ¼³Á¤µÇ¾ú´õ¶óµµ ¸îºÐÀÌ ¼Ò¿äµÇ¸ç, Å©·¡½¬¿¡ update³»¿ëÀÌ ¸¹¾ÒÀ»¶§¿¡´Â
UndoÀÇ Ã³¸®¿¡ ½Ã°£ÀÌ ´õ ¼Ò¿äµÈ´Ù.
 
À̰Ϳ¡ ºñ±³ÇÑ´Ù¸é Semi-Synchronous Replication´Â ¹ÌÀû¿ëµÈ relay log°¡ Àû¿ëµÇ´Â °ÍÀ» ±â´Ù¸°´Ù.
ÆÄÀÏ ½Ã½ºÅÛÀ» ¸¶¿îÆ®ÇÒ Çʿ䵵 MySQL¼­¹ö¸¦ ±âµ¿½Ãų Çʿ䵵 ¾ø´Ù.
 
relay logÀÇ Àû¿ëÀº Å©·¡½¬ recovery¿¡ ºñÇÏ¸é ¸Å¿ì ªÀº ó¸®ÀϰÍÀÌ´Ù.
 
Á¦´ë·Î commitÀ» ÀûÀýÇÑ Size(ÀÛÀºsize)·Î TRANSACTION »óŶó¸é °æ¿ì¿¡ µû¶ó¼­´Â
1ÃÊ ¹Ì¸¸À¸·Î fail over°¡ ¿Ï·áµÉ°ÍÀÌ´Ù.
 
¾Æ¿ï·¯¼­ Slave¿¡ ´ëÇØ ¸ðµç relay log Àû¿ëÀÌ ¿Ï·á µÇ¾ú´ÂÁö show slave status\G
±×¸®°í Exec_Master_Log_Pos ¿Í Read_Master_Log_Pos µ¿ÀÏÇÑÁö,
show processlist , SQL_Thread»óŰ¡
¡¸Has read all relay log; waiting for the slave I/O thread to update it¡¹ÀÎÁö È®ÀÎÇÒ¼ö ÀÖ´Ù.
 
¶Ç¡¢Semi-Synchronous Replication¸¦ ÀÌ¿ëÇØHA(À»)¸¦ ±¸¼ºÇßÀ» °æ¿ì¡¢
¸¶½ºÅÍÃø¿¡¼­ÀÇ ½ºÅ丮Áö ¿£Áø¿¡ÀÇ °»½Å°ú¡¢½½·¹À̺꿡ÀÇ °»½ÅÀÇ Àü¼ÛÀº º´·Ä£¨ºñµ¿±â)·Î ÇàÇØÁø´Ù
 
±× ¶§¹®¿¡¡¢¸¶½ºÅÍ¿¡¸¸ Á¸ÀçÇÏ´Â µ¥ÀÌÅͳª ½½·¹À̺꿡¸¸ Á¸ÀçÇÏ´Â
µ¥ÀÌÅͰ¡ ¹ß»ýÇØ ¹ö¸®´Â ºÎÁ¤ÇÕÀÎ »óȲÀÌ º¸±â µå¹°°Ô ¹ß»ýÇÑ´Ù.
 
±×·¯ÇÑ »óȲÀÌ »óÁ¤µÇ±â À§ÇØ¡¢ÇÑ ¹ø fail over°¡ ¹ß»ýÇßÀ» ¶§´Â ¸¶½ºÅÍ¿¡ µ¥ÀÌÅ͸¦ ´Ù½Ã
Ä«ÇÇÇÒ Çʿ䰡 »ý±æ °ÍÀÌ´Ù.
 
½½·¹À̺ê·ÎºÎÅÍ Ç®¹é ¾÷À» ÃëµæÇØ ¸¶½ºÅÍ¿¡ restore ÇÏ´Â °ÍÀÌ´Ù.
¹é¾÷ÀÇ ¹æ¹ýÀºLVMsnapshot¡¢InnoDB Hot Backup¡¢mysqldumpµîÀ» ÀÌ¿ëÇϸé ÁÁ´Ù.
 
ºÎÇÏ·®ÀÌ ÀûÀ» ¶§¸¦ °è»êÇÏ¿© ÀÛ¾÷À» ½Ç½ÃÇϸé ÁÁÀ» °ÍÀÌ´Ù.
 
¶ÇÇÑ¡¢½½·¹À̺ê Ãø¿¡ ¶Ç´Ù¸¥ ½½·¹À̺긦 Ãß°¡ÇØ µÎ¸é¡¢
Ãß°¡µÈ ½½·¹À̺ê·ÎºÎÅÍ ¸¶½ºÅÍ¿¡ restore¸¦ ½Ç½ÃÇÏ´Â °ÍÀÌ °¡´ÉÇÏ°Ô µÈ´Ù.
 
Ãß°¡ ½½·¹À̺긦 ºÙ¿© µÎ¸é2Áß ÀåÇØ¿¡µµ ´ëÀÀÇÒ ¼ö ÀÖ°Ô Çϱâ À§ÇØ¡¢
Semi-Synchronous ReplicationÀ» ¿î¿ëÇÏ´Â °æ¿ì¿¡´Â Ãß°¡ ½½·¹À̺êÀÇ ¼³Ä¡¸¦ °ËÅäÇÏÀÚ.
(Ãß°¡ ½½·¹À̺긦 ¼³Ä¡ÇÏ·Á¸é ½½·¹À̺꿡 ´ëÇØ--log-slave-updates¿É¼ÇÀ» ÀÌ¿ëÇϱ⠶§¹®¿¡
½½·¹À̺êÀÇ fail over½Ã¿¡´ÂFLUSH LOGS(À»)¸¦ ÇÒ Çʿ䰡 ÀÖÀ» °ÍÀÌ´Ù.£©
 
[ÀÌ °Ô½Ã¹°Àº ¿î¿µÀÚ´Ô¿¡ ÀÇÇØ 2011-10-13 09:08:29 ¿£Áö´Ï¾î ³ëÆ®¿¡¼­ º¹»ç µÊ]
ÀÌÀü±Û MySQL5.5.x Performance Schema 
´ÙÀ½±Û MySQL 5.5GA(¾ÈÁ¤È­) ¹öÀüÀÇ »õ·Î¿î ±â´Éµé.. 
MySQL Korea »çÀÌÆ®ÀÇ ÄÁÅÙÃ÷ ¼ÒÀ¯±ÇÀº (ÁÖ)»ó»óÀ̺ñÁî¿¡ ÀÖÀ¸¹Ç·Î ¹«´ÜÀüÀ縦 ±ÝÇÕ´Ï´Ù.
Copyright ¨Ï ssebiz All Rights Reserved.