MySql 主从复制+Keepalived

SunnyFan大约 5 分钟约 1368 字

MySql 主从复制+Keepalived

安装MySql

安装MySql点击

准备节点

10.6.251.248
10.6.251.249

编辑节点配置文件

1.主节点

vi /etc/my.cnf

#启用二进制日志文件
log-bin = mysql-bin  # 开启二进制日志，主节点必须要开启，从节点可以不开。
server-id = 248  # 服务ID号，主从节点都需要，且要唯一，一般配置为IP地址后面几位

# 重启mysql
service mysql restart

# 查看二进制文件是否开启成功
mysql -uroot -p

SHOW GLOBAL VARIABLES LIKE '%log%';

#  查看主库信息
SHOW master STATUS;

#mysql-bin.000002
#154

图

2.从节点

# 修改my.cnf
vi /etc/my.cnf

log-bin = mysql-bin  # 开启二进制日志，主节点必须要开启，从节点可以不开。
server-id = 249  # 服务ID号，主从节点都需要，且要唯一，一般配置为IP地址后面几位

# 重启mysql
service mysql restart
# 从库执行
mysql -uroot -p
change master to  master_host='10.6.251.248',master_user='root',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;

# 填写正确的主服务器信息，账号密码信息
# master_log_file和master_log_pos用查询到主库里的信息；
# 配置完成后，启动slave服务：

start slave;

# 查看slave信息：
show slave status \G;

# Slave_IO_Running 和 Slave_SQL_Running这两项必须要全部显示为yes才可以。

# 如果上keepalived 248也需要做如下操作
#mysql -uroot -p
#change master to  master_host='10.6.251.249',master_user='root',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;

# 填写正确的主服务器信息，账号密码信息
# master_log_file和master_log_pos用查询到主库里的信息；此处获取249的信息
# 配置完成后，启动slave服务：

#start slave;

# 查看slave信息：
#show slave status \G;

提示

如果第二台是直接克隆的第一台的环境,此时Slave_IO_Running应该是NO

需要去安装目录修改auto.cnf的server-uuid cd /opt/mysql/data

vi auto.cnf

service mysql restart

继续查看参数是否为Yes

mysql -uroot -p

stop slave; 停止链路

start slave; 启动链路

show slave status \G; 查看链路

操作主库，做同步验证

自行测试，建库，建表，数据更新等操作

Keepalived搭建

安装Keepalived

# 下载包
wget https://www.keepalived.org/software/keepalived-2.2.7.tar.gz

# 解压包到指定目录
tar zxvf keepalived-2.2.7.tar.gz -C /usr/local

# 进入目录
cd /usr/local

# 重命名 keepalived-2.2.7
mv keepalived-2.2.7  keepalived

# 进入keepalived目录
cd keepalived


./configure --prefix=/usr/local/keepalived
# 如有openssl错误，请执行安装 
yum -y install openssl openssl-devel

# 安装完成后再 ./configure --prefix=/usr/local/keepalived

#安装
make && make install

# 启动脚本拷贝到/etc/init.d，方便用service命令进行操作
cp /usr/local/keepalived/keepalived/etc/init.d/keepalived /etc/init.d/keepalived
ll /etc/init.d/ | grep keepalived


pidfile: /var/run/keepalived.pid
config: /etc/keepalived/keepalived.conf
#把配置文件移动到启动应用默认找的路径/etc/keepalived/keepalived.conf
mkdir -p /etc/keepalived
mv  /usr/local/keepalived/etc/keepalived/keepalived.conf.sample  /etc/keepalived/keepalived.conf

主库配置

rm /etc/keepalived/keepalived.conf
vi /etc/keepalived/keepalived.conf

#配置内容如下
global_defs {
   router_id MySQL-HA
} 
 
vrrp_script check_run {
script "/opt/mysql/mysql_check.sh"  #此文件待会创建
interval 60
}
 
vrrp_sync_group VG1 {
group {
VI_1
}
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100  
    advert_int 1
    nopreempt
    track_script {
    check_run
    }
    notify_master /opt/mysql/master.sh
    notify_stop /opt/mysql/stop.sh
 
    virtual_ipaddress {
       10.6.251.247
    }
}

编写/opt/mysql/mysql_check.sh

vi /opt/mysql/mysql_check.sh

# 内容
#!/bin/bash
count=1
 
while true
do
 
mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show status;" > /dev/null 2>&1
i=$?
ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
j=$?
if [ $i = 0 ] && [ $j = 0 ]
then
   exit 0
else
   if [ $i = 1 ] && [ $j = 0 ]
   then
       exit 0
   else
        if [ $count -gt 5 ]
        then
              break
        fi
   let count++
   continue
   fi
fi
 
done
 
/etc/init.d/keepalived stop

编写/opt/mysql/master.sh

vi /opt/mysql/master.sh

# 内容
#!/bin/bash
 
Master_Log_File=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
Relay_Master_Log_File=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
Read_Master_Log_Pos=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
Exec_Master_Log_Pos=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')
 
i=1
 
while true
do
 
if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]
then
   echo "ok"
   break
else
   sleep 1
 
   if [ $i -gt 60 ]
   then
      break
   fi
   continue
   let i++
fi
done
 
mysql -uroot -p1111111 -S /tmp/mysql.sock -e "stop slave;"
mysql -uroot -p1111111 -S /tmp/mysql.sock -e "reset slave all;"
mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt

编写/opt/mysql/stop.sh

vi /opt/mysql/stop.sh

#内容
#!/bin/bash
 
M_File1=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position1=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show master status\G" | awk -F': ' '/Position/{print $2}')
sleep 1
M_File2=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position2=$(mysql -uroot -p1111111 -S /tmp/mysql.sock -e "show master status\G" | awk -F': ' '/Position/{print $2}')
 
i=1
 
while true
do
 
if [ $M_File1 = $M_File1 ] && [ $M_Position1 -eq $M_Position2 ]
then
   echo "ok"
   break
else
   sleep 1
 
   if [ $i -gt 60 ]
   then
      break
   fi
   continue
   let i++
fi
done

启动keepalived

service keepalived start #启动
service keepalived stop #停止
service keepalived restart #重启

从库配置，同主库配置

# 安装Keepalived 同主库
# 编写/opt/mysql/mysql_check.sh 同主库
# 编写/opt/mysql/master.sh 同主库
# 编写/opt/mysql/stop.sh 同主库

从库/etc/keepalived/keepalived.conf配置

rm /etc/keepalived/keepalived.conf
vi /etc/keepalived/keepalived.conf
# 内容
global_defs {
   router_id MySQL-HA
} 
 
vrrp_script check_run {
script "/opt/mysql/mysql_check.sh"  #此文件待会创建
interval 60
}
 
vrrp_sync_group VG1 {
group {
VI_1
}
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90  
    advert_int 1
    nopreempt
    track_script {
    check_run
    }
    notify_master /opt/mysql/master.sh
    notify_stop /opt/mysql/stop.sh
 
    virtual_ipaddress {
       10.6.251.247
    }
}

启动keepalived

service keepalived start #启动
service keepalived stop #停止
service keepalived restart #重启

验证 keepalived

使用10.6.251.247 虚拟ip连接

图

1.杀死主库进程，看是否依然能正常操作数据库

pkill -9 mysqld

2.启动主库，杀死从库，互相测试

service mysql start

Copyright © 2024 SunnyFan