shell脚本安装mysql主从样例

自定义参数

BUFFER_SIZE= #InnoDB缓冲池大小(单位:G)
MYSQL_ADMIN= #设置MYSQL管理账号
MYSQL_ADMIN_PASS= #设置管理账号密码
MYSQL_BASE_PATH= #MYSQL基础目录
MYSQL_NAMEP= #MYSQL名称
MYSQL_PASS= #设置ROOT密码
MYSQL_PATH= #MYSQL安装路径
MYSQL_USER_PASS= #设置普通用户密码
PKG_NAME= #PKG包名称
START_PORT= #监听端口
TEMP_PATH= #临时目录

部署参数检查

# 检查参数是否设置
echo "=====参数是否设置====="
if [ ! $MYSQL_PATH ];then
	echo "=====$MYSQL_PATH 此参数未设置，请检查！====="
    exit 1
fi

if [ ! $MYSQL_BASE_PATH ];then
    echo "=====$MYSQL_BASE_PATH 参数未设置，请检查！====="
    exit 1 
fi 

if [ ! $TEMP_PATH ];then
    echo "=====$TEMP_PATH 参数未设置，请检查！====="
    exit 1 
fi

if [ ! $START_PORT ];then
    echo "=====$START_PORT 参数未设置，请检查！====="
    exit 1 
fi

if [ ! $MYSQL_PASS ];then
    echo "=====$MYSQL_PASS 参数未设置，请检查！====="
    exit 1 
fi

if [ ! $MYSQL_USER_PASS ];then
    echo "=====$MYSQL_USER_PASS 参数未设置，请检查！====="
    exit 1 
fi

ISSTART=$(netstat -nutlp|grep ${START_PORT}|grep -v "grep"|wc -l)

echo "=====检查端口是否已被占用===="
if [ "${ISSTART}" != "0" ]; then
    echo "=====MYSQL 启动端口已占用，请重新选择端口====="  
    exit 1
else
    echo "=====MYSQL 启动端口未占用，符合预期！=====" 
fi

# 检查目标目录是否存在
echo "=====检查目标目录是否存在====="
if [ ! -d $TEMP_PATH ];then
    echo "=====$TEMP_PATH 目录不存在，创建目录====="
	mkdir -p $TEMP_PATH
else
    echo "=====$TEMP_PATH目录已存在，无需创建====="
fi

if [ ! -d $MYSQL_PATH/$START_PORT ];then
    echo "$MYSQL_PATH/$START_PORT 目录不存在，创建目录"
	mkdir -p $MYSQL_PATH/$START_PORT/{run,tmp,binary,relay}
else 
    echo "=====$MYSQL_PATH/$START_PORT 目录已存在，退出安装====="
    exit 1
fi	
	
 echo "=====部署前检查通过====="

上传并解压安装包并修改配置文件

NUM_ID=`hostname -I |awk -F '.' '{print $NF}'`

echo "=====解压安装包到${TEMP_PATH}====="
unzip -o ${PKG_NAME}.zip -d ${TEMP_PATH}
echo "=====查看解压后的内容====="
ls ${TEMP_PATH}/${PKG_NAME}

echo "=====拷贝配置文件到$MYSQL_PATH/$START_PORT/run====="
cp $START_PORT.cnf $MYSQL_PATH/$START_PORT/run
ls $MYSQL_PATH/$START_PORT/run

# 修改配置文件，开启gtid模式
echo "=====修改配置文件，开启gtid模式====="
sed -i "s@gtid_mode.*@gtid_mode = on@g" $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf
sed -i "s@enforce_gtid_consistency.*@enforce_gtid_consistency = 1@g" $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf
sed -i "s@innodb_data_file_path.*@innodb_data_file_path=ibdata1:12M;ibdata2:512M:autoextend@g" $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf
sed -i "s@server-id.*@server-id=${NUM_ID}@g" $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf
sed -i "s@innodb_buffer_pool_size.*@innodb_buffer_pool_size = ${BUFFER_SIZE}G@g" $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf

cat $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf

安装+设置启停脚本+启动

# 创建普通启动用户
echo "=====开始创建普通用户====="
if id -u mysql >/dev/null 2>&1; then
    echo "=====mysql user exists====="
    getent group mysql >/dev/null 2>&1
    if [ $? != '0' ];then
        usermod -a -G mysql mysql
    fi
else
    groupadd mysql
    useradd -g mysql -s /sbin/nologin mysql
fi

# 卸载本机自带的数据库，业务评审需要确认是否需要增加其他内容
echo "=====开始卸载本机自带的数据库====="
rpm -qa |grep mariadb-libs
if [ $? == '0' ];then
    yum remove mariadb-libs -y
fi

# 检查依赖包是否安装
echo "=====开始检查依赖包是否安装======"
rpm -qa |grep libaio
if [ $? != '0' ];then
    echo "=====正在安装依赖包====="
    yum -y install libaio
else
    echo "=====无需安装依赖包====="
fi

# 进入临时安装目录,并重命名基础目录
echo "=====进入临时安装目录${TEMP_PATH}/${PKG_NAME}，并重命名基础目录$MYSQL_BASE_PATH====="
if [ ! -d ${MYSQL_BASE_PATH} ];then
    cd ${TEMP_PATH}/${PKG_NAME}
    tar -xf ${MYSQL_NAME}.tar.gz
    mv ${MYSQL_NAME} ${MYSQL_BASE_PATH}
fi
cd ${MYSQL_BASE_PATH}

# 给普通用户授权
echo "=====给普通用户授权====="
chown -R mysql:mysql $MYSQL_BASE_PATH
chown -R mysql:mysql $MYSQL_PATH

# 初始化数据
echo "=====开始初始化数据====="
./bin/mysqld --initialize-insecure  --user=mysql --basedir=$MYSQL_BASE_PATH --datadir=${MYSQL_PATH}/${START_PORT}/data --port=${START_PORT} --innodb_buffer_pool_size=${BUFFER_SIZE}G 
if [ $? == '0' ];then
    echo "mysql 初始化成功！"
else
    echo "mysql 初始化失败！"
    exit 1
fi

# 启动
echo "=====开始设置启动脚本====="
# 设置启动脚本
cat > /usr/lib/systemd/system/mysql${START_PORT}.service <<-EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(7)
After=network.target
After=syslog.target

[Service]
User=mysql
Group=mysql
ExecStart= ${MYSQL_BASE_PATH}/bin/mysqld --defaults-file=${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.cnf
ExecReload=/bin/kill -HUP $MAINPID
Restart=always
LimitNOFILE = 65535

[Install]
WantedBy=multi-user.target
EOF
echo "=====启动并设置开机自启====="
systemctl daemon-reload
systemctl start mysql${START_PORT}
#systemctl enable mysql${START_PORT}(针对mysql数据库，不建议开机自启，防止磁盘损坏时启动服务导致数据二次破坏)
echo "=====启停方式：systemctl start|stop|restart mysql${START_PORT}====="

\cp ${MYSQL_BASE_PATH}/bin/* /usr/local/bin


#按照进程判断，默认启动时间2分钟，判断两次，按需修改循环次数
echo "=====开始判断服务是否已启动成功====="
    for((i=1;i<=12;i++));
    do
        sleep 10
        ISSTART=$(netstat -nutlp|grep ${START_PORT}|grep -v "grep"|wc -l)
        if [ "$ISSTART" != "0" ]; then
            echo "发现服务端口，为确保服务真正启动成功，等待15秒后进行第二次判断！"
                sleep 15
                ISSTART=$(netstat -nutlp|grep ${START_PORT}|grep -v "grep"|wc -l)
                if [ "$ISSTART" != "0" ]; then
                    echo "等待15秒后端口仍启动，服务第二次判断通过，服务启动成功！"
                    exit 0
                else
                    echo "等待15秒后端口未启动，服务第二次判断失败，服务启动失败！"
                    exit 1
                fi
        else
            echo "服务启动中，请等待！"
        fi
    done
    echo "等待2分钟后未发现服务端口，服务启动失败！"
    exit 1

设置密码并授权登录

sleep 5 #启动成功后，为了防止sock文件找不到，增加5秒等待
echo "=====设置管理员密码====="
mysql -uroot -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "SET PASSWORD = PASSWORD('${MYSQL_PASS}');"

echo "=====授权root登录，并设置和授权复制用户，并reset master和slave====="
mysql -uroot -p${MYSQL_PASS} -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "

grant all privileges  on  *.*  to 'root'@'localhost' identified  by '${MYSQL_PASS}' with grant option;
grant all privileges  on  *.*  to '${MYSQL_ADMIN}'@'%' identified  by '${MYSQL_ADMIN_PASS}' with grant option;
grant replication slave on *.* to 'repl'@'%' identified by '${MYSQL_USER_PASS}';reset master;reset slave;"

配置主从-主库执行

echo "=====开始进行主从配置====="
mysql  -uroot -p${MYSQL_PASS} -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "
change master to 
master_host='$slave',
master_port=${START_PORT},
master_user='repl',
master_password='${MYSQL_USER_PASS}',
MASTER_AUTO_POSITION=1;
start slave;"

echo "=====主从配置完成====="

配置主从-从库执行

echo "=====开始进行主从配置====="
mysql  -uroot -p${MYSQL_PASS} -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "
change master to 
master_host='$master',
master_port=${START_PORT},
master_user='repl',
master_password='${MYSQL_USER_PASS}',
MASTER_AUTO_POSITION=1;
start slave;"

echo "=====主从配置完成====="

配置半同步并重启

echo "=====开始配置半同步====="
# 加载插件
mysql  -u${MYSQL_ADMIN} -p${MYSQL_ADMIN_PASS} -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
show plugins;
"
# 修改配置文件，添加配置
echo "
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON ">> $MYSQL_PATH/$START_PORT/run/$START_PORT.cnf

# 重启mysql
systemctl restart mysql${START_PORT}

#按照进程判断，默认启动时间2分钟，判断两次，按需修改循环次数
echo "=====开始判断服务是否已启动成功====="
    for((i=1;i<=12;i++));
    do
        sleep 10
        ISSTART=$(netstat -nutlp|grep ${START_PORT}|grep -v "grep"|wc -l)
        if [ "$ISSTART" != "0" ]; then
            echo "发现服务端口，为确保服务真正启动成功，等待15秒后进行第二次判断！"
                sleep 15
                ISSTART=$(netstat -nutlp|grep ${START_PORT}|grep -v "grep"|wc -l)
                if [ "$ISSTART" != "0" ]; then
                    echo "等待15秒后端口仍启动，服务第二次判断通过，服务启动成功！"
                    exit 0
                else
                    echo "等待15秒后端口未启动，服务第二次判断失败，服务启动失败！"
                    exit 1
                fi
        else
            echo "服务启动中，请等待！"
        fi
    done
    echo "等待2分钟后未发现服务端口，服务启动失败！"
    exit 1

检查主从状态

# 重新启动线程，保挂主从同步正常运行
sleep 10 
mysql  -u${MYSQL_ADMIN} -p${MYSQL_ADMIN_PASS} -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "start slave;"
echo "=====检查主从状态====="
STATUS_NUM=`mysql  -uroot -p${MYSQL_PASS} -S ${MYSQL_PATH}/${START_PORT}/run/${START_PORT}.sock -e "show slave status\G" |grep "Running: Yes" |wc -l`

if [ ${STATUS_NUM} == '2' ];then
    echo "=====Running: Yes值：${STATUS_NUM}，主从状态正常====="
else
    echo "=====主从搭建异常，请检查====="
    exit 1
fi