mysql双主多从高可用配置_双主MySQL+keepalived高可用配置

部署双节点双主数据库服务器MySQL

分别在二台服务器上安装mysql，要求同版本或主服务器比从服务器版本高。

主机mysql配置：

Vi /etc/my.cnf

[mysqld]

Log-bin=mysql-bin

Server-id=1

Service mysqld restart

Mysql –uroot –p

从mysql配置：

Vi /etc/my.cnf

[mysqld]

Log-bin=mysql-bin

Server-id=2

然后从主mysql导出数据，mysql –uroot –p dump –single-transaction *** > ***.sql

导入到从mysql数据库：mysql –uroot –p  *** < ***.sql(***表示数据库名)

从服务器：

可能遇到的问题：log-bin一定不是log_bin，并且位置在[mysqld]的里面。否则日志不能正常开启。

主服务器：

测试：

以下为部署keepalived双主高可用：

二个服务器IP10.16.37.101,10.16.37.94，一个VIP10.16.37.200。

安装keepalived：

下载keepalived-1.2.23.tar.gz

tar –xvf keepalived-1.2.23.tar.gz –C /opt

cd /opt/keepalived-1.2.23/

./configure prefix=/usr/local/keepalived

make & make install

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

mkdir /etc/keepalived

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

vi /etc/keepalived/keepalived.conf

主配置：(10.16.37.101)

! Configuration File for keepalived

global_defs {

notification_email {

}

notification_email_from linuxidc@linuxidc.com

smtp_server 127.0.0.1      #表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现

smtp_connect_timeout 30 #连接smtp超时时间

router_id MySQL-ha    #一般为主机名

}

vrrp_instance VI_1 {

state BACKUP  #都为BACKUP

interface eth0

virtual_router_id 51

priority 100    #权限100

advert_int 1

nopreempt        #不抢占

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.16.37.200  #虚拟IP地址，即VIP

}

}

virtual_server 10.16.37.200 3306 {  #监控端口及配置

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 10.16.37.101 3306 {  #真实IP地址

weight 3

notify_down /usr/local/MySQL/bin/MySQL.sh #宕机脚本

TCP_CHECK {

connect_timeout 3

connect_port 3306

}

}

}

从配置：(10.16.37.94)

! Configuration File for keepalived

global_defs {

notification_email {

}

notification_email_from linuxidc@linuxidc.com

smtp_server 127.0.0.1      #表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现

smtp_connect_timeout 30 #连接smtp超时时间

router_id Nginx-ha    #一般为主机名

}

vrrp_instance VI_1 {

state BACKUP  #都为BACKUP

interface eth0

virtual_router_id 51

priority 90    #权限90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.16.37.200  #虚拟IP地址，即VIP

}

}

virtual_server 10.16.37.200 3306 {  #监控端口及配置

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 10.16.37.94 3306 {  #真实IP地址

weight 3

notify_down /usr/local/MySQL/bin/MySQL.sh #宕机脚本

TCP_CHECK {

connect_timeout 3

connect_port 3306

}

}

}

vi  /usr/local/MySQL/bin/MySQL.sh

#!/bin/bash

pkill keepalived

# chmod +x  /usr/local/MySQL/bin/MySQL.sh

测试：关闭一台服务器mysql，ps aux | grep keepalved查看keepalived是否自动关闭。注意防火墙和selinux的关闭！！