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服务，假如此台数据损坏，必须从另外机器恢复数据与二进制日志文件。