前言
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
