master:
[root@server17 ~]# more /etc/my.cnf
[mysqld]
server-id=1
datadir=/usr/local/mysql3306/data
socket=/usr/local/mysql3306/mysqld.sock
innodb_buffer_pool_size=16g
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir=/usr/local/mysql3306/data/
innodb_data_home_dir=/usr/local/mysql3306/data/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table=1
innodb_buffer_pool_instances=16
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 64M
innodb_read_io_threads = 12
innodb_write_io_threads = 12
innodb_log_file_size=512m
innodb_log_files_in_group=3
thread-cache-size = 300
open-files-limit = 65535
#---thread pool
thread_handling=pool-of-threads
thread_pool_max_threads=2200
thread_pool_size=100
transaction_isolation = REPEATABLE-READ
log_bin = mysql-bin
binlog_format = row
expire_logs_days = 14
slow_query_log = 1
long_query_time = 2
slow_query_log_file =/usr/local/mysql3306/data/mysql-slow.log
max_binlog_size=1g
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
table_open_cache=512
table_definition_cache=512
max_connections=3000
back_log=600
max_connect_errors=100000
connect_timeout=50
wait_timeout=28800
interactive_timeout=28800
#query_cache_size=64m
#query_cache_limit = 4M
#query_cache_min_res_unit = 2k
#query_cache_type=1
#semi sync
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_no_slave=on
report-host=172.28.15.17
report-port =3306
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES,STRICT_TRANS_TABLES,NO_ZERO_DATE
slave:
[root@server16 ~]# more /etc/my.cnf
[mysqld]
server-id=2
datadir=/usr/local/mysql3306/data
socket=/usr/local/mysql3306/mysqld.sock
innodb_buffer_pool_size=16g
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir=/usr/local/mysql3306/data/
innodb_data_home_dir=/usr/local/mysql3306/data/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table=1
innodb_buffer_pool_instances=16
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 64M
innodb_read_io_threads = 12
innodb_write_io_threads = 12
innodb_log_file_size=512m
innodb_log_files_in_group=3
thread-cache-size = 100
open-files-limit = 65535
#---thread pool
thread_handling=pool-of-threads
thread_pool_max_threads=128
thread_pool_size=50
transaction_isolation = REPEATABLE-READ
#log_bin = mysql-bin
#binlog_format = row
#expire_logs_days = 14
#--replication
report-host=172.28.15.16
report-port =3306
master-info-repository=table
relay-log-info-repository=table
slow_query_log = 1
long_query_time = 2
slow_query_log_file =/usr/local/mysql3306/data/mysql-slow.log
#-----qc
query_cache_size=64m
query_cache_limit = 4M
query_cache_min_res_unit = 2k
query_cache_type=1
max_binlog_size=1g
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
table_open_cache=256
table_definition_cache=256
max_connections=1000
back_log=600
max_connect_errors=10000
connect_timeout=10
wait_timeout=28800
interactive_timeout=28800
#---semi_sync slave
rpl_semi_sync_slave_enabled=1
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES,STRICT_TRANS_TABLES,NO_ZERO_DATE
keepavlied配置：
master:
vim /etc/keepalived/keepalived.conf
global_defs {
router_id KeepAlive_Mysql
}
vrrp_script check_run {
script "/usr/local/mysql/mysql_check.sh"
interval 300
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
notify_master /usr/local/mysql/master.sh
virtual_ipaddress {
192.168.134.220
}
}
slave:
[root@mysqldb2 mysql]# vim /etc/keepalived/keepalived.conf
global_defs {
{
router_id KeepAlive_Mysql
}
vrrp_script check_run {
script "/usr/local/mysql/mysql_check.sh"
interval 300
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
notify_master /usr/local/mysql/master.sh
virtual_ipaddress {
192.168.134.220
}
}
检查mysql服务脚本：
vim /usr/local/mysql/mysql_check.sh
#!/bin/bash
. /root/.bash_profile
count=1
while true
do
mysql -uroot -p123456 -e "show status;" > /dev/null 2>&1
i=$?
ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
j=$?
if [ $i = 0 ] && [ $j = 0 ]
then
exit 0
else
if [ $i = 1 ] && [ $j = 0 ]
then
exit 0
else
if [ $count -gt 5 ]
then
break
fi
let count++
continue
fi
fi
done
/etc/init.d/keepalived stop
切换脚本：
[root@mysqldb1 mysql]# vim /usr/local/mysql/master.sh
#!/bin/bash
. /root/.bash_profile
Master_Log_File=$(mysql -uroot -p123456 -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
Relay_Master_Log_File=$(mysql -uroot -p123456 -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
Read_Master_Log_Pos=$(mysql -uroot -p123456 -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
Exec_Master_Log_Pos=$(mysql -uroot -p123456 -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')
i=1
while true
do
if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]
then
echo "At time: `date`:replication zero delay,narmal switch" >>/tmp/changemaster.log
break
else
sleep 1
if [ $i -gt 60 ]
then
break
fi
continue
let i++
fi
done
mysql -uroot -p123456 -e "set global read_only=OFF;reset master;stop slave;reset slave;set global innodb_support_xa=1;set global sync_binlog=500;set global innodb_flush_log_at_trx_commit=2;show master status;" >/tmp/master_status_$(date "+%y%m%d-%H%M").txt
echo "`date "+%Y-%m-%d-%H-%M-%S"`:`uname -n ` switch to primary " | mail -s "16<->17 HA switch" wzgchen@163.com
sed -i 's/read_only=1/read_only=0/' /usr/local/mysql/my.cnf
#mysql -uroot -p123456 -e "reset master;"
#mysql -uroot -p123456 -e "stop slave;"
#mysql -uroot -p123456 -e "reset slave;"
#mysql -uroot -p123456 -e "set global innodb_support_xa=1;"
#mysql -uroot -p123456 -e "set global sync_binlog=500;"
#mysql -uroot -p123456 -e "set global innodb_flush_log_at_trx_commit=2;"
#mysql -uroot -p123456 -e "set global read_only=off;" >>/tmp/resetmaster.log
#mysql -uroot -p123456 -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'app1'@'%' IDENTIFIED BY '123456';flush privileges;" >>/tmp/resetmaster.log
#mysql -uroot -p123456 -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt
配置postfix邮件服务：
service sendmail stop
chkconfig sendmail off
rpm -e sendmail --nodeps
yum install postfix -y
postconf -e myhostname=mysqldb2.gewara.com
more /etc/postfix/main.cf
vi /etc/hosts
127.0.0.1 mysqldb1.gewara.com gewara.com
service postfix restart
测试发邮件：
echo "Mail Content2" | mail -s "Mail Subject" wzgchen@163.com