mysql主主复制+keepalived高可用

双机热备:

DB1：192.168.0.13
DB1：192.168.0.14
VIP ：192.168.0.88        #注意关闭selinux和iptables

1、做主主复制同步之前工作准备
做主主复制时先检查两台服务器系统防火墙、selinux：
iptables -F
setenforce 0

stop slave;
reset slave;

reset slave all;    #清除所有同步信息
reset master;       #删除master的binlog
reset slave;       #删除slave的中继日志
flush logs;         #生成新的binlog日志,主服务器需要
      
flush tables with read lock;   主机阻断写操作，更新前先把主服务器锁表
unlock tables;   解锁

2、DB1 上操作：
vim /etc/my.cnf 

server-id = 1

log-bin = /usr/local/mysql/master-bin
replicate-do-db=test     >>> 只复制指定（test）的数据库里的内容
binlog-ignore-db=mysql   >>>忽略mysql 数据库的更新操作
binlog-ignore-db=information_schema
slave-skip-errors =all
character_set_server = utf-8
interactive_timeout = 57600
log-bin = /usr/local/mysql/master-bin
relay-log = /usr/local/mysql/relay-bin
relay-log-index = /usr/local/mysql/relay-bin.index
expire-logs-days = 100
auto-increment-increment=2 >>> 自增长每次增长2 这是为了在同步的时候ID不冲突
auto-increment-offset=1 >>> 设置自增长的起始值（第二台机器设为2）

3、DB2上操作：

vim /etc/my.cnf

server-id = 2
log-bin = /usr/local/mysql/master-bin
replicate-do-db=test >>> 只复制指定（test）的数据库里的内容
binlog-ignore-db=mysql >>>忽略mysql 数据库的更新操作
binlog-ignore-db=information_schema
slave-skip-errors =all
character_set_server = utf-8
interactive_timeout = 57600
log-bin = /usr/local/mysql/master-bin
relay-log = /usr/local/mysql/relay-bin
relay-log-index = /usr/local/mysql/relay-bin.index
expire-logs-days = 100
auto-increment-increment=2 >>> 自增长每次增长2 这是为了在同步的时候ID不冲突
auto-increment-offset=2 >>> 设置自增长的起始值（第二台机器设为2）

两台DB都创建log-bin和relay-bin目录，并赋予所属组及所属用户
mkdir -p /usr/local/mysql/master-bin
mkdir -p /usr/local/mysql/relay-bin
chown -R mysql:mysql /usr/local/mysql/master-bin
chown -R mysql:mysql /usr/local/mysql/relay-bin

service mysqld stop
service mysqld restart    #重启服务器

创建主从复制用户，DB1主机运行
GRANT REPLICATION SLAVE ON *.* TO 'syncuser'@'192.168.0.14' IDENTIFIED BY '123456';
flush privileges;
flush tables with read lock;     #备份之前先锁表，以防主服务器数据变化

创建主从复制用户，DB2主机运行
GRANT REPLICATION SLAVE ON *.* TO 'syncuser'@'192.168.0.13' IDENTIFIED BY '123456';
flush privileges;

4、DB2 开启 slave服务
stop slave;
show master status;   #A主机运行,查看master_log_file、master_log_pos信息

change master to 
master_host='192.168.0.13', 
master_user='syncuser',
master_password='123456',
master_log_file='master-bin.000001',
master_log_pos=120;

start slave;
show slave status\G;
查看同步信息：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

5、DB1开启 slave服务
stop slave;
show master status;   #B主机运行,查看master_log_file、master_log_pos信息

change master to 
master_host='192.168.0.13', 
master_user='syncuser',
master_password='123456',
master_log_file='master-bin.000001',
master_log_pos=120;

start slave;
show slave status\G;
查看同步信息：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

编译keepalived

yum -y install openssl openssl-devel kernel-devel popt-devel gcc gcc-c++
wget http://www.keepalived.org/software/keepalived-1.2.0.tar.gz
tar zxf keepalived-1.1.20.tar.gz
cd keepalived-1.1.20
./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
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/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

修改 etc/keepalived/keepalived.conf （DB1）

! Configuration File for keepalived
 
global_defs {
   router_id MYSQL_HA#标识，双主相同
}
vrrp_script check_run{
script '/usr/local/mysql/mysql_check.sh'
interval 300
}
vrrp_instance VI_1 {
    state BACKUP#两台都设置BACKUP
    interface eth0
    virtual_router_id 51#主备相同
    priority 100#优先级，backup设置90(最关建点之一，同为backup时优先级别越高，就会主动去抢VIP，所以如果不想让这种情况出现加参数nopreempt)
    advert_int 1#主备心跳检查间隔
    nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置(当master恢复时，不会把VIP抢过来)
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.88  #VIP
    }
}
 
virtual_server 192.168.0.88 3306 {
    delay_loop 2
    #lb_algo rr#LVS算法，用不到，我们就关闭了
    #lb_kind NAT
    #nat_mask 255.255.255.0
    persistence_timeout 50#同一IP的连接60秒内被分配到同一台真实服务器
    protocol TCP
 
    real_server 192.168.0.13 3306 { #检测本地mysql，backup也要写检测本地mysql
        weight 3
    notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
    TCP_CHECK {
    connect_timeout 3#连接超时
    nb_get_retry 3#重试次数
    delay_before_retry 3#重试间隔时间
    }
}

修改 etc/keepalived/keepalived.conf （DB2）

! Configuration File for keepalived
 
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MYSQL_HA
   #router_id LVS_DEVEL
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.88
    }
}
 
virtual_server 192.168.0.88 3306 {
    delay_loop 2
    #lb_algo rr
    #lb_kind NAT
    #nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP
 
    real_server 192.168.0.14 3306 {
        weight 3
    notify_down /usr/local/keepalived/mysql.sh
    TCP_CHECK {
    connect_timeout 3
    nb_get_retry 3
    delay_before_retry 3
    }
}

kill 掉keepalived脚本

Vim /usr/local/keepalived/mysql.sh
#!/bin/bash
/usr/bin/pkill  keepalived

授权两台DB服务器允许管理员登录，用于其它服务器登录测试

grant all on *.* to 'admin'@'%' identified by '3306';
flush privileges;
/etc/init.d/keepalived start    >>>开启服务

通过MYSQL客户端通过VIP连接，看是否成功

mysql -uadmin -h192.168.0.88 –p
可以通过 ip a 查看VIP地址飘逸过程 另外mysql客户端不能和主服务器装在一起 不然飘逸时客户端会连接失败。