MySQL涓讳粠澶囦唤

鏁版嵁搴撶殑瑁稿鏃朵唬

濡備綍瑙e喅鍗曠偣闂

MySQL涓讳粠澶嶅埗鏋舵瀯

  1. 涓诲簱灏嗗彉鏇村啓鍏ュ埌涓诲簱鐨刡inlog
    > 涓浜汳ySQL鐗堟湰骞朵笉浼氬紑鍚簩杩涘埗鏃ュ織锛屾墍浠ヤ竴瀹氳妫鏌ユ槸鍚﹀紑鍚
    > 澧為噺澶囦唤涔熼渶瑕佸紑鍚簩杩涘埗鏃ュ織
  2. 浠庡簱鐨処O杩涚▼璇诲彇涓诲簱鐨刡inlog鍐呭瀛樺偍鍒皉elay log鏃ュ織涓
  3. 浠庡簱鐨凷QL杩涚▼璇诲彇relay log鏃ュ織涓唴瀹瑰湪浠庡簱涓噸鏀

MySQL涓讳粠閰嶇疆姝ラ

  1. 閰嶇疆涓讳粠鏁版嵁搴撴湇鍔″櫒鍙傛暟
    > 涓绘湇鍔″櫒閰嶇疆
log_bin = /data/mysql/sql_log/mysql-bin
server_id= 100  ##寤鸿鐢╥p鐨勬渶鍚庝竴娈

浠庢湇鍔″櫒閰嶇疆

log_bin = /data/mysql/sql_log/mysql-bin
server-id = 101
relay_log = /data/mysql/sql_log/relay-bin
read_only = on
super_read_only = on
skip_slave_sart = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
  1. 鍦∕aster鏈嶅姟鍣ㄤ笂鍒涘缓鐢ㄤ簬澶嶅埗鐨勬暟鎹簱璐﹀彿
    鐢ㄤ簬IO杩涚▼杩炴帴master鏈嶅姟鍣ㄨ幏鍙朾inlog鏃ュ織
    闇瑕乺eplication slave鏉冮檺
create user 'repl'@`ip娈礰 identified by 'password';
grant replication slave on *.* to 'repl'@`ip娈礰;
  1. 澶囦唤master鏈嶅姟鍣ㄤ笂鐨勬暟鎹苟鍒濆鍖杝lave鏈嶅姟鍣ㄦ暟鎹

    寤鸿涓讳粠鏁版嵁搴撴湇鍔″櫒閲囩敤鐩稿悓鐨凪ySQL鐗堟湰
    寤鸿浣跨敤鍏ㄥ簱澶囦唤鐨勬柟寮忓垵濮嬪寲slave鏁版嵁
    mysqldump --master-data=2 -uroot-p -A --single-transcation -R --triggers --routines --all-databases > all.sql

  2. 鍚姩鍩轰簬鏃ュ織鐐圭殑澶嶅埗閾捐矾

change master to 
    master_host = 'master_host_ip',
    master_user = 'repl',
    master_password = 'password',
    master_log_file = 'mysql_log_file_name',
    master_log_pos = xxx;
start slave;
show slave status \G;
#鏌ョ湅SLAVE_IO_Running 鍜孲LAVE_SQL_Running鐨勭姸鎬
  1. 鍚姩鍩轰簬GTID鐨勫鍒堕摼璺
gtid_mode = on
enforce_gtid-consistency
log-slave-updates = on

change master to 
    master_host = 'master_host_ip',
    master_user = 'repl',
    master_password = 'password',
    master_auto_position = 1

gtid澶嶅埗鐨勯檺鍒
– 鏃犳硶鍐嶄娇鐢╟reate table .. select 绠鍘嗚〃
– 鏃犳硶鍦ㄤ簨鍔′腑浣跨敤create temporary table寤虹珛涓存椂琛
– 鏃犳硶浣跨敤鍏宠仈鏇存柊鍚屾椂鏇存柊浜嬪姟琛ㄥ拰闈炰簨鍔¤〃

寮曞叆澶嶅埗鍚庣殑鏁版嵁搴撴灦鏋

铏氭嫙ip(vip)

灏辨槸涓涓湭鍒嗛厤缁欑湡瀹炰富鏈虹殑ip锛屼篃灏辨槸璇村澶栨彁渚涙湇鍔″櫒鐨勪富鏈洪櫎浜嗘湁涓涓湡瀹瀒p澶栬繕鏈変竴涓櫄ip

璁剧疆铏氭嫙ip鏈夊緢澶氱鏂规硶

涓讳富澶嶅埗閰嶇疆璋冩暣

淇濊瘉鍙湁涓涓富鎻愪緵鏈嶅姟 鍙︿竴涓彁渚涘彧璇荤殑鏈嶅姟
master 鏁版嵁搴撻厤缃慨鏀

auto_increment_increment = 2
auto_increment_offset = 1
1,3,5,7,9...

涓诲鏁版嵁搴撻厤缃慨鏀

auto_increment_increment = 2
auto_increment_offset = 2
2,4,6,8,10...

keepalived绠浠

keeplived 鍩轰簬arrp缃戠粶鍗忚
yum install keepalived y
vim /etc/keepalived/keeplived.conf
chmod a+x /etc/keepalived/check_mysql.sh
systemctl keepalived start

All posts

Other pages

鍙戣〃璇勮

閭鍦板潃涓嶄細琚叕寮銆 蹇呭~椤瑰凡鐢*鏍囨敞