mysql主从(主备)同步一键配置,配自动检测功能

时间：2021-08-14 05:46:34

主从一键shell配置

做个笔记。

#!/bin/bash

#Mysql sync

#chenglee

#master机器ip

MasterIP="192.168.137.174"

#slave机器ip

SlaveIp="192.168.137.170"

#主从同步账号密码

MASTER_USER="sync"

MASTER_PASSWORD="sync"

#master机mysql登录账号密码

Muser="root"

Mpass="123456"

#slave机mysql登录账号密码

Suser="root"

Spass="123456"

#slave机ssh的登录账号密码

SSHuser="root"

SSHpass="lizhenghua"

function Master(){

    #MASTER

    function create(){

        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "flush PRIVILEGES;" >/dev/null 2>&1

        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "CREATE USER '${MASTER_USER}'@'${SlaveIp}' IDENTIFIED BY '${MASTER_PASSWORD}';" >/dev/null 2>&1

        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "grant replication slave on *.* to '${MASTER_USER}'@'${SlaveIp}' identified by '${MASTER_PASSWORD}';" >/dev/null 2>&1

        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "flush PRIVILEGES;" >/dev/null 2>&1

    }

    #my.cnf

    function addbin(){

        number=`cat -n /etc/my.cnf | grep -F "[mysqld]" | awk '{print$1}'`

        serverID=`echo ${MasterIP} | awk -F'.' '{print$NF}'`

        text1="server-id=${serverID}"

        text2="log-bin=mysql-bin"

        text3="lower_case_table_names=1"

        sed -i "${number}a\\${text1}\n${text2}\n${text3}" /etc/my.cnf

    }

    #restart

    function Mrestart(){

        service mysql restart

    }

    #ssh

    function mstscSSH(){

        echo -e "\033[43;35m 开始配置双机交互，请按回车后输入slave机登录密码 \033[0m"

        ssh-keygen -N ''

        ssh-copy-id ${SSHuser}@${SlaveIp}

    }

echo "创建同步账号"

create

echo "添加master端id到my.cnf文件"

addbin

echo "重启master端数据库"

Mrestart

echo "开始账号交互"

mstscSSH

}

#SLAVE

function Slave(){

    #my.cnf

    function addbin2(){

        Snumber2=`ssh ${SSHuser}@${SlaveIp} "cat -n /etc/my.cnf | grep -F "[mysqld]" | awk '{print$1}'" | awk '{print$1}'`

        SserverID2=`echo ${SlaveIp} | awk -F'.' '{print$NF}'`

        Stext1="server-id=${SserverID2}"

        Stext3="lower_case_table_names=1"

        ssh ${SSHuser}@${SlaveIp} "sed -i '${Snumber2}a\\${Stext1}\n${Stext3}' /etc/my.cnf"

    }

    #restart

    function Srestart(){

        ssh ${SSHuser}@${SlaveIp} "service mysql restart"

    }

    function Ssync(){

        mkdir logs

        mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e "show master status;" > logs/chenglee.logs

        MASTER_HOST=${MasterIP}

        MASTER_LOG_FILE=`cat logs/chenglee.logs | tail -n 1 | awk '{print$1}'`

        MASTER_LOG_POS=`cat logs/chenglee.logs | tail -n 1 | awk '{print$2}'`

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"stop slave;\"" >/dev/null 2>&1

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave;\"" >/dev/null 2>&1

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave all;\"" >/dev/null 2>&1

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"change master to master_host='${MasterIP}', master_user='${MASTER_USER}', master_password='${MASTER_PASSWORD}', master_log_file='${MASTER_LOG_FILE}', master_log_pos=${MASTER_LOG_POS}, MASTER_CONNECT_RETRY=10;\"" >/dev/null 2>&1

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"start slave;\"" >/dev/null 2>&1

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"show slave status \G;\"" > logs/slave_sync.logs

}

echo "添加master端id到my.cnf文件"

addbin2

echo "重启slave端数据库"

Srestart

echo "开始同步配置"

Ssync

}

#校准同步

#:提示

#当发现主从同步失败的时候， 开启此选项则自动检测并重新配置同步

function calibrate(){

    mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e "show master status;" > logs/chenglee.logs

    ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"show slave status \G;\"" > logs/slave_sync.logs

    MASTER_HOST=${MasterIP}

    MASTER_LOG_FILE=`cat logs/chenglee.logs | tail -n 1 | awk '{print$1}'`

    MASTER_LOG_POS=`cat logs/chenglee.logs | tail -n 1 | awk '{print$2}'`

    string1=`cat logs/slave_sync.logs | sed -n '12,13p' | awk -F ':' '{print$2}' | head -n 1`

    string2=`cat logs/slave_sync.logs | sed -n '12,13p' | awk -F ':' '{print$2}' | head -n 2 | tail -n 1`

    if [ ${string1} = ${string2} ];then

        if ( echo ${string1} |grep -q 'Yes' && echo ${string2} |grep -q 'Yes' );then

            echo "主从已同步，无需校准"

        else

            echo "1主从已断开，准备校准"

            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"stop slave;\""

            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave;\""

            ssh ${SSHuser}@${SlaveIp} "service mysql restart"

            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"change master to master_host='${MasterIP}', master_user='${MASTER_USER}', master_password='${MASTER_PASSWORD}', master_log_file='${MASTER_LOG_FILE}', master_log_pos=${MASTER_LOG_POS}\""

            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"start slave;\""

            echo "1校准成功"

        fi

        exit

    else

        echo "2主从已断开，准备校准"

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"stop slave;\""

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave;\""

        ssh ${SSHuser}@${SlaveIp} "service mysql restart"

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"change master to master_host='${MasterIP}', master_user='${MASTER_USER}', master_password='${MASTER_PASSWORD}', master_log_file='${MASTER_LOG_FILE}', master_log_pos=${MASTER_LOG_POS}\""

        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"start slave;\""

        echo "2校准成功"

    fi

}

function main(){

    Master

    Slave

    #calibrate

}

main

　　

相关文章