熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

通過Heartbert2 讓Mysql Replication 具有

2013-11-23 20:57:42  來源: MySQL 

  前言

  MasterSlave 的數據庫機構解決了很多問題特別是read/write 比較高的應用結構如圖

  

  

  寫操作全部在Master 結點執行並由Slave 數據庫結點定時(默認s)讀取Master 的binlog

  將眾多的用戶讀請求分散到更多的數據庫節點從而減輕了單點的壓力

  它的缺點是

  Slave 實時性的保障對於實時性很高的場合可能需要做一些處理

  高可用性問題Master 就是那個致命點(SPOF:Single point of failure)

  本文主要討論的是如何解決第 個缺點

  解決方案如下圖

  

  使用兩個MySQL 主庫mastermaster數據存在共享設備上用heartbeat 進行監控當master 發生故障時將資源切換到master

  故障發生後無需對slave 進行修改slave 自動切到master(斷電切換需要手工同步slave)

  網絡設置
    
  

  

  磁盤設置

  

   mysql 安裝

   到官方網址 下載最新版本mysql找到對應的包在haha 分別安裝

  # cd /usr/local/

  # tar xzvf mysqllinuxx_glibctargz

  # mv mysqllinuxx_glibc mysql

  # groupadd mysql

  # useradd g mysql mysql

  #passwd mysql

   修改/etc/mycf數據文件路徑設置在共享磁盤修改相關參數 個參數在haha上必須一樣

  datadir=/u/data #數據文件路徑

  serverid = #數據庫ID

  logbin=/u/data/master # binlog 路徑

   下面步驟先在ha 上執行然後在ha 執行

  先在ha 把磁盤mount

  [root@ha mysql]# mount /dev/sdb /u

  [root@ha u]# mkdir data

  [root@ha u]# chown R mysqlmysql data/

  安裝mysql

  [root@ha u]# cd /usr/local/mysql

  # /scripts/mysql_install_db user=mysql

  # cp supportfiles/mysqlserver /etc/rcd/initd/mysqld

  # chmod +x /etc/rcd/initd/mysqld

  # chkconfig add mysqld

  # /etc/rcd/initd/mysqld start

   在ha 把共享磁盤umount在ha 上mount把上面的數據刪除後執行 步驟完成後ha 也umount

   在slave 上安裝mysql數據放在slave 本地過程略

  heartbeat 安裝

   官方網址分別在haha 安裝確認下列包安裝

  [root@ha ~]# rpm ivh libnetx_rpm

  [root@ha ~]# rpm ivh heartbeatpilsx_rpm

  [root@ha ~]# rpm ivh heartbeatstonithx_rpm

  [root@ha ~]# rpm ivh perlTimeDateelnoarchrpm

  [root@ha ~]# rpm ivh heartbeatx_rpm

  [root@ha ~]# rpm ivh heartbeatdevelx_rpm

   開始編輯配置文件(haha 都執行)

  [root@ha local]# cp /usr/share/doc/packages/heartbeat/hacf /etc/had/

  [root@ha local]# cp /usr/share/doc/packages/heartbeat/authkeys /etc/had/

  編輯/etc/had/authkeys使用的是第 種認證方式(crc)接著把文件的權限改為

  cat /etc/had/authkeys

  顯示

  auth

   crc

  更改文件權限

  chmod /etc/had/authkeys

  [root@ha ~]# cat /etc/had/hacf

  debugfile /var/log/hadebug

  logfile /var/log/halog

  logfacility local

  keepalive

  deadtime

  warntime

  udpport

  crm yes

  node ha ha

  bcast eth

  auto_failback off

  apiauth cibmon uid=hacluster

  respawn hacluster /usr/lib/heartbeat/cibmon –d

  配置資源共享IP共享磁盤MYSQL 個服務組成 組資源

  [root@ha ~]# cat /etc/had/haresources

  ha Filesystem::/dev/sdb::/u::ext mysqld

  啟動的時候從左到右依次運行腳本關閉的時候從右到左依次關閉

  這個文件原名為haresources 在x 上使用不過為了區別使用此名稱

  將資源文件轉換成cibxmlx 裡編譯好後自帶有轉換腳本

  [root@ha ~]# cd /var/lib/heartbeat/crm/

  [root@ha crm]# rm rf cibxml*

  [root@ha crm]#

  /usr/lib/heartbeat/haresourcescibpy stout c /etc/had/hacf /etc/had/haresources

  [root@ha crm]# cat cibxml|grep mysql

  <primitive class=lsb id=mysqld_ provider=heartbeat type=mysqld>

  <op id=mysqld__mon interval=s name=monitor timeout=s/>

  即每 秒檢測資源運行情況如果發現資源不在則嘗試啟動資源如果s 後還未啟動

  成功則資源切換向另節點可根據業務進行修改

   啟動heartbeat在ha 和ha 都啟動

  [root@ha ~]# /etc/initd/heartbeat start

  查看資源情況

  ============

  Last updated: Tue Feb ::

  Current DC: ha (cdebcbbbbeef)

   Nodes configured

   Resources configured

  ============

  Node: ha (cdebcbbbbeef): online

  Node: ha (abdfbeacfcfc): online

  Resource Group: group_

  IPaddr____ (ocf::heartbeat:IPaddr): Started ha

  Filesystem_ (ocf::heartbeat:Filesystem): Started ha

  mysqld_ (lsb:mysqld): Started ha

   將heartbeat 設置成開機自動重啟

  [root@ha ~]# chkconfig add heartbeat

  [root@ha ~]# chkconfig level heartbeat on

  [root@ha ~]# chkconfig list heartbeat

  heartbeat :off :off :on :on :on :on :off

  mysql slave 配置

   在master 賦予slave 權限(任一節點操作)

  [root@ha ~]# /usr/local/mysql/bin/mysql u root p

  Enter password:

  Welcome to the MySQL monitor Commands end with ; or \g

  Your MySQL connection id is

  Server version: log MySQL Community Server (GPL)

  Type help; or \h for help Type \c to clear the buffer

  mysql> GRANT RELOAD REPLICATION SLAVE ON ** TO slave@

  IDENTIFIED BY nslave;

  mysql> show master status\G;

  *************************** row ***************************

  File: master

  Position:

  Binlog_Do_DB:

  Binlog_Ignore_DB:

   row in set ( sec)

   slave 同步

  因為沒有數據所以省略數據同步的步驟在slalve 執行下列命令

  mysql> CHANGE MASTER TO MASTER_HOST=

  MASTER_PORT =

  MASTER_USER=slave

  MASTER_PASSWORD=nslave

  MASTER_LOG_FILE=master

  MASTER_LOG_POS = ;

  Query OK rows affected ( sec)

  mysql> start slave;

  Query OK rows affected ( sec)

  mysql> show slave status\G;

  *************************** row ***************************

  Slave_IO_State:Waiting for master to send event

  Master_Host:

  Master_User: slave

  Master_Port:

  Connect_Retry:

  Master_Log_File: master

  Read_Master_Log_Pos:

  Relay_Log_File: mysqlrelaybin

  Relay_Log_Pos:

  Relay_Master_Log_File: master

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

   row in set ( sec)

  失敗測試

   網絡切換測試

  將ha 的網線拔掉在ha 看資源的切換情況

  Node: ha (cdebcbbbbeef): online

  Node: ha (abdfbeacfcfc): OFFLINE

  Resource Group: group_

  IPaddr____ (ocf::heartbeat:IPaddr): Started ha

  Filesystem_ (ocf::heartbeat:Filesystem): Started ha

  mysqld_ (lsb:mysqld): Started ha

  沒有問題把ha 網線插上資源又回切到ha

  Node: ha (cdebcbbbbeef): online

  Node: ha (abdfbeacfcfc): online

  Resource Group: group_

  IPaddr____ (ocf::heartbeat:IPaddr): Started ha

  Filesystem_ (ocf::heartbeat:Filesystem): Started ha

  mysqld_ (lsb:mysqld): Started ha

   宕機切換

  將ha 強制關機在開機這和 的情況差不多能正常切換

   服務切換

  將ha 的mysql 服務強制中斷修改/etc/mycf

  datadir=/u/data #實際這個路徑不存在

  [root@ha ~]# ps ef|grep mysql

  root : ? :: /bin/sh /bin/mysqld_safe

  datadir=/u/data pidfile=/u/data/hapid

  mysql : ? :: /usr/local/mysql/bin/mysqld

  basedir=/usr/local/mysql datadir=/u/data user=mysql logerror=/u/data/haerr

  pidfile=/u/data/hapid socket=/tmp/mysqlsock port=

  root : ? :: /bin/sh /etc/initd/mysqld start

  root : pts/ :: grep mysql

  [root@ha ~]# kill

  [root@ha ~]# ps ef|grep mysql

  root : pts/ :: grep mysql

  在ha 上查看資源情況已經切換過來

  Node: ha (cdebcbbbbeef): online

  Node: ha (abdfbeacfcfc): online

  Resource Group: group_

  IPaddr____ (ocf::heartbeat:IPaddr): Started ha

  Filesystem_ (ocf::heartbeat:Filesystem): Started ha

  mysqld_ (lsb:mysqld): Started ha

  Failed actions:

  mysqld__monitor_ (node=ha call= rc=): complete

  mysqld__start_ (node=ha call= rc=): complete

   slave 同步測試

  將ha 的heartbeat 關閉

  [root@ha data]# /etc/initd/heartbeat stop

  在ha 看資源情況

  Node: ha (cdebcbbbbeef): online

  Node: ha (abdfbeacfcfc): OFFLINE

  Resource Group: group_

  IPaddr____ (ocf::heartbeat:IPaddr): Started ha

  Filesystem_ (ocf::heartbeat:Filesystem): Started ha

  mysqld_ (lsb:mysqld): Started ha

  資源全部切換到ha我們執行一些操作看是否同步

  [root@ha ~]# /usr/local/mysql/bin/mysql u root p D test

  Enter password:

  Welcome to the MySQL monitor Commands end with ; or \g

  Your MySQL connection id is

  Server version: log MySQL Community Server (GPL)

  Type help; or \h for help Type \c to clear the buffer

  root@test >create table t(t int);

  root@test >insert into t values();

  root@test >select * from t;

  ++

  | t |

  ++

  | |

  ++

   row in set ( sec)

  在slave 查看是否同步

  [root@mysql ~]# /root/cron/lgsh

  Welcome to the MySQL monitor Commands end with ; or \g

  Your MySQL connection id is

  Server version: log MySQL Community Server (GPL)

  Type help; or \h for help Type \c to clear the buffer

  mysql> use test

  Database changed

  mysql> select * from t;

  ++

  | t |

  ++

  | |

  ++

   row in set ( sec)

  在將資源切回到ha在進行操作看slave 的情況

  [root@ha ~]# /etc/initd/heartbeat start

  Node: ha (cdebcbbbbeef): online

  Node: ha (abdfbeacfcfc): online

  Resource Group: group_

  IPaddr____ (ocf::heartbeat:IPaddr): Started ha

  Filesystem_ (ocf::heartbeat:Filesystem): Started ha

  mysqld_ (lsb:mysqld): Started ha

  資源已經切回到ha對其進行操作

  [root@ha ~]# /usr/local/mysql/bin/mysql u root p D test

  Enter password:

  Welcome to the MySQL monitor Commands end with ; or \g

  Your MySQL connection id is

  Server version: log MySQL Community Server (GPL)

  Type help; or \h for help Type \c to clear the buffer

  mysql> insert into t values();

  mysql> insert into t values();

  mysql> select * from t;

  ++

  | t |

  ++

  | |

  | |

  | |

  ++

   rows in set ( sec)

  在slave 查看是否同步

  mysql> select * from t;

  ++

  | t |

  ++

  | |

  | |

  | |

  ++

   rows in set ( sec)

  mysql> show slave status\G;

  *************************** row ***************************

  Slave_IO_State:Waiting for master to send event

  Master_Host:

  Master_User: slave

  Master_Port:

  Connect_Retry:

  Master_Log_File: master

  Read_Master_Log_Pos:

  Relay_Log_File: mysqlrelaybin

  Relay_Log_Pos:

  Relay_Master_Log_File: master

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

  Master 切換後slave 無需任何人工介入自動同步

   模擬寫入切換測試

  寫個簡單的循環

  [root@mysql cron]# cat testsh

  for ((num=;num<;num=num+))

  do

  echo

  /usr/local/mysql/bin/mysql u sg psg h D test e

  insert into t values($num);

  

  if (( $? )); then

  echo $num:no

  else

  echo $num:ok

  fi

  done

  在寫入過程中手工切換手工reboot 等操作 的測試結果一樣在寫入過程中強制ha 斷電需要手工處理slave 同步將ha 斷電後資源切到ha在slave 會報錯

  mysql> show slave status\G;

  *************************** row ***************************

  Slave_IO_State:

  Master_Host:

  Master_User: slave

  Master_Port:

  Connect_Retry:

  Master_Log_File: master

  Read_Master_Log_Pos:

  Relay_Log_File: mysqlrelaybin

  Relay_Log_Pos:

  Relay_Master_Log_File: master

  Slave_IO_Running: No

  Slave_SQL_Running: Yes

  查看slave 報錯日志

   :: [Note] Slave I/O thread: Failed reading log event reconnecting to retry log

  master at postion

   :: [ERROR] Error reading packet from server: Client requested master to start

  replication from impossible position ( server_errno=)

   :: [ERROR] Got fatal error : Client requested master to start replication from

  impossible position from master when reading data from binary log

   :: [Note] Slave I/O thread exiting read up to log master position

  在master 中找不到position

  查看master 的binlog

  [root@ha data]# /usr/local/mysql/bin/mysqlbinlog master|tail

  /*!*/;

  # at

  # :: server id end_log_pos Query thread_id=

  exec_time= error_code=

  SET TIMESTAMP=/*!*/;

  insert into t values()

  /*!*/;

  DELIMITER ;

  # End of log file

  ROLLBACK /* added by mysqlbinlog */;

  /*! SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

  在master 上master 最後值是而在slave 卻是Slave 的Log_Pos比master 大應該是在斷電的時候binlog 有部分沒有及時寫入磁盤但從庫已經讀入我們需要在slave 執行change maseter 操作MASTER_LOG_FILE 在原來的基礎上加Log_Pos 為

  mysql> stop slave;

  Query OK rows affected ( sec)

  mysql> CHANGE MASTER TO MASTER_HOST=MASTER_PORT =

  MASTER_USER

  =slaveMASTER_PASSWORD=nslaveMASTER_LOG_FILE=masterMAST

  ER_LOG_POS = ;

  Query OK rows affected ( sec)

  mysql> start slave;

  Query OK rows affected ( sec)

  mysql> show slave status\G;

  *************************** row ***************************

  Slave_IO_State:Waiting for master to send event

  Master_Host:

  Master_User: slave

  Master_Port:

  Connect_Retry:

  Master_Log_File: master

  Read_Master_Log_Pos:

  Relay_Log_File: mysqlrelaybin

  Relay_Log_Pos:

  Relay_Master_Log_File: master

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

  在這次斷電的切換過程中將會有 減去 條binlog 操作在slave 存在而在masert 沒有在後面的復制中可能會產生錯誤不過這些錯誤影響不大如果對數據的同步要求很嚴格可以在主庫添加參數sync_binlog=這樣最多將只會有 條數據錯誤不過這樣將影響mysql 寫性能

   heartbeat crm 常用命令

  查看資源狀態

  #crm_mon –i

  查看節點資源

  #crm_resource L

  查看資源在那個節點上運行

  # crm_resource W r mysqld_

  啟動/停止資源

  #crm_resource r mysqld_ p target_role v started

  #crm_resource r mysqld _p target_role v stopped

  將資源組從當前節點轉移到另個節點

  #crm_resource M r group_

  將資源組轉移到指定節點

  #crm_resource M r group_ H ha

  允許資源組回到正常的節點

  #crm_resource U r group_

  將資源從CRM 中刪除

  #crm_resource D r mysqld_ t primitive

  將資源組從CRM 中刪除

  #crm_resource D –r group_ t group

  將資源從CRM 中禁用

  #crm_resource p is_managed r mysqld_ t primitive v off

  將資源從新從CRM 中啟用

  #crm_resource p is_managed r mysqld _t primitive v on

  重啟資源

  #crm_resource C H ha r mysqld_

  檢查所有節點上未在CRM 中的資源

  #crm_resource P

  檢查指定節點上未在CRM 中的資源

  #crm_resource P H ha

  檢查所有節點上未在CRM 中的資源

  #crm_resource P

  檢查指定節點上未在CRM 中的資源

  #crm_resource P H ha

  討論

  對寫要求不是很高的應用我覺得可以考慮用NFS 來代替共享存儲設備結構圖如下

  

  

  將數據文件放在NFS 上這樣幾台廉價的PC 機就能實現相對成本降低

  參數sync_binlog=對寫的性能有多少影響個人始終覺得會帶來很大的寫性能問題在數據完整和性能之間做個平衡有得有失吧

  此方案相對網上流傳的MasterMaster Replication 方案個人覺得實施起來相對簡單維護也相對簡單缺點是需要一個共享設備備機在處於空閒狀態在master 斷電的時候MMM 方案也應該存在主機的內存binlog 丟失的問題當然這些我並沒有測試過只是自己的推測


From:http://tw.wingwit.com/Article/program/MySQL/201311/29350.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.