环境描述:
OS:CentOS6.5_X64
MySQL-VIP:192.168.1.200
MySQL-master1:192.168.1.201
MySQL-master2:192.168.1.202
1、配置两台Mysql主主同步
1
2
3
4
5
6
7
8
9
10
11
12
|
[root@master ~] # yum install mysql-server mysql -y
[root@master ~] # service mysqld start
[root@master ~] # mysqladmin -u root password 123.com
[root@master ~] # vi /etc/my.cnf #开启二进制日志,设置id
[mysqld] server- id = 1 #backup这台设置2
log-bin = mysql-bin binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
slave-skip-errors = all #忽略所有复制产生的错误
[root@master ~] # service mysqld restart
|
如果是Centos7,则卸载mariadb,安装mysql,参考文章:http://blog.51cto.com/sf1314/2073389
安装mysql,参考文章:
mysql5.6.37 http://blog.51cto.com/sf1314/2058754
# mysql -uroot -p'密码'
先查看下log bin日志和pos值位置
master配置如下:
1
2
3
4
5
6
7
8
9
10
|
[root@ master ~] # mysql -u root -p123.com
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication' @ '192.168.0.%' IDENTIFIED BY 'replication' ;
mysql> flush privileges; mysql> change master to -> master_host= '192.168.0.203' ,
-> master_user= 'replication' ,
-> master_password= 'replication' ,
-> master_log_file= 'mysql-bin.000002' ,
-> master_log_pos=106; #对端状态显示的值
mysql> start slave; #启动同步
|
backup配置如下:
1
2
3
4
5
6
7
8
9
10
|
[root@backup ~] # mysql -u root -p123.com
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication' @ '192.168.0.%' IDENTIFIED BY 'replication' ;
mysql> flush privileges; mysql> change master to -> master_host= '192.168.0.202' ,
-> master_user= 'replication' ,
-> master_password= 'replication' ,
-> master_log_file= 'mysql-bin.000002' ,
-> master_log_pos=106;
mysql> start slave; |
#主主同步配置完毕,查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功。
在master插入数据测试下:
在backup查看是否同步成功:
可以看到已经成功同步过去,同样在backup插入到user表数据,一样同步过去,双主就做成功了。
2、配置keepalived实现热备
[root@backup ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包
1
2
3
4
5
6
7
|
[root@master ~] # wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
[root@master ~] # tar -xf keepalived-1.2.7.tar.gz -C /usr/local/src/
[root@master ~] # cd /usr/local/
[root@master ~] # ln -sv /usr/local/src/keepalived-1.2.7/ keepalived #根据实际情况修改
[root@master ~] # cd keepalived
[root@master ~] #./configure --prefix=/usr/local/keepalived
make && make install
|
#将keepalived配置成系统服务
1
2
3
4
5
6
7
8
|
[root@master ~] # cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d/
chmod +x /etc/init .d /keepalived [root@master ~] # cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
[root@master ~] # chkconfig --add keepalived
[root@master ~] # chkconfig keepalived on
[root@master ~] # mkdir /etc/keepalived
[root@master ~] # cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
[root@master ~] # cp /usr/local/sbin/keepalived /usr/sbin/ #或者ln -s /usr/local/sbin/keepalived /usr/sbin/
|
配置keepalived
我们自己在新建一个配置文件,默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
-
1234567891011121314151617181920212223242526272829303132333435363738394041424344
[root@master ~]
# vi /etc/keepalived/keepalived.conf
global_defs {
notification_email {
862572301@qq.com
}
notification_email_from 862572301@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA
#标识,双主相同
}
vrrp_instance VI_1 {
state BACKUP
#两台配置此处均是BACKUP
interface eth0
virtual_router_id 51
#主备相同
priority 100
#优先级,另一台backup改为90
advert_int 1
nopreempt
#不抢占,只在优先级高master的机器上设置即可,优先级低backup的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.200
}
}
virtual_server 192.168.1.200 3306 {
delay_loop 2
#每个2秒检查一次real_server状态
#lb_algo wrr #LVS算法,用不到,我们就关闭了
#lb_kind DR #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
persistence_timeout 60
#会话保持时间,同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 192.168.1.201 3306 {
#检测本地mysql,backup也要写检测本地mysql
weight 3
notify_down
/usr/local/keepalived/mysql
.sh
#当mysq服down时,执行此脚本,杀死keepalived实现切换
TCP_CHECK {
connect_timeout 10
#连接超时时间
nb_get_retry 3
#重连次数
delay_before_retry 3
#重连间隔时间
connect_port 3306
#健康检查端口
}
}
}
1
2
3
4
5
6
|
编写检测服务down后所要执行的脚本 [root@master ~] # vi /usr/local/keepalived/mysql.sh
#!/bin/bash pkill keepalived [root@master ~] # chmod +x /usr/local/keepalived/mysql.sh
[root@master ~] # /etc/init.d/keepalived start
|
注:此脚本是上面配置文件notify_down选项所用到的,keepalived使用notify_down选项来检查real_server 的服务状态,当发现real_server服务故障时,便触发此脚本;我们可以看到,脚本就一个命令,通过pkill keepalived强制杀死keepalived进程,从而实现了MySQL故障自动转移。另外,我们不用担心两个MySQL会同时提供数据更新操作, 因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP,而不是两台MySQL的IP+VIP
启动keepalived
1
2
|
[root@master ~] # /usr/local/keepalived/sbin/keepalived –D 或者/etc/init.d/keepalived start
[root@master ~] # ps -aux | grep keepalived
|
测试
找一台局域网PC,然后去ping MySQL的VIP,这时候MySQL的VIP是可以ping的通的
停止MySQL服务,看keepalived健康检查程序是否会触发我们编写的脚本
#backup服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。
#授权两台Mysql服务器允许root远程登录,用于在其他服务器登陆测试!
mysql> grant all on *.* to'root'@'192.168.0.%' identified by '123.com';
mysql> flush privileges;
3、测试高可用性
1、通过Mysql客户端通过VIP连接,看是否连接成功。
2、停止master这台mysql服务,是否能正常切换过去,可通过ip addr命令来查看VIP在哪台服务器上。
3、可通过查看/var/log/messges日志,看出主备切换过程
4、master服务器故障恢复后,是否主动抢占资源,成为活动服务器。
附:keepalived-1.2.7 keepalived实现服务高可用 http://down.51cto.com/data/2440924
本文转自 Mr_sheng 51CTO博客,原文链接:http://blog.51cto.com/sf1314/2073383