Linux shell实现MySQL5.6/5.7/8.0 单主，双主，MGR集群部署

说明：

单主安装脚本，根据用户所需（大）版本安装。

双主安装脚本，可实现双主自动同步。

MGR安装脚本，可实现MGR集群。前提（single primary,三节点部署）

代码仅为单主脚本。如有需要可提供所需package

如下截图仅为单主安装脚本之 5.6版本示例

[root@dba-virtual-host-220122 local]# cat install.sh
#!/bin/bash
##################################
# File Name:install.sh
# Author:zhangjiale
# Version:V1.0
# Description:HotDB-Cloud components Configure

#释放系统cache占用
function purge_sys_cache(){
CLEANMEMFLAG=0
echo 3 > proc/sys/vm/drop_caches
if [ $? -eq 0 ]; then
    CLEANMEMFLAG=1
fi
}

#服务器总内存
function check_sys_mem(){
export TOTAL_MEM=$(free |sed -n '2p'|awk -F ' ' '{print $2}')
export SERVER_TOTAL_MEM=$(free|sed -n '2p'|awk -F ' ' '{print $4}')
export SERVER_TOTAL_MEM=$[SERVER_TOTAL_MEM/1024]

if [ $CLEANMEMFLAG -eq 1 ];then
    export SERVER_TOTAL_MEM=$[SERVER_TOTAL_MEM*90/100]
else
    export TOTAL_MEM=$[TOTAL_MEM*90/100]
    if [ $TOTAL_MEM -lt $SERVER_TOTAL_MEM ];then
            export SERVER_TOTAL_MEM=$TOTAL_MEM
    fi
fi
echo "The free memery is "$SERVER_TOTAL_MEM"M"
}


case "$1" in
    mysql_single)
    sh ./install_mysql_single.sh;;
    mysql_mgr)
    sh ./install_mysql_mgr.sh
    sh ./ntpdate_service.sh;;
    mysql_mm_init)
    sh ./install_mysql_mm_1.sh;;
    mysql_mm_sync)
    sh ./install_mysql_mm_2.sh
    sh ./ntpdate_service.sh;;
    check)
    check_sys_mem;;
    *)
    echo "usage: $0 {mysql_single|mysql_mgr|mysql_mm_init|mysql_mm_sync|check}"
    exit 1
esac

[root@dba-virtual-host-220122 local]# cat install_mysql_single.sh
#!/bin/bash
##################################
# File Name:install_mysql_single.sh
# Author:zhangjiale
# Version:V1.0
# Description:install single instance for MySQL

export OPERATE_LOG=/tmp/operate_mgr.log
export FORMAT_TIME=`date +"%Y-%m-%d_%H:%M:%S"`
export STARTTIME=`date +"%Y-%m-%d %H:%M:%S"`
export LOCAL_IP=$LOCAL_IP
export MYSQL="/usr/local/mysql/bin/mysql"
export BASEDIR="$(dirname "$0")"
yum list net-tools |grep "Installed Packages" >/dev/null 2>&1
[ $? -ne 0 ] && yum -y install net-tools >>$OPERATE_LOG 2>&1
export SID=`/sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|head -1|awk -F'.' '{print $NF}' `
export EXISTS_PORT=`netstat -ntpl |grep mysqld |awk '{print $4}' |awk -F ':' '{print $NF}'|awk BEGIN{RS=EOF}'{gsub(/\n/," ");print}'`
export EXISTS_PORT_NUM=`netstat -ntpl |grep mysqld |awk '{print $4}' |awk -F ':' '{print $NF}'|awk BEGIN{RS=EOF}'{gsub(/\n/," ");print}'|wc -l`
if [ $EXISTS_PORT_NUM -gt 0 ];then
        echo -e "MySQL instance $EXISTS_PORT already exists. Please choice an unused port to use !!!"
        read -p "Input you will use MySQL-Instance Port:" MYSQL_PORT
else
        export MYSQL_PORT="3306"
        echo -e "No MySQL Instance started. choice 3306 port to use."
fi

read -p "Input you will use MySQL-Version{5.6 or 5.7 or 8.0}:" MYSQL_VERSION

cat etc/passwd |grep -w mysql >/dev/null 2>&1 && echo "User:mysql is exists" >/dev/null || groupadd mysql >/dev/null 2>&1  && useradd -r -g mysql -s bin/false mysql >/dev/null 2>&1
export DBPATH="/data/mysqldata/mysql$MYSQL_PORT"
if [ ! -d ${DBPATH} ];then
        mkdir -p data/mysqldata/mysql$MYSQL_PORT/{binlog,innodb_log,innodb_ts,log,backup,sock,mydata,relaylog,tmpdir}
fi
chown -R mysql.mysql data/mysqldata/mysql$MYSQL_PORT/

export MY_V=$MYSQL_VERSION
#mysql_tarball=`find $basedir -maxdepth 1 -name 'mysql*-glibc*.tar*' |grep $my_v |awk -F'/' '{print $2}'`


#MySQL buffer_pool调优
MYSQL_INSTANCE_NUM=`ps aux |grep mysqld |grep sock |grep port |wc -l`
if [ $MYSQL_INSTANCE_NUM -eq 0 ];then
  export INNODB_BUFFER_POOL_SIZE=$[SERVER_TOTAL_MEM*75/100]
else
  export MYSQL_INSTANCE_NUM=$[MYSQL_INSTANCE_NUM+1]
  export INNODB_BUFFER_POOL_SIZE=$[SERVER_TOTAL_MEM/$MYSQL_INSTANCE_NUM*75/100]
fi

if [ $INNODB_BUFFER_POOL_SIZE -lt 1024 ];then
  export INNODB_BUFFER_POOL_SIZE=1024
fi

export INNODB_BUFFER_POOL_SIZE=$INNODB_BUFFER_POOL_SIZE"M"

#MySQL InnoDB read/write io_thread调优
CPU_NUM=`lscpu |grep -w 'CPU(s)' |head -1 |awk '{print $2}'`
export INNODB_READ_IO_THREADS=$CPU_NUM
export INNODB_WRITE_IO_THREADS=$CPU_NUM

cp -r $BASEDIR/my_template.cnf data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT

# 修改配置文件中关于性能参数
sed -i "s#\(innodb_buffer_pool_size=\).*#\1$INNODB_BUFFER_POOL_SIZE#g" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
sed -i "s#\(innodb_read_io_threads=\).*#\1$INNODB_READ_IO_THREADS#g" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
sed -i "s#\(innodb_write_io_threads=\).*#\1$INNODB_WRITE_IO_THREADS#g" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
# 修改配置文件基础参数
sed -i "s#\(server_id=\).*#\1$SID#g" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
sed -i "s#3333#$MYSQL_PORT#g" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
sed -i "s#report_host=node1_ip#report_host=$LOCAL_IP#g" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT

[ $? -eq 0 ] && echo "Modify my.cnf.$MYSQL_PORT Successfully" >/dev/null || echo "Modify my.cnf.$MYSQL_PORT failed "


if [[ $MY_V == "5.6" ]];then
    sed -i "s/^disabled_storage_engines/#&/" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
    sed -i "s/^plugin_load/#&/" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
    sed -i "s/^transaction_write_set_extraction/#&/" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
    sed -i "s/^loose-group_replication/#&/" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
    sed -i "s/^slave-preserve-commit-order/#&/" data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT
    
    if [ ! -f $BASEDIR/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz ];then
        echo "No package found"
        exit 1
    fi

    tar xzf $BASEDIR/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz  -C usr/local/
    if [ ! -d usr/local/mysql56 ];then
        mv usr/local/mysql-5.6.28-linux-glibc2.5-x86_64 usr/local/mysql56
        chown -R mysql.mysql usr/local/mysql56
        ln -s usr/local/mysql56/bin/mysql* usr/bin/ >/dev/null 2>&1
        cd usr/local/mysql56
        ./scripts/mysql_install_db --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql >>$OPERATE_LOG 2>&1
        sleep 10
        export ERROR_NUM=`cat data/mysqldata/mysql$MYSQL_PORT/log/error.log |grep -w '[ERROR]' |wc -l`
        if [ $ERROR_NUM -gt 0 ];then
        echo "Initialize error,check you initialize!!! " >>$OPERATE_LOG
        fi
        ./bin/mysqld_safe --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql &
    else
        cd usr/local/mysql56
        ./scripts/mysql_install_db --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql >>$OPERATE_LOG 2>&1
        sleep 10
        export ERROR_NUM=`cat data/mysqldata/mysql$MYSQL_PORT/log/error.log |grep -w '[ERROR]' |wc -l`
        if [ $ERROR_NUM -gt 0 ];then
        echo "Initialize error,check you initialize!!! " >>$OPERATE_LOG
        fi
        ./bin/mysqld_safe --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql &
        
    fi
    
elif [[ $MY_V == "5.7" ]];then
    if [ ! -f $BASEDIR/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz ];then
        echo "No package found"
        exit 1
    fi
    tar xzf $BASEDIR/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz  -C usr/local/
    if [ ! -d usr/local/mysql57 ];then
        mv usr/local/mysql-5.7.27-linux-glibc2.12-x86_64 usr/local/mysql57
        chown -R mysql.mysql usr/local/mysql57
        ln -s usr/local/mysql57/bin/mysql* usr/bin/ >/dev/null 2>&1
        cd usr/local/mysql57
        ./bin/mysqld --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --initialize-insecure
        sleep 10
        export ERROR_NUM=`cat data/mysqldata/mysql$MYSQL_PORT/log/error.log |grep -w '[ERROR]' |wc -l`
        if [ $ERROR_NUM -gt 0 ];then
        echo "Initialize error,check you initialize!!! " >>$OPERATE_LOG
        fi
        ./bin/mysqld_safe --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql & >>$OPERATE_LOG
    else
        cd usr/local/mysql57
        ./bin/mysqld --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --initialize-insecure
        sleep 10
        export ERROR_NUM=`cat data/mysqldata/mysql$MYSQL_PORT/log/error.log |grep -w '[ERROR]' |wc -l`
        if [ $ERROR_NUM -gt 0 ];then
        echo "Initialize error,check you initialize!!! " >>$OPERATE_LOG
        fi
        ./bin/mysqld_safe --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql & >>$OPERATE_LOG
        
    fi
elif [[ $MY_V == "8.0" ]]; then
    if [ ! -f $BASEDIR/mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz ];then
        echo "No package found"
        exit 1
    fi
    tar xf $BASEDIR/mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz  -C usr/local/
    if [ ! -d usr/local/mysql80 ];then
        mv usr/local/mysql-8.0.17-linux-glibc2.12-x86_64 usr/local/mysql80
        chown -R mysql.mysql usr/local/mysql80
        ln -s usr/local/mysql80/bin/mysql* usr/bin/ >/dev/null 2>&1
        cd usr/local/mysql80
        ./bin/mysqld --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --initialize-insecure
        sleep 10
        export ERROR_NUM=`cat /data/mysqldata/mysql$MYSQL_PORT/log/error.log |grep -w '[ERROR]' |wc -l`
        if [ $ERROR_NUM -gt 0 ];then
        echo "Initialize error,check you initialize!!! " >>$OPERATE_LOG
        fi
        ./bin/mysqld_safe --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql & >>$OPERATE_LOG
    else
        cd /usr/local/mysql80
        ./bin/mysqld --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --initialize-insecure
        sleep 10
        export ERROR_NUM=`cat /data/mysqldata/mysql$MYSQL_PORT/log/error.log |grep -w '[ERROR]' |wc -l`
        if [ $ERROR_NUM -gt 0 ];then
        echo "Initialize error,check you initialize!!! " >>$OPERATE_LOG
        fi
        ./bin/mysqld_safe --defaults-file=/data/mysqldata/mysql$MYSQL_PORT/my.cnf.$MYSQL_PORT --user=mysql & >>$OPERATE_LOG
    fi
fi


#启动MySQL服务后连续连接实例30次，判断连接是否正常，正常连接后退出。
export TIME=1
while [ $TIME -lt 30 ]
do
   $MYSQL -uroot -S /data/mysqldata/mysql$MYSQL_PORT/sock/mysql.sock -e "select version();" >/dev/null 2>&1
   if [ $? -eq 0 ]
   then
       echo "Try $TIME times connect to MySQL-$MY_V instance successfully">>$OPERATE_LOG
       break
   elif [ $TIME -gt 30 ]
   then
       echo "Try $TIME times connect to MySQL-$MY_V instance end in failure" >>$OPERATE_LOG
       break
   else
       sleep 1
       TIME=`expr $TIME + 1`
       echo "Try $TIME times connect MySQL-$MY_V instance ing ......" >>$OPERATE_LOG
  fi
done

export ENDTIME=`date +"%Y-%m-%d %H:%M:%S"`
export BEGIN_DATE=`date -d "$STARTTIME" +%s`
export END_DATE=`date -d "$ENDTIME" +%s`
export SPENDTIME=`expr $END_DATE - $BEGIN_DATE`
echo "AT $FORMAT_TIME: takes $SPENDTIME sec for install single instance for MySQL operation" >>$OPERATE_LOG

由于篇幅有限，太冗长大伙看了可能太沉闷。有兴趣的可私聊，欢迎拍砖。

写的不好还请见谅。

本人写作的初衷：希望以自我的记录温故，突破自我知识体系。更希望能给大家带来帮助。不足和错误之处更希望各位能拍砖指正。

本人尽力保证每周都能写作一两篇。内容不限。但主要围绕小工具的开发，数据库原理的实践验证，性能优化，高可用架构设计。