MySQL雙機熱備+heartbeat集群+自動故障轉(zhuǎn)移（2秒） - Linux高級應(yīng)用 ...
環(huán)境說明：本環(huán)境由兩臺mysql 數(shù)據(jù)庫和heartbeat 組成，一臺的ip
為192.168.10.197，一臺為192.168.10.198，對外提供服務(wù)的vip
為192.168.10.200
備注：heartbeat 本身是不能做到服務(wù)不可用自動切換的，所以用結(jié)合
額外的腳本才可以做到，本文中提到的moniter 腳本即為實現(xiàn)某個
mysql 服務(wù)不可用的時候自動切換的還可以自動報警
安裝和配置過程分為如下幾步：
第一部分：mysql 的安裝配置
1 安裝
1.1. 添加mysql 運行所需的用戶和組
groupadd mysql
useradd -g mysql mysql
1.2. 解壓安裝
tar mysql-5.1.26-rc-linux-x86_64-glibc23.tar.gz
mv mysql-5.1.26-rc-linux-x86_64-glibc23 /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql
1.3. 復(fù)制主配置文件和啟動腳本
cd /usr/local/mysql
cp support-files/my-medium.cnf /etc/my.cf
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
1.4. 初始化數(shù)據(jù)庫
cd /usr/local/mysql
scripts/mysql_install_db --user=mysql
1.5. 更改數(shù)據(jù)目錄的所有者和組
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
2 / 14
chown -R mysql:mysql ./data
1.6. 注冊mysql 為系統(tǒng)服務(wù)
chkconfig --add mysqld
chkconfig --levels 2345 mysqld on
1.7. 輸出環(huán)境變量
#Vi /etc/profile(內(nèi)容如下)
PATH=$PATH:/usr/local/mysql/bin
# source /etc/profile
1.8. 設(shè)置數(shù)據(jù)庫密碼
service mysqld start
mysqladmin password 123456（密碼自己定這里只是個例子）
1.9. 開啟root 遠程訪問權(quán)限
mysql –p123456
mysql> grant all on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
mysql> quit
2 參數(shù)設(shè)置
2.1. 創(chuàng)建相關(guān)目錄并設(shè)置權(quán)限
mkdir /usr/local/mysql/binlog/
chown –R mysql:mysql /usr/local/mysql/binlog/
touch /var/log/mysql.log
chown –R mysql:mysql /var/log/mysql.log
2.2. 添加同步復(fù)制的賬號（主庫和輔庫的設(shè)置相同）
[root@master ~]# mysql -p
Enter password:（輸入root 密碼）
mysql> grant all on *.* to qiangao identified by '123456';（其中的賬號和密碼要
和配置文件中指定的相同）
mysql> flush privileges;
2.3. 修改主配置文件
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
3 / 14
注意：其中ip 要互相指向?qū)Ψ降膇p 按實際情況來設(shè)定，用戶名和密碼要和上文的用戶名密
碼保持一致
主庫設(shè)置
vi /etc/my.cnf（用如下內(nèi)容覆蓋原有內(nèi)容）
################################################
##########
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
################### auto_increment
###########################
auto_increment_offset = 1
auto_increment_increment = 2
############## other options ##############
default-character-set = utf8
default-storage-engine = InnoDB
default-table-type = INNODB
max_connections = 800
port = 3306
socket = /tmp/mysql.sock
skip-locking
########## MyISAM options #################
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_sort_buffer_size = 10M
myisam_repair_threads = 1
################ select cache options ##################
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 5M
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
4 / 14
max_allowed_packet = 1M
table_cache = 2048
query_cache_size = 32M
query_cache_limit = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_concurrency = 8
################ index cache options ##################
key_buffer_size = 32M
key_buffer_size = 32M
################# master #########################
server-id = 1
log-bin=/usr/local/mysql/binlog/master-bin
binlog_format=mixed
relay-log=/usr/local/mysql/binlog/mysqld-relay-bin
##################### slave
##########################
relay-log=/usr/local/mysql/binlog/slave-relay-bin
master-host=192.168.10.198
master-user=qiangao
master-password=123456
master-connect-retry=10
############## log ##################
log-error=/var/log/mysql.log
######### INNODB #########
innodb_file_per_table
#+++++++ log ++++++++#
innodb_log_buffer_size = 10M
innodb_mirrored_log_groups = 1
innodb_log_files_in_group = 3
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
5 / 14
innodb_log_file_size = 50M
innodb_flush_log_at_trx_commit = 0
#innodb_log_archive = 0
#+++++++ System buffer +++++++#
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 40M
#+++++++ Other +++++++#
innodb_file_io_threads = 4
innodb_lock_wait_timeout = 5
innodb_force_recovery = 0
innodb_fast_shutdown = 1
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
transaction-isolation = READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
6 / 14
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
輔庫設(shè)置
vi /etc/my.cnf（用如下內(nèi)容覆蓋原有內(nèi)容，）
################################################
##########
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
################### auto_increment
###########################
auto_increment_offset = 1
auto_increment_increment = 2
############## other options ##############
default-character-set = utf8
default-storage-engine = InnoDB
default-table-type = INNODB
max_connections = 800
port = 3306
socket = /tmp/mysql.sock
skip-locking
########## MyISAM options #################
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_sort_buffer_size = 10M
myisam_repair_threads = 1
################ select cache options ##################
read_buffer_size = 2M
read_rnd_buffer_size = 16M
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
7 / 14
bulk_insert_buffer_size = 5M
max_allowed_packet = 1M
table_cache = 2048
query_cache_size = 32M
query_cache_limit = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_concurrency = 8
################ index cache options ##################
key_buffer_size = 32M
key_buffer_size = 32M
################# master #########################
server-id = 2
log-bin=/usr/local/mysql/binlog/master-bin
binlog_format=mixed
relay-log=/usr/local/mysql/binlog/mysqld-relay-bin
##################### slave
##########################
relay-log=/usr/local/mysql/binlog/slave-relay-bin
master-host=192.168.10.197
master-user=qiangao
master-password=123456
master-connect-retry=10
############## log ##################
log-error=/var/log/mysql/error.log
######### INNODB #########
innodb_file_per_table
#+++++++ log ++++++++#
innodb_log_buffer_size = 10M
innodb_mirrored_log_groups = 1
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
8 / 14
innodb_log_files_in_group = 3
innodb_log_file_size = 50M
innodb_flush_log_at_trx_commit = 0
#innodb_log_archive = 0
#+++++++ System buffer +++++++#
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 40M
#+++++++ Other +++++++#
innodb_file_io_threads = 4
innodb_lock_wait_timeout = 5
innodb_force_recovery = 0
innodb_fast_shutdown = 1
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
transaction-isolation = READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
9 / 14
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
2.4. 重新啟動服務(wù)
service mysqld restart
3 防火墻是設(shè)置
注意主庫和輔庫的防火墻設(shè)置是一樣的
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
service iptables save
第二部分：heartbeat 的安裝和配置
兩臺主機硬件環(huán)境(不必完全一致)：
eth0: 對外IP
eth1: 對內(nèi)IP(HA 專用)
兩臺主機的eht1 使用雙機對聯(lián)線直接連接。
【二】安裝前網(wǎng)絡(luò)環(huán)境設(shè)定：
================================================
========
node1: 主機名：master.qiangao.com
eth0: 192.168.10.197 //對外IP 地址
eth1: 172.16.1.3 //HA 心跳使用地址
---------------------------
node2: 主機名：slave.qiangao.com
eth0: 192.168.10.198 //對外IP 地址
eth1: 172.16.1.4 //HA 心跳使用地址
特別注意要檢查以下幾個文件：
/etc/hosts
/etc/host.conf
/etc/resolv.conf
/etc/sysconfig/network
/etc/sysconfig/network-scripts/ifcfg-eth0
/etc/sysconfig/network-scripts/ifcfg-eth1
/etc/nsswitch.conf
#vi /etc/hosts
node1 的hosts 內(nèi)容如下：
127.0.0.1 master.qiangao.com master localhost.localdomain localhost
192.168.10.197 master.qiangao.com
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
10 / 14
192.168.10.198 slave.qiangao.com
::1 localhost6.localdomain6 localhost6
---------------------------
node2 的hosts 內(nèi)容如下:
127.0.0.1 slave.qiangao.com slave localhost.localdomain localhost
192.168.10.197 master.qiangao.com
192.168.10.198 slave.qiangao.com
::1 localhost6.localdomain6 localhost6
#cat /etc/host.conf
order hosts,bind
#cat /etc/resolv.conf
nameserver 202.96.134.133 //DNS 地址
#cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME= master.qiangao.com //主機名
GATEWAY="192.168.10.1" //網(wǎng)關(guān)
GATEWAY="eth0" //網(wǎng)關(guān)使用網(wǎng)卡
ONBOOT=YES //啟動時加載
FORWARD_IPV4="yes" //只允許IPV4
---------------------------
#cat /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
ONBOOT=yes
BOOTPROTO=static
IPADDR=192.168.10.197
NETMASK=255.255.255.0
GATEWAY=192.168.10.1
TYPE=Ethernet
IPV6INIT=no
---------------------------
#cat /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE=eth1
ONBOOT=yes
BOOTPROTO=none
IPADDR=172.16.1.3
NETMASK=255.255.0.0
TYPE=Ethernet
[node1] 與 [node2] 在上面的配置中，除了
/etc/hosts
/etc/sysconfig/network
/etc/sysconfig/network-scripts/ifcfg-eth0
/etc/sysconfig/network-scripts/ifcfg-eth1
要各自修改外，其他一致。
配置完成后，試試在各自主機上ping 對方的主機名，應(yīng)該可以ping 通:
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
11 / 14
[root@master ~]# ping slave.qiangao.com
PING slave.qiangao.com (192.168.10.198) 56(84) bytes of data.
64 bytes from slave.qiangao.com (192.168.10.198): icmp_seq=1 ttl=64
time=0.136 ms
【三】安裝HA 與HA 依賴包
================================================
===========
yum install heartbeat
yum install ipvsadm
yum install libnet
【四】 配置 HA 的各配置文件
首先復(fù)制配置文件到/etc 目錄
cd /usr/share/doc/heartbeat-2.1.3
cp ha.cf haresources /etc/ha.d/
cp authkeys /etc/ha.d/
================================================
===========
配置心跳的加密方式:authkeys
如果使用雙機對聯(lián)線(雙絞線)，可以配置如下：
#vi /etc/hc.d/authkeys
auth 1
1 crc
存盤退出，然后
#chmod 600 authkeys
================================================
===========
配置心跳的監(jiān)控：haresources
創(chuàng)建檢測腳本（本處如果沒有個監(jiān)測腳本hearbeat 無法啟動）
vi /etc/init.d/test(內(nèi)容如下)
#!/bin/bash
echo "" $>/dev/null
chmod 777 /etc/init.d/test
================================================
===========
#vi /etc/ha.d/haresources(配置資源文件)
各主機這部分應(yīng)完全相同。
master.qiangao.com 192.168.10.200 test
指定 master.qiangao.com 調(diào)用預(yù)先寫好的一個測試腳本，系統(tǒng)附加一個虛擬IP
192.168.10.200 給eth0:0
這里如果master.qiangao.com 宕機后slave.qiangao.com 可以新分配IP
192.168.10.200
================================================
===========
配置心跳的配置文件：ha.cf
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
12 / 14
#vi /etc/ha.d/ha.cf
logfile /var/log/ha_log/ha-log.log ## ha 的日志文件記錄位置。如沒有
該目錄，則需要手動添加
bcast eth1 ##使用eht1 做心跳監(jiān)測
keepalive 2 ##設(shè)定心跳(監(jiān)測)時間時間為2 秒
warntime 4####警告時間
deadtime 6#########確定服務(wù)以死的時間
initdead 30
hopfudge 1
udpport 694 ##使用udp 端口694 進行心跳監(jiān)測
auto_failback on
node master.qiangao.com ##節(jié)點1，必須要與 uname -n 指令得到的結(jié)果一致。
node slave.qiangao.com ##節(jié)點2
ping 172.16.1.04 ##通過ping 對方來監(jiān)測心跳是否正常。
respawn hacluster /usr/lib64/heartbeat/ipfail(因操作系統(tǒng)而異)
apiauth ipfail gid=haclient uid=hacluster
debugfile /var/logs/ha-debug.log
---------------------------
【五】 HA 服務(wù)的啟動、關(guān)閉
[root@master ha.d]# chkconfig --add heartbeat
[root@master ha.d]# chkconfig --levels 2345 heartbeat on
啟動HA: service heartbeat start
關(guān)閉HA; service heartbeat stop
【六】 防火墻設(shè)置
================================================
====
heartbeat 默認(rèn)使用udp 694 端口進行心跳監(jiān)測。 如果系統(tǒng)有使用iptables 做
防火墻，應(yīng)記住把這個端口打開。
#vi /etc/sysconfig/iptables
加入以下內(nèi)容（互相指向?qū)Ψ絠p）
-A RH-Firewall-1-INPUT -p udp -m udp --dport 694 -d 172.16.1.4 -j ACCEPT
意思是udp 694 端口對 對方的心跳網(wǎng)卡地址 172.16.1.4 開放。
#service iptables restart
重新加載iptables。
第三部分：監(jiān)控腳本
cat /usr/local/mysql/bin/moniter.sh
#!/bin/bash
mysql_path=/usr/local/mysql/bin/
user="root"
password="123456"
email="qubq@qian-gao.com"
logfile=/var/log/moniter.log
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
13 / 14
date=`(date +%y-%m-%d--%H:%M:%S)`
sleeptime=30
ip=$(/sbin/ifconfig | grep "inet addr" | grep -v "127.0.0.1" | awk '{print $2;}' |
awk -F':' '{print $2;}' | head -1)
Slave_IO_Running=$(mysql -u$user -p$password -e 'show slave status\G' |
grep "Slave_IO_Running" | awk '{print $2}')
Slave_SQL_Running=$(mysql -u$user -p$password -e 'show slave status\G' |
grep "Slave_SQL_Running" | awk '{print $2}')
echo "plese fix the server of $ip error now!" >$mysql_path/letter
letter=$mysql_path/letter
mysql -p$password -e "use test;"
if [[ $? != 0 ]]
then
mail -s "{$ip}_database connect lost the srcprits fix it now "
$email<$letter
killall -9 heartbeat
killall -9 mysqld
/etc/init.d/mysqld start
sleep $sleeptime
mysql -p$password -e "use test;"
if [ $? == 0 ]
then
echo
"==============>$date<=====================">>$logfile
mail -s "{$ip}_database up now " $email
sleep $sleeptime
service heartbeat start
sleep $sleeptime
netstat -an |grep udp |grep 694
if [ $? == 0 ]
then
echo "complete!">>$logfile
else
mail -s "{$ip} heartbeat can't to up please fix it !"
$email<$letter
fi
else
mail -s "{$ip}_database cant't to up plese fix it" $email
fi
else
if [ "$Slave_IO_Running" = "Yes" -a "$Slave_SQL_Running" =
"Yes" ]
then
echo "Slave is running!" >/dev/null
作者：曲寶全，轉(zhuǎn)載標(biāo)明出處！
14 / 14
else
echo
"==============>$date<=====================">>$logfile
echo "Slave is not running!" >> $logfile
/bin/mail -s "{$ip}_replicate error please fix it "
$email<$letter
fi
fi
然后設(shè)置自動化任務(wù)每2分鐘檢測一次就可以了，現(xiàn)在就可以實現(xiàn)雙機熱備了！ | | |
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報。
