mysql drbd ha_CentOS6安裝MySQL HA架構部署(MySQL + Heartbeat + DRBD)

Heartbeat + DRBD安裝配置參考上一篇文章：CentOS6安裝NFS HA架構部署(NFS + Heartbeat + DRBD)

這里只說下mysql的安裝方法

Mysql的編譯安裝(默認都是雙台配置)這里選取的是mysql-5.6.30進行源碼編譯安裝。

yum -y install make gcc-c++ cmake bison bison-devel  ncurses-devel libaio

groupadd mysql

useradd -r -g mysql mysql

mkdir -p /drbd_data/mysql/data

mkdir -p /drbd_data/mysql/mysql-bin

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.30.tar.gz

tar zxvf mysql-5.6.30.tar.gz

cd mysql-5.6.30

cmake \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_DATADIR=/drbd_data/mysql/data \

-DSYSCONFDIR=/etc \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \

-DEXTRA_CHARSETS=all \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=all \

-DENABLED_LOCAL_INFILE=1

/drbd_data/mysql/data是drbd切換的目錄

make

make install

chown -R mysql.mysql /usr/local/mysql

cd /usr/local/mysql/scripts

./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/drbd_data/mysql/data

cd /usr/local/mysql/support-files

cp mysql.server /etc/rc.d/init.d/mysqld

修改/etc/my.cnf，更新datadir取值。

修改環境變量：

#vi /etc/profile

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

export PATH

#source /etc/profile

設置用戶訪問mysql權限，允許root從任意一台訪問，可以針對這個虛擬IP設置權限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

flush privileges;

haresources的配置：

vi /etc/ha.d/haresources

testserver2.bj IPaddr::10.111.32.177/24/eth0:1 drbddisk::r0 Filesystem::/dev/drbd0::/drbd_data::ext4 mysqld

如果有多個服務切換最后空格加上就好,例如：

testserver2.bj IPaddr::10.111.32.177/24/eth0:1 drbddisk::r0 Filesystem::/dev/drbd0::/drbd_data::ext4 mysqld killnfsd

只再drbd主的節點啟動mysqld服務，drbd切換的時候會自動啟動mysqld服務(單台機器操作)

service mysqld start

mysql_secure_installation #設置密碼

刪除drbd從節點的/hadata/mysql/中文件，因為切換后會將數據漂移過來。

提示：

配置好heartbeat之后，需要將mysql從自啟動服務器中去掉，因為主heartbeat啟動的時候會掛載drdb文件系統以及啟動mysql，切換的時候會將主上的mysql停止並卸載文件系統，從上會掛載文件系統，並啟動mysql。因此需要做如下操作(兩台服務器)：

chkconfig mysqld off  #不開啟開機啟動

mysql配置文件參考：

# more my.cnf

[client]

port            = 3306

socket          = /var/lib/mysql/mysql.sock

default-character-set=utf8

[mysqld]

port            = 3306

socket          = /var/lib/mysql/mysql.sock

character-set-server = utf8

init-connect='SET NAMES utf8'

basedir         = /usr/local/mysql

datadir         = /drbd_data/mysql/data

skip-external-locking

skip-name-resolve

default-storage-engine = InnoDB

back_log = 2048

max_connections = 1024

max_connect_errors = 256

max_allowed_packet = 32M

table_open_cache = 2048

sort_buffer_size = 8M

read_buffer_size = 8M

read_rnd_buffer_size = 64M

join_buffer_size = 512K

max_heap_table_size = 256M

tmp_table_size = 256M

tmpdir = /dev/shm

#query_cache_type = 0

query_cache_size = 128M

query_cache_limit = 4M

ft_min_word_len = 8

bulk_insert_buffer_size = 64M

thread_cache_size = 16384

thread_concurrency = 8

thread_stack = 512K

key_buffer_size = 4096M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 15G

myisam_repair_threads = 1

myisam_recover_options = DEFAULT

server-id = 1

log-bin=/hadata/mysql/mysql-bin

binlog_format=mixed

binlog_cache_size = 4M

max_binlog_size = 1024M

expire-logs-days = 7

slow_query_log = 1

long_query_time = 10

slow_query_log_file=/hadata/mysql/slow_query.log

log-error = /var/log/mysqld.log

innodb_data_file_path = ibdata1:12M:autoextend

innodb_buffer_pool_size = 15G

innodb_buffer_pool_instances = 8

innodb_additional_mem_pool_size = 64M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_log_buffer_size = 64M

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 50

innodb_max_dirty_pages_pct = 75

innodb_support_xa = 1

innodb_thread_concurrency = 0

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_io_capacity = 1600

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_file_format = Barracuda

innodb_strict_mode = 1

innodb_purge_threads = 1

innodb_change_buffering = all

transaction_isolation = REPEATABLE-READ

explicit_defaults_for_timestamp=true

[mysqldump]

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

default-character-set = utf8

[myisamchk]

key_buffer_size = 1024M

sort_buffer_size = 1024M

read_buffer = 32M

write_buffer = 32M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 65535

default-character-set = utf8

正常情況/etc/init.d/heartbeat stop會進行切換drbd和mysql的動作，但是mysql本身異常停止，並不能切換drbd和mysql，會導致無法提供服務。

對於mysqld服務掛掉的情況無法實現自動切換，所以需要一個腳本來幫助我們完成，我這里有個簡單的腳本，能實現當mysqld服務不可用時進行自動切換，當進行切換時發送郵件等。該腳本放在主服務器執行，也就是運行mysqld服務的服務器上執行。

安裝mutt email程序

# yum install -y mutt

后台監控腳本

# cat mysqlmon.sh

#!/bin/bash

trap 'echo PROGRAM INTERRUPTED; exit 1' INT

username=root

password=123456

n=0

log='/var/log/mysqlmon.log'

while true

do

if /usr/bin/mysql -u${username} -p${password} -e "use mysql" >&/dev/null

then

echo `date +"%Y-%m-%d %H:%M:%S"` mysqld is alive! >> ${log}

n=0

else

echo "`date +"%Y-%m-%d %H:%M:%S"` mysqld cannot be connected!" >> ${log}

n=$[n + 1]

if [ $n -eq 3 ]

then

/etc/init.d/heartbeat stop

echo "`date +"%Y-%m-%d %H:%M:%S"` mysqld switched to backup!" >> ${log}

echo "`date +"%Y-%m-%d %H:%M:%S"` mysqld switched to backup" | mutt -s "mysqld switched to backup" test@163.com

break

fi

fi

sleep 10

done

后台執行：

# chmod 755 mysqlmon.sh

# nohup ./mysqlmon.sh &

停止mysqld服務，看是否進行切換以及發送郵件：

# /etc/init.d/mysqld stop

Shutting down MySQL.                                       [  OK  ]

查看下郵件是否收到，這個腳本只在主mysql運行。

參考：http://www.cnblogs.com/gomysql/p/3674030.html