keepalived的双主搭建

centos6.5   ip:192.168.1.106

node1         ip:192.168.1.108

vip              ip:192.168.1.200

 

1.在两个机器上安装keepalived

yum install MySQL-python

yum -y install keepalived

2. 搭建双主复制结构

检查两个机器MySQL的配置文件

  serverid

  gtid-mode=0（禁掉）

  binlog_format=row

  log-bin=/data/mysql/mysql3306/logs/mysql-bin

在centos6.5上面

(1)启动mysql

(2)创建复制用的帐户

grant replication slave on *.* to 'reli'@'%' identified by 'repl4slave';

(3)创建监控用的帐户

grant replication client on *.* to 'monitor'@'%' identified by 'm0n1tor';

(4)对MySQL进行备份（用mysqldump）

mysqldump --master-data=2 --single-transaction -A >db1063306_20160116_1504

记录下备份中的语句：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=199;

并将备份数据传到node1上：scp db1063306_20160116_1504 root@192.168.1.108:/data/mysql/mysql3306/backup/

(5)在node1上恢复数据

启动node1上的数据库并将数据进行恢复

mysql -S /tmp/mysql.sock <db1063306_20160116_1504

（6）复制同步

登入数据库，执行chang master to 语句：

CHANGE MASTER TO master_host='192.168.1.106',\

master_user='reli',\

master_password='repl4slave',\

MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=199;

检查slave是否正常

show slave status\G;

无错误情况下start slave

show slave status\G;此时已经创建出centos6.5>=node1的复制。

（7）创建node1>=centos6.5的复制

在node1上执行show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

记录两个值，改写chang master to 语句

在centos6.5客户端登入数据库

执行chang master to 语句

CHANGE MASTER TO master_host='192.168.1.108',\

master_user='reli',\

master_password='repl4slave',\

MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

检查slave是否正常

show slave status\G;

无错误情况下start slave

show slave status\G;此时已经创建出node1>=centos6.5的复制。

双主建成。

3.在目录keepalived下编辑配置文件及连接MySQL的脚本

主机node1下：

[root@bogon keepalived]# cat keepalived.conf

vrrp_script vs_mysql_82 {

    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3306"

    interval 60

}

vrrp_instance VI_82 {

    state BACKUP

    nopreempt

    interface eth0

    virtual_router_id 82

    priority 100

    advert_int 5

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

       vs_mysql_82

    }

    virtual_ipaddress {

        192.168.1.200

    }

}

脚本为连接MySQL的脚本

注意脚本要有执行的权限

[root@bogon keepalived]# /etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3306

[root@bogon keepalived]# echo $?

0

[root@bogon keepalived]# /etc/init.d/keepalived start

Starting keepalived:                                       [  OK  ]

[root@bogon keepalived]# ping 192.168.1.200

PING 192.168.1.200 (192.168.1.200) 56(84) bytes of data.

64 bytes from 192.168.1.200: icmp_seq=1 ttl=64 time=8.00 ms

64 bytes from 192.168.1.200: icmp_seq=2 ttl=64 time=0.021 ms

64 bytes from 192.168.1.200: icmp_seq=3 ttl=64 time=0.020 ms

^C

--- 192.168.1.200 ping statistics ---

3 packets transmitted, 3 received, 0% packet loss, time 2110ms

rtt min/avg/max/mdev = 0.020/2.682/8.005/3.763 ms

同样方法在centos6.5上启动keepalived

两个就搭建完成了。