CentoS 7.8.2003 安装篇五：Mysql

一、安装前准备

     1、检查是否已经安装过mysql，执行命令

rpm -qa | grep mysql

        如果已存在，则执行删除命令 后边为Mysql目录

rpm -e --nodeps mysql-xxxx

2.查询所有Mysql对应的文件夹

whereis mysqlm

find / -name mysql

    删除相关目录或文件

 rm -rf /usr/bin/mysql /usr/include/mysql /data/mysql /data/mysql/mysql 

   验证是否删除完毕

 whereis mysqlm

find / -name mysql

3.检查服务器上是否已经开放mysql对应的端口

查看已开放端口命令 firewall-cmd --list-ports

以上图片表示，本台服务器防火墙端口仅开放 3389

如：我们需要开放mysql端口为3406，需要如下操作

开放3406端口： firewall-cmd --zone=public --add-port=3406/tcp --permanent

如上图，提示SUCCESS表示端口开启成功

重启防火墙，使开放3406端口生效

重启防火墙 systemctl reload firewalld

4.创建mysql安装目录及数据存放目录

二、下载或者上传mysql安装包

       mysql下载地址：MySQL :: Download MySQL Community Serverhttps://dev.mysql.com/downloads/mysql/

   1.mysql约定目录

     解压： tar -zxvf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz 

     安装目录：/home/jysoft/mysql/mysql-8.0.29

     数据目录：/home/jysoft/mysql/mysql-3406/data

    日志目录：/home/jysoft/mysql/mysql-3406/data

2.检查mysql用户组和用户是否存在，如果没有，则创建

cat /etc/group | grep mysql

cat /etc/passwd |grep mysql

3.创建mysql用户

 #groupadd -g 500 mysql
 #useradd -r -g mysql -u 500 -s /sbin/nologin mysql

   出现下图表示安装成功

4.安装mysql依赖包，执行一下命令

yum install -y libaio 

3.改变mysql目录用户

chown  -R  mysql:mysql  /home/jysoft/mysql

chmod -R 755  /home/jysoft/mysql

4.上传配置文件至/home/jysoft/mysql/mysql-3406目录下

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client] 
no-beep
port=3406

[mysql] 
default-character-set=utf8mb4

[mysqld]
port=3406 
character-set-server=utf8mb4

default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
explicit_defaults_for_timestamp=true
 
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/home/jysoft/mysql/mysql-3406/data
socket=/home/jysoft/mysql/mysql-3406/data/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
lower_case_table_names=1

server-id = 1
log-output=NONE
general-log=0
slow-query-log=0
long_query_time=10
log-bin =/home/jysoft/mysql/mysql-3406/data/mysql-bin
binlog_format=row
#expire_logs_days=20

max_connections=2000
#query_cache_type=0

table_open_cache=34000
tmp_table_size=256M
max_heap_table_size=256M
#thread_cache_size=64
thread_cache_size=512
performance_schema_max_table_instances = 10000
myisam_max_sort_file_size=100G
#myisam_sort_buffer_size=502M
myisam_sort_buffer_size=256M
#key_buffer_size=2M
key_buffer_size=1024M

#read_buffer_size=4M
read_buffer_size=16M
read_rnd_buffer_size=512K

innodb_flush_log_at_trx_commit=1
#innodb_log_buffer_size=128M
innodb_log_buffer_size=8M
innodb_buffer_pool_size=4G
#innodb_log_file_size=256M
innodb_log_file_size=1024M
innodb_thread_concurrency=20
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65535
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_lock_wait_timeout = 50

back_log=103
flush_time=0
join_buffer_size=64M
# max_allowed_packet=4M
max_allowed_packet=500M
max_connect_errors=100
open_files_limit=65535
sort_buffer_size=5M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
wait_timeout=620
interactive_timeout=620
# zengjia
net_read_timeout=200
net_write_timeout=1000
log_timestamps=SYSTEM
[mysqld_dump]
quick
max_allowed_packet=32M

[mysqld_safe]
log-error=/home/jysoft/mysql/mysql-3406/data/mysqld-err.log
pid-file=/home/jysoft/mysql/mysql-3406/data/mysqld.pid

   三、初始化数据库

    1.初始化

cd /home/jysoft/mysql
/home/jysoft/mysql/mysql-8.0.29/bin/mysqld --defaults-file=/home/jysoft/mysql/mysql-3406/my.cnf  --initialize --user=mysql --basedir=/home/jysoft/mysql/mysql-8.0.29 --datadir=/home/jysoft/mysql/mysql-3406/data 

bin/mysql_ssl_rsa_setup  --datadir=/data/mysql/mysql-3406/data
bin/mysqld_safe --defaults-file=/data/mysql/mysql-3406/my.cnf --user=mysql &

以上初始化完毕之后，请务必记录初始化之后root账号密码

2.再次更改目录权限

chown –R   mysql:mysql  /home/jysoft/mysql/mysql-3406/data

 3.添加环境变量

vi /etc/profile 
export PATH=/home/jysoft/mysql/mysql-8.0.29/bin:$PATH

4.启动数据库，并修改账号密码

/home/jysoft/mysql/mysql-8.0.29/bin/mysqld_safe --defaults-file=/home/jysoft/mysql/mysql-3406/my.cnf --user=mysql &

5.连接数据库

/home/jysoft/mysql/mysql-8.0.29/bin/mysql -uroot  --socket=/home/jysoft/mysql/mysql-8.0.29/data/mysql.sock --port=3406 -p

 输入以上命令之后，敲回车即可 

6.修改密码

 alter user 'root'@'localhost' identified by 'xxxxxxx'; 

注：修改你的msyql数据库root账号密码，此处密码为yourpassword，修改为你自己想要的密码。 

7.设置root账户的host地址（修改了才可以远程连接）

mysql> create user 'root'@'%' identified by  'password';
Query OK, 0 rows affected (0.03 sec)
 
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

上传mysql启动脚本mysql-3406到/etc/init.d目录

启动脚本如下：

#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
# This file is public domain and comes with NO WARRANTY of any kind

# MySQL daemon start/stop script.

# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.

# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.

# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start:  2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
 
# If you install MySQL on some other places than /ddserver/mysql/mysql-5.7.11, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
#   [mysqld]
#   basedir=<path-to-mysql-installation-directory>
# - Add the above to any other configuration file (for example ~/.my.ini)
#   and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
#   below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.

# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

basedir=/home/jysoft/mysql/mysql-5.7.25
datadir=/home/jysoft/mysql/mysql-3406/data
conf=/home/jysoft/mysql/mysql-3406/my.cnf
mysql_user=root
mysql_pwd=123456



# Default value, in seconds, afterwhich the script should timeout waiting
# for server start. 
# Value here is overriden by value in my.cnf. 
# 0 means don't wait at all
# Negative numbers mean to wait indefinitely
service_startup_timeout=900

# Lock directory for RedHat / SuSE.
lockdir='/var/lock/subsys'
lock_file_path="$lockdir/mysql"



# The following variables are only set for letting mysql.server find things.

# Set some defaults
mysqld_pid_file_path=$datadir/mysqld.pid
if test -z "$basedir"
then
  basedir=/home/jysoft/mysql/mysql-5.7.25
  bindir=/home/jysoft/mysql/mysql-5.7.25/bin
  if test -z "$datadir"
  then
    datadir=/home/jysoft/mysql/mysql-3406/data
  fi
  sbindir=/home/jysoft/mysql/mysql-5.7.25/bin
  libexecdir=/home/jysoft/mysql/mysql-5.7.25/bin
else
  bindir="$basedir/bin"
  if test -z "$datadir"
  then
    datadir="$basedir/data"
  fi
  sbindir="$basedir/sbin"
  libexecdir="$basedir/libexec"
fi

# datadir_set is used to determine if datadir was set (and so should be
# *not* set inside of the --basedir= handler.)
datadir_set=

#
# Use LSB init script functions for printing messages, if possible
#
lsb_functions="/lib/lsb/init-functions"
if test -f $lsb_functions ; then
  . $lsb_functions
else
  log_success_msg()
  {
    echo " SUCCESS! $@"
  }
  log_failure_msg()
  {
    echo " ERROR! $@"
  }
fi

PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATH

mode=$1    # start or stop

[ $# -ge 1 ] && shift


other_args="$*"   # uncommon, but needed when called from an RPM upgrade action
           # Expected: "--skip-networking --skip-grant-tables"
           # They are not checked here, intentionally, as it is the resposibility
           # of the "spec" file author to give correct arguments only.

case `echo "testing\c"`,`echo -n testing` in
    *c*,-n*) echo_n=   echo_c=     ;;
    *c*,*)   echo_n=-n echo_c=     ;;
    *)       echo_n=   echo_c='\c' ;;
esac


wait_for_pid () {
  verb="$1"           # created | removed
  pid="$2"            # process ID of the program operating on the pid-file
  pid_file_path="$3" # path to the PID file.

  i=0
  avoid_race_condition="by checking again"

  while test $i -ne $service_startup_timeout ; do

    case "$verb" in
      'created')
        # wait for a PID-file to pop into existence.
        test -s "$pid_file_path" && i='' && break
        ;;
      'removed')
        # wait for this PID-file to disappear
        test ! -s "$pid_file_path" && i='' && break
        ;;
      *)
        echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"
        exit 1
        ;;
    esac

    # if server isn't running, then pid-file will never be updated
    if test -n "$pid"; then
      if kill -0 "$pid" 2>/dev/null; then
        :  # the server still runs
      else
        # The server may have exited between the last pid-file check and now.  
        if test -n "$avoid_race_condition"; then
          avoid_race_condition=""
          continue  # Check again.
        fi

        # there's nothing that will affect the file.
        log_failure_msg "The server quit without updating PID file ($pid_file_path)."
        return 1  # not waiting any more.
      fi
    fi

    echo $echo_n ".$echo_c"
    i=`expr $i + 1`
    sleep 1

  done

  if test -z "$i" ; then
    log_success_msg
    return 0
  else
    log_failure_msg
    return 1
  fi
}

# Get arguments from the my.cnf file,
# the only group, which is read from now on is [mysqld]
if test -x ./bin/my_print_defaults
then
  print_defaults="./bin/my_print_defaults"
elif test -x $bindir/my_print_defaults
then
  print_defaults="$bindir/my_print_defaults"
elif test -x $bindir/mysql_print_defaults
then
  print_defaults="$bindir/mysql_print_defaults"
else
  # Try to find basedir in /etc/my.cnf
  # conf=/etc/my.cnf
  print_defaults=
  if test -r $conf
  then
    subpat='^[^=]*basedir[^=]*=\(.*\)$'
    dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`
    for d in $dirs
    do
      d=`echo $d | sed -e 's/[ 	]//g'`
      if test -x "$d/bin/my_print_defaults"
      then
        print_defaults="$d/bin/my_print_defaults"
        break
      fi
      if test -x "$d/bin/mysql_print_defaults"
      then
        print_defaults="$d/bin/mysql_print_defaults"
        break
      fi
    done
  fi

  # Hope it's in the PATH ... but I doubt it
  test -z "$print_defaults" && print_defaults="my_print_defaults"
fi

#
# Read defaults file from 'basedir'.   If there is no defaults file there
# check if it's in the old (depricated) place (datadir) and read it from there
#

extra_args=""
if test -r "$basedir/my.cnf"
then
  extra_args="-e $basedir/my.cnf"
else
  if test -r "$datadir/my.cnf"
  then
    extra_args="-e $datadir/my.cnf"
  fi
fi

#
# Set pid file if not given
#
if test -z "$mysqld_pid_file_path"
then
  mysqld_pid_file_path=$datadir/mysql.pid
else
  case "$mysqld_pid_file_path" in
    /* ) ;;
    * )  mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;
  esac
fi

case "$mode" in
  'start')
    # Start daemon

    # Safeguard (relative paths, core dumps..)
    cd $basedir

    echo $echo_n "Starting MySQL"
    if test -x $bindir/mysqld_safe
    then
      # Give extra arguments to mysqld with the my.cnf file. This script
      # may be overwritten at next upgrade.
      $bindir/mysqld_safe --defaults-file=$conf  $other_args >/dev/null 2>&1 &
      wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

      # Make lock for RedHat / SuSE
      if test -w "$lockdir"
      then
        touch "$lock_file_path"
      fi

      exit $return_value
    else
      log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)"
    fi
    ;;

  'stop')
    # Stop daemon. We use a signal here to avoid having to know the
    # root password.

    if test -s "$mysqld_pid_file_path"
    then
      # signal mysqld_safe that it needs to stop
      touch "$mysqld_pid_file_path.shutdown"

      mysqld_pid=`cat "$mysqld_pid_file_path"`

      if (kill -0 $mysqld_pid 2>/dev/null)
      then
        echo $echo_n "Shutting down MySQL"
       # kill $mysqld_pid
       $bindir/mysqladmin --defaults-file=$conf --socket=$datadir/mysql.sock --user=$mysql_user --password=$mysql_pwd  shutdown

        # mysqld should remove the pid file when it exits, so wait for it.
        wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?
      else
        log_failure_msg "MySQL server process #$mysqld_pid is not running!"
        rm "$mysqld_pid_file_path"
      fi

      # Delete lock for RedHat / SuSE
      if test -f "$lock_file_path"
      then
        rm -f "$lock_file_path"
      fi
      exit $return_value
    else
      log_failure_msg "MySQL server PID file could not be found!"
    fi
    ;;

  'restart')
    # Stop the service and regardless of whether it was
    # running or not, start it again.
    if $0 stop  $other_args; then
      $0 start $other_args
    else
      log_failure_msg "Failed to stop running server, so refusing to try to start."
      exit 1
    fi
    ;;

  'reload'|'force-reload')
    if test -s "$mysqld_pid_file_path" ; then
      read mysqld_pid <  "$mysqld_pid_file_path"
      kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"
      touch "$mysqld_pid_file_path"
    else
      log_failure_msg "MySQL PID file could not be found!"
      exit 1
    fi
    ;;
  'status')
    # First, check to see if pid file exists
    if test -s "$mysqld_pid_file_path" ; then 
      read mysqld_pid < "$mysqld_pid_file_path"
      if kill -0 $mysqld_pid 2>/dev/null ; then 
        log_success_msg "MySQL running ($mysqld_pid)"
        exit 0
      else
        log_failure_msg "MySQL is not running, but PID file exists"
        exit 1
      fi
    else
      # Try to find appropriate mysqld process
      mysqld_pid=`pidof $libexecdir/mysqld`

      # test if multiple pids exist
      pid_count=`echo $mysqld_pid | wc -w`
      if test $pid_count -gt 1 ; then
        log_failure_msg "Multiple MySQL running but PID file could not be found ($mysqld_pid)"
        exit 5
      elif test -z $mysqld_pid ; then 
        if test -f "$lock_file_path" ; then 
          log_failure_msg "MySQL is not running, but lock file ($lock_file_path) exists"
          exit 2
        fi 
        log_failure_msg "MySQL is not running"
        exit 3
      else
        log_failure_msg "MySQL is running but PID file could not be found"
        exit 4
      fi
    fi
    ;;
    *)
      # usage
      basename=`basename "$0"`
      echo "Usage: $basename  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]"
      exit 1
    ;;
esac

exit 0

12. 配置mysql开机自动启动

# chmod +x  /etc/init.d/mysql-3406

# chkconfig --add mysql-3406

# chkconfig  mysql-3406 on

以后可以用：

/etc/init.d/mysql-3406 start|stop|restart   启动，停止，重启服务。