Ubuntu搭建Mysql+Keepalived高可用(双主热备) mysqlkeepalived

Mysql5.5双机热备实现方案

文章图片

安装两台Mysql
安装Mysql5.5

sudo apt-get updateapt-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.1mysql -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.000001245mysql,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 -pCHANGE 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.0000011029mysql,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

【Ubuntu搭建Mysql+Keepalived高可用(双主热备)】开启同步

mysql -u root -pCHANGE 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/messagessudo service rsyslog restart tail -f /var/log/messagessudo 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还是UDPreal_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还是UDPreal_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/shkillall 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: 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 aeth0: 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连接测试