出于学习目的， 编写一键搭建Mysql脚本

主机信息

使用三台机器，搭建一主两从

前期准备

• 使用Ansible， 便于集群主机节点管理

• 配置文件， 机器IP，角色信息

脚本

配置文件

config.ini

[ssh_password]
halou

[mysql]
192.168.114.133 master
192.168.114.134 slave
192.168.114.135 slave

脚本内容

mysql_master_slave_install.sh

#!/bin/bash


CUR_PATH=$(readlink -f $(dirname $0))


#读取变量
HOST_LINE=`sed -n '/\[mysql\]/,/\[.*\]/p' ./config.ini | grep -v "\[.*\]" | grep -v ^$ | grep -v ^#`
HOST_LIST=`sed -n '/\[mysql\]/,/\[.*\]/p' ./config.ini | grep -v "\[.*\]" | grep -v ^$ | grep -v ^# | awk '{print $1}'`
SSH_PASSWORD=`sed -n '/\[ssh_password\]/,/\[.*\]/p' ./config.ini | grep -v "\[.*\]" | grep -v ^$ | grep -v ^# `
MYSQL_DATA_DIR="/www/mysql/data_dir"


function result_echo
{
    if [ $? -eq 0 ]; then 
        echo "$1" | tee -a $CUR_PATH/log.log
    else
        echo "$2" | tee -a $CUR_PATH/log.log
        exit 1
    fi
}

#运行一次，清空一次log
if [ -e $CUR_PATH/log.log ]; then
    rm -f $CUR_PATH/log.log
fi

# if [ -e $MYSQL_DATA_DIR ]; then 
#     rm -rf $MYSQL_DATA_DIR
# fi

# if [ ! -d $MYSQL_DATA_DIR ]; then
#     mkdir -p "$MYSQL_DATA_DIR"
# fi


#第一步 安装依赖的基础软件包
yum repolist | grep epel &> /dev/null

#管理节点增加yum源
if [ $? -ne 0 ];then
    yum install epel-release -y
    yum makecache
fi

yum install sshpass ansible -y

#生成秘钥，并发送给各个节点执行
echo "y\n" | ssh-keygen -t rsa -q -N "" -f ~/.ssh/id_rsa


for host in $HOST_LIST;do
    sshpass -p$SSH_PASSWORD ssh-copy-id -o StrictHostKeyChecking=no [email protected]$host &> /dev/null
    ssh -o StrictHostKeyChecking=no [email protected]$host 'ls -al' &> /dev/null

    result_echo "Ok... No password login is success" "Failed...Use no password login failed, please check！！！"
    
done



#使用ansible管理主机节点

if [ -e $CUR_PATH/hosts ]; then
    rm -f $CUR_PATH/hosts
fi

for host in $HOST_LIST;do
    echo $host >> $CUR_PATH/hosts
done

#关闭防火墙
ansible -i $CUR_PATH/hosts all -m shell -a 'systemctl stop firewalld'
result_echo "Ok...Stop filewall success" "Failed... Stop firewalld is failed"

ansible -i $CUR_PATH/hosts all -m shell -a 'systemctl disable firewalld'
result_echo "Ok...Disable filewall success" "Failed... Stop Disable is failed"

ansible -i $CUR_PATH/hosts all -m shell -a 'getenforce | grep Disabled &> /dev/null || setenforce 0'
result_echo "OK... setenforce firewall is success" "Faild... setenforce firewalld is failed"

ansible -i $CUR_PATH/hosts all -m lineinfile -a 'path=/etc/sysconfig/selinux regexp="^SELINUX=" line="SELINUX=disabled"'
result_echo "OK... disabled config firewall is success" "Faild... disabled config  firewalld is failed"



#安装mysql

ansible -i $CUR_PATH/hosts all -m shell -a 'yum install mariadb mariadb-server -y'
result_echo "Ok... install mariab success" "Failed...install mariadb failed,please check"

ansible -i $CUR_PATH/hosts all -m shell -a "[[ -d $MYSQL_DATA_DIR ]] && rm -rf $MYSQL_DATA_DIR"
ansible -i $CUR_PATH/hosts all -m shell -a "[[ -d $MYSQL_DATA_DIR ]] || mkdir -p $MYSQL_DATA_DIR"

INDEX=1
for host in $HOST_LIST; do

    STATUS=`echo "$HOST_LINE" | grep $host | sort | uniq | awk '{print $2}'`

    ansible -i $CUR_PATH/hosts $host -m lineinfile -a "path=/etc/my.cnf regexp="^datadir" line="datadir=$MYSQL_DATA_DIR" insertafter="\[mysqld\]""
    ansible -i $CUR_PATH/hosts $host -m lineinfile -a "path=/etc/my.cnf line="server_id=$INDEX" insertafter=\"\[mysqld\]\""

    if [[ "$STATUS" == "master" ]]; then
        ansible -i $CUR_PATH/hosts $host -m lineinfile -a 'path=/etc/my.cnf regexp="^log-bin" line="log-bin=master-bin" insertafter="^\[mysqld\]"'

        #保存MASTER_MARIADB_IP
        MASTER_MARIADB_IP=$host
        result_echo "OK... Set master node mysql config /etc/my.cnf log-bin=masert.bin is success" "Failed... Set /etc/my.cnf log-bin=master.bin is failed!!!"
    fi

    if [[ "$STATUS" == "slave" ]]; then
        ansible -i $CUR_PATH/hosts $host -m lineinfile -a 'path=/etc/my.cnf line="relay-log=slave-log" insertafter="^\[mysqld\]"'
        result_echo "Ok... Set slave node mysql config /etc/my.cnf log-bin=relay-log=slave-log is sucesss" "Failed... Set slave node mysql config /etc/my.cnf log-bin=relay-log is failed!!!"
    fi
    INDEX=`expr $INDEX + 1`

    ansible -i $CUR_PATH/hosts $host -m shell -a "chown -R mysql:mysql $MYSQL_DATA_DIR"
    ansible -i $CUR_PATH/hosts $host -m shell -a "systemctl restart mariadb"


    #登录后授权
    if [[ "$STATUS" == "master" ]]; then
        #新的服务需要修改root密码， 这个略过
        #授权复制账户
        ansible -i $CUR_PATH/hosts $host -m shell -a "mysql -e \"GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'demo_pass'\""
        #直接执行mysql指令(注意， 这个限定了安装脚本只能在master上跑) 后期需要改成ansible的
        MASTER_BIN_LOG_NAME=`mysql -e "show master status\G;" | grep File | awk -F: '{print $2}' | awk '{gsub(/^\s+|\s+$/, "");print}'`
        MASTER_BIN_LOG_POSITION=`mysql -e "show master status\G;" |grep Position | awk -F: {'print $2'} | awk '{gsub(/^\s+|\s+$/, "");print}'`
        
    fi

    if [[ "$STATUS" == "slave" ]]; then

        #重置并开启slave
        ansible -i $CUR_PATH/hosts $host -m shell -a  "mysql -e \"reset slave \""
        ansible -i $CUR_PATH/hosts $host -m shell \
            -a "mysql -e \
                \"CHANGE MASTER TO master_host='$MASTER_MARIADB_IP', master_user='slave_user', master_password='demo_pass', master_log_file='$MASTER_BIN_LOG_NAME',master_log_pos=$MASTER_BIN_LOG_POSITION \""
        ansible -i $CUR_PATH/hosts $host -m shell -a  "mysql -e \"start slave \""
    fi

done