Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)

mysql5.5双机热备

实现方案

安装两台mysql

安装mysql5.5

sudo apt-get update

apt-get install aptitude
aptitude install mysql-server-5.5
或
sudo apt-cache search mariadb-server
apt-get install -y mariadb-server-5.5

卸载

sudo apt-get remove mysql-*
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -p

配置权限

vim /etc/mysql/my.cnf
#bind-address = 127.0.0.1

mysql -u root -p
grant all on *.* to root@'%' identified by 'root' with grant option;
flush privileges;

配置两台mysql主主同步

配置节点1

vim /etc/mysql/my.cnf

server-id       = 1                                            #节点id
log_bin         = mysql-bin.log               #日志 
binlog_format   = "row"                                    #日志格式
auto_increment_increment = 2                        #自增id间隔(=节点数，防止id冲突)
auto_increment_offset  = 1                            #自增id起始值(节点id)
binlog_ignore_db=mysql                                    #不同步的数据库
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema

重启mysql

service mysql restart
mysql -u root -p

记录节点1的binlog日志位置

show master status;
mysql-bin.000001    245        mysql,information_schema,performance_schema

配置节点2

vim /etc/mysql/my.cnf

server-id       = 2
log_bin         = mysql-bin.log                    
relay_log       = mysql-relay-bin.log        #中继日志
log_slave_updates = on                                  #中继日志执行后，变化计入日志
read_only       = 0
binlog_format   = "row"
auto_increment_increment = 2
auto_increment_offset  = 2
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema

配置主从

mysql -u root -p

change master to 
       master_host='192.168.1.21', 
       master_user='root', 
       master_password='root', 
       master_log_file='mysql-bin.000001', 
       master_log_pos=245;

#开启同步
start slave

#查看同步状态 slave_io_running和slave_sql_running需要均为yes       
show slave status;  

记录节点2的binlog日志位置

show master status;

mysql-bin.000001    1029        mysql,information_schema,performance_schema

配置主主（节点1）

vim /etc/mysql/my.cnf

relay_log       = mysql-relay-bin.log
log_slave_updates = on
read_only       = 0
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema

开启同步

mysql -u root -p

change master to 
       master_host='192.168.1.20', 
       master_user='root', 
       master_password='root', 
       master_log_file='mysql-bin.000001', 
       master_log_pos=1029;

#开启同步
start slave

#查看同步状态 slave_io_running和slave_sql_running需要均为yes       
show slave status; 

异常处理

could not initialize master info structure, more error messages can be found in the mysql error log
解决：reset slave

安装配置keepalived

安装keepalived

#依赖
sudo apt-get install -y libssl-dev
sudo apt-get install -y openssl 
sudo apt-get install -y libpopt-dev
sudo apt-get install -y libnl-dev libnl-3-dev libnl-genl-3.dev
apt-get install daemon
apt-get install libc-dev
apt-get install libnfnetlink-dev
apt-get install libnl-genl-3.dev

#安装
apt-get install keepalived

#编译安装
cd /usr/local
wget https://www.keepalived.org/software/keepalived-2.2.2.tar.gz
tar -zxvf keepalived-2.2.2.tar.gz 
mv keepalived-2.2.2 keepalived
./configure --prefix=/usr/local/keepalived
sudo make && make install

#开启日志
sudo vim /etc/rsyslog.d/50-default.conf 

*.=info;*.=notice;*.=warn;\
        auth,authpriv.none;\
        cron,daemon.none;\
        mail,news.none          -/var/log/messages
        
sudo service rsyslog restart 
tail -f /var/log/messages

sudo mkdir /etc/sysconfig
sudo cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
sudo cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
sudo cp /usr/local/keepalived/sbin/keepalived /sbin/
sudo mkdir /etc/keepalived
sudo cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

配置节点信息

节点1 192.168.1.21

vim /etc/keepalived/keepalived.conf

global_defs {
   router_id mysql_ha　　#当前节点名
}
vrrp_instance vi_1 {    
    state backup         #两台配置节点均为backup
    interface eth0       #绑定虚拟ip的网络接口
    virtual_router_id 51 #vrrp组名，两个节点的设置必须一样，以指明各个节点属于同一vrrp组
    priority 101         #节点的优先级,另一台优先级改低一点
    advert_int 1         #组播信息发送间隔，两个节点设置必须一样
    nopreempt            #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
    authentication {      #设置验证信息，两个节点必须一致
        auth_type pass
        auth_pass 123456
    }
    virtual_ipaddress {   #指定虚拟ip,两个节点设置必须一样
        192.168.1.111
    }
}
virtual_server 192.168.1.111 3306 {   #linux虚拟服务器(lvs)配置 
    delay_loop 2     #每个2秒检查一次real_server状态
    lb_algo wrr      #lvs调度算法,rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind dr      #lvs集群模式 ,nat|dr|tun
    persistence_timeout 60    #会话保持时间
    protocol tcp    #使用的协议是tcp还是udp

    real_server 192.168.1.21 3306 {
        weight 3   #权重
        notify_down  /usr/local/bin/mysql.sh    #检测到服务down后执行的脚本
        tcp_check {
            connect_timeout 10   #连接超时时间
            nb_get_retry 3      #重连次数
            delay_before_retry 3 #重连间隔时间
            connect_port 3306    #健康检查端口
        }
    }    
}

节点2 192.168.1.20

vim /etc/keepalived/keepalived.conf

global_defs {
   router_id mysql_ha　　#当前节点名
}
vrrp_instance vi_1 {
    state backup         #两台配置节点均为backup
    interface eth0       #绑定虚拟ip的网络接口
    virtual_router_id 51 #vrrp组名，两个节点的设置必须一样，以指明各个节点属于同一vrrp组
    priority 100         #节点的优先级,另一台优先级改低一点
    advert_int 1         #组播信息发送间隔，两个节点设置必须一样
    nopreempt            #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
    authentication {      #设置验证信息，两个节点必须一致
        auth_type pass
        auth_pass 123456
    }
    virtual_ipaddress {   #指定虚拟ip,两个节点设置必须一样
        192.168.1.111
    }
}
virtual_server 192.168.1.111 3306 {   #linux虚拟服务器(lvs)配置
    delay_loop 2     #每个2秒检查一次real_server状态
    lb_algo wrr      #lvs调度算法,rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind dr      #lvs集群模式 ,nat|dr|tun
    persistence_timeout 60    #会话保持时间
    protocol tcp    #使用的协议是tcp还是udp

    real_server 192.168.1.20 3306 {
        weight 3   #权重
        notify_down  /usr/local/bin/mysql.sh    #检测到服务down后执行的脚本
        tcp_check {
            connect_timeout 10   #连接超时时间
            nb_get_retry 3      #重连次数
            delay_before_retry 3 #重连间隔时间
            connect_port 3306    #健康检查端口
        }
    }
}

编写异常处理脚本

vim /usr/local/bin/mysql.sh

#!/bin/sh
killall keepalived

分配权限

chmod +x /usr/local/bin/mysql.sh
###测试
重启keepalived

service keepalived restart

查看日志

tail -f /var/log/messages

查看虚拟ip

ip addr  #或ip a 或ifconfig

#主节点会有虚拟ip
eth0: <broadcast,multicast,up,lower_up> mtu 1500 qdisc pfifo_fast state up group default qlen 1000
    link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever

关闭主节点的mysql服务

service mysql stop

日志信息

#主节点
aug 10 15:00:30 i-7jaope92 keepalived_healthcheckers[4949]: tcp connection to [192.168.1.20]:3306 failed !!!
aug 10 15:00:30 i-7jaope92 keepalived_healthcheckers[4949]: removing service [192.168.1.20]:3306 from vs [192.168.1.111]:3306
aug 10 15:00:30 i-7jaope92 keepalived_healthcheckers[4949]: executing [/usr/local/bin/mysql.sh] for service [192.168.1.20]:3306 in vs [192.168.1.111]:3306
aug 10 15:00:30 i-7jaope92 keepalived_healthcheckers[4949]: lost quorum 1-0=1 > 0 for vs [192.168.1.111]:3306
aug 10 15:00:30 i-7jaope92 keepalived_vrrp[4950]: vrrp_instance(vi_1) sending 0 priority
aug 10 15:00:30 i-7jaope92 kernel: [100918.976041] ipvs: __ip_vs_del_service: enter

#从节点
aug 10 15:00:31 i-6gxo6kx7 keepalived_vrrp[718]: vrrp_instance(vi_1) transition to master state
aug 10 15:00:32 i-6gxo6kx7 keepalived_vrrp[718]: vrrp_instance(vi_1) entering master state

虚拟ip从主节点漂移到从节点

ip a

eth0: <broadcast,multicast,up,lower_up> mtu 1500 qdisc pfifo_fast state up group default qlen 1000
    link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever

mysql连接测试

mysql -h 192.168.1.111 -u root -p 

到此这篇关于ubuntu搭建mysql+keepalived高可用的实现(双主热备)的文章就介绍到这了,更多相关mysql+keepalived高可用内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！