聽

• 3.娣卞叆浜嗚В澶嶅埗

  聽

  3.1銆佸熀浜庤鍙ョ殑澶嶅埗(Statement-Based Replication)MySQL 5.0鍙婁箣鍓嶇殑鐗堟湰浠呮敮鎸佸熀浜庤鍙ョ殑澶嶅埗锛堜篃鍙仛閫昏緫澶嶅埗锛宭ogical replication锛夛紝杩欏湪鏁版嵁搴撳苟涓嶅父瑙併€俶aster璁板綍涓嬫敼鍙樻暟鎹殑鏌ヨ锛岀劧鍚庯紝slave浠庝腑缁ф棩蹇椾腑璇诲彇浜嬩欢锛屽苟鎵ц瀹冿紝杩欎簺SQL璇彞涓巑aster鎵ц鐨勮鍙ヤ竴鏍枫€傝繖绉嶆柟寮忕殑浼樼偣灏辨槸瀹炵幇绠€鍗曘€傛澶栵紝鍩轰簬璇彞鐨勫鍒剁殑浜岃繘鍒舵棩蹇楀彲浠ュ緢濂界殑杩涜鍘嬬缉锛岃€屼笖鏃ュ織鐨勬暟鎹噺涔熻緝灏忥紝鍗犵敤甯﹀灏戔€斺€斾緥濡傦紝涓€涓洿鏂癎B鐨勬暟鎹殑鏌ヨ浠呴渶瑕佸嚑鍗佷釜瀛楄妭鐨勪簩杩涘埗鏃ュ織銆傝€宮ysqlbinlog瀵逛簬鍩轰簬璇彞鐨勬棩蹇楀鐞嗗崄鍒嗘柟渚裤€?浣嗘槸锛屽熀浜庤鍙ョ殑澶嶅埗骞朵笉鏄儚瀹冪湅璧锋潵閭ｄ箞绠€鍗曪紝鍥犱负涓€浜涙煡璇㈣鍙ヤ緷璧栦簬master鐨勭壒瀹氭潯浠讹紝渚嬪锛宮aster涓巗lave鍙兘鏈変笉鍚岀殑鏃堕棿銆傛墍浠ワ紝MySQL鐨勪簩杩涘埗鏃ュ織鐨勬牸寮忎笉浠呬粎鏄煡璇㈣鍙ワ紝杩樺寘鎷竴浜涘厓鏁版嵁淇℃伅锛屼緥濡傦紝褰撳墠鐨勬椂闂存埑銆傚嵆浣垮姝わ紝杩樻槸鏈変竴浜涜鍙ワ紝姣斿锛孋URRENT USER鍑芥暟锛屼笉鑳芥纭殑杩涜澶嶅埗銆傛澶栵紝瀛樺偍杩囩▼鍜岃Е鍙戝櫒涔熸槸涓€涓棶棰樸€?鍙﹀涓€涓棶棰樺氨鏄熀浜庤鍙ョ殑澶嶅埗蹇呴』鏄覆琛屽寲鐨勩€傝繖瑕佹眰澶ч噺鐗规畩鐨勪唬鐮侊紝閰嶇疆锛屼緥濡侷nnoDB鐨刵ext-key閿佺瓑銆傚苟涓嶆槸鎵€鏈夌殑瀛樺偍寮曟搸閮芥敮鎸佸熀浜庤鍙ョ殑澶嶅埗銆?/h3>

  3.2銆佸熀浜庤褰曠殑澶嶅埗(Row-Based Replication) MySQL澧炲姞鍩轰簬璁板綍鐨勫鍒讹紝鍦ㄤ簩杩涘埗鏃ュ織涓褰曚笅瀹為檯鏁版嵁鐨勬敼鍙橈紝杩欎笌鍏跺畠涓€浜汥BMS鐨勫疄鐜版柟寮忕被浼笺€傝繖绉嶆柟寮忔湁浼樼偣锛屼篃鏈夌己鐐广€備紭鐐瑰氨鏄彲浠ュ浠讳綍璇彞閮借兘姝ｇ‘宸ヤ綔锛屼竴浜涜鍙ョ殑鏁堢巼鏇撮珮銆備富瑕佺殑缂虹偣灏辨槸浜岃繘鍒舵棩蹇楀彲鑳戒細寰堝ぇ锛岃€屼笖涓嶇洿瑙傦紝鎵€浠ワ紝浣犱笉鑳戒娇鐢╩ysqlbinlog鏉ユ煡鐪嬩簩杩涘埗鏃ュ織銆傚浜庝竴浜涜鍙ワ紝鍩轰簬璁板綍鐨勫鍒惰兘澶熸洿鏈夋晥鐨勫伐浣滐紝濡傦細mysql> INSERT INTO summary_table(col1, col2, sum_col3) -> SELECT col1, col2, sum(col3) -> FROM enormous_table -> GROUP BY col1, col2; 鍋囪锛屽彧鏈変笁绉嶅敮涓€鐨刢ol1鍜宑ol2鐨勭粍鍚堬紝浣嗘槸锛岃鏌ヨ浼氭壂鎻忓師琛ㄧ殑璁稿琛岋紝鍗翠粎杩斿洖涓夋潯璁板綍銆傛鏃讹紝鍩轰簬璁板綍鐨勫鍒舵晥鐜囨洿楂樸€?鍙︿竴鏂归潰锛屼笅闈㈢殑璇彞锛屽熀浜庤鍙ョ殑澶嶅埗鏇存湁鏁堬細 mysql> UPDATE enormous_table SET col1 = 0;姝ゆ椂浣跨敤鍩轰簬璁板綍鐨勫鍒朵唬浠蜂細闈炲父楂樸€傜敱浜庝袱绉嶆柟寮忎笉鑳藉鎵€鏈夋儏鍐甸兘鑳藉緢濂界殑澶勭悊锛屾墍浠ワ紝MySQL 5.1鏀寔鍦ㄥ熀浜庤鍙ョ殑澶嶅埗鍜屽熀浜庤褰曠殑澶嶅埗涔嬪墠鍔ㄦ€佷氦鎹€備綘鍙互閫氳繃璁剧疆session鍙橀噺binlog_format鏉ヨ繘琛屾帶鍒躲€?/h3>

  3.3銆佸鍒剁浉鍏崇殑鏂囦欢闄や簡浜岃繘鍒舵棩蹇楀拰涓户鏃ュ織鏂囦欢澶栵紝杩樻湁鍏跺畠涓€浜涗笌澶嶅埗鐩稿叧鐨勬枃浠躲€傚涓嬶細

  (1)mysql-bin.index鏈嶅姟鍣ㄤ竴鏃﹀紑鍚簩杩涘埗鏃ュ織锛屼細浜х敓涓€涓笌浜屾棩蹇楁枃浠跺悓鍚嶏紝浣嗘槸浠?index缁撳熬鐨勬枃浠躲€傚畠鐢ㄤ簬璺熻釜纾佺洏涓婂瓨鍦ㄥ摢浜涗簩杩涘埗鏃ュ織鏂囦欢銆侻ySQL鐢ㄥ畠鏉ュ畾浣嶄簩杩涘埗鏃ュ織鏂囦欢銆?/h4>

  (2)mysql-relay-bin.index璇ユ枃浠剁殑鍔熻兘涓巑ysql-bin.index绫讳技锛屼絾鏄畠鏄拡瀵逛腑缁ф棩蹇楋紝鑰屼笉鏄簩杩涘埗鏃ュ織銆?/h4>

  (3)master.info淇濆瓨浠庢湇鍔″櫒杩炴帴鑷充富鏈嶅姟鏃舵墍闇€瑕佺殑淇℃伅锛屾瘡琛屼竴涓€间繚瀛榤aster鐨勭浉鍏充俊鎭€備笉瑕佸垹闄ゅ畠锛屽惁鍒欙紝slave閲嶅惎鍚庝笉鑳借繛鎺aster銆?/h4>

  (4)relay-log.info 淇濆瓨浜嗗鍒朵綅缃細鍖呮嫭浜岃繘鍒舵棩蹇楀拰涓户鏃ュ織鐨勬枃浠跺強浣嶇疆銆?/h4>

  3.4銆佸彂閫佸鍒朵簨浠跺埌鍏跺畠slave

  褰撹缃甽og_slave_updates鏃讹紝浣犲彲浠ヨslave鎵紨鍏跺畠slave鐨刴aster銆傛鏃讹紝slave鎶奡QL绾跨▼鎵ц鐨勪簨浠跺啓杩涜鑷繁鐨勪簩杩涘埗鏃ュ織(binary log)锛岀劧鍚庯紝瀹冪殑slave鍙互鑾峰彇杩欎簺浜嬩欢骞舵墽琛屽畠銆傚涓嬶細

  QTlBTWGdZZUFBR09GMUtqaHNVMTI0LmpwZw==" border="0" height="349" hspace="0" src="http://www.it165.net/uploadfile/files/2014/0419/201404190833212.jpg" title="yyyy.jpg" vspace="0" width="733" />

  聽

  3.5銆佸鍒惰繃婊?Replication Filters)

  澶嶅埗杩囨护鍙互璁╀綘鍙***鍔″櫒涓殑涓€閮ㄥ垎鏁版嵁锛屾湁涓ょ澶嶅埗杩囨护锛氬湪master涓婅繃婊や簩杩涘埗鏃ュ織涓殑浜嬩欢锛涘湪slave涓婅繃婊や腑缁ф棩蹇椾腑鐨勪簨浠躲€傚涓嬶細

  

  聽

  4.澶嶅埗鐨勫父鐢ㄦ嫇鎵戠粨鏋勫鍒剁殑浣撶郴缁撴瀯鏈変互涓嬩竴浜涘熀鏈師鍒欙細(1)姣忎釜slave鍙互鏈変竴涓垨澶氫釜master锛?2)姣忎釜slave鍙兘鏈変竴涓敮涓€鐨勬湇鍔″櫒ID锛?3)姣忎釜master鍙互鏈夊緢澶歴lave锛?4)濡傛灉璁剧疆log_slave_updates锛宻lave鍙互鏄叾瀹僺lave鐨刴aster锛屼粠鑰屾墿鏁aster鐨勬洿鏂般€?/h1>

  鍦ㄦ棭鏈熺殑MySQL鏄笉鏀寔澶氫富鏈嶅姟鍣ㄥ鍒?Multimaster Replication)鈥斺€斿嵆涓€涓猻lave鍙互鏈夊涓猰aster鐨勫満鏅?浣嗘槸MariaDB鐩墠宸茬粡鏀寔銆備絾鏄紝閫氳繃涓€浜涚畝鍗曠殑缁勫悎锛屾垜浠嵈鍙互寤虹珛鐏垫椿鑰屽己澶х殑澶嶅埗浣撶郴缁撴瀯銆?/p>

  聽

  4.1銆佸崟涓€master鍜屽slave鐢变竴涓猰aster鍜屼竴涓猻lave缁勬垚澶嶅埗绯荤粺鏄渶绠€鍗曠殑鎯呭喌銆係lave涔嬮棿骞朵笉鐩镐簰閫氫俊锛屽彧鑳戒笌master杩涜閫氫俊銆?/h3>

  鍦ㄥ疄闄呭簲鐢ㄥ満鏅腑锛?a style="border-bottom: #333333 1px dotted; color: #333333; text-decoration: none;" href="http://www.it165.net/database/dbmy/" class="keylink" target="_blank">MySQL澶嶅埗90%浠ヤ笂閮芥槸涓€涓狹aster澶嶅埗鍒颁竴涓垨鑰呭涓猄lave鐨勬灦鏋勬ā寮忥紝涓昏鐢ㄤ簬璇诲帇鍔涙瘮杈冨ぇ鐨勫簲鐢ㄧ殑鏁版嵁搴撶寤変环鎵╁睍瑙ｅ喅鏂规銆傚洜涓哄彧瑕丮aster鍜孲lave鐨勫帇鍔涗笉鏄お澶э紙灏ゅ叾鏄疭lave绔帇鍔涳級鐨勮瘽锛屽紓姝ュ鍒剁殑寤舵椂涓€鑸兘寰堝皯寰堝皯銆傚挨鍏舵槸鑷粠Slave绔殑澶嶅埗鏂瑰紡鏀规垚涓や釜绾跨▼澶勭悊涔嬪悗锛屾洿鏄噺灏忎簡Slave绔殑寤舵椂闂銆傝€屽甫鏉ョ殑鏁堢泭鏄紝瀵逛簬鏁版嵁瀹炴椂鎬ц姹備笉鏄壒鍒獵ritical鐨勫簲鐢紝鍙渶瑕侀€氳繃寤変环鐨刾cserver鏉ユ墿灞昐lave鐨勬暟閲忥紝灏嗚鍘嬪姏鍒嗘暎鍒板鍙癝lave鐨勬満鍣ㄤ笂闈紝鍗冲彲閫氳繃鍒嗘暎鍗曞彴鏁版嵁搴撴湇鍔″櫒鐨勮鍘嬪姏鏉ヨВ鍐虫暟鎹簱绔殑璇绘€ц兘鐡堕锛屾瘯绔熷湪澶у鏁版暟鎹簱搴旂敤绯荤粺涓殑璇诲帇鍔涜繕鏄姣斿啓鍘嬪姏澶у緢澶氥€傝繖鍦ㄥ緢澶х▼搴︿笂瑙ｅ喅浜嗙洰鍓嶅緢澶氫腑灏忓瀷缃戠珯鐨勬暟鎹簱鍘嬪姏鐡堕闂锛岀敋鑷虫湁浜涘ぇ鍨嬬綉绔欎篃鍦ㄤ娇鐢ㄧ被浼兼柟妗堣В鍐虫暟鎹簱鐡堕銆?/p>

  濡備笅锛?/p>

  

  濡傛灉鍐欐搷浣滆緝灏戯紝鑰岃鎿嶄綔寰堟椂锛屽彲浠ラ噰鍙栬繖绉嶇粨鏋勩€備綘鍙互灏嗚鎿嶄綔鍒嗗竷鍒板叾瀹冪殑slave锛屼粠鑰屽噺灏弇aster鐨勫帇鍔涖€備絾鏄紝褰搒lave澧炲姞鍒颁竴瀹氭暟閲忔椂锛宻lave瀵筸aster鐨勮礋杞戒互鍙婄綉缁滃甫瀹介兘浼氭垚涓轰竴涓弗閲嶇殑闂銆傝繖绉嶇粨鏋勮櫧鐒剁畝鍗曪紝浣嗘槸锛屽畠鍗撮潪甯哥伒娲伙紝瓒冲婊¤冻澶у鏁板簲鐢ㄩ渶姹傘€備竴浜涘缓璁細(1) 涓嶅悓鐨剆lave鎵紨涓嶅悓鐨勪綔鐢?渚嬪浣跨敤涓嶅悓鐨勭储寮曪紝鎴栬€呬笉鍚岀殑瀛樺偍寮曟搸)锛?2) 鐢ㄤ竴涓猻lave浣滀负澶囩敤master锛屽彧杩涜澶嶅埗锛?3) 鐢ㄤ竴涓繙绋嬬殑slave锛岀敤浜庣伨闅炬仮澶嶏紱

  澶у搴旇閮芥瘮杈冩竻妤氾紝浠庝竴涓狹aster鑺傜偣鍙互澶嶅埗鍑哄涓猄lave鑺傜偣锛屽彲鑳芥湁浜轰細鎯筹紝閭ｄ竴涓猄lave鑺傜偣鏄惁鍙互浠庡涓狹aster鑺傜偣涓婇潰杩涜澶嶅埗鍛紵杩欐槸瀹屽叏鍙互瀹炵幇鐨?鐩墠鍦∕ariaDB涓凡缁忓疄鐜癿ulti-master replication 鍔熻兘;鍙弬鑰冦€奙ariaDB澶氭簮(涓?澶嶅埗銆?/p>

  聽

  4.2銆佷富鍔ㄦā寮忕殑Master-Master(Master-Master in Active-Active Mode)Master-Master澶嶅埗鐨勪袱鍙版湇鍔″櫒锛屾棦鏄痬aster锛屽張鏄彟涓€鍙版湇鍔″櫒鐨剆lave銆傝繖鏍凤紝浠讳綍涓€鏂规墍鍋氱殑鍙樻洿锛岄兘浼氶€氳繃澶嶅埗搴旂敤鍒板彟澶栦竴鏂圭殑鏁版嵁搴撲腑銆傚彲鑳芥湁浜涜鑰呮湅鍙嬩細鏈変竴涓媴蹇冿紝杩欐牱鎼缓澶嶅埗鐜涔嬪悗锛岄毦閬撲笉浼氶€犳垚涓ゅ彴MySQL涔嬮棿鐨勫惊鐜鍒朵箞锛熷疄闄呬笂MySQL鑷繁鏃╁氨鎯冲埌浜嗚繖涓€鐐癸紝鎵€浠ュ湪MySQL鐨凚inaryLog涓褰曚簡褰撳墠MySQL鐨剆erver-id锛岃€屼笖杩欎釜鍙傛暟涔熸槸鎴戜滑鎼缓MySQLReplication鐨勬椂鍊欏繀椤绘槑纭寚瀹氾紝鑰屼笖Master鍜孲lave鐨剆erver-id鍙傛暟鍊兼瘮闇€瑕佷笉涓€鑷存墠鑳戒娇MySQLReplication鎼缓鎴愬姛銆備竴鏃︽湁浜唖erver-id鐨勫€间箣鍚庯紝MySQL灏卞緢瀹规槗鍒ゆ柇鏌愪釜鍙樻洿鏄粠鍝竴涓狹ySQLServer鏈€鍒濅骇鐢熺殑锛屾墍浠ュ氨寰堝鏄撻伩鍏嶅嚭鐜板惊鐜鍒剁殑鎯呭喌銆傝€屼笖锛屽鏋滄垜浠笉鎵撳紑璁板綍Slave鐨凚inaryLog鐨勯€夐」锛?-log-slave-update锛夌殑鏃跺€欙紝MySQL鏍规湰灏变笉浼氳褰曞鍒惰繃绋嬩腑鐨勫彉鏇村埌BinaryLog涓紝灏辨洿涓嶇敤鎷呭績鍙兘浼氬嚭鐜板惊鐜鍒剁殑鎯呭舰浜嗐€傚鍥撅細

  

  涓诲姩鐨凪aster-Master澶嶅埗鏈変竴浜涚壒娈婄殑鐢ㄥ銆備緥濡傦紝鍦扮悊涓婂垎甯冪殑涓や釜閮ㄥ垎閮介渶瑕佽嚜宸辩殑鍙啓鐨勬暟鎹壇鏈€傝繖绉嶇粨鏋勬渶澶х殑闂灏辨槸鏇存柊鍐茬獊銆傚亣璁句竴涓〃鍙湁涓€琛?涓€鍒?鐨勬暟鎹紝鍏跺€间负1锛屽鏋滀袱涓湇鍔″櫒鍒嗗埆鍚屾椂鎵ц濡備笅璇彞锛氬湪绗竴涓湇鍔″櫒涓婃墽琛岋細mysql> UPDATE tbl SET col=col + 1;鍦ㄧ浜屼釜鏈嶅姟鍣ㄤ笂鎵ц锛歮ysql> UPDATE tbl SET col=col * 2;閭ｄ箞缁撴灉鏄灏戝憿锛熶竴鍙版湇鍔″櫒鏄?锛屽彟涓€涓湇鍔″櫒鏄?锛屼絾鏄紝杩欏苟涓嶄細浜х敓閿欒銆?

  瀹為檯涓婏紝MySQL骞朵笉鏀寔鍏跺畠涓€浜汥BMS鏀寔鐨勫涓绘湇鍔″櫒澶嶅埗(Multimaster Replication)锛岃繖鏄疢ySQL鐨勫鍒跺姛鑳藉緢澶х殑涓€涓檺鍒?澶氫富鏈嶅姟鍣ㄧ殑闅剧偣鍦ㄤ簬瑙ｅ喅鏇存柊鍐茬獊)锛屼絾鏄紝濡傛灉浣犲疄鍦ㄦ湁杩欑闇€姹傦紝浣犲彲浠ラ噰鐢∕ySQL Cluster锛屼互鍙婂皢Cluster鍜孯eplication缁撳悎璧锋潵锛屽彲浠ュ缓绔嬪己澶х殑楂樻€ц兘鐨勬暟鎹簱骞冲彴銆備絾鏄紝鍙互閫氳繃鍏跺畠涓€浜涙柟寮忔潵妯℃嫙杩欑澶氫富鏈嶅姟鍣ㄧ殑澶嶅埗銆?/p>

  聽

  绀轰緥:鍙屼富妯″紡閰嶇疆:

  聽

  鐜锛?/strong>

  Master1/Master2	Platfrom	IP	APP Version
  Master1	CentOS6.5_X86-64	172.16.41.1	mariadb-10.0.10
  Master2	CentOS6.5_X86-64	172.16.41.2	mariadb-10.0.10

  1.閰嶇疆鍙屼富:

  #Master1鐨勯厤缃細

  聽

  (1)淇敼server-id涓婚厤缃枃浠?etc/my.cnf [mysqld]娈典腑,淇敼濡備笅琛岋細

  聽

  server-id = 1

  (2)鍚敤涓户鏃ュ織,浜岃繘鍒舵棩蹇?/strong>

  涓婚厤缃枃浠?etc/my.cnf [mysqld]娈典腑,淇敼濡備笅琛岋細

  log-bin = /mydata/binlogs/master1-bin relay-log = /mydata/relaylogs/relay-bin 聽 纭繚涓户鏃ュ織閫夐」寮€鍚?/strong> MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'relay_log';+---------------+-----------------------------+| Variable_name | Value |+---------------+-----------------------------+| relay_log | /mydata/relaylogs/relay-bin |+---------------+-----------------------------+1 row in set (0.00 sec) 聽

  (3)娣诲姞涓嬮潰涓ら」,浠ラ伩鍏嶅湪MySQL鑷姩涓篒NSERT璇彞閫夋嫨涓嶄簰鐩稿啿绐佺殑鍊?/strong>

  auto-increment-offset = 1 //璧峰鍊?/strong> auto-increment-increment = 2 //姝ラ暱 聽 纭繚鑷姩澧為暱閫夐」宸插紑鍚細 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 2 | | auto_increment_offset | 1 | +--------------------------+-------+

  聽

  (4)鍒涘缓鏈夊鍒舵潈闄愮殑鐢ㄦ埛,鍛戒护濡備笅锛?/strong>

  聽

  MariaDB [(none)]> CREATE USER 'luccy'@'172.16.41.2' IDENTIFIED BY 'qazwsx123';MariaDB [(none)]> REVOKE ALL PRIVILEGES ,GRANT OPTION FROM 'luccy'@'172.16.41.2';MariaDB [(none)]> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'luccy'@'172.16.41.2'; MariaDB [(none)]> FLUSH PRIVILEGES;

  聽

  #Master2鐨勯厤缃細

  (1)淇敼server-id#

  聽

  涓婚厤缃枃浠?etc/my.cnf [mysqld]娈典腑,淇敼濡備笅琛岋細

  聽

  server-id = 2

  (2)鍚敤涓户鏃ュ織,浜岃繘鍒舵棩蹇?/strong>

  聽

  log-bin = /mydata/binlogs/master2-bin relay-log = /mydata/relaylogs/relay-bin 纭繚涓户鏃ュ織閫夐」寮€鍚?/strong> MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'relay_log';+---------------+-----------------------------+| Variable_name | Value |+---------------+-----------------------------+| relay_log | /mydata/relaylogs/relay-bin |+---------------+-----------------------------+

  聽

  (3)娣诲姞涓嬮潰涓ら」,浠ラ伩鍏嶅湪MySQL鑷姩涓篒NSERT璇彞閫夋嫨涓嶄簰鐩稿啿绐佺殑鍊?/strong>

  auto-increment-offset = 2 //璧峰鍊?/strong> auto-increment-increment = 2 //姝ラ暱 聽 聽 纭繚鑷姩澧為暱閫夐」宸插紑鍚細 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 2 | | auto_increment_offset | 2 | +--------------------------+-------+

  (4)鍒涘缓鏈夊鍒舵潈闄愮殑鐢ㄦ埛,鍛戒护濡備笅锛?/strong>

  聽

  MariaDB [(none)]> CREATE USER 'jerry'@'172.16.41.1' IDENTIFIED BY 'qazwsx123';MariaDB [(none)]> REVOKE ALL PRIVILEGES ,GRANT OPTION FROM 'jerry'@'172.16.41.1';MariaDB [(none)]> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'jerry'@'172.16.41.1'; MariaDB [(none)]> FLUSH PRIVILEGES;

  濡傛灉姝ゆ椂涓ゅ彴鏈嶅姟鍣ㄥ潎涓烘柊寤虹珛锛屼笖鏃犲叾瀹冨啓鍏ユ搷浣滐紝鍚勬湇鍔″櫒鍙渶璁板綍褰撳墠鑷繁浜岃繘鍒舵棩蹇楁枃浠跺強浜嬩欢浣嶇疆锛屼互涔嬩綔涓哄彟澶栫殑鏈嶅姟鍣ㄥ鍒惰捣濮嬩綅缃嵆鍙?/strong>

  #Master1: 聽 MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000001 | 969 | | | +--------------------+----------+--------------+------------------+ #Master2: 聽 聽 MariaDB [(none)]> SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master2-bin.000001 | 314 | | | +--------------------+----------+--------------+------------------+

  鍚勬湇鍔″櫒鎺ヤ笅鏉ユ寚瀹氬鍙︿竴鍙版湇鍔″櫒涓鸿嚜宸辩殑涓绘湇鍔″櫒

  #Master1鎸囧悜Master2 MariaDB [(none)]> CHANGE MASTER TO MASTER_USER='luccy',MASTER_HOST='172.16.41.2',MASTER_PASSWORD='qazwsx123',MASTER_LOG_FILE='master2-bin.000005',MASTER_LOG_POS=328; 聽 #Master2鎸囧悜Master1 MariaDB [(none)]> CHANGE MASTER TO MASTER_USER='jerry',MASTER_HOST='172.16.41.1',MASTER_PASSWORD='qazwsx123',MASTER_LOG_FILE='master1-bin.000005',MASTER_LOG_POS=1592;

  鍚姩涓湇鍔″櫒澶嶅埗杩涚▼

  #Master1 MariaDB [(none)]> START SLAVE; MariaDB [mysql]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.41.2 Master_User: jerry Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master2-bin.000006 Read_Master_Log_Pos: 328 Relay_Log_File: relay-bin.000004 Relay_Log_Pos: 617 Relay_Master_Log_File: master2-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: .......... .......... 聽 #Master2 MariaDB [(none)]> START SLAVE; 聽 MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.41.1 Master_User: luccy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master1-bin.000005 Read_Master_Log_Pos: 1592 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 537 Relay_Master_Log_File: master1-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: .......... .......... 聽

  鍙屼富娴嬭瘯锛?/strong>

  (1)鍦∕aster1涓婂垱寤烘暟鎹簱 testdb: MariaDB [(none)]> CREATE DATABASE testdb; MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 鍦∕aster2涓婃煡鐪嬬粨鏋滀笌鍦∕aster1涓婃墍鏌ュ緱缁撴灉涓€鏍凤紒 (2)鍦∕aster1涓婂垱寤烘暟鎹簱 mydb: MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | | testdb | +--------------------+ 鍦∕aster1涓婃煡鐪嬬粨鏋滀笌鍦∕aster2涓婃墍鏌ュ緱缁撴灉涓€鏍凤紒 (3)鍦∕aster1鐨刴ydb涓婇潰鏂板缓涓€寮犺〃mytable1,骞舵彃鍏ヨ鍙?/strong> MariaDB [(none)]> use mydb MariaDB [mydb]> CREATE TABLE mytable1 (ID INT AUTO_INCREMENT UNIQUE KEY, Name CHAR(20)); MariaDB [mydb]> DESC mytable1; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ MariaDB [mydb]> INSERT INTO mytable1(Name) VALUES ('larry'),('jim'),('jerry'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 聽 MariaDB [mydb]> SELECT * FROM mytable1; +----+-------+ | ID | Name | +----+-------+ | 1 | larry| //鑷姩澧為暱鐨勬晥鏋?鍦∕aster1涓婂畾涔夌殑璧峰鍊间负1,姝ュ緞涓? | 3 | jim | | 5 | jerry| +----+-------+ 鍦∕aster2涓婇潰鎻掑叆瀛楁(浠ヤ笂鍐呭宸茬粡鍚屾鍒癕aster2) MariaDB [mydb]>INSERT INTO mytable1(Name) VALUES ('Zhang San'),('Li Si'),('Wang Wu'); MariaDB [mydb]> SELECT * FROM mytable1; +----+-----------+ | ID | Name | +----+-----------+ | 1 | larry | | 3 | jim | | 5 | jerry | | 6 | Zhang San | | 8 | Li Si | | 10 | Wang Wu | +----+-----------+ //鐪嬫潵涓嶆槸鎯宠鐨勭粨鏋?铏界劧璇碝ySQL鑷姩涓篒NSERT閫夋嫨鐨勫€间笉浼氬啀鍑虹幇浜掔浉鍐茬獊鐨勬儏鍐?浣嗘槸璨屼技娌℃湁鎸夋暟瀛楁帓搴?鎵句笉鍒拌В鍐宠繖涓棶棰樼殑鍔炴硶,闄ら潪涓嶉€夋嫨鐢ㄥ弻涓绘ā寮忥紒

  聽

  4.3銆佷富鍔?琚姩妯″紡鐨凪aster-Master(Master-Master in Active-Passive Mode)

  杩欐槸master-master缁撴瀯鍙樺寲鑰屾潵鐨勶紝瀹冮伩鍏嶄簡M-M鐨勭己鐐癸紝瀹為檯涓婏紝杩欐槸涓€绉嶅叿鏈夊閿欏拰楂樺彲鐢ㄦ€х殑绯荤粺銆傚畠鐨勪笉鍚岀偣鍦ㄤ簬鍏朵腑涓€涓湇鍔″彧鑳借繘琛屽彧璇绘搷浣溿€傚鍥撅細

  聽

  

  聽

  4.4 绾ц仈澶嶅埗鏋舵瀯 Master 鈥揝laves - Slaves

  鍦ㄦ湁浜涘簲鐢ㄥ満鏅腑锛屽彲鑳借鍐欏帇鍔涘樊鍒瘮杈冨ぇ锛岃鍘嬪姏鐗瑰埆鐨勫ぇ锛屼竴涓狹aster鍙兘闇€瑕佷笂10鍙扮敋鑷虫洿澶氱殑Slave鎵嶈兘澶熸敮鎾戞敞璇荤殑鍘嬪姏銆傝繖鏃跺€欙紝Master灏变細姣旇緝鍚冨姏浜嗭紝鍥犱负浠呬粎杩炰笂鏉ョ殑SlaveIO绾跨▼灏辨瘮杈冨浜嗭紝杩欐牱鍐欑殑鍘嬪姏绋嶅井澶т竴鐐圭殑鏃跺€欙紝Master绔洜涓哄鍒跺氨浼氭秷鑰楄緝澶氱殑璧勬簮锛屽緢瀹规槗閫犳垚澶嶅埗鐨勫欢鏃躲€?/strong>

  閬囧埌杩欑鎯呭喌濡備綍瑙ｅ喅鍛紵杩欐椂鍊欐垜浠氨鍙互鍒╃敤MySQL鍙互鍦⊿lave绔褰曞鍒舵墍浜х敓鍙樻洿鐨凚inaryLog淇℃伅鐨勫姛鑳斤紝涔熷氨鏄墦寮€鈥攍og-slave-update閫夐」銆傜劧鍚庯紝閫氳繃浜岀骇锛堟垨鑰呮槸鏇村绾у埆锛夊鍒舵潵鍑忓皯Master绔洜涓哄鍒舵墍甯︽潵鐨勫帇鍔涖€備篃灏辨槸璇达紝鎴戜滑棣栧厛閫氳繃灏戞暟鍑犲彴MySQL浠嶮aster鏉ヨ繘琛屽鍒讹紝杩欏嚑鍙版満鍣ㄦ垜浠涓旂О涔嬩负绗竴绾lave闆嗙兢锛岀劧鍚庡叾浠栫殑Slave鍐嶄粠绗竴绾lave闆嗙兢鏉ヨ繘琛屽鍒躲€備粠绗竴绾lave杩涜澶嶅埗鐨凷lave锛屾垜绉颁箣涓虹浜岀骇Slave闆嗙兢銆傚鏋滄湁闇€瑕侊紝鎴戜滑鍙互缁х画寰€涓嬪鍔犳洿澶氬眰娆＄殑澶嶅埗銆傝繖鏍凤紝鎴戜滑寰堝鏄撳氨鎺у埗浜嗘瘡涓€鍙癕ySQL涓婇潰鎵€闄勫睘Slave鐨勬暟閲忋€傝繖绉嶆灦鏋勬垜绉颁箣涓篗aster-Slaves-Slaves鏋舵瀯

  杩欑澶氬眰绾ц仈澶嶅埗鐨勬灦鏋勶紝寰堝鏄撳氨瑙ｅ喅浜哅aster绔洜涓洪檮灞濻lave澶鑰屾垚涓虹摱棰堢殑椋庨櫓銆備笅鍥惧睍绀轰簡澶氬眰绾ц仈澶嶅埗鐨凴eplication鏋舵瀯銆?/strong>

  

  褰撶劧锛屽鏋滄潯浠跺厑璁革紝鎴戞洿鍊惧悜浜庡缓璁ぇ瀹堕€氳繃鎷嗗垎鎴愬涓猂eplication闆嗙兢鏉ヨВ鍐?/strong>

  涓婅堪鐡堕闂銆傛瘯绔烻lave骞舵病鏈夊噺灏戝啓鐨勯噺锛屾墍鏈塖lave瀹為檯涓婁粛鐒惰繕鏄簲鐢ㄤ簡鎵€鏈夌殑鏁版嵁鍙樻洿鎿嶄綔锛屾病鏈夊噺灏戜换浣曞啓IO銆傜浉鍙嶏紝Slave瓒婂锛屾暣涓泦缇ょ殑鍐橧O鎬婚噺涔熷氨浼氳秺澶氾紝鎴戜滑娌℃湁闈炲父鏄庢樉鐨勬劅瑙夛紝浠呬粎鍙槸鍥犱负鍒嗘暎鍒颁簡澶氬彴鏈哄櫒涓婇潰锛屾墍浠ヤ笉鏄緢瀹规槗琛ㄧ幇鍑烘潵銆?/strong>

  姝ゅ锛屽鍔犲鍒剁殑绾ц仈灞傛锛屽悓涓€涓彉鏇翠紶鍒版渶搴曞眰鐨凷lave鎵€闇€瑕佺粡杩囩殑MySQL涔熶細鏇村锛屽悓鏍峰彲鑳介€犳垚寤舵椂杈冮暱鐨勯闄┿€傝€屽鏋滄垜浠€氳繃鍒嗘媶闆嗙兢鐨勬柟寮忔潵瑙ｅ喅鐨勮瘽锛屽彲鑳藉氨浼氳濂藉緢澶氫簡锛屽綋鐒讹紝鍒嗘媶闆嗙兢涔熼渶瑕佹洿澶嶆潅鐨勬妧鏈拰鏇村鏉傜殑搴旂敤绯荤粺鏋舵瀯銆?/strong>

  4.5銆佸甫浠庢湇鍔″櫒鐨凪aster-Master缁撴瀯(Master-Master with Slaves)杩欑缁撴瀯鐨勪紭鐐瑰氨鏄彁渚涗簡鍐椾綑銆傚湪鍦扮悊涓婂垎甯冪殑澶嶅埗缁撴瀯锛屽畠涓嶅瓨鍦ㄥ崟涓€鑺傜偣鏁呴殰闂锛岃€屼笖杩樺彲浠ュ皢璇诲瘑闆嗗瀷鐨勮姹傛斁鍒皊lave涓娿€?/strong>

  

  绾ц仈澶嶅埗鍦ㄤ竴瀹氱▼搴︿笂闈㈢‘瀹炶В鍐充簡Master鍥犱负鎵€闄勫睘鐨凷lave杩囧鑰屾垚涓虹摱棰堢殑闂锛屼絾鏄粬骞朵笉鑳借В鍐充汉宸ョ淮鎶ゅ拰鍑虹幇寮傚父闇€瑕佸垏鎹㈠悗鍙兘瀛樺湪閲嶆柊鎼缓Replication鐨勯棶棰樸€傝繖鏍峰氨寰堣嚜鐒剁殑寮曠敵鍑轰簡DualMaster涓庣骇鑱斿鍒剁粨鍚堢殑Replication鏋舵瀯锛屾垜绉颁箣涓篗aster-Master-Slaves鏋舵瀯

  鍜孧aster-Slaves-Slaves鏋舵瀯鐩告瘮锛屽尯鍒粎浠呭彧鏄皢绗竴绾lave闆嗙兢鎹㈡垚浜嗕竴鍙板崟鐙殑Master锛屼綔涓哄鐢∕aster锛岀劧鍚庡啀浠庤繖涓鐢ㄧ殑Master杩涜澶嶅埗鍒颁竴涓猄lave闆嗙兢銆?/strong>

  杩欑DualMaster涓庣骇鑱斿鍒剁粨鍚堢殑鏋舵瀯锛屾渶澶х殑濂藉灏辨槸鏃㈠彲浠ラ伩鍏嶄富Master鐨勫啓鍏ユ搷浣滀笉浼氬彈鍒癝lave闆嗙兢鐨勫鍒舵墍甯︽潵鐨勫奖鍝嶏紝鍚屾椂涓籑aster闇€瑕佸垏鎹㈢殑鏃跺€欎篃鍩烘湰涓婁笉浼氬嚭鐜伴噸鎼璕eplication鐨勬儏鍐点€備絾鏄紝杩欎釜鏋舵瀯涔熸湁涓€涓紛绔紝閭ｅ氨鏄鐢ㄧ殑Master鏈夊彲鑳芥垚涓虹摱棰堬紝鍥犱负濡傛灉鍚庨潰鐨凷lave闆嗙兢姣旇緝澶х殑璇濓紝澶囩敤Master鍙兘浼氬洜涓鸿繃澶氱殑SlaveIO绾跨▼璇锋眰鑰屾垚涓虹摱棰堛€傚綋鐒讹紝璇ュ鐢∕aster涓嶆彁渚涗换浣曠殑璇绘湇鍔＄殑鏃跺€欙紝鐡堕鍑虹幇鐨勫彲鑳芥€у苟涓嶆槸鐗瑰埆楂橈紝濡傛灉鍑虹幇鐡堕锛屼篃鍙互鍦ㄥ鐢∕aster鍚庨潰鍐嶆杩涜绾ц仈澶嶅埗锛屾灦璁惧灞係lave闆嗙兢銆傚綋鐒讹紝绾ц仈澶嶅埗鐨勭骇鍒秺澶氾紝Slave闆嗙兢鍙兘鍑虹幇鐨勬暟鎹欢鏃朵篃浼氭洿涓烘槑鏄撅紝鎵€浠ヨ€冭檻浣跨敤澶氬眰绾ц仈澶嶅埗涔嬪墠锛屼篃闇€瑕佽瘎浼版暟鎹欢鏃跺搴旂敤绯荤粺鐨勫奖鍝嶃€?/strong>

聽MariaDB/Mysql涔嬩富浠庢灦鏋勭殑澶嶅埗鍘熺悊鍙婁富浠?鍙屼富閰嶇疆璇﹁В(涓€)

聽