服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)

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

2022-01-12 18:18程序员苟哥 Mysql

本文主要介绍了Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)，文中通过示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

Mysql5.5双机热备

实现方案

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

安装两台Mysql

安装Mysql5.5

?
1
2
3
4
5
6
7
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

卸载

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

配置权限

?
1
2
3
4
5
6
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

?
1
2
3
4
5
6
7
8
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

?
1
2
service mysql restart
mysql -u root -p

记录节点1的binlog日志位置

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

配置节点2

vim /etc/mysql/my.cnf

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

配置主从

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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日志位置

?
1
2
3
show master status;
 
mysql-bin.000001    1029        mysql,information_schema,performance_schema

配置主主（节点1）

vim /etc/mysql/my.cnf

?
1
2
3
4
5
6
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

开启同步

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#依赖
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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

?
1
2
#!/bin/sh
killall keepalived

分配权限

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

?
1
service keepalived restart

查看日志

?
1
tail -f /var/log/messages

查看虚拟IP

?
1
2
3
4
5
6
7
8
9
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服务

?
1
service mysql stop

日志信息

?
1
2
3
4
5
6
7
8
9
10
11
#主节点
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从主节点漂移到从节点

?
1
2
3
4
5
6
7
8
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连接测试

?
1
mysql -h 192.168.1.111 -u root -p<font face="Arial, Verdana, sans-serif"><span style="white-space: normal;"> </span></font>

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

原文链接：https://blog.csdn.net/yyyy_11119/article/details/121596994

  • mysql
  • Keepalived
  • 高可用

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 2006 　 1054 　 1215 　 1615 　 10048 　 1146 　 2003 　 1366 　 28000 　 1577 　 1786 　 1175 　 1698 　 1135 　 1093 　 2002 　 1067 　
    © 2019-2022 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图