一键执行MySQL安装

版本信息：

OS: CentOS7.* /Redhal 7* 

DB: MySQL 8.* / MySQL 5.7.* 

简介：shell脚本执行，通过传入对应参数，执行mysql 数据库安装，包括系统依赖包，系统参数，防火墙，用户创建，数据库部分参数自动调整（根据cpu,内存），初始化localhost 数据库root 权限，新建root@% 用户，授权。 以及启动脚本自动修改等。

 脚本可以反复执行。

脚本分为两部分：

1，pre_01.sh  数据的传参。

2，body_01.sh 真正执行主体脚本

脚本一，参数传入，当然这参数可以自行调整新增。

#!/bin/bash 
sh list1-hopped-m8c7-install.sh \
--dbdir=/data/mysql \
--dbport=3308 \
--basedir=/usr/local/mysql \
--dbsuperpwd='xxxxxxx' \

备注： dbdir 为 存储数据目录

            dbport 为端口号指定

            basedir 为mysql 软件解压目录

            dbsuperpwd 为mysql数据库root 账号密码

脚本二，执行主体。

#! /bin/bash
# editor Dominic.Lee 
# editime 20230612
# golden

#set -x
# 接受传输数据提示
# Get the key value of input argumenttime format like '--args=value'.
function get_key_value()
{
    echo "$1" | sed 's/^--[a-zA-Z_-]*=//' 
}

# Usage will be helpful when you need to input the valid argumenttime.

function usage()
{
cat <<EOF
Usage: $0 [configure-options]
    -?, --help                Show this help message.
    --dbdir=<>                Set 数据目录:dbdir/{datafile,logfile}
    --basedir=<>              Set 软件目录:/usr/local/mysql/version*
    --dbport=<>               Set 数据库port:默认3306
    --dbsuperpwd=<>           Set 数据库super密码
EOF
}

# Parse the input argumenttime and get the value of the input argument.
if [ $# -eq 0 ];then
      usage
#      print_default
      exit 0;
fi

function parse_options()
{
  while test $# -gt 0
  do
    case "$1" in
    --dbdir=*)
      db_directory=`get_key_value "$1"`;;
    --basedir=*)
      base_dir=`get_key_value "$1"`;;
    --dbport=*)
      db_port=`get_key_value "$1"`;;
    --dbsuperpwd=*)
      dbroot_pwd=`get_key_value "$1"`;;
    -? | --help )
      usage
#      print_default
      exit 0;;
    *)
      echo "Unknown option '$1'"
      exit 1;;
    esac
    shift
  done
}

## 
Tdate=`date +%Y%m%d`
Ttime=`date '+%F %T'`
current_dir=`pwd`
parse_options "$@"

## 打印出对应传入参数 ##
echo  "             "
echo -e "...............${Ttime}. Begin................. "
echo  "             "
echo -e "数据目录->${db_directory}...... " 
echo -e "软件目录->${base_dir}....... "
echo -e "DB端口号->${db_port}...... "
echo -e "super密码->${dbroot_pwd}...... "

data_dir=${db_directory}/${db_port}/datafile
log_dir=${db_directory}/${db_port}/logfile

echo -e "数据目录->$data_dir......"
echo -e "数据日志目录->$log_dir......"

##  一,防止重复执行,端口占用异常  ##
echo ".1-清理环境......" 
# 检查端口是否被占用并且是MySQL运行的
# 指定要检查的端口号
port=${db_port}

# 检查端口是否被占用
is_port_in_use() {
  netstat -ln | grep ":$1 " >/dev/null
  return $?
}

# 检查端口是否是MySQL运行的
is_mysql_running_on_port() {
  pid=$(lsof -i :$1 | awk 'NR==2{print $2}')
  process_name=$(ps -p $pid -o comm= 2>/dev/null)
  if [ "$process_name" = "mysqld" ]; then
    return 0
  else
    return 1
  fi
}

# 杀死MySQL进程
kill_mysql_process() {
  pid=$(lsof -i :$1 | awk 'NR==2{print $2}')
  kill $pid
}

# 检查端口是否被占用且是MySQL运行的
if is_port_in_use $port && is_mysql_running_on_port $port; then
  echo "Port $port is in use by MySQL."
  echo "Killing the MySQL process..."
  kill_mysql_process $port
  echo "MySQL process killed."
else
  echo "Port $port is not in use by MySQL."
fi
sleep 30

##  二,定义登录提醒/etc/motd ##
echo -e ".2-/etc/motd提示......"
cat >> /etc/motd  <<EOF 
 ###################################################
#              ---------------                      #
#       datadir -> ${data_dir} 数据文件目录         #
#       logdir -> ${log_dir} 数据日志目录           #
#       baseidir  -> ${base_dir}数据目录            #
#       MySQL Port ${db_port} utf8mb4               # 
#       /etc/init.d/mysqld  start && stop           # 
#              --------------                       #
 ###################################################
EOF

## 三,调整limits.conf系统参数 ##
echo -e ".3-limit.conf参数设置......"
cat >> /etc/security/limits.conf << EOF
mysql    soft    nproc    16384
mysql    hard    nproc    16384
mysql    soft    nofile    65536
mysql    hard    nofile    65536
mysql    soft    stack    1024000
mysql    hard    stack    1024000

EOF

## 四,调整sysctl.conf 文件参数 ##
echo -e ".4-sysctl.conf参数设置......"

cat >> /etc/sysctl.conf << EOF
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

EOF

sysctl -p & > /etc/null

## 五,关闭防火墙及selinux ##
echo -e ".5-关闭防火墙......"
systemctl stop firewalld
systemctl disable firewalld
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

## 六,安装依赖包及处理 yum install ##
# 函数：安装依赖包
echo -e ".6-依赖包检测...... "
function install_dependencies() 
{
  local dependencies=(
    "wget"
    "numactl"
    "libaio"
    "libstdc++"
    "libgcc"
    "numactl-libs"
    "gcc-"
  )

  for dependency in "${dependencies[@]}"; do
    if ! rpm -qa | grep -qw "$dependency"; then
      echo "安装依赖包: $dependency"
      sudo yum remove libnuma.so.1
      sudo yum install -y "$dependency"
      if [ $? -ne 0 ]; then
        echo "安装依赖包 $dependency 失败"
        exit 1
      fi
    else
      echo "依赖包 $dependency 已安装"
    fi
  done
}

## 七,创建mysql用户及用户组 ##
echo -e ".7-用户组检测......"
user=mysql
group=mysql

 #create group if not exists
 egrep "^$group" /etc/group >& /dev/null
 if [ $? -ne 0 ]
  then
    groupadd $group
 fi

 #create user if not exists
 egrep "^$user" /etc/passwd >& /dev/null
 if [ $? -ne 0 ]
   then
     useradd -r -g $group -s /sbin/nologin $user
 fi

## 八,创建数据文件目录 ##
echo -e ".8-创建对应文件目录及清理......"
if [ ! -d ${base_dir} ];then
   mkdir -p ${base_dir}
else 
    rm ${base_dir}/* -rf
fi
if [ ! -d "${data_dir}" ];then
  mkdir -p ${data_dir}
  mkdir -p ${log_dir}
 else 
  rm  ${data_dir}/* -rf
  rm  ${log_dir}/* -rf
fi

## 九,mysql8数据库初始化 ##
echo -e ".9-数据库初始化I......"
tar_name=$(ls .|grep mysql-8.*.tar.*)
tar_dir=${tar_name%.tar.xz*}
  tar -xvf "${current_dir}"/"${tar_name}"
  mv "${current_dir}"/"${tar_dir}" ${base_dir}/

# 判断文件目录是否为空
if [  "$(ls -A ${data_dir})" ];then
    echo -e "初始化时,$data_dir目录必须为空"
else 
   rm  ${data_dir}/* -rf
fi

# 初始化数据库
echo -e ".9-初始化数据库II......"
touch ${log_dir}/error.log
chown -R mysql:mysql ${base_dir}/
chown -R mysql:mysql ${log_dir}/
chown -R mysql:mysql ${data_dir}/
chown -R mysql:mysql /etc/my.cnf
chmod -R 755 ${data_dir}/
chmod -R 755 ${log_dir}

cd ${base_dir}/${tar_dir} && bin/mysqld --initialize-insecure --user=mysql --basedir=${base_dir}/${tar_dir} --datadir=${data_dir}
if [[ $? -eq 0 ]];then
      echo -e "MySQL初始化完毕" 
      echo -e "\n"
else
      echo -e "MySQL初始化失败请查看error.log日志"  
      exit
fi

## 十,配置基本参数my.cnf ## 
# 这里的my.cnf 直接放在系统/etc/init.d/下.
echo -e ".10-数据库参数设置......"
echo  "             "

cat > ${base_dir}/my.cnf << EOF
[client]
port    = ${db_port}
socket  = /tmp/mysql.sock
default_character_set = utf8mb4

[mysql]
prompt="\u@mysqldb \R:\m:\s [\d]> "
no-auto-rehash
default_character_set = utf8mb4

[mysqld]
bind-address = 0.0.0.0
port    = ${db_port}
datadir = ${data_dir}
basedir = ${base_dir}/${tar_dir}
socket  = /tmp/mysql.sock
log-error = ${log_dir}/error.log
pid-file = ${data_dir}/mysqld.pid
default_authentication_plugin = mysql_native_password
default_time_zone = "+8:00"
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 0
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = off
open_files_limit = 65535
max_connections = 600
max_connect_errors = 999999999
back_log = 170
thread_cache_size = 30
max_allowed_packet = 512M
sort_buffer_size = 8M
join_buffer_size = 8M
interactive_timeout = 1800
wait_timeout = 1800
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = ${log_dir}/slow.log
long_query_time = 2
log_slow_admin_statements = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000
log_throttle_queries_not_using_indexes = 60
relay_log_info_repository = TABLE
master_info_repository = TABLE
server-id = ${db_port}01
log-bin =  ${log_dir}/mybinlog
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 864000
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1
binlog_cache_size = 2M
max_binlog_cache_size = 2G
max_binlog_size = 2G
gtid_mode = on
enforce_gtid_consistency = 1
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 4G
innodb_io_capacity = 5000
innodb_io_capacity_max = 15000
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 20
innodb_print_all_deadlocks = 1
innodb_rollback_on_timeout = 1
innodb_sort_buffer_size = 64M
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 1

[mysqldump]
quick
max_allowed_packet = 512M

EOF

# 十一, 修改参数  #
echo -e ".11-基于OS内存修改数据库参数......"
if [ -f /etc/my.cnf ];then 
 mv /etc/my.cnf /etc/my.cnf-$(date +%F-%H:%H:%S)
 cp ${base_dir}/my.cnf /etc/
  system_mem_size=$(grep "^MemTotal" /proc/meminfo |awk '{print $2}')
  innodb_buffer_pool_size="$(echo "${system_mem_size}"/1024/10*6|bc)M"
  sed -i "s#^socket.*#socket = ${data_dir}/mysql.sock#g" /etc/my.cnf
  sed -i "s#^slow_query_log_file.*#slow_query_log_file = ${log_dir}/slow.log#g" /etc/my.cnf
  sed -i "s#^log-error.*#log-error = ${log_dir}/error.log#g" /etc/my.cnf
  sed -i "s#^log-bin.*#log-bin = ${log_dir}/mybinlog#g" /etc/my.cnf
  sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = ${innodb_buffer_pool_size}M#g" /etc/my.cnf
  sed -i "s#^innodb_undo_directory.*#innodb_undo_directory = ${data_dir}/undolog#g" /etc/my.cnf
# sed -i "s#^basedir.*#basedir = ${base_dir}/mysql#g" /etc/my.cnf
  sed -i "s#^datadir.*#datadir = ${data_dir}#g" /etc/my.cnf
  echo -e "\n"
fi

# 十二, mysqld 程序 设置   #
echo  "             "
echo ".12-设置启动脚本......"
cd ${base_dir}/${tar_dir}/support-files && cp mysql.server /etc/init.d/mysqld
sed -i "s#^basedir=.*#basedir=${base_dir}/${tar_dir}/#g" /etc/init.d/mysqld
sed -i "s#^datadir=.*#datadir=${data_dir}#g"  /etc/init.d/mysqld
#touch ${log_dir}/error.log
chown -R mysql:mysql ${base_dir}/
chown -R mysql:mysql ${log_dir}/
chown -R mysql:mysql ${data_dir}/
chown -R mysql:mysql /etc/my.cnf
chmod -R 755 ${data_dir}/
chmod -R 755 ${log_dir}
chown -R mysql:mysql /etc/init.d/mysqld

/etc/init.d/mysqld start
sleep 20

## 十三,登录mysql 修改默认root@localhost密码,及创建一个裁剪权限的root@'%'用户
echo -e ".13-设置super账号权限...... "
echo  "             "
echo  "             "
login_mysql="${base_dir}/${tar_dir}/bin/mysql -uroot  -P${db_port} -S ${data_dir}/mysql.sock "
${login_mysql} << EOF
flush privileges;
alter user root@'localhost' identified by '${dbroot_pwd}';
create user root@'%' identified by '${dbroot_pwd}';
flush privileges;
grant select,insert,update,delete,create on *.* to root@'%' with grant option;
grant drop,reload,file,references, index, alter on *.* to root@'%' with grant option;
grant show databases, create temporary tables on *.* to root@'%' with grant option;
grant lock tables, execute, create view on *.* to root@'%' with grant option;
grant show view, create routine, alter routine on *.* to root@'%' with grant option;
grant create user,create tablespace, create role on *.* to root@'%' with grant option;
grant drop role,event,trigger on *.* to root@'%' with grant option;
flush privileges;
EOF

echo -e " ............... ${Ttime} The End .................  "
echo  "             "
echo -e " ..... 数据库MySQL8.0.*安装完成 ....."
exit;