Ò»
¡¢Òý×Ó
±ÊÕ߸տªÊ¼½øÈ빫˾µÄʱºò£¬Ö÷ÒªÊÇæÓÚ·Ö²¼Ê½MySQLϵͳ----MyShardµÄ¹¹½¨£¬¹«Ë¾Ê¹ÓÃÁË´óÁ¿µÄIDC»ú·¿£¬»ùÓÚÕâÖÖÍøÂçÌØµã£¬MyShardÉè¼Æµ±³õÍêÈ«ÊÇΪÁËÊÇÒ»Ì×Ö§³ÖMulti-Master²Ù×÷µÄ¸ß¿ÉÓÃÐԵķֲ¼Ê½Êý¾Ý¿â£¬¿ÉÒÔÔÚ¶à¸ö»ú·¿Öв¿ÊðµÄÒµÎñÉÏÌṩ¿ìËÙµÄд²Ù×÷£¬ÊµÏÖÁË·Ö²¼Ê½¸ß¿ÉÓô洢ÄÜÁ¦¡£
ÔÚÒµÎñÔö³¤ÆÚ£¬MyShard½â¾öÁ˹«Ë¾µÄºÜ¶à´óÐ͵ÄÊý¾Ý¿â´æ´¢ÒµÎñ£¬Ëæ×Ź«Ë¾ÒµÎñÖð½¥Îȶ¨ÏÂÀ´£¬·Ö²¼Ê½´æ´¢ÐèÇóÔ½À´Ô½ÉÙ¡£¶ø¹«Ë¾È´ÓдóÁ¿µÄСҵÎñÒÔ¼°²»¶Ï³¢ÊԵĸ÷ÖÖÐÂÒµÎñ£¬ÐèÒªÔ½À´Ô½¶àµÄСÊý¾ÝÁ¿µÄÊý¾Ý¿â´æ´¢¡£
ËùÒÔÕâʱºò·¢ÏÖ£¬Ö®Ç°µÄ¹¤×÷·½ÏòÒ»Ö±¼¯ÖÐÔÚ¹«Ë¾µÄ10%²»µ½µÄÒµÎñÉÏ£¬¶ø¹«Ë¾µÄ90%ÒÔÉϵĴ洢ÐèÇóÊÇMySQLµÄÐèÇó£¬Ä¿Ç°ÓкÃÉÏǧÌ×µÄMySQLÔÚ¸ø²»Í¬µÄÒµÎñÌṩ·þÎñ¡£¶øÔÚµ±Ê±£¬²»¹ÜÊÇMySQLµÄ½»¸¶»¹ÊǹÜÀí¶¼±È½ÏÔʼ£¬¼«¶ËÇé¿öÏ£¬ÎÒÃÇÐèÒªÒµÎñÉêÇë·½×Ô¼ºÌṩ·þÎñÆ÷À´²¿ÊðMySQL£¬ËùÒÔ½»¸¶µÄÖÜÆÚÒ²ºÜ³¤¡£¶øÔڸ߿ÉÓ÷½Ã棬¶¼ÊÇÐèÒªÒµÎñ·½×Ô¼ºÈ¥´¦ÀíÖ÷´ÓÇл»µÈµÈÎÊÌ⣬³öÏÖÖ÷Êý¾Ý¿â¹ÊÕϵÄʱºò£¬ÍùÍùÐèÒªÒµÎñ·½×Ô¼ºÈ¥ÐÞ¸ÄÅäÖÃÎļþ£¬ÖØÆô½ø³Ì£¬Ôö¼ÓÁË·þÎñµÄÖжÏʱ¼ä¡£
¶þ¡¢ÎªÊ²Ã´Ã»ÓвÉÓÿªÔ´µÄ¸ß¿ÉÓ÷½°¸
Òµ½ç±È½ÏÁ÷ÐеÄMySQLµÄ¸ß¿ÉÓ÷½°¸Ö÷ÒªÓУºMMMºÍMHAÁ½ÖÖ£¬¶ÔÕâ¸ö·½°¸µÄ·ÖÎöÍøÉÏÓкܶ࣬MHAÊÇÓÅÏÈѡȡµÄ·½°¸¡£
MHAµÄ¹¤×÷ÔÀí£º

clipboard.png
µ±master³öÏÖ¹ÊÕÏʱ£¬Í¨¹ý¶Ô±ÈslaveÖ®¼äI/OÏ̶߳ÁÈ¡master binlogµÄλÖã¬Ñ¡È¡×î½Ó½üµÄslave×öΪlatestslave¡£ÆäËüslaveͨ¹ýÓëlatest
slave¶Ô±ÈÉú³É²îÒìÖмÌÈÕÖ¾¡£ÔÚlatest slaveÉÏÓ¦ÓôÓmaster±£´æµÄbinlog£¬Í¬Ê±½«latest
slaveÌáÉýΪmaster¡£×îºóÔÚÆäËüslaveÉÏÓ¦ÓÃÏàÓ¦µÄ²îÒìÖмÌÈÕÖ¾²¢¿ªÊ¼´ÓеÄmaster¿ªÊ¼¸´ÖÆ¡£
ÔÚMHAʵÏÖMaster¹ÊÕÏÇл»¹ý³ÌÖУ¬MHA Node»áÊÔͼ·ÃÎʹÊÕϵÄmaster£¨Í¨¹ýSSH£©£¬Èç¹û¿ÉÒÔ·ÃÎÊ£¨²»ÊÇÓ²¼þ¹ÊÕÏ£¬±ÈÈçInnoDBÊý¾ÝÎļþË𻵵ȣ©£¬»á±£´æ¶þ½øÖÆÎļþ£¬ÒÔ×î´ó³Ì¶È±£Ö¤Êý¾Ý²»¶ªÊ§¡£MHAºÍ°ëͬ²½¸´ÖÆÒ»ÆðʹÓûá´ó´ó½µµÍÊý¾Ý¶ªÊ§µÄΣÏÕ¡£
MHAµÄÓŵã
MHAÌṩÁËÒ»¸öͨÓõĿò¼Ü£¬ÎÒÃÇ¿ÉÒÔ×Ô¶¨ÒåÅжϺÍÇл»²Ù×÷µÄ²½Ö裻¶øÇÒ£¬MHAµÄ´úÂ뿪Դ£¬ÎÒÃÇÉõÖÁ¿ÉÒÔ½øÐжþ´Î¿ª·¢£¬ÕⶼΪ¸ß¿ÉÓÃϵͳÌṩÁ˺ܺõÄÀ©Õ¹
ÄÜÁ¦¡£
MHAµÄȱµã
ÐèÒªÔÚ¸÷¸ö½Úµã¼ä´òͨsshÐÅÈΣ¬Õâ¶ÔijЩ¹«Ë¾°²È«ÖƶÈÀ´ËµÊǸöÌôÕ½£¬ÒòΪÈç¹ûij¸ö½Úµã±»ºÚ¿Í¹¥ÆÆµÄ»°£¬ÆäËû½ÚµãÒ²»á¸ú×ÅÔâÑꣻ
×Ô´øÌṩµÄ½Å±¾»¹ÐèÒª½øÒ»²½²¹³äÍêÉÆ£¬µ±È»ÁË£¬Ò»°ãµÄʹÓû¹Êǹ»Óõġ£
ËäȻһ¸öMHA Manger¿ÉÒÔ¹ÜÀí¶à¸ö¼¯Èº£¬µ«ÊÇûÓдó¹æÄ£¼¯ÈºµÄ¾Ñé¡£
¸ß¿ÉÓÃÒÀÀµÓÚvipµÄ·½°¸£¬Æ©Èç²ÉÓÃkeepaliveÀ´´ïµ½vipµÄÇл»£¬µ«ÊÇkeepalive»áÏÞÖÆÇл»µÄÖ÷»ú±ØÐëÔÚÒ»¸öÍø¶Î£¬¶ÔÓÚ¿ç»ú·¿²»ÔÚÒ»¸öÍø¶ÎµÄ·þÎñÆ÷À´Ëµ£¬¾ÍÎÞ·¨Ö§³ÖÁË¡£ÔÚ´ó¹æÄ£ÎªÃ¿¸öMySQL¼¯Èº°²ÅÅÒ»¸övipÒ²ÊÇÄÑÒÔʵÏֵġ£keepaliveÔÚÒ»¸öÍø¶ÎÄÚ£¬²¿Êð¶àÌ×Ò²»á»¥ÏàÓ°Ïì¡£
Ϊʲô²»²ÉÓÃ
ÎÒÃǹ«Ë¾µÄÊý¾Ý¿âµÄÌØµã£º
Êý¾Ý¿â¶à»ú·¿²¿Êð
Êý¾Ý¿â¼¯Èº¹æÄ£ÉÏǧ
°²È«ÐÔ¿¼ÂÇ
Èý¡¢ËIJã´úÀí----RDSÏîÄ¿
³ýÁËMMMºÍMHAÖ®Í⣬MySQL»¹¿ÉÒÔ²ÉÓôúÀíÀ´ÊµÏָ߿ÉÓã¬MySQL´úÀí»á±ÈMHA·½°¸¸üÊʺϴó¹æÄ£µÄʹÓá£
µ±Ê±°¢ÀïºÍÌÚѶ·Ö±ðÍÆ³öÁËrdsºÍcdb¡£ÎÒÃÇÑо¿ÁËÌÚѶµÄcdb·½°¸£¬cdbÊÇ»ùÓÚTGWÒ²¼´ÊÇNATµÄģʽʵÏÖÁË4²ã´úÀíÄ£ÐÍ£¬À´´ïµ½MySQLµÄ¸ß¿ÉÓÃÒÔ¼°¸ºÔؾùºâ¹¦ÄÜ¡£
µ«ÊÇTGW·½°¸Óиö±È½Ï´óµÄÎÊÌâ¾ÍÊÇÐèÒªÐÞ¸ÄMySQLÐÒ飬һµ©ÐÞ¸ÄMySQLÐÒ飬ËùÓеĿͻ§¶Ë£¨¸÷ÖÖÓïÑÔµÄÇý¶¯£©¶¼ÐèÒª½øÐÐÐ޸ģ¬ÕâÔÚÍÆ¹ãÉÏÊǷdz£Äѵġ£
ËùÒÔÎÒÃDzÉÓÃÁËÒ»ÖÖÕÛÖеķ½°¸£¬Æô¶¯ÁËRDSÏîÄ¿£¬Ö÷ÒªÓÃÓÚÌṩMySQLÄÚ²¿ÔÆ·þÎñ£¬ÆäÖи߿ÉÓ÷½°¸ÈçÏÂͼËùʾ£¬²ÉÓÃÁËiptablesµÄNATÀ´ÊµÏÖMySQLµÄ´úÀí·Óɹ¦ÄÜ¡£

clipboard.png
4²ã´úÀí²ã»ù±¾ÊµÏÖÔÀí
ÒµÎñ·½Ê×ÏÈÁ¬½Óµ½´úÀí·þÎñÆ÷ÉÏ£¬ÓÉÓÚ´úÀí·þÎñÆ÷ÉÏÓÐNATÄ¿±êµØÖ·×ª»»¹æÔò£¬ËùÒÔ»áתµ½Ä¿±êMySQLÖ÷»úÉÏ£¬Í¬Ê±´ÓMySQLÖ÷»ú»Ø°üµ½´úÀí·þÎñÆ÷ºó£¬ÓÉÓÚÓÐNATÔ´µØÖ·×ª»»¹æÔò£¬ÓÖ»áת·¢»ØÒµÎñ·½£¬´Ó¶øÊµÏÖÁË´úÀí¹¦ÄÜ¡£
ÏÂÃæ¾ÙÀý˵Ã÷ÎÒÃÇÈçºÎΪһ¸öÒµÎñÆô¶¯RDSËIJã´úÀí£º
ÎÒÃÇÏÈ×¼±¸ºÃÒÔϼ¸Ì¨»úÆ÷£º
¿Í»§¶Ë£º172.26.14.16
´úÀí £º172.26.82.45 Æô¶¯ÒµÎñ´úÀí¶Ë¿Ú20000
Ä¿±ê»úÆ÷£º172.26.82.7 ²¿ÊðMySQL ¶Ë¿ÚºÅΪ3306
ÔÚ´úÀí»úÆ÷ÉÏÉèÖùæÔò£º
1¡¢´ò¿ªforward
echo 1 > /proc/sys/net/ipv4/ip_forward
2¡¢ÉèÖôӿͻ§¶ËÇëÇóµÄÄ¿±êµØÖ·ºÍÔµØÖ·×ª»»¹æÔò
iptables -t nat -A PREROUTING -p tcp -s 172.26.14.16
--dport 3306 -j DNAT --to-destination 172.26.82.7:20000
iptables -t nat -A POSTROUTING -p tcp -d 172.26.82.7
--dport 20000 -j SNAT --to-source 172.26.82.45
3¡¢ÉèÖúùæÔòÖ®ºó£¬¾Í¿ÉÒÔͨ¹ýmysqlÀ´Á¬½Óµ½´úÀíµÄ20000¶Ë¿ÚÁË¡£
mysql -utest -ptest -h172.26.82.45 -P20000
Õâʱºò£¬Êµ¼Ê·ÃÎʵÄÊÇ172.26.82.7ÉÏ3306¶Ë¿ÚµÄMySQLÊý¾Ý¿â¡£
ËÄ¡¢Êý¾Ý¿âÅäÖÃÖÐÐÄ----´úÀí²ã£¨7²ã´úÀí£©
±ÊÕß֮ǰһֱ¶¼ÔÚ¹«Ë¾ÔÆ´æ´¢ÖÐÐŤ×÷£¬ÓÉÓÚÖÖÖÖÔÒò£¬2015ÄêÄêÖе÷µ½ÁËÔËά²¿µÄÊý¾Ý¿âÍŶӣ¬ÔÚÕâÀï²Å·¢ÏÖ£¬rdsÏîÄ¿ÆäʵֻÊÇÔÚÊý¾Ý¿âÔËάƽ̨ÖÐ×ß³öÁ˺ÜСµÄÒ»²½¡£ÎªÁËÌṩȫ·½Î»µÄÊý¾Ý¿âÔÆ·þÎñƽ̨£¬ÓÚÊÇÎÒÃÇ¿ªÊ¼´òÔìÁËȫеÄÊý¾Ý¿âÅäÖÃÖÐÐÄ£¬Í¬Ê±ÌṩMySQL¡¢Redis¡¢MongodbµÈÊý¾Ý¿âºÍ»º´æÄÚ²¿ÔÆ·þÎñ¡£
Óë´Ëͬʱ£¬Ö®Ç°ÔÚrdsÏîÄ¿ÖÐʵÏÖµÄ4²ã´úÀíµÄȱµãÒ²Ô½À´Ô½Ã÷ÏÔ£º
1¡¢Ö»ÄÜʹÓÃMySQLÖ÷¿â£¬M-M-S½á¹¹ÊǵÄMySQL×ÊÔ´¼«ÆäÀË·Ñ£»
2¡¢Ö»ÄÜÔÚµ¥»ú·¿Ê¹Ó㬿ç»ú·¿·ÃÎÊЧÂʷdz£µÍ£»
3¡¢ÔËά¹¦ÄÜÌ«ÉÙ£¬ÓÉÓÚ²ÉÓÃiptables£¬ÔÚ´úÀí»úÆ÷ÉÏÎÞ·¨¿´µ½ÈκεÄÁ¬½ÓÐÅÏ¢£¬Ò²ÎÞ·¨²¶»ñÈκÎÒµÎñ·ÃÎʵÄÖ¸±ê£¬ÉõÖÁÓÚÁ¬½ÓÐÅÏ¢¶¼ÎÞ·¨»ñÈ¡£»
»ùÓÚÒÔÉϼ¸µãÔÒò£¬±ÊÕß¾ö¶¨¿ª·¢»ùÓÚ7²ãÓ¦ÓòãµÄMySQL´úÀí²ãƽ̨£¬ÏµÍ³µÄ¾ßÌå¼Ü¹¹ÈçÏÂËùʾ£º

clipboard.png
ÓÉÓÚ´úÀí²ãÊÇ×Ô¼ºÊµÏÖµÄÓ¦ÓóÌÐò£¬ËùÒÔ±ÊÕßÔÚ´úÂëÖкÜÈÝÒ×¾ÍʵÏÖÒÔϼ¸¸öºËÐĵŦÄÜ£º
1¡¢ÊÚȨÈÏ֤ģÐÍ£»
2¡¢SQLÀ¹½Ø£»
3¡¢¸ºÔؾùºâ£»
4¡¢¶Áд·ÖÀ룻
5¡¢¸ß¿ÉÓã»
6¡¢´óSQL¸ôÀ룻
³ýÁËÕâÐ©ÌØÐÔÒÔÍ⣬»ùÓÚÎÒÃǹ«Ë¾µÄ¶à»ú·¿Ìص㣬±ÊÕ߸ø´úÀí²ãÌí¼ÓÁË»ú·¿¸ÐÖªÄÜÁ¦¡£ÔÚÕû¸öÊý¾Ý¿âÅäÖÃÖÐÐÄ£¬Ã¿¸ö´úÀí²ã³ÌÐò¡¢Ã¿¸öMySQLʵÀý¶¼Óлú·¿ÊôÐÔ¡£ÓÐÁË»ú·¿ÊôÐÔ£¬´úÀí²ã¿ÉÒÔʵÏÖ×Ô¶¯¾Í½ü·ÃÎÊMySQLµÄÄÜÁ¦£¬´Ó¶øÌá¸ßÁËϵͳÐÔÄÜͬʱ£¬¼ò»¯ÁËÒµÎñ³ÌÐòµÄ²¿Êð¡£
Ò»¸öµäÐ͵ÄÒµÎñ²¿ÊðÀý×ÓÈçÏ£º

clipboard.png
´ÓÉÏͼ¿ÉÒÔ¿´³ö£¬Ó¦ÓóÌÐòÓÀÔ¶Ò²²»ÔÙÐèÒª¿¼ÂǶà»ú·¿¸ß¿ÉÓᢸºÔؾùºâ¡¢¶Áд·ÖÀëµÄÎÊÌâ¡£¶øÇÒÓÉÓÚ´úÀí²ãʵÏÖÁ˸߿ÉÓù¦ÄÜ£¬Ò»µ©·¢ÏÖÖ÷дMySQL¹ÊÕÏ£¬»á×Ô¶¯°ÑÖ÷¶ÁÇл»ÎªÖ÷д£¬´Ó¶øÔÚÃ뼶ʵÏÖFAILOVER¡£
ƽ̨¼¶Éè¼Æ--¶à×⻧ģʽ
ÓÉÓÚÎÒÃǵĴúÀí²ã²ÉÓÃÁËÆ½Ì¨¼¶µÄÉè¼Æ£¬ÉÏͼÖеĴúÀí²ã¿ÉÒÔÁ¬½Ó¶àÌ×ÒµÎñ£¨MySQL¼¯Èº£©£¬ÐµÄÒµÎñÖ»ÐèÒªÔÚzookeeperÅäÖú㬴úÀí²ã¾Í»á×Ô¶¯¸ÐÖª£¬ÒµÎñ·½ÂíÉÏÄܹ»ÔÚ´úÀí²ãÉÏʹÓ㬶ø²»ÐèҪΪÿ¸öÒµÎñ²¿Êð×Ô¼ºµÄ¶ÀÁ¢µÄ´úÀí²ã£¬´Ó¶ø¼«´óµÄ¼õÉÙÁËÔËά³É±¾¡£
³ý´ËÒÔÍâ²ÉÓôúÀí²ã»¹ÎªÊý¾Ý¿âÔÆ·þÎñƽ̨´øÀ´²»Éٺô¦£º
ÒµÎñ·½Á¬½Ó´úÀí»úÆ÷ºÍÏàÓ¦µÄ¶Ë¿Ú£¬µ×²ãMySQLÖ÷´ÓÇл»¿ÉÒÔ¶ÔÒµÎñ·½Í¸Ã÷£»
MySQLʵÀýά»¤»òÕßÇ¨ÒÆ¿ÉÒÔ¶ÔÒµÎñ·½Í¸Ã÷£¨Ò»¼üÇ¨ÒÆ£©£»
MySQLÒµÎñÀ©ÈÝ/ËõÈÝÒ²¶ÔÒµÎñ͸Ã÷£¨Ò»¼üÀ©ËõÈÝ£©£»
´úÀí²ãÉÏÏßÍÆ¹ãµ½ÏÖÔÚ£¬ÒѾÓкü¸°ÙÌ×µÄMySQL¼¯ÈºÅÜÔÚÉÏÃæÁË£¬MySQLµÄ¸ß¿ÉÓÃÆ½Ì¨³É¹¦Â䵨¡£
Îå¡¢ºó¼Ç
MongodbÏà¶ÔÓÚMySQLµÄÒ»¸öºÜ´óµÄÓÅÊÆ¾ÍÊǸ߿ÉÓÃÐÔ£¬MySQLµÄ¸ß¿ÉÓ÷½°¸ºÜ¶à£¬µ«ÊÇÍêÃÀµÄ·½Ê½²»¶à£¬´úÀí²ãÊÇÔÚÎÒÃǹ«Ë¾³É¹¦ÊµÊ©µÄÒ»Ìׯ½Ì¨£¬Ï£ÍûÓлú»áÄܺÍÒµ½çÒ»Æð̽ÌÖºÍѧϰ£¬ÊµÏÖ¸ü¶àÍêÃÀµÄ½â¾ö·½°¸¡£
´úÀí²ãËäÈ»ÒѾÔÚ¹«Ë¾´ó¹æÄ£Ê¹Ó㬵«ÊÇ»¹Óкܶ෢չºÍ¸ÄÉÆµÄ¿Õ¼ä£¬Ëæ×ÅMySQL 5.6ºÍMySQL 5.7µÄ¹ã·ºÓ¦Óã¬GTIDÒѾ·Ç³£³ÉÊ죬ÓÉÓÚ¹«Ë¾ÄÚ²¿Ê¹Óó¡¾°ÉÙ£¬´úÀí²ãµÄÇл»»¹Ã»ÓÐʵÏÖGTIDģʽµÄÇл»£¬ËùÒÔÏÂÒ»½×¶Î£¬±ÊÕ߻ῼÂÇʵÏÖ»ùÓÚGTIDµÄ¸ß¿ÉÓÃģʽ¡£ |