mysql雙機熱備+heartbeat叢集+自動容錯移轉

標籤：io   os   使用   ar   for   檔案   資料   sp   art   

環境說明：本環境由兩台mysql 資料庫和heartbeat 組成，一台的ip

為 192.168.10.197，一台為192.168.10.198，對外提供服務的vip

為192.168.10.200

備忘：heartbeat 本身是不能做到服務不可用自動切換的，所以用結合

額外的指令碼才可以做到，本文中提到的moniter 指令碼即為實現某個

mysql 服務停用時候自動切換的還可以自動警示

安裝和配置過程分為如下幾步：

第一部分：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. 複製主設定檔和啟動指令碼

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. 初始化資料庫

cd /usr/local/mysql

scripts/mysql_install_db --user=mysql

1.5. 更改資料目錄的所有者和組

chown -R mysql:mysql ./data

1.6. 註冊mysql 為系統服務

chkconfig --add mysqld

chkconfig --levels 2345 mysqld on

1.7. 輸出環境變數

#Vi /etc/profile(內容如下)

PATH=$PATH:/usr/local/mysql/bin

# source /etc/profile

1.8. 設定資料庫密碼

service mysqld start

mysqladmin password 123456（密碼自己定這裡只是個例子）

1.9. 開啟root 遠端存取許可權

mysql –p123456

mysql> grant all on *.* to [email protected]‘%‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

mysql> quit

2 參數設定

2.1. 建立相關目錄並設定許可權

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. 添加同步複製的帳號（主庫和輔庫的設定相同）

[[email protected] ~]# mysql -p

Enter password:（輸入root 密碼）

mysql> grant all on *.* to qiangao identified by ‘123456‘;（其中的帳號和密碼要

和設定檔中指定的相同）

mysql> flush privileges;

2.3. 修改主設定檔

注意：其中ip 要互相指向對方的ip 按實際情況來設定，使用者名稱和密碼要和上文的使用者名稱密碼保持一致主庫設定

vi /etc/my.cnf（用如下內容覆蓋原有內容）

################################################

##########

[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

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

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

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

輔庫設定

vi /etc/my.cnf（用如下內容覆蓋原有內容，）

################################################

##########

[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

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

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

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

2.4. 重新啟動服務

service mysqld restart

3 防火牆是設定

注意主庫和輔庫的防火牆設定是一樣的

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

service iptables save

第二部分：heartbeat 的安裝和配置

兩台主機硬體環境(不必完全一致)：

eth0: 對外IP

eth1: 對內IP(HA 專用)

兩台主機的eht1 使用雙機對聯線直接連接。

【二】安裝前網路環境設定：

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

========

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 內容如下：

127.0.0.1 master.qiangao.com master localhost.localdomain localhost

192.168.10.197 master.qiangao.com

192.168.10.198 slave.qiangao.com

::1 localhost6.localdomain6 localhost6

---------------------------

node2 的hosts 內容如下:

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" //網關

GATEWAY="eth0" //網關使用網卡

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 對方的主機名稱，應該可以ping 通:

[[email protected] ~]# 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 的各設定檔

首先複製設定檔到/etc 目錄

cd /usr/share/doc/heartbeat-2.1.3

cp ha.cf haresources /etc/ha.d/

cp authkeys /etc/ha.d/

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

===========

配置心跳的加密方式:authkeys

如果使用雙機對聯線(雙絞線)，可以配置如下：

#vi /etc/hc.d/authkeys

auth 1

1 crc

存檔退出，然後

#chmod 600 authkeys

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

===========

配置心跳的監控：haresources

建立檢測指令碼（本處如果沒有個監測指令碼hearbeat 無法啟動）

vi /etc/init.d/test(內容如下)

#!/bin/bash

echo "" $>/dev/null

chmod 777 /etc/init.d/test

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

===========

#vi /etc/ha.d/haresources(配置資源檔)

各主機這部分應完全相同。

master.qiangao.com 192.168.10.200 test

指定 master.qiangao.com 調用預先寫好的一個測試指令碼，系統附加一個虛擬IP

192.168.10.200 給eth0:0

這裡如果master.qiangao.com 宕機後slave.qiangao.com 可以新分配IP

192.168.10.200

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

===========

配置心跳的設定檔：ha.cf

#vi /etc/ha.d/ha.cf

logfile /var/log/ha_log/ha-log.log ## ha 的記錄檔記錄位置。如沒有

該目錄，則需要手動添加

bcast eth1 ##使用eht1 做心跳監測

keepalive 2 ##設定心跳(監測)時間時間為2 秒

warntime 4####警告時間

deadtime 6#########確定服務以死的時間

initdead 30

hopfudge 1

udpport 694 ##使用udp 連接埠694 進行心跳監測

auto_failback on

node master.qiangao.com ##節點1，必須要與 uname -n 指令得到的結果一致。

node slave.qiangao.com ##節點2

ping 172.16.1.04 ##通過ping 對方來監測心跳是否正常。

respawn hacluster /usr/lib64/heartbeat/ipfail(因作業系統而異)

apiauth ipfail gid=haclient uid=hacluster

debugfile /var/logs/ha-debug.log

---------------------------

【五】 HA 服務的啟動、關閉

[[email protected] ha.d]# chkconfig --add heartbeat

[[email protected] ha.d]# chkconfig --levels 2345 heartbeat on

啟動HA: service heartbeat start

關閉HA; service heartbeat stop

【六】 防火牆設定

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

====

heartbeat 預設使用udp 694 連接埠進行心跳監測。 如果系統有使用iptables 做

防火牆，應記住把這個連接埠開啟。

#vi /etc/sysconfig/iptables

加入以下內容（互相指向對方ip）

-A RH-Firewall-1-INPUT -p udp -m udp --dport 694 -d 172.16.1.4 -j ACCEPT

意思是udp 694 連接埠對 對方的心跳網卡地址 172.16.1.4 開放。

#service iptables restart

重新載入iptables。

第三部分：監控指令碼

cat /usr/local/mysql/bin/moniter.sh

#!/bin/bash

mysql_path=/usr/local/mysql/bin/

user="root"

password="123456"

email="[email protected]"

logfile=/var/log/moniter.log

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

else

echo

"==============>$date<=====================">>$logfile

echo "Slave is not running!" >> $logfile

/bin/mail -s "{$ip}_replicate error please fix it "

$email<$letter

fi

fi

然後設定自動化任務每2分鐘檢測一次就可以了，現在就可以實現雙機熱備了

mysql雙機熱備+heartbeat叢集+自動容錯移轉