一键搭建MySql主从

已完成

• 一键安装mysql
• 一键安装ansible
• 一键配置主从
• 支持一主一从
• 支持一主多从

未完成

集成控制系统

• 显示主从状态
• 显示当前服务器负载情况
• 管理mysql数据库（赠删改查
• 在线编辑mysql配置文件

详细代码

#!/bin/bash
# Author: Ropon
# Blog: https://www.ropon.top

#借助ansible、expect
downurl="http://panel.ropon.top/panel/lnmp/"
mysqlname="mysql-5.6.43-1.el7.x86_64.rpm"
#mysqlname="mysql-5.7.25-1.el7.x86_64.rpm"
ip="172.16.7.124"
#多个从服务器
declare -A CserverLst
#CserverLst=([s1]="172.16.7.125")
CserverLst=([s1]="172.16.7.125" [s2]="172.16.7.126")
cport="22"
cpasswd="ropon.top"
ansible_host="/etc/ansible/hosts"
mysqlpasswd="West.cn2020"
user="westdemo"
passwd="west.cn"
host="172.16.7.%"
tmpsshfile="/tmp/ssh.exp"
mysqlmasterfile="/tmp/master.info"
cmysqllibfile="/tmp/cmysqllib.sh"
cmysqlslavefile="/tmp/slave.sh"

MySql() {
    yum install -y ncurses ncurses-devel libaio numactl numactl-libs perl-Module-Install
    if [ $? -eq 0 ]; then 
        wget ${downurl}${mysqlname}
        rpm -ivh $mysqlname
        sleep 1
        . /etc/profile
        mysql -uroot -p$mysqlpasswd -e "grant replication slave on *.* to '${user}'@'${host}' identified by '${passwd}';"
        mysql -uroot -p$mysqlpasswd -e "flush privileges;"
        mysql -uroot -p$mysqlpasswd -e "show master status;" > $mysqlmasterfile
        CFirwall
    fi
}

Ansible() {
    if [[ -f /etc/yum.repos.d/epel*.repo ]]; then
        mv /etc/yum.repos.d/epel*.repo{,_bak}
    fi
    wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
    yum install -y ansible
}

Ssh() {
    yum install -y expect
    echo '#!/usr/bin/expect
spawn ssh-keygen
expect {
    "*.ssh/id_rsa*" {exp_send "\r";exp_continue}
    "*passphrase*"  {exp_send "\r";exp_continue}
    "*again*"       {exp_send "\r"}
}' > $tmpsshfile
    expect $tmpsshfile
    sleep 1
    echo "[db]" >> $ansible_host

    for key in ${!CserverLst[*]}; do
        cat > $tmpsshfile << EOF
#!/usr/bin/expect
spawn ssh-copy-id ${CserverLst[$key]} -p ${cport}
expect {
    "*yes/no*"   {exp_send "yes\r";exp_continue}
    "*password*" {exp_send "${cpasswd}\r";exp_continue}
}
EOF
        expect $tmpsshfile
        echo "${CserverLst[$key]} ansible_ssh_port=${cport}" >> $ansible_host
    done
    ansible db -m ping
}

CMySql() {
    cat > $cmysqllibfile << EOF
#!/bin/bash
yum install -y ncurses ncurses-devel libaio numactl numactl-libs perl-Module-Install
if [ $? -eq 0 ]; then 
        wget $downurl${mysqlname}
        rpm -ivh $mysqlname
        sleep 1
        sed -i "s@server-id.*@server-id = 2@g" /etc/my.cnf
        sed -i "s@log_bin.*@#log_bin = mysql-bin@g" /etc/my.cnf
        service mysqld restart
    fi
EOF
    ansible db -m script -a "${cmysqllibfile}"
    sleep 1
    mysql_bin_name=$(cat $mysqlmasterfile |awk  '{if (NR>1){print $1}}')
    mysql_bin_offset=$(cat $mysqlmasterfile |awk  '{if (NR>1){print $2}}')
    cat > $cmysqlslavefile << EOF
#!/bin/bash
/usr/local/mysql/bin/mysql -uroot -p${mysqlpasswd} -e "change master to master_host='${ip}',master_user='${user}',master_port=3306,master_password='${passwd}',master_log_file='${mysql_bin_name}',master_log_pos=${mysql_bin_offset};"
/usr/local/mysql/bin/mysql -uroot -p${mysqlpasswd} -e "start slave;"
/usr/local/mysql/bin/mysql -uroot -p${mysqlpasswd} -e "show slave status\G;"
service mysqld restart
EOF
    ansible db -m script -a "${cmysqlslavefile}"
}

CFirwall() {
    iptables -L -n |grep -w dpt:$cport >/dev/null
    if  [ $? -eq 0 ] ;then
        iptables -L -n |grep -w dpt:3306 >/dev/null
        if  [ $? -ne 0 ] ;then
            sed -i "/dport ${cport} -j ACCEPT/a\-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT" /etc/sysconfig/iptables
            service iptables restart

        fi
    fi
}

Clean() {
    rm -rf $tmpsshfile
    rm -rf $mysqlmasterfile
    rm -rf $cmysqllibfile
    rm -rf $cmysqlslavefile
}

Main() {
    MySql
    Ansible
    Ssh
    CMySql
    Clean
}

Main