MySQL 在线一键安装

@土豆
已于 2023-05-10 23:40:19 修改
阅读量510
点赞数 1
文章标签： mysql linux 运维
于 2023-04-22 21:26:01 首次发布
本文链接：https://blog.csdn.net/weixin_44770684/article/details/130298987
版权
使用说明：
自定义数据库账号、密码和数据库安装目录。脚本最后输出的是主机第一张网卡的地址，实际可用连接地址要根据自己实际情况，尤其是云服务器。
安装包来源于华为云，可自行指定华为云支持下载的MySQL 5.7 和 MySQL 8.0，不支持从华为云下载的其他MySQL 5.7 和 MySQL 8.0 版本可提前从其他网站下载放置于安装目录，该脚本只测试MySQL 8.0 和 MySQL 5.7版本在线安装和使用提前下载的二进制安装包安装。
具体可在线下载安装MySQL 5.7 和 MySQL 8.0 版本，请登录华为云开源镜像站核对：
https://repo.huaweicloud.com/mysql/Downloads/https://repo.huaweicloud.com/mysql/Downloads/ 支持的二进制安装包格式如：
1、MySQL 5.7 版本的格式：mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
2、MySQL 8.0 版本的格式：mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz
#!/bin/bash
 
# create by tudou
# 2023-04-24
set -e

# 用户自定义参数，可选，不写则按默认值安装
  #==========================================================================
  #==========================================================================
  #                                                                          
  #  # 用户自定义参数使用说明，非必填；直接复制脚本即可完成安装MySQL                                         
  #  1. 默认情况 root@'localhost' 账号只能本地登录，且密码为空                
  #  2.不设置用户则使用账号 root@% 且生成随机密码                             
  #  3.设置了用户没设置密码，密码和用户一致                                   
  #  4.设置了密码，但是没有设置用户账号，则使用该密码作为 root@'%' 的密码     
  #  5.若没有指定安装目录，默认值为:   DBinstall_root=/opt/mysql
  #  6.未指定数据库端口，则使用默认值: 3306                                   
  #  7.自定义安装mysql版本，请从华为云获取mysql版本压缩包文件名
  #  8.可用下载版本号见华为云：https://repo.huaweicloud.com/mysql/Downloads
  #  9.一键安装脚本只支持 mysql-5.7 和mysql-8.0 两个大版本
  #  10.默认安装版本: mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
 
   # DBuser=tudou
   # DBpassword=tudou
   # DBport=3306
   # DBinstall_root=/opt/mysql
   
   # MySQL 8.0 
   #mysql_version='mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz'
   #mysql_version='mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz'
 
   # MySQL 5.7
   #mysql_version='mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz'
   #mysql_version='mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz'
 
  #==========================================================================
  #==========================================================================
  
 
#  # MySQL 安装目录结构简介
#                                                                                                                                 
#  ${DBinstall_root}                                                                                                              
#  ├── ${mysql_version%.tar*}                                                                                                           
#  └── mysqldata                                                                                                                  
#      ├── data                                                                                                                   
#      ├── etc                                                                                                                    
#      └── logs                                                                                                                   
#

# 确认是 Centos7 使用 root 用户执行
check(){
  # 确认是 Centos7 系统
  systemver=`cat /etc/redhat-release|sed -r 's/.* ([0-9]+)\..*/\1/'`
  echo $systemver
   
  if [[ $systemver != "7" ]];then
      echo "请在Centos7系统执行脚本,请检查系统版本，终止作业"
      exit 1
  fi
  
  # 确认执行用户是 root
  if [[ $(id -u --name) != "root" ]];then
      echo "请使用 root 用户登录，执行脚本;请检查执行用户，终止作业"
      exit 1
  fi
  
  # 已存在运行的 mysqld 则退出安装
  if [[ `ps -ef |grep mysqld |wc -l` -ge 2 ]];then
       echo "mysqld is running..."
       echo "将退出安装程序"
       exit 1
   fi
}
 
InitSystem(){
  ## stop && disable firewalld ##
  systemctl stop firewalld
  systemctl disable firewalld
  
#  if [ -z $DBport ];then
#     firewall-cmd --permanent --add-port=3306/tcp
#    else
#     firewall-cmd --permanent --add-port=$DBport/tcp
#  fi
  
  ## edit /etc/sysctl.conf ##
  echo "kernel.shmall = 2043878" >> /etc/sysctl.conf
  echo "kernel.shmmax = 8175512" >> /etc/sysctl.conf  
  echo "kernel.shmmni = 4096"  >> /etc/sysctl.conf
  echo "kernel.sem = 250 32000 100 128"  >> /etc/sysctl.conf
  echo "fs.file-max = 65536"  >> /etc/sysctl.conf
  echo "net.ipv4.ip_local_port_range = 1024 65000"  >> /etc/sysctl.conf
  echo "net.core.rmem_default = 262144"  >> /etc/sysctl.conf
  echo "net.core.rmem_max = 262144"  >> /etc/sysctl.conf
  echo "net.core.somaxconn = 2048"  >> /etc/sysctl.conf
  echo "net.core.wmem_default = 262144"  >> /etc/sysctl.conf
  echo "net.core.wmem_max = 262144"  >> /etc/sysctl.conf
  echo "net.ipv4.tcp_rmem = 262144"  >> /etc/sysctl.conf
  echo "net.ipv4.tcp_wmem = 262144"  >> /etc/sysctl.conf
  
  ## ulimit settings ##
  echo "* soft nofile 65536" >> /etc/security/limits.conf
  echo "* hard nofile 65536" >> /etc/security/limits.conf
  echo "* soft nproc 65536"  >> /etc/security/limits.conf
  echo "* hard nproc 65536"  >> /etc/security/limits.conf
  
  sysctl -p
  ## echo install resault ##
}
 
InstallPrepare(){
  # install yum repo
  mkdir -p /etc/yum.repos.d/backup
  mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/backup/
  curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.cloud.tencent.com/repo/centos7_base.repo
  yum clean all && yum makecache
  yum install -y vim wget tree net-tools numactl-libs libaio tar
  rm -rf /etc/localtime && ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
}
 
InstallDB(){  
  # 安装目录
  if [ -z ${DBinstall_root} ];then
      echo -e "\e[33m 安装目录：/opt/mysql \e[0m"
      DBinstall_root=/opt/mysql
    else
      echo -e "\e[33m 安装目录: ${DBinstall_root} \e[0m"
      DBinstall_root=${DBinstall_root}
  fi
 
  # 判断需要安装的mysql二进制压缩包文件,本地不存在则从华为云下载; https://repo.huaweicloud.com/mysql/Downloads/
  if [ -z ${mysql_version} ];then
      mysql_version='mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz'
      download_url='https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0'
  fi
  if [ ! -z ${mysql_version} ];then
      mysql_version=${mysql_version}
      if [[ ${mysql_version:0:9} == mysql-8.0 ]];then
          download_url='https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0'
      fi 
      if [[ ${mysql_version:0:9} == mysql-5.7 ]];then
          download_url='https://repo.huaweicloud.com/mysql/Downloads/MySQL-5.7'
      fi 
  fi
 
  # 设置 MySQL 相关目录
  echo -e "\e[33m"
  basedir=${DBinstall_root}/${mysql_version%.tar*}
  mysqldata=${DBinstall_root}/mysqldata
  echo -e "\e[0m"
 
  if [[ -d /usr/local/mysql || -L /usr/local/mysql ]];then
      rm -rf /usr/local/mysql  
  fi
  if [ -d $basedir ];then
      # mv  $basedir $basedir-$(date +%F)
      rm -rf  $basedir 
  fi
  if [ -d $mysqldata ];then
      # mv  $mysqldata $mysqldata-$(date +%Y%m%d%H%M%S)
      rm -rf   $mysqldata 
      mkdir -p $mysqldata/{etc,data,logs}
    else
      mkdir -p $mysqldata/{etc,data,logs}
  fi
 
  # 不存在压缩包,就从华为云下载,https://repo.huaweicloud.com/mysql/Downloads/
  if [ ! -e ${DBinstall_root}/${mysql_version} ];then
      wget ${download_url}/${mysql_version} -P ${DBinstall_root}
  fi
  tar -xvf ${DBinstall_root}/${mysql_version} -C ${DBinstall_root} 
  ln -s ${DBinstall_root}/${mysql_version%.tar*}  /usr/local/mysql
 
  # mysql 和 mysqld 必要的动态库
  echo "${DBinstall_root}/${mysql_version%.tar*}/lib/private" > /etc/ld.so.conf.d/mysql.conf
  ldconfig 
 
  # 创建 mysql 用户
  if id -u mysql >/dev/null 2>&1; then
      echo "user exists"
  else
      groupadd mysql
      useradd -r -g mysql mysql
  fi
  chown -R mysql:mysql $basedir $mysqldata /usr/local/mysql
  # chmod -R 755 $basedir
}
 
InitFile(){
  # my.cnf 配置文件
  if [ -z $DBport ];then
     DBport=3306
   else
     DBport=$DBport
  fi
 
  cat > ${mysqldata}/etc/my.cnf << EOF
     [client]
     port=${DBport}
     socket=${mysqldata}/data/mysql.sock
     # pipe
     # socket=0.0
     [mysql]
     default-character-set=UTF8MB4
     socket=${mysqldata}/data/mysql.sock
     [mysqld]
     user=mysql
     port=${DBport}
     skip_ssl
     default-time-zone='+8:00'
     log_timestamps='SYSTEM'
     transaction_isolation=read-committed
     default_authentication_plugin=mysql_native_password
     # binlog_expire_logs_seconds=604800  # MySQL8.0设置bilong过期时间，MySQL5.7不支持
     # expire-logs-days=7      # MySQL5.7设置binlog过期时间，MySQL8.0将丢弃该参数
     slow_query_log=1
     long_query_time=1
     open_files_limit=65535
     innodb_flush_log_at_trx_commit=1
     interactive_timeout = 28800
     wait_timeout = 28800
     innodb_io_capacity=2000
     read_buffer_size=16M
     basedir=${basedir}
     datadir=${mysqldata}/data
     pid-file=${mysqldata}/data/mysqld.pid
     socket = ${mysqldata}/data/mysql.sock
     log-bin=${mysqldata}/logs/log_bin
     log-error=${mysqldata}/logs/mysql.log
     slow-query-log-file=${mysqldata}/logs/mysql_slow_query.log
     lower_case_table_names=1
     #skip-grant-tables
     skip-name-resolve
     character-set-server=UTF8MB4
     default-storage-engine=INNODB
     explicit_defaults_for_timestamp = true
     # Server Id.
     server-id=30001
     max_connections = 3000
     local_infile=1
     table_open_cache=2000
     tmp_table_size=246M
     thread_cache_size=300
     #限定用于每个数据库线程的栈大小。默认设置足以满足大多数应用
     thread_stack = 192k
     key_buffer_size=512M
     read_rnd_buffer_size=32M
     innodb_data_home_dir = ${mysqldata}/data
     innodb_log_buffer_size=16M
     innodb_buffer_pool_size=3G
     innodb_log_file_size=512M
     innodb_thread_concurrency=128
     innodb_autoextend_increment=1000
     innodb_buffer_pool_instances=8
     innodb_concurrency_tickets=5000
     innodb_old_blocks_time=1000
     innodb_stats_on_metadata=0
     innodb_file_per_table=1
     innodb_checksum_algorithm=0
     innodb_open_files=65535
     back_log=80
     flush_time=0
     join_buffer_size=128M
     max_allowed_packet=1024M
     max_connect_errors=99999
     sort_buffer_size=32M
     table_definition_cache=1400
     binlog_row_event_max_size=8K
     sync_master_info=10000
     sync_relay_log=10000
     sync_relay_log_info=10000
     #批量插入数据缓存大小，可以有效提高插入效率，默认为8M
     bulk_insert_buffer_size = 64M
     log-bin-trust-function-creators=1
     sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
     net_write_timeout=600
     net_read_timeout=300
     #collation_server=utf8mb4_unicode_ci
     collation_server=utf8mb4_general_ci
     group_concat_max_len=10240
     sync_binlog=1
     replicate-wild-ignore-table    = mysql.%
     replicate-wild-ignore-table    = performance_schema.%
     gtid_mode = on
     enforce_gtid_consistency = 1
     #
     # include all files from the config directory
     # !includedir /etc/my.cnf.d
EOF
  
  if [[ -f /etc/my.cnf || -L /etc/my.cnf ]];then
      mv /etc/my.cnf /etc/my.cnf-$(date +%F)
  fi
  ln -s ${mysqldata}/etc/my.cnf /etc/my.cnf
  \cp -rf ${mysqldata}/etc/my.cnf ${basedir}/
  # my.cnf 配置文件
   
  # 写入全局环境变量
  echo "export  MYSQL_HOME=${basedir}" >> /etc/profile
  echo "export  PATH=\${MYSQL_HOME}/bin:\$PATH" >> /etc/profile
  source /etc/profile
}
 
InitDB(){
  ${basedir}/bin/mysqld --initialize-insecure --basedir=${basedir} --datadir=${mysqldata}/data  --lower-case-table-names=1 --user=mysql
  # \cp -rf ${basedir}/support-files/mysql.server /etc/init.d/mysqld
  # chmod 755 /etc/init.d/mysqld
  
  if [ -f  /usr/lib/systemd/system/mysqld.service ];then
     mv  /usr/lib/systemd/system/mysqld.service  /usr/lib/systemd/system/mysqld.service-$(date +%F)
  fi
  
  cat > /usr/lib/systemd/system/mysqld.service <<EOF
    [Unit]
    Description=MySQL Community Server
    After=network.target
    After=syslog.target
    [Service]
    User=mysql
    Group=mysql
    ExecStart=${basedir}/bin/mysqld_safe --defaults-file=${mysqldata}/etc/my.cnf --basedir=${basedir}
    PrivateTmp=false
    LimitNOFILE=65536
    LimitNPROC=65536
    [Install]
    WantedBy=multi-DBuser.target
    Alias=mysql.service
EOF
    systemctl daemon-reload
    systemctl enable mysqld
    systemctl restart mysqld
}
 
AddUser(){
  sleep 15s
  # echo "用户：root@localhost 密码为空;localhost免密登录" > ${DBinstall_root}/install.log
   echo "连接信息：" 
 
  if [ -z $DBport ];then
     DBport=3306
     echo "DBport=3306" 
   else
     DBport=$DBport
     echo "DBport=$DBport" 
  fi
 
    # 用户设置了 DBuser  
  if [ ! -z  $DBuser ];then
       DBuser=${DBuser}
       mysql -P${DBport} -e "create user "$DBuser"@'%';"
       mysql -P${DBport} -e "grant  all on *.* to "$DBuser"@'%';"
       echo "DBuser：$DBuser" 
       if [ ! -z  $DBpassword ];then
            #mysql -P${DBport} -e "ALTER USER "$DBuser"@'%' IDENTIFIED WITH mysql_native_DBpassword BY '$DBpassword';"
            mysql -P${DBport} -e "ALTER USER "$DBuser"@'%' IDENTIFIED BY '$DBpassword';"
            echo "DBpassword：$DBpassword"
          else
            mysql -P${DBport} -e "ALTER USER "$DBuser"@'%' IDENTIFIED BY '$DBuser';"
            echo "DBpassword：$DBuser"
       fi
    else
       # 用户没有设置 DBuser
       DBuser=root
       mysql -P${DBport} -e "create user "$DBuser"@'%';"
       mysql -P${DBport} -e "grant  all on *.* to "$DBuser"@'%';"
       echo "DBuser: root" 
       if [ ! -z  $DBpassword ];then
            mysql -P${DBport} -e "ALTER USER "$DBuser"@'%' IDENTIFIED BY '$DBpassword';"
            echo "DBpassword: $DBpassword" 
          else
            DBpassword=`echo $RANDOM |base64` # 随机生成密码
            mysql -P${DBport} -e "ALTER USER "$DBuser"@'%' IDENTIFIED BY '$DBpassword';"
            echo "DBpassword: $DBpassword" 
       fi
  fi
}

Mysqlinfo(){
  # 默认获取服务器第一个网卡的IP
  DBhost=`ifconfig |grep inet|grep -oP "\d{1,3}\.\d{1,3}\.\d{1,3}.\d{1,3}"| grep -vE "127.0.0.1|^255"|head -n 1`

  echo "MySQL info:" |tee  ${DBinstall_root}/install.log
  echo "DBhost: $DBhost" | tee -a ${DBinstall_root}/install.log
  echo "DBport: $DBport" | tee -a ${DBinstall_root}/install.log
  echo "DBuser: $DBuser" | tee -a ${DBinstall_root}/install.log
  echo "DBpassword: $DBpassword" | tee -a ${DBinstall_root}/install.log
  echo "DBinstall_root: ${DBinstall_root}" | tee -a ${DBinstall_root}/install.log
  echo "basedir: $basedir" | tee -a ${DBinstall_root}/install.log
  echo "mysqldata: $mysqldata" | tee -a ${DBinstall_root}/install.log
  echo "mysql_version: ${mysql_version}" | tee -a ${DBinstall_root}/install.log

  echo -e "\e[33m"
  cat ${DBinstall_root}/install.log
  echo -e "\e[0m"
}
 
 
# 执行脚本
# check
InitSystem
InstallPrepare
InstallDB
InitFile
InitDB
AddUser
Mysqlinfo