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

Mysql5.5双机热备

实现方案

安装两台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