MySQL5.7.25 初始化安装脚本

#!/bin/bash
echo "初始化安装脚本"
<<COMMENT
  注:
   AUTHOR: ws, 779269823@qq.com
   CREATED: 2019.11.06
   MYSQL版本：5.7.25
   CentOS Linux release 7.6.1810 (Core) 
COMMENT
######################定义变量######################
SOFT_DIR=/opt/mysql
BINLOG_DIR=/data/binlog_3306
RUN_DIR=/var/run/mysql
GROUP_NAME=mysql
USER_NAME=mysql
PORT=3306
MYSQLDB_HOME=/usr/local/mysql
MYSQLDB_DATA_HOME=/data/mysql_$PORT
TMP_DIR=/data/tmp
MYSQL_VERSION="mysql-5.7.25-linux-glibc2.12-x86_64"
CPU_NUMBERS=$(cat /proc/cpuinfo |grep "processor"|wc -l)
#MYSQL_ID=`ifconfig eno16777736 | grep "inet addr" | awk -F. ‘{print $4}‘ | awk ‘{print $1}‘`
# 获取服务器内存
COMPUTER_MEM=`free -m |grep "Mem"|awk ‘{print $2}‘`
###################################################

#check if user is root
if [ $(id -u) != "0" ];then
   echo "Error: You must be root to run this script!"
   exit 1
fi

echo "========================================================================="
echo " install MySQL 5.7.25 on Redhat/CentOS Linux "
echo "========================================================================="

#addGroup
if [ -z $(cat /etc/group|awk -F: ‘{print $1}‘| grep -w "$GROUP_NAME") ]
then
  groupadd  $GROUP_NAME
    if(( $? == 0 ))
      then
         echo "group $GROUP_NAME add sucessfully!"
    fi
else
  echo "$GROUP_NAME is exsits"
fi

#addUser
if [ -z $(cat /etc/passwd|awk -F: ‘{print $1}‘| grep -w "$USE_NAME") ]
then
  adduser -g $GROUP_NAME $USER_NAME
     if (( $? == 0 ))
       then
       echo "user $USER_NAME add sucessfully!"
     fi
else
  echo "$USER_NAME is exsits"
fi


# 判断mysql目录是否创建
if [ -d $MYSQLDB_HOME ];then
    echo "目录已创建"
    else
    mkdir -p $MYSQLDB_HOME
fi

# 判断运行binlog目录是否创建
if [ -d $BINLOG_DIR ];then
    echo "目录已创建"
    else
    mkdir -p $BINLOG_DIR
fi

# 判断运行日志目录是否创建
if [ -d $RUN_DIR ];then
    echo "目录已创建"
    else
    mkdir -p $RUN_DIR
fi

#downMySQL
# 判断安装介质目录是否创建
if [ -d $SOFT_DIR ];then
    echo "目录已创建"
    else
    mkdir -p $SOFT_DIR
fi

#删除centos系统自带的mariadb数据库防止发生冲突
rpm -qa|grep mariadb
rpm -e mariadb-libs --nodeps

# 安装libaio
yum -y install libaio

if [ -s /etc/my.cnf ]; then
        mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%M%S`.bak
fi

echo "====================安装mysql5.7.25=========================="

# 创建mysql配置文件：
cat >>/etc/my.cnf<<EOF
[mysql]
# CLIENT #
port                           = 3306
socket                         = /var/run/mysql/mysql_3306.sock
 
[mysqld]
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/run/mysql/mysql_3306.sock
pid-file                       = /var/run/mysql/mysqld_3306.pid
port                           = 3306
local-infile                   = on
bind-address                   = 0.0.0.0
character-set-server           = utf8mb4
collation-server               = utf8mb4_general_ci
performance_schema             = OFF
# MyISAM #
key-buffer-size                = 128M
# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql_mode                       = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
sysdate-is-now                 = 1
#innodb                         = FORCE
symbolic-links                 = 0
explicit_defaults_for_timestamp
# DATA STORAGE #
datadir                        = /data/mysql_3306/
tmpdir                         = /data/tmp
lc-messages-dir                = /usr/local/mysql/share
 
# BINARY LOGGING #
server-id                      = 10165
expire-logs-days               = 7
sync-binlog                    = 1
log_bin                        = /data/binlog_3306/mysql-bin
max_binlog_size                = 1G
binlog_format                  = ROW
 
# REPLICATION #
read-only                     = 0
skip-slave-start                 = 0
log-slave-updates              = 1
relay-log                      = /data/binlog_3306/relay-bin
slave-net-timeout              = 60
sync-master-info               = 1000
sync-relay-log                 = 1000
sync-relay-log-info            = 1000
master-info-repository         = TABLE
relay-log-info-repository      = TABLE
relay_log_recovery             = ON
 
# CACHES AND LIMITS #
tmp-table-size                 = 64M
max-heap-table-size            = 128M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 4096
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096
join_buffer_size               = 16M
# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 10G
innodb_buffer_pool_instances   = 4
innodb_buffer_pool_dump_pct    = 80
innodb_buffer_pool_dump_at_shutdown = 1  #该命令用于在关闭时把热数据dump到本地磁盘
innodb_buffer_pool_load_at_startup  = 1   #在启动时把热数据加载到内存
innodb_flush_log_at_trx_commit      = 0  #每个事务提交时。每隔一秒，把事务日志缓冲区的数据写到日志文件中，以及吧日志文件的数据刷到磁盘上他的性能是最好的，同样安全性也是最差的，当系统宕机时，会丢失1秒的数据
innodb_flush_method            = O_DIRECT #避免双写缓冲的参数：
innodb_lock_wait_timeout       = 50 # 锁等待超时时间 单位秒
innodb_status_output           = ON #启用标准InnoDB监视器
query_cache_size               = 56MB # 查询缓存
 
# LOGGING #
log-error                      = /var/run/mysql/error_3306.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/run/mysql/slow-query_3306.log
long_query_time                = 1
log_timestamps                 = system

# 半同步复制参数
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 1000 # 单位秒

# 启用gtid复制：
gtid_mode=ON
enforce-gtid-consistency=true
 
[mysqld_safe]
socket                         = /var/run/mysql/mysql_3306.sock
pid-file                       = /var/run/mysql/mysqld_3306.pid
nice                           = 0
[mysqldump]
quick
quote-names
max-allowed-packet             = 16M
socket                         = /var/run/mysql/mysql_3306.sock

EOF

# 判断mysql软件包是否存在
if [ -f $SOFT_DIR/${MYSQL_VERSION}.tar.gz ]
then
cd $SOFT_DIR
tar -zxvf $SOFT_DIR/$MYSQL_VERSION.tar.gz 
rm -rf $MYSQLDB_HOME
mv -Tf $SOFT_DIR/$MYSQL_VERSION $MYSQLDB_HOME
else
echo "没有发现mysql二进制文件"
echo "下载mysql二进制文件放到和本脚本在同一目录中"
wget http://downloads.mysql.com/archives/get/file/${MYSQL_VERSION}.tar.gz -P $SOFT_DIR
    if(( $? == 0 ))
      then
       echo "MySQL DownLoad sucessfully!"
     else
      echo "MySQL DownLoad failed!"
      exit $ERROR_EXIT
    fi
fi

# 设置mysql目录权限
if [ -d $MYSQLDB_DATA_HOME ];then
    echo "目录已存在"
    else
    mkdir -p $MYSQLDB_DATA_HOME
    chown -R mysql:mysql $MYSQLDB_DATA_HOME
    echo "目录和权限创建成功！"
fi

# 设置tmp目录权限
if [ -d $TMP_DIR ];then
    echo "目录已存在"
    else
    mkdir -p $TMP_DIR
    chown -R mysql:mysql $TMP_DIR
    echo "tmp目录和权限创建成功！"
fi

# 目录授权
chown -R mysql:mysql $MYSQLDB_HOME
chown -R mysql:mysql $RUN_DIR
chown -R mysql:mysql $BINLOG_DIR

# 配置开机启动
function add_auto_start
{
   cp $MYSQLDB_HOME/support-files/mysql.server /etc/rc.d/init.d/mysqld
   chmod +x /etc/rc.d/init.d/mysqld
   chkconfig --add mysqld
}

# 初始化数据库
$MYSQLDB_HOME/bin/mysqld --initialize --user=mysql --basedir=$MYSQLDB_HOME/ --datadir=$MYSQLDB_DATA_HOME/ >> $TMP_DIR/mysqlpass.txt

# 设置环境变量
cat >> /etc/profile <<EOF
PATH=$MYSQLDB_HOME/bin:\$PATH
export PATH
EOF

source /etc/profile

add_auto_start

echo "启动MySQL实例..."
systemctl start mysqld

sleep 5
#########################################################################################
echo "mysql 5.7.25 安装完成，初始密码请查看$RUN_DIR/error_$PORT.log文件"：
INIT_PASSWD=`cat $RUN_DIR/error_$PORT.log  | grep password | awk ‘{print $NF}‘`

echo "初始化root密码...$INIT_PASSWD"
mysql --connect-expired-password -e "alter user ‘root‘@‘localhost‘ identified by ‘mysql‘;flush privileges;" -p$INIT_PASSWD

echo "配置服务器本地免密登录..."
mysql_config_editor set -S $RUN_DIR/mysql_$PORT.sock -u root -p
mysql_config_editor print --all

echo "放开root用户客户端IP限制..."
mysql<<EOF
grant all privileges on *.* to ‘root‘@‘%‘ identified by ‘mysql‘ with grant option;
flush privileges;
quit
EOF

echo "DONE SUCCESSFULLY !"