环境:centos 6.7 mysql 5.6.31(两台都一样)
server1 ip:192.168.209.130
server2 ip:192.168.209.131
一:配置两台MySQL主主同步:
MySQL安装过程省略
设置MySQL登陆密码:mysqladmin -uroot password
两台MySQL创建一样的库
然后配置MySQL:
server1: vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
log-slave-updates
sync_binlog=1
以上这些是需要增加或者修改的
然后重启:/etc/init.d/mysqld restart
更改server2:
vim /etc/my.cnf
[mysqld]
server-id = 2
log-bin = mysql-bin
auto-increment-increment = 2 #字段变化增量值 (这是根据服务器台数设定的,我用的两台虚拟机)
auto-increment-offset = 2 #初始字段ID为1
log-slave-updates
sync_binlog=1
以上这些是需要增加或者修改的
然后重启:/etc/init.d/mysqld restart
登陆server1:mysql -uroot -p
授权:grant replication slave on *.* to 'repl'@'192.168.209.131' identified by'repl';
然后输入:flush privilegs;
继续输入:show master status; 记住file 和position的值
继续输入:change master to master_host='192.168.209.131',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=199;
继续输入:start slave;
登陆server2:mysql -uroot -p (没加入环境变量的应该是/usr/local/mysql/bin/mysql -uroot -p )
授权: grant replication slave on *.* to 'repl'@'192.168.209.130' identified by'repl';
然后输入:flush privileges;
继续输入:show master status;
记住file和position的值
继续输入:change master to master_host='192.168.209.130',master_port=3306.master_user='repl',master_password='repl',master_log_file='mysql-bin.000002',master_log_pos=120;
继续输入:start slave;
配置完毕后在两台机器上输入show slave status\G;查看Slave_IO_Running和Slave_SQL_Running是两个yes说明配置成功.
然后配置keepalived实现热备
两台机器上安装keepalived :yum install -y keepalived
在server1上操作:vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MYSQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt #不主动抢占资源，只在server1这台优先级高的设置，server2不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.209.128
}
}
virtual_server 192.168.209.128 3306 {
delay_loop 6
# lb_algo rr
# lb_kind NAT
# nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.209.130 3306 {
weight 1
notify_down /usr/local/sbin/mysql.sh #当mysq down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
}
然后编辑/usr/local/sbin/mysql.sh
#! /bin/bash
pkill keepalived
保存退出,增加x权限 chmod +x /usr/local/sbin/mysql.sh
在server2上操作:vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MYSQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.209.128
}
}
virtual_server 192.168.209.128 3306 {
delay_loop 6
# lb_algo rr
# lb_kind NAT
# nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.209.131 3306 {
weight 1
notify_down /usr/local/sbin/mysql.sh
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
}
保存退出
编辑/usr/local/sbin/mysql.sh
#! /bin/bash
pkill keepalived
保存退出,增加x权限
最后启动keepalived 我这里因为server1比server2优先级高,所以先启动server1,再启动server2
启动完成后查看server1 的vip :ip addr
然后停掉server1上的mysql服务,在server2上查看vip : ip addr 如果server2上有VIP说明切换过来了.
如果server1的mysql重新启动了,需要手动启动keepalived高可用继续生效大概思路就是这样,版本不同可能操作会有些区别.