影者东升 发表于 2021-7-3 21:45:21

MySQL5.7多实例自动化部署脚本

  一、安装说明
  ------------------------------------------------------
  mysql5.7.10_onekey_install.sh自动化部署脚本支持mysql5.7.10初始化安装,多实例创建,且使用经过优化后的my.cnf配置文件和mysql.server启动脚本,该SHELL脚本在CentOS6.5_x86_64操作系统测试通过。部署示意图如下:

  1、安装方式

  需要准备的文件如下,放到同一个目录下,然后执行shell脚本
  执行方式:
./mysql5.7.10_onekey_install.sh 3307
端口自定义,要求整数,且不和服务器端口冲突# tree mysql5.7.10_onekey_install
mysql5.7.10_onekey_install
├── my.cnf
├── mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
├── mysql5.7.10_onekey_install.sh
└── mysql.server  

1、如果是首次安装mysql,则会构建所需的所有预安装环境并创建第一个实例;
2、如果不是首次安装mysql,则会在原有基础上创建多实例;
  

  2、安装软件版本

- 操作系统:CentOS6.5_x86_64
- MySQL版本:mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz (最新版本5.7.10)
  

  3、安装目录规划

上传软件包目录:/home/zkyw/install
mysql程序目录:/opt/application/mysql
mysql实例数据文件目录:/data/$Port
  

  4、程序启停操作

启动mysql实例:/data/$Port/mysql.server start
停止mysql实例: /data/$Port/mysql.server stop
查看mysql实例状态:/data/$Port/mysql.server status
  

  5、脚本执行过程

第一步:对传入的参数(端口号)做判断,要求为整数且不能与服务器已有端口号冲突;
第二步:如果服务器还没有安装过mysql,则进行初始化安装,自动配置所需安装环境;
第三步:如果服务器上已经安装了mysql,则会创建多实例;
第四步:初始化安装过程:删除msyql相关的rpm包→安装libaio包→创建安装所需的用户目录等→安装mysql→配置mysql
第五步:创建多实例过程:创建目录并授权→安装mysql→配置mysql
二、自动化部署脚本
--------------------------------------------------------
#!/bin/sh
#auto install mysql5.7.10

#setting default directory
Port=$1
SoftDir="/home/zkyw/install"
tarbag="mysql-5.7.10-linux-glibc2.5-x86_64"
BaseDir="/opt/application/mysql"
DataDir="/data/$Port"

#remove before being installed mysql
function rmMysql() {
      yum -y erase mysql >/dev/null 2>&1
      yum -y erase mysql-server >/dev/null 2>&1
      ret=`rpm -aq | grep -i "mysql-5" | wc -l`
      num=`rpm -aq | grep -i "mysql-server" | wc -l`
      test $ret -eq 1 && echo "mysql uninstall failed" && exit 1
      test $num -eq 1 &&echo "mysql-server uninstall failed" && exit 2
}
#libaio package is needed for mysql5.7.10
function chkEnv() {
      yum -y install libaio >/dev/null 2>&1
      res=`rpm -aq|grep libaio | wc -l`
      test $res -ne 1 && echo "libaio package install failed..." && exit 3
}

#create mysql user and group, authorization, extract
function preInstall() {
      /usr/sbin/groupadd mysql
      /usr/sbin/useradd -r -g mysql -s /bin/false mysql
      mkdir -p $BaseDir
      mkdir -p $DataDir/data
      chown mysql.mysql $DataDir
      if test -f $SoftDir/$tarbag.tar.gz
          then
                cd $SoftDir && tar -zxf $tarbag.tar.gz
                cd $SoftDir/$tarbag && cp -r * $BaseDir
          else
                echo "$tarbag.tar.gz is not found..."
                exit 10
      fi
}

function multPreInstall() {
    mkdir -p $DataDir/data
      chown mysql.mysql $DataDir
}

function install_mysql() {
      #initialize mysql database
      $BaseDir/bin/mysqld \
      --initialize \
      --user=mysql \
      --basedir=$BaseDir \
      --datadir=$DataDir/data \
      --character-set-server=utf8 \
      --collation-server=utf8_general_ci \
      --initialize-insecure >/dev/null 2>&1
}
#get my.cnf and start/stop script, attention alter parameters by your envionment
function conf_mysql() {
      cp $SoftDir/my.cnf $DataDir
      cp $SoftDir/mysql.server $DataDir
/usr/bin/vim $DataDir/my.cnf<<EOF >/dev/null 2>&1
:%s/3306/$Port/g
:wq
EOF
      sed -i "s/port=3306/port=$Port/" $DataDir/mysql.server
      sed -i "s%CmdPath=\"\"%CmdPath=\"${BaseDir}\/bin\"%" $DataDir/mysql.server
      sed -i "s%mysql_sock=\"\"%mysql_sock=\"${DataDir}\/mysql.sock\"%" $DataDir/mysql.server
      chmod 600 $DataDir/my.cnf
      chmod 700 $DataDir/mysql.server
      $DataDir/mysql.server start >/dev/null 2>&1
      sleep 3
#      ren=`netstat -natp|grep mysqld | grep "$1" | wc -l`
   
      if test -e $DataDir/mysql.sock;then
      echo "$DataDir/mysql.sock"
                echo -e "\033[33;1mmysql install success...\033[0m"
      pro=`grep $BaseDir /root/.bash_profile | wc -l`
      if test "$pro" -ne 1;then
            sed -i "s%PATH=\$PATH\:\$HOME\/bin%PATH=\$PATH\:\$HOME\/bin\:$BaseDir\/bin%" /root/.bash_profile
            source /root/.bash_profile
                fi
      else
                echo -e "\033[31;1mmysql install failed...\033[0m"
      fi
}

if [[ "$1" =~ ^+$ ]]; then
   inPort=`netstat -natp | grep "mysqld" | grep "LISTEN" | awk '{print $4}' | cut -b 4-`
   if test ! -z "$inPort";then
      for myPort in $inPort
          do
            if test "$myPort" -eq "$1";then
                echo -e "\033[33;1m$1 instance has already existed...\033[0m"
                exit 1
            fi
      done
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1mStarting create new instance $1\033[0m"
       multPreInstall
       install_mysql
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1mconfiguration and starting $1 instance...\033[0m"
       conf_mysql
   else
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1mremove before being installed mysql...\033[0m"
       rmMysql
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1minstall libaio package...\033[0m"
       chkEnv
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1mget ready install envionment...\033[0m"
       preInstall
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1mStarting install mysql ver5.7.10...\033[0m"
       install_mysql
       echo -e "\033[32;1m===========================================\033[0m"
       echo -e "\033[32;1mconfiguration mysql and starting mysql...\033[0m"
       conf_mysql
   fi
else
   echo "Usage: $0 Port (Port is inteager)"
fi  三、MySQL优化后的配置文件(my.cnf)

port = 3306
socket = /data/3306/mysql.sock
character-set-server = utf8


no-auto-rehash


user    = mysql
port    = 3306
character-set-server = utf8
socket= /data/3306/mysql.sock
basedir = /opt/application/mysql
datadir = /data/3306/data
explicit_defaults_for_timestamp = true
skip-ssl
secure-file-priv = NULL
lower_case_table_names = 1
back_log = 300
max_connections = 3000
max_connect_errors = 100
table_open_cache = 4096
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
max_heap_table_size = 128M

###*** slow query parameters
long_query_time = 6
slow_query_log
slow_query_log_file = /data/3306/slow.log

###*** binlog parameters
log-bin = /data/3306/mysql-bin
binlog_cache_size = 4M
max_binlog_cache_size = 16M
max_binlog_size = 32M
binlog_format = MIXED
expire_logs_days = 7

###*** relay-log parameters
#relay-log = /data/3307/relay-bin
#relay-log-info-file = /data/3307/relay-log.info
#master-info-repository = table
#relay-log-info-repository = table
#relay-log-recovery = 1

#*** MyISAM parameters
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 1M

#skip-name-resolve

###*** master-slave replication parameters
server-id = 105
slave-skip-errors = all

#*** Innodb storage engine parameters
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on


quick
max_allowed_packet = 32M


key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M


open-files-limit = 8192
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid四、MySQL启动脚本(mysql.server)  #!/bin/sh
  # This is an interactive program, we needthe current locale
  [ -f /etc/profile.d/lang.sh ] && . /etc/profile.d/lang.sh
  # We can't Japanese on normal console atboot time, so force.
  if [ "$LANG" = "ja" -o "$LANG" = "ja_JP.eucJP" ]; then
     if [ "$TERM" = "linux" ] ; then
         LANG=C
     fi
  fi
     
  # Source function library.
  . /etc/init.d/functions
  #init
  port=3306
  mysql_user="root"
  mysql_pwd=""
  CmdPath=""
  mysql_sock=""
  #startup function
  function_start_mysql()
  {
      if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --ledir=${CmdPath} 2>&1 > /dev/null &
        sleep 2
      else
        printf "MySQL is running...\n"
        exit
      fi
  }
  #stop function
  function_stop_mysql()
  {
      if [ ! -e "$mysql_sock" ];then
         printf "MySQL is stopped...\n"
         exit
      else
         printf "Stoping MySQL...\n"
         ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S $mysql_sock shutdown
         sleep 2
     fi
  }
  #restart function
  function_restart_mysql()
  {
      printf "Restarting MySQL...\n"
      function_stop_mysql
      sleep 2
      function_start_mysql
  }
  case $1 in
  start)
      function_start_mysql
  ;;
  stop)
      function_stop_mysql
  ;;
  restart)
      function_restart_mysql
  ;;
  status)
      status mysqld
  ;;
  *)
      printf "Usage: /data/${port}/mysql {start|stop|restart|status}\n"
  esac
  


  
页: [1]
查看完整版本: MySQL5.7多实例自动化部署脚本