Keepalived+Mysql互为主从高可用安装配置

Keepalived+Mysql互为主从高可用安装配置
环境介绍：
keepalived_vip=192.168.1.210    (写虚拟ip)
mysql_master01      eth0:192.168.1.211  eth1:172.20.27.211 (1核1G)
mysql_master02      eth0:192.168.1.212  eth1:172.20.27.212 (1核1G)

1.安装mysql数据库(所有节点安装)
  //此处省略安装mysql服务
2.编辑my.cnf配置文件
2.1修改mysql_master01配置文件

vim /usr/local/lnmp/mysql/etc/my.cnf

server_id = 211log-bin=mysql-binlog-slave-updates

2.2修改mysql_master02配置文件

vim /usr/local/lnmp/mysql/etc/my.cnf

server_id = 212log-bin=mysql-binlog-slave-updates

3.搭建互为主从
3.1在mysql_master01上面操作
3.1.1为mysql_master02建立主从同步权限

mysql -u root -p123456 //进入数据库grant replication slave on *.* to 'backup'@'192.168.1.212' identified by 'backup'; //建立权限flush privileges; //刷新权限表

3.1.2拷贝数据到mysql_master02节点

mysql -u root -p123456 //进入数据库flush tables with read lock; //锁表，准备备份数据mysql> show master status\G  //查看主节点信息*************************** 1. row ***************************             File: mysql-bin.000005         Position: 1231     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)mysqldump -u root -p123456 liu >liu.sql //备份liu这个数据库mysql -u root -p123456 //进入数据库unlock tables; //解锁scp liu.sql root@192.168.1.212:/root/   //拷贝数据

3.2在mysql_master02上面操作
3.2.1恢复liu.sql到数据库

mysql -u root -p123456 //进入数据库create database liu; //创建恢复的数据库名use liu; //进入数据库source /root/liu.sql //恢复数据

3.2.2建立主从同步关系

mysql -u root -p123456 //进入数据库change master to master_host='192.168.1.211',master_user='backup',master_password='backup',master_log_file='mysql-bin.000005',master_log_pos=1231; //建立主从同步关系start slave; //启动从库同步show slave status\G  //检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。

3.2.3为mysql_master01建立主从同步权限

mysql -u root -p123456 //进入数据库grant replication slave on *.* to 'backup'@'192.168.1.211' identified by 'backup'; //建立权限flush privileges; //刷新权限表mysql> show master status\G //查看主节点信息*************************** 1. row ***************************             File: mysql-bin.000004         Position: 1242     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

3.3在mysql_master01上面操作
3.3.1建立主从同步关系

mysql -u root -p123456 //进入数据库change master to master_host='192.168.1.212',master_user='backup',master_password='backup',master_log_file='mysql-bin.000004',master_log_pos=1242; //建立主从同步关系start slave; //启动从库同步show slave status\G  //检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。

4.安装keepalived(所有节点都安装)
4.1安装配置keepalived

wget http://www.keepalived.org/software/keepalived-1.2.15.tar.gztar zxvf keepalived-1.2.15.tar.gzcd keepalived-1.2.15./configure --prefix=/usr/local/keepalivedmake && make install

4.2将keepalived配置成系统服务

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/

4.3修改keepalived配置文件
4.3.1修改mysql_master01的keepalived配置文件

echo '' >/etc/keepalived/keepalived.conf //清空配置文件，自己写vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived   global_defs {        notification_email {        [e-mail]        }      notification_email_from [e-mail]       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      #主备必须一样,范围只能是0-255之间   priority 100       #优先级，另一台改为90   advert_int 1   nopreempt          #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置   authentication {       auth_type PASS       auth_pass 1111   }   virtual_ipaddress {       192.168.1.210   }}virtual_server 192.168.1.210 3306 {   delay_loop 6   lb_algo wrr   lb_kind DR   persistence_timeout 50        #会话保持时间   protocol TCPreal_server 192.168.1.211 3306 {       weight 3       notify_down /usr/local/lnmp/mysql/bin/killkeepalived.sh    #检测到mysql服务挂了就执行这个脚本       TCP_CHECK {       connect_timeout 10        #连接超时时间       nb_get_retry 3            #重连次数       delay_before_retry 3      #重连间隔时间       connect_port 3306         #健康检查端口        }       }}

4.3.2修改mysql_master02的keepalived配置文件

echo '' >/etc/keepalived/keepalived.conf //清空配置文件，自己写vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived   global_defs {        notification_email {        [e-mail]        }      notification_email_from [e-mail]       smtp_server 127.0.0.1       smtp_connect_timeout 30       router_id MySQL-ha       }  vrrp_instance VI_1 {   state backup     interface eth0   virtual_router_id 51   priority 90    advert_int 1   nopreempt   authentication {       auth_type PASS       auth_pass 1111   }   virtual_ipaddress {       192.168.1.210   }}virtual_server 192.168.1.210 3306 {   delay_loop 6   lb_algo wrr   lb_kind DR   persistence_timeout 50   protocol TCPreal_server 192.168.1.212 3306 {       weight 3       notify_down /usr/local/lnmp/mysql/bin/killkeepalived.sh       TCP_CHECK {       connect_timeout 10       nb_get_retry 3       delay_before_retry 3       connect_port 3306        }       }}

4.4编写killkeepalived.sh(所有节点安装)

vim /usr/local/lnmp/mysql/bin/killkeepalived.sh

#!/bin/sh  pkill keepalived

chmod +x /usr/local/lnmp/mysql/bin/killkeepalived.sh

4.5 启动keepalived(所有节点安装)

service keepalived restart echo 'service keepalived start' >>/etc/rc.local

5.测试数据库高可用
5.1为测试用户建立权限

grant all privileges on *.* to 'liuzhuang'@'%' identified by '123';flush privileges; //刷新权限表

5.2模拟故障,看是否关闭一台mysql服务，vip会自动切换，如果会，说明环境正常。

本文出自 “成都@阿状” 博客，请务必保留此出处http://azhuang.blog.51cto.com/9176790/1636594