Mysql5.5双机热备实现方案安装两台Mysql

安装Mysql5.5

sudo apt-get updateapt-get install aptitudeaptitude install mysql-server-5.5或sudo apt-cache search mariadb-serverapt-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 -pgrant all on *.* to root@'%' identified by 'root' with grant option;flush privileges;

配置节点1

vim /etc/mysql/my.cnf

server-id       = 1                                            #节点IDlog_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_schemabinlog_ignore_db=performance_schema

重启mysql

service mysql restartmysql -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       = 2log_bin         = mysql-bin.log                    relay_log       = mysql-relay-bin.log        #中继日志log_slave_updates = ON                                  #中继日志执行后，变化计入日志read_only       = 0binlog_format   = "ROW"auto_increment_increment = 2auto_increment_offset  = 2binlog_ignore_db=mysqlbinlog_ignore_db=information_schemabinlog_ignore_db=performance_schemareplicate_ignore_db=mysqlreplicate_ignore_db=information_schemareplicate_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.000001    1029        mysql,information_schema,performance_schema

配置主主（节点1）

vim /etc/mysql/my.cnf

relay_log       = mysql-relay-bin.loglog_slave_updates = ONread_only       = 0replicate_ignore_db=mysqlreplicate_ignore_db=information_schemareplicate_ignore_db=performance_schema

开启同步

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

#依赖sudo apt-get install -y libssl-devsudo apt-get install -y openssl sudo apt-get install -y libpopt-devsudo apt-get install -y libnl-dev libnl-3-dev libnl-genl-3.devapt-get install daemonapt-get install libc-devapt-get install libnfnetlink-devapt-get install libnl-genl-3.dev#安装apt-get install keepalived#编译安装cd /usr/localwget  -zxvf keepalived-2.2.2.tar.gz mv keepalived-2.2.2 keepalived./configure --prefix=/usr/local/keepalivedsudo 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/messagessudo mkdir /etc/sysconfigsudo 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/keepalivedsudo 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/shkillall keepalived

分配权限

chmod +x /usr/local/bin/mysql.sh

测试

重启keepalived

service keepalived restart

查看日志

tail -f /var/log/messages

查看虚拟IP

ip addr  #或ip a 或ifconfig#主节点会有虚拟IPeth0: <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]:3306Aug 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]:3306Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Lost quorum 1-0=1 > 0 for VS [192.168.1.111]:3306Aug 10 15:00:30 i-7jaope92 Keepalived_vrrp[4950]: VRRP_Instance(VI_1) sending 0 priorityAug 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 STATEAug 10 15:00:32 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Entering MASTER STATE

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

ip aeth0: <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