mysql双主加keepalive_mysql双主模型(单活)+keepalived

mysql双主模型是两台mysql互为主从设计，在同一时刻只能有一台写入数据，另一台做热备，利用keepalived实现热备切换。

实验环境：两台都是干净的mariadb。

现实环境应该是单机允许N年，需要扩展为双主，如何实现？

1、实现主从架构

2、暂停前端写入数据到主mysql，重置从mysql的二进制日志

3、配置主mysql 向从mysql同步，并开启同步，测试数据

4、安装配置keepalive，前段代码修改连接数据IP到keepalive的浮动IP

node1:(复制帐号提前创建好了)

server-id = 1

log-bin=/data/mysql/binlogs/mysql-bin

log-bin-index=/data/mysql/binlogs/mysql-bin.index

relay_log_purge=0

relay_log=/data/mysql/binlogs/mysql-relay-log

relay_log_index=/data/mysql/binlogs/mysql-relay-log.index

datadir=/data/mysql/data

basedir=/usr/local/mysql

innodb-file-per-table=on

skip-name-resolve=on

log_slave_updates=true

MariaDB [(none)]> reset master;

MariaDB [(none)]> reset slave;

MariaDB [(none)]> change master to master_host='192.168.3.6',master_user='test',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=312;

MariaDB [(none)]> start slave;

node2:(复制帐号提前创建好了)

server-id = 2

log-bin=/data/mysql/binlogs/mysql-bin

log-bin-index=/data/mysql/binlogs/mysql-bin.index

relay_log_purge=0

relay_log=/data/mysql/binlogs/mysql-relay-log

relay_log_index=/data/mysql/binlogs/mysql-relay-log.index

datadir=/data/mysql/data

basedir=/usr/local/mysql

innodb-file-per-table=on

skip-name-resolve=on

log_slave_updates=true

MariaDB [(none)]> reset master;

MariaDB [(none)]> reset slave;

MariaDB [(none)]> change master to master_host='192.168.1.154',master_user='test',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=312;

MariaDB [(none)]> start slave;

两边更新数据测试：

安装keepalived

yum install -y keepalived

默认keepalived的允许日志记录在/var/log/message，调整输出到/var/log/keepalived.log

vi /etc/sysconfig/keepalived

KEEPALIVED_OPTIONS="-D -d -S 0"

vi /etc/rsyslog.conf

local0.* /var/log/keepalived.log

配置keepalived

node1:

vi /etc/keepalived/keepalived.conf

global_defs {

router_id node1

}

vrrp_script chk_mysql {

script "/etc/keepalived/checkmysql.sh"

interval 15

}

vrrp_instance VI_MYSQL1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 100

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

chk_mysql

}

virtual_ipaddress {

192.168.5.5

}

}

============

node2:

global_defs {

router_id node2

}

vrrp_script chk_mysql {

script "/etc/keepalived/checkmysql.sh"

interval 15

}

vrrp_instance VI_MYSQL1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 80

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

chk_mysql

}

virtual_ipaddress {

192.168.5.5

}

}

我采用的checkmysql.sh脚本：

#!/bin/bash

mysql_con='/usr/local/mysql/bin/mysql -uroot -hlocalhost'

error_log="/etc/keepalived/logs/check_mysql.err"

if [ `ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then

echo -e "`date "+%F %H:%M:%S"`--script is running---" >> ${error_log}

exit 0

fi

function excute_query {

${mysql_con} -e "select 1;" 2>> ${error_log}

}

function kill_keep {

service keepalived stop 2>> ${error_log}

pkill keepalived 2>> ${error_log}

}

excute_query

if [ $? -ne 0 ]; then

echo -e "`date "+%F %H:%M:%S"`--mysql filad---" >> ${error_log}

sleep 10

kill_keep

else

echo -e "`date "+%F %H:%M:%S"`--mysql sucessfull---" >> ${error_log}

fi

======

存在的问题：

1、keepalived执行监控脚本的周期是15s,是否太长？

2、在检测脚本中，当执行查询的数据库失败时，会有10s的sleep时间，接着停止keepalived的服务，10s时间是为了应对mysql同步复制数据，是否合理？

1、是否可以比较两台mysql的日志记录点，来保证数据一致性。

2、什么情况下mysql 查询执行不成功，且数据未同步？服务器物理资源不够用，任何系统处理变得缓慢。

3、每次发生切换，需要手动修复好mysql，启动mysql与keepalived服务，假如此台数据损坏，必须从另外机器恢复数据与二进制日志文件。