自动化部署Mysql数据库的脚本

# !/bin/bash
# author hlc
# createTime 2024-06-17
# modifyTime 2024-06-18
# version 1.0
# description 自动安装Mysql
source /etc/init.d/functions


# 定义参数
# 用于循环
count=0
# 时间
date=$(date "+%H:%M:%S:%N")
#==========Mysql服务器参数==========
# Mysql服务器用户名
name=root
# Mysql服务器IP地址
host=192.168.217.101
# Mysql用户名
mysqlName=root
# Mysql要设置的密码
mysqlPasswd=Xls@123..
# mysql安装包校验文件名称
mysqlMD5=mysql.tar.gz.flag
# 需要MD5生成的配置校验文件
configMD5=mysqlconfig.${date}.flag
# 远程登录用户名
remoteName=root
# 远程登录用户密码
remotePasswd=Remote@123..
# 要存放脚本报错的文件
error=/tmp/error.log
# **********这三个参数必须跟资源服务器中的配置文件同步*********
# data的存放路径
datadir=/Auto_MySQL_Data/mysql
# binlog的存放路径
binlogdir=/Auto_MySQL_Data/mysql_bin/
# slowlog的存放路径
slowlogdir=/Auto_MySQL_Data/mysql_slow/


#==========资源服务器参数==========
# 资源服务器用户名
sourceName=root
# 资源服务器密码
sourcePasswd=123456
# 资源服务器IP地址
sourceHost=192.168.217.200
# Mysql安装包的路径
mysqlPath=/tools/mysql/
# mysql安装包名称
mysqlRpm=mysql.tar.gz
# mysql配置文件名称
configName=mysqlconfig


#==========自定义函数==========
# 创建SSH密钥
createSSHKey() {
expect << eof
    spawn  ssh-keygen
    expect ".ssh/id_rsa):"
    send "\r"
    expect {
        # 如果没有密钥则创建密钥
        "no passphrase):"
        { send "\r" }
        # 如果有密钥则退出
        "Overwrite (y/n)? "
        { send "exit\r" }
    }
    expect "again:"
    send "\r"
expect eof
eof
}

# 与资源服务器建立SSH连接
createSSHLink() {
expect << eof
    spawn  ssh-copy-id -i /${name}/.ssh/id_rsa.pub ${sourceName}@${sourceHost}
    expect "connecting (yes/no)? "
    send "yes\r"
    expect "'s password: "
    send "${sourcePasswd}\r"
expect eof
eof
}


# 对目录进行检测
checkDir() {
    # 检测Mysql的数据存储目录
    if [[ -d $datadir} ]]
        then
            action "${datadir}已经被使用了" /bin/false
            echo "error code: 0 --> ${datadir} has been used" >> ${error}
            exit 127
    fi
    action "${datadir}可以使用" /bin/true 
    
    # 检测Mysql的binlog存储目录
    if [[ -d $binlogdir} ]]
        then
            action "${binlogdir}已经被使用了" /bin/false
            echo "error code: 0 --> ${binlogdir} has been used" >> ${error}
            exit 127
    fi
    action "${binlogdir}可以使用" /bin/true 
    
    # 检测Mysql的slowlog存储目录
    if [[ -d $slowlogdir} ]]
        then
            action "${slowlogdir}已经被使用了" /bin/false
            echo "error code: 0 --> ${slowlogdir} has been used" >> ${error}
            exit 127
    fi
    action "${slowlogdir}可以使用" /bin/true 
}

# 对环境进行检测
checkEnvironment() {
    # 判断是否有expect
    line=$(rpm -qa | grep expect | wc -l)
    if [[ ${line} -eq 0 ]]
        then
            # 下载expect
            yum install expect -y && action "安装expect成功" /bin/true || (action "安装expect失败" /bin/false; echo "error code: 1 --> can not download expect" >> ${error}; exit 127)
    fi
    
    # 创建SSH密钥
    createSSHKey >> /dev/null 2>&1
    
    # 测试SSH连接
    while [ ${count} -lt 3 ]
    do
        timeout 5 ssh ${sourceName}@${sourceHost} "echo ''" >> /dev/null 2>&1
        if [[ $? -eq 0 ]]
            then
                action "可以连接到资源服务器" /bin/true
                break
            else
                # 创建SSH连接
                createSSHLink >> /dev/null 2>&1
                ((count++))
        fi
    done
    # 如果连接失败则报错
    if [[ ${count} -eq 3 ]]
        then
            action "建立SSH连接失败" /bin/false
            echo "error code: 1 --> can not create ssh connection" >> ${error}
            exit 127
    else
        action "建立SSH连接成功" /bin/true
    fi
    
    # 判断Mysql服务器上是否有与资源服务器一样的目录
    if [[ -d ${mysqlPath} ]]
        then
            action "${mysqlPath}已经被使用了" /bin/false
            echo "error code: 1 --> ${mysqlPath} has been used" >> ${error}
            exit 127
    fi
    # 如果不存在该目录则创建
    mkdir -p ${mysqlPath}
    if [[ $? -ne 0 ]]
        then
            action "创建${mysqlPath}失败" /bin/false
            echo "error code: 1 --> create ${mysqlPath} fail" >> ${error}
            exit 127
    fi
    action "创建${mysqlPath}成功" /bin/true
}

# 下载Mysql安装包，并进行校验
downloadMysql() {
    # 从资源服务器上将mysql安装包拉取下来
    count=0
    while [ ${count} -lt 3 ]
    do
        rsync -a ${sourceName}@${sourceHost}:${mysqlPath}/${mysqlRpm} ${mysqlPath}
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功拉取文件
    if [[ ${count} -eq 3 ]]
        then
            action "从资源服务器拉取mysql安装包失败" /bin/false
            echo "error code: 2 --> download mysql fail" >> ${error}
            exit 127
    else
        action "从资源服务器拉取mysql安装包成功" /bin/true
    fi
    
    # 从资源服务器上将mysqlMD5校验文件拉取下来
    count=0
    while [ ${count} -lt 3 ]
    do
        rsync -a ${sourceName}@${sourceHost}:${mysqlPath}/${mysqlMD5} ${mysqlPath}
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功拉取文件
    if [[ ${count} -eq 3 ]]
        then
            action "从资源服务器拉取mysql校验文件失败" /bin/false
            echo "error code: 2 --> download mysqlconfig fail" >> ${error}
            exit 127
    else
        action "从资源服务器拉取mysql校验文件成功" /bin/true
    fi
    
    # 跳转到Mysql安装包路径
    cd ${mysqlPath}
    
    # 校验安装包
    result=$(md5sum -c ${mysqlMD5} | grep "${mysqlRpm}: OK" | wc -l)
    if [[ ${result} -ne 1 ]]
        then
            action "mysql安装文件已损失" /bin/false
            echo "error code: 2 --> mysql is corrupted" >> ${error}
            exit 127
    else
        action "mysql安装文件完整" /bin/true  
    fi
}

# 解压Mysql安装包并安装Mysql
installMysql() {
    # 解压安装包
    if [[ -f ${mysqlRpm} ]]
        then
            tar -zxf ${mysqlRpm} -C ./
            if [[ $? -ne 0 ]]
                then
                    action "mysql安装包解压失败" /bin/false
                    echo "error code: 3 --> unzip mysql.tar.gz" >> ${error}
                    exit 127
            fi
            action "mysql安装包解压成功" /bin/true
    fi
    
    # 安装mysql
    # 卸载mariadb
    rpm -e --nodeps mariadb-libs && action "卸载mariadb成功" /bin/true ||  (action "卸载mariadb失败" /bin/false; echo "error code: 3 --> uninstall mariadb fail" >> ${error}; exit 127)
    # 安装ncurses-devel libaio-devel
    yum install ncurses-devel libaio-devel -y && action "安装ncurses-devel libaio-devel成功" /bin/true || (action "安装ncurses-devel libaio-devel失败" /bin/false; echo "error code: 3 --> install ncurses-devel libaio-devel fail" >> ${error}; exit 127)
    # 安装mysql-community-common
    rpm -ivh mysql-community-common-5.7.38-1.el7.x86_64.rpm && action "安装mysql-community-common成功" /bin/true || (action "安装mysql-community-common失败" /bin/false; echo "error code: 3 --> install mysql-community-common fail" >> ${error}; exit 127)
    # 安装mysql-community-libs
    rpm -ivh mysql-community-libs-5.7.38-1.el7.x86_64.rpm && action "安装mysql-community-libs成功" /bin/true || (action "安装mysql-community-libs失败" /bin/false; echo "error code: 3 --> install mysql-community-libs fail" >> ${error}; exit 127)
    # 安装mysql-community-devel
    rpm -ivh mysql-community-devel-5.7.38-1.el7.x86_64.rpm && action "安装mysql-community-devel成功" /bin/true || (action "安装mysql-community-devel失败" /bin/false; echo "error code: 3 --> install mysql-community-devel fail" >> ${error}; exit 127)
    # 安装mysql-community-client
    rpm -ivh mysql-community-client-5.7.38-1.el7.x86_64.rpm && action "安装mysql-community-client成功" /bin/true || (action "安装mysql-community-client失败" /bin/false; echo "error code: 3 --> install mysql-community-client fail" >> ${error}; exit 127)
    # 安装mysql-community-server
    rpm -ivh mysql-community-server-5.7.38-1.el7.x86_64.rpm && action "安装mysql-community-server成功" /bin/true || (action "安装mysql-community-server失败" /bin/false; echo "error code: 3 --> install mysql-community-server fail" >> ${error}; exit 127)
}

# 下载Mysql配置文件并进行更改
changeMysqlConfig() {
    # 在资源服务器上为mysql配置文件生成校验文件
    count=0
    while [ ${count} -lt 3 ]
    do
        ssh ${sourceName}@${sourceHost} "cd ${mysqlPath} && md5sum ${configName} > ${configMD5}"
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功生成configMD5文件
    if [[ ${count} -eq 3 ]]
        then
            action "生成configMD5失败" /bin/false
            echo "error code: 4 --> create configMD5 fail" >> ${error}
            exit 127
    else
        action "生成configMD5成功" /bin/true
    fi
    
    # 从资源服务器上将mysql配置文件拉取下来
    count=0
    while [ ${count} -lt 3 ]
    do
        rsync -a ${sourceName}@${sourceHost}:${mysqlPath}/${mysqlconfig} ${mysqlPath}
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功拉取文件
    if [[ ${count} -eq 3 ]]
        then
            action "从资源服务器拉取mysql配置文件失败" /bin/false
            echo "error code: 4 --> download mysqlconfigMD5 fail" >> ${error}
            exit 127
    else
        action "从资源服务器拉取mysql校验配置成功" /bin/true
    fi
    
    # 配置文件拉取成功后更改资源服务器的配置文件
    id=$(cat ${mysqlPath}${configName} | grep 'server-id' | awk -F '=' '{print $2}')
    if [[ ${id} -gt 1000000 ]]
        then
            action "配置文件的server-id太大了，存在风险" /bin/false
            echo "error code: 4 --> server-id is so large, it is a dangerous operation" >> ${error}
            exit 127
    fi
    # 记录当前id数值
    oldid=${id}
    action "本次配置mysql使用的server-id为${oldid}"
    # id+1写回配置文件
    ((id++))
    action "为资源服务器的配置文件更新的server-id为${id}"
    count=0
    while [ ${count} -lt 3 ]
    do
        ssh ${sourceName}@${sourceHost} "sed -i "s/server-id=${oldid}/server-id=${id}/" ${mysqlPath}${configName}"
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功更改配置文件
    if [[ ${count} -eq 3 ]]
        then
            action "更新资源服务器的mysql配置文件失败" /bin/false
            echo "error code: 4 --> update mysqlconfig fail" >> ${error}
            exit 127
    fi
    action "更新资源服务器的mysql配置文件成功" /bin/true
    
    # 从资源服务器上将configMD5文件拉取下来
    count=0
    while [ ${count} -lt 3 ]
    do
        rsync -a ${sourceName}@${sourceHost}:${mysqlPath}/${configMD5} ${mysqlPath}
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功拉取文件
    if [[ ${count} -eq 3 ]]
        then
            action "从资源服务器拉取mysqlMD5文件失败" /bin/false
            echo "error code: 4 --> download configMD5 fail" >> ${error}
            exit 127
    else
        action "从资源服务器拉取mysqlMD5文件成功" /bin/true
    fi
    
    # 校验配置文件
    result=$(md5sum -c ${configMD5} | grep "${configName}: OK" | wc -l)
    if [[ ${result} -ne 1 ]]
        then
            action "mysql配置文件已损失" /bin/false
            echo "error code: 4 --> mysqlconfig is corrupted" >> ${error}
            exit 127
    else
        action "mysql配置文件完整" /bin/true  
    fi
    
    # 校验成功后将configMD5校验文件删除
    count=0
    while [ ${count} -lt 3 ]
    do
        ssh ${sourceName}@${sourceHost} "cd ${mysqlPath} && rm -f ${configMD5}"
        if [[ $? -eq 0 ]]
            then
                break;
        else
            ((count++))
        fi
    done
    # 判断是否成功删除文件
    if [[ ${count} -eq 3 ]]
        then
            action "无法删除config校验文件：${configMD5}" /bin/false
            echo "waring code: 4 --> delete mysqlconfigMD5 fail" >> ${error}
    else
        action "成功删除config校验文件：${configMD5}" /bin/true
    fi
    
    # 删除原有的配置文件
    rm -rf /etc/my.cnf
    # 替换拉取的配置文件
    mv ${mysqlPath}${configName} /etc/my.cnf
}

# 创建Mysql的文件目录
createDir() {
    # 根据配置文件创建目录
    # 创建data存放路径
    mkdir -p ${datadir}
    if [[ $? -ne 0 ]]
        then
            action "不能创建目录：${datadir}" /bin/false
            # 将错误结果输出到文件中
            echo "error code: 5 --> can not create ${datadir}" >> ${error}
            exit 127
    else
        cd ${datadir}
        chown -R mysql:mysql ..
        action "可以创建目录：${datadir}" /bin/true
    fi
    
    # 创建binlog存放路径
    mkdir -p ${binlogdir}
    if [[ $? -ne 0 ]]
        then
            action "不能创建目录：${binlogdir}" /bin/false
            # 将错误结果输出到文件中
            echo "error code: 5 --> can not create ${binlogdir}" >> ${error}
            exit 127
    else
        cd ${binlogdir}
        chown -R mysql:mysql ..
        action "可以创建目录：${binlogdir}" /bin/true
    fi
    
    # 创建slowlog的存放路径
    mkdir -p ${slowlogdir}
    if [[ $? -ne 0 ]]
        then
            action "不能创建目录：${slowlogdir}" /bin/false
            # 将错误结果输出到文件中
            echo "error code: 5 --> can not create ${slowlogdir}" >> ${error}
            exit 127
    else
        cd ${slowlogdir}
        chown -R mysql:mysql ..
        action "可以创建目录：${slowlogdir}" /bin/true
    fi
}

# 启动Mysql，并配置Mysql自启动
startMysql() {
    # 启动Mysql
    systemctl start mysqld
    if [[ $? -ne 0 ]]
        then
            action "启动Mysql失败" /bin/false
            echo "error code: 6 --> start mysql fail" >> ${error}
            exit 127
    fi
    action "启动Mysql成功" /bin/true
    
    # 设置Mysql自启动
    systemctl enable mysqld
    if [[ $? -ne 0 ]]
        then
            action "不能设置Mysql自启动" /bin/false
            echo "error code: 6 --> set mysql enable fail" >> ${error}
            exit 127
    fi
    action "设置Mysql自启动成功" /bin/true
}

# 对Mysql进行初始化配置，并创建远程用户
initMysql() {    
    # 获取mysql的初始密码
    startPasswd=$(cat /var/log/mysqld.log | grep root@localhost: | awk -F ': ' '{print $2}')
    # 登录mysql，并重置密码
    mysql -u"${mysqlName}" -p"${startPasswd}" --connect-expired-password -e "alter user '${mysqlName}'@'localhost' identified by '${mysqlPasswd}';grant all privileges on *.* to '${mysqlName}'@'localhost';flush privileges;" >> /dev/null 2>&1
    if [[ $? -ne 0 ]]
        then
            action "无法重置初始密码" /bin/false
            echo "error code: 7 --> can not reset password" >> ${error}
            exit 127
    fi
    action "成功重置初始密码" /bin/true
    
    # 创建远程登录
    mysql -u"${mysqlName}" -p"${mysqlPasswd}" -e "create user '${remoteName}'@'%' IDENTIFIED WITH mysql_native_password BY '${remotePasswd}';GRANT ALL ON *.* TO '${remoteName}'@'%' WITH GRANT OPTION;flush privileges;"  >> /dev/null 2>&1
    if [[ $? -ne 0 ]]
        then
            action "创建远程登录用户失败" /bin/false
            echo "error code: 7 --> create remote user fail" >> ${error}
            exit 127
    fi
    action "创建远程登录用户成功" /bin/true
    
    # 校验mysql的root用户
    mysql -u"${mysqlName}" -p"${mysqlPasswd}" -e "show databases\G;" >> /dev/null 2>&1
    if [[ $? -ne 0 ]]
        then
            action "mysql的root用户初始化失败" /bin/false
            echo "error code: 7 --> mysql root user init fail" >> ${error}
            exit 127
    fi
    action "mysql的root用户初始化成功" /bin/true
    
    # 校验mysql的远程用户
    mysql -h"${host}" -u"${remoteName}" -p"${remotePasswd}" -e "show databases\G;" >> /dev/null 2>&1
    if [[ $? -ne 0 ]]
        then
            action "mysql的远程用户初始化失败" /bin/false
            echo "error code: 7 --> mysql remote user init fail" >> ${error}
            exit 127
    fi
    action "mysql的远程用户初始化成功" /bin/true
}

#==========shell执行流程，case控制==========
# 使用变量接收用户输入，便于操作
single=$1

# whlie循环保证安装流程顺利执行
while true
do
    case ${single} in
        # 对Mysql的文件目录进行检测
        0)
            checkDir
            ((single++))
            ;;
        # 检查通信环境
        1)
            checkEnvironment
            ((single++))
            ;;
        # 下载Mysql安装包，并进行校验
        2)
            downloadMysql
            ((single++))
            ;;
        # 解压安装包并安装mysql
        3)
            installMysql
            ((single++))
            ;;
        # 下载Mysql配置文件并进行更改
        4)
            changeMysqlConfig
            ((single++))
            ;;
        # 创建Mysql的文件目录
        5)
            createDir
            ((single++))
            ;;
        # 启动Mysql，并配置Mysql自启动
        6)
            startMysql
            ((single++))
            ;;
        # 对Mysql进行初始化配置，并创建远程用户
        7)
            initMysql
            ((single++))
            ;;
        # 退出循环
        8)
            action "成功安装Mysql" /bin/true
            exit 0
            ;;
        # 用户传入错误参数
        *)
            action "请使用正确的参数进行安装，从头安装请使用0" /bin/false
            exit 127
    esac
done