Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)
程序员文章站
2022-03-13 10:04:13
mysql5.5双机热备实现方案安装两台mysql安装mysql5.5sudo apt-get updateapt-get install aptitudeaptitude install mysql...
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高可用内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!