mysql+keepalived 实现双主高可用

demn

mysql+keepalived实现双主高可用
mysql_master1:192.168.3.126
mysql_master2 :192.168.3.127
vip :192.168.3.218
############################################################################
配置前准备：yum install -y mysql mysql-server
一.mysql主主复制
############################################################################
1.master1的配置文件
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server_id = 1
# Disabling symbolic-links is recommended to prevent assorted security risks
log-bin=binlog
log-bin-index=binlog.index
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
############################################################################
2.master2的配置文件
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id = 2
user=mysql
log-bin=binlog
log-bin-index=binlog.index
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#############################################################################
3.master1 && master2
service mysqld restart && chkconfig mysqld on
#############################################################################
4.master1 && master2实现同步
##############################################################################
4.1 master2去同步master1的数据
master1：
mysql> GRANT ALL ON *.* TO 'root@%' identified by 'root';
mysql> GRANT replication slave ON *.* TO 'master1'@'%' identified by 'maser1';
mysql> flush privileges;
mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 | 295 | | |

mysql>unlock tables;

master2 :
mysql> change master to master_host='192.168.3.126', master_port=3306, master_user='master1', master_password='master1', master_log_file='binlog.000003',master_log_pos=295;
mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
若出现上面两个都是yes说明同步成功
###############################################################################
4.2 master1去同步master2
master2：
mysql> GRANT ALL ON *.* TO 'root@%' identified by 'root';
mysql> GRANT replication slave ON *.* TO 'master2'@'%' identified by 'master2';
mysql> flush privileges;
mysql> flush tables with read lock;
mysql> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 | 298 | | |

mysql>unlock tables;

master1 :
mysql> change master to master_host='192.168.3.127', master_port=3306, master_user='master2', master_password='master2', master_log_file='binlog.000003',master_log_pos=298;
mysql> start slave;
mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
若出现上面两个都是yes说明同步成功
################################################################################

二.keepalived的安装配置
安装前准备
yum install -y openssl* kernel-devel
ln -s /usr/src/kernels/2.6.32-358.18.1.el6.x86_64/ /usr/src/linux
###############################################################################
1.安装keepalived
wget http://www.keepalived.org/software/keepalived-1.2.8.tar.gz
tar fvxz keepalived-1.2.8.tar.gz
cd keepalived-1.2.8
./configure --prefix=/usr/local/keepalived
make && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
ln -s /usr/local/keepalived/sbin/keepalived /sbin/
service keepalived restart
##############################################################################
2.配置keepalived主配文件
2.1 master1的主配文件如下：
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
router_id mysql-ha
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script check_run {
script "/etc/keepalived/check_mysql.sh"
interval 5
}
vrrp_sync_group mysql {
group {
mysql
}
}
vrrp_instance mysql {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
virtual_ipaddress {
192.168.3.218
}
}
service keepalived restart && chkconfig keepalived on
2.2 master2的主配文件如下：

! Configuration File for keepalived

global_defs {
router_id mysql-ha
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script check_run {
script "/etc/keepalived/check_mysql.sh"
interval 5
}
vrrp_sync_group mysql {
group {
mysql
}
}
vrrp_instance mysql {
state BACKUP
interface eth0
virtual_router_id 51
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
virtual_ipaddress {
192.168.3.218
}
}
service keepalived restart && chkconfig keepalived on
2.3 检测脚本如下：
vim /etc/keepalived/check_mysql.sh
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=root
CHECK_TIME=3

#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done

3.测试mysql+keepalived
3.1 master1上面做主，master2备用
master1上面：ip addr|grep eth0，显示如下：
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.3.126/24 brd 192.168.3.255 scope global eth0
inet 192.168.3.218/32 scope global eth0
master2上面：ip addr|grep eth0，显示如下：
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.3.127/24 brd 192.168.3.255 scope global eth0
测试：mysql -uroot -proot -h 192.168.3.218

hhnf333

不要抱怨老天，终有一天奇迹会降临在每个人身上。