創建ECS并自建mysql服務器

前提條件

1.已創建ECS虛機，請參考指定ECS規格創建實例創建MySQL客戶端的彈性云服務器，如追求數據庫IO讀寫性能，推薦使用本地盤云主機Ip3。

2.已下載MySQL客戶端安裝包，MySQL官網提供了針對不同操作系統的客戶端安裝包，單擊下載8.0的最新版本，單擊下載其他歸檔版本，用戶需根據自己的操作系統類型選擇符合要求的安裝包。

mysql部署

系統設置

確認系統及依賴庫版本

本篇使用OS鏡像版本為CTyunOS 22.06.3

[root@localhost ~]# cat /etc/ctyunos-release
CTyunOS release 22.06.3

[root@localhost ~]# ldd --version
ldd (GNU libc) 2.28
Copyright (C) 2018 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

確認mysql版本

二進制搭建，mysql 對應的 OS Version 必須和操作系統依賴庫版本保持一致，找到對應版本進行下載。

防火墻及SELinux

[root@localhost ~]# systemctl disable firewalld --now #關閉并禁用防火墻

[root@localhost ~]# setenforce 0 #關閉selinux
setenforce: SELinux is disabled

[root@localhost ~]# sed -i 's/^SELINUX=enforcing$/SELINUX=disabled/' /etc/selinux/config #配置持久化

創建用戶及目錄

[root@localhost ~]# groupadd mysql

[root@localhost ~]# useradd -r -g mysql -s /sbin/nologin mysql

[root@localhost ~]# mkdir -p /mysql/install/data

[root@localhost ~]# mkdir -p /mysql/install/tmp

[root@localhost ~]# mkdir -p /mysql/install/file

[root@localhost ~]# mkdir -p /mysql/install/log

創建MySQL

上傳并解壓

[root@localhost install]# pwd
/mysql/install

[root@localhost install]# ls
data  file  log  mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz  tmp

[root@localhost install]# tar -xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

[root@localhost install]# ln -s mysql-8.4.0-linux-glibc2.28-x86_64 mysql  #創建軟鏈接

[root@localhost install]# ll
total 459M
drwxr-xr-x 2 root root    6 Oct  8 17:04 data
drwxr-xr-x 2 root root    6 Oct  8 17:04 file
drwxr-xr-x 2 root root    6 Oct  8 17:04 log
lrwxrwxrwx 1 root root   34 Oct  8 17:08 mysql -> mysql-8.4.0-linux-glibc2.28-x86_64
drwxr-xr-x 9 root root  129 Oct  8 17:07 mysql-8.4.0-linux-glibc2.28-x86_64
-rw-r--r-- 1 root root 459M Oct  8 17:04 mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
drwxr-xr-x 2 root root    6 Oct  8 17:04 tmp

環境變量

[root@localhost install]# vi ~/.bash_profile

#PATH 增加路徑 /mysql/install/mysql/bin
[root@localhost install]# cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
PATH=$PATH:/mysql/install/mysql/bin:$HOME/bin
export PATH


[root@localhost install]# source ~/.bash_profile

初始化配置文件

#注意bind_address的IP地址和對應路徑的修改
cat &lt;<eof> /mysql/install/my.cnf
[mysql]
no-beep
prompt="\u@mydb \R:\m:\s [\d]&gt; "
#no-auto-rehash
auto-rehash
default-character-set=utf8

[mysqld]
lower_case_table_names=1
server-id=3306
port=3306
user = mysql
bind_address= 192.168.2.134
basedir=/mysql/install/mysql
datadir=/mysql/install/data
#socket = /mysql/install/mysql.sock
socket = /tmp/mysql.sock
pid-file = /mysql/install/mysql.pid
character-set-server=utf8
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

#query_cache_type = 1
#query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

log-output=FILE
general_log = 0
general_log_file=/mysql/install/log/hiri-general.err
slow_query_log = ON
slow_query_log_file=/mysql/install/log/hiri-query.err
long_query_time=10
log-error=/mysql/install/log/hiri-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
#expire_logs_days = 90
#binlog_expire_logs_seconds=2592000
binlog_expire_logs_seconds=604800
min_examined_row_limit = 100

log_bin=/mysql/install/log/hiri-binlog
log_bin_index=/mysql/install/log/hiri-binlog.index
binlog_format='ROW'
binlog_rows_query_log_events=on

#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

#innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#default_authentication_plugin=mysql_native_password  #mysql 8.4版本之后不允許這樣配置了，使用mysql_native_password=ON可以正常使用
mysql_native_password=ON
#default_authentication_plugin=caching_sha2_password
EOF

[root@localhost install]# chown -R mysql.mysql /mysql #修改mysql目錄的屬組和屬主

[root@localhost install]# ll
total 459M
drwxr-xr-x 2 mysql mysql    6 Oct  8 17:04 data
drwxr-xr-x 2 mysql mysql    6 Oct  8 17:04 file
drwxr-xr-x 2 mysql mysql    6 Oct  8 17:04 log
-rw-r--r-- 1 mysql mysql 3.5K Oct  8 17:13 my.cnf
lrwxrwxrwx 1 mysql mysql   34 Oct  8 17:08 mysql -&gt; mysql-8.4.0-linux-glibc2.28-x86_64
drwxr-xr-x 9 mysql mysql  129 Oct  8 17:07 mysql-8.4.0-linux-glibc2.28-x86_64
-rw-r--r-- 1 mysql mysql 459M Oct  8 17:04 mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
drwxr-xr-x 2 mysql mysql    6 Oct  8 17:04 tmp

初始化數據庫

[root@localhost install]# /mysql/install/mysql/bin/mysqld --defaults-file=/mysql/install/my.cnf --initialize --user=mysql --basedir=/mysql/install/mysql --datadir=/mysql/install/data

[root@localhost log]# grep 'temporary password' /mysql/install/log/hiri-error.err
2024-10-08T17:34:43.831698+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ,H4xd+oy0M>t

啟庫并修改密碼

[root@localhost install]# /mysql/install/mysql/bin/mysqld_safe --defaults-file=/mysql/install/my.cnf --datadir=/mysql/install/data --pid-file=/mysql/install/mysql.pid &
[1] 2152
[root@localhost install]# 2024-10-08T11:26:28.216260Z mysqld_safe Logging to '/mysql/install/log/hiri-error.err'.
2024-10-08T11:26:28.470158Z mysqld_safe Starting mysqld daemon with databases from /mysql/install/data

[root@localhost install]# mysqladmin -uroot -p',H4xd+oy0M>t' password 'redhat' -S /mysql/install/mysql.sock
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

配置遠程登錄

[root@localhost install]# ln -sf /mysql/install/mysql.sock /tmp/mysql.sock

[root@localhost install]# mysql -uroot -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql> create user 'root'@'%' identified by 'redhat'; #創建root用戶并設置密碼為redhat
Query OK, 0 rows affected (0.09 sec)

mysql> grant all privileges on *.* to 'root'@'%' with grant option; #賦予root用戶所有權限
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges; #刷新權限使之生效
Query OK, 0 rows affected (0.01 sec)

mysql> select Host,User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

連接測試

配置啟動服務

在/mysql/install/mysql/support-files目錄下，官方提供的有個腳本模板文件 mysql.server，可以拷貝一份起名叫mysql并修改腳本里面對應的路徑和參數即可使用，這里已經改好的腳本內容如下：

[root@localhost support-files]# vim mysql
#!/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 /usr/local/mysql, 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=/mysql/install/mysql
datadir=/mysql/install/data

# 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=/mysql/install/mysql.pid
if test -z "$basedir"
then
  basedir=/mysql/install/mysql
  bindir=/mysql/install/mysql/bin
  if test -z "$datadir"
  then
    datadir=/mysql/install/data
  fi
  sbindir=/mysql/install/mysql/bin
  libexecdir=/mysql/install/mysql/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 ] &amp;&amp; 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

parse_server_arguments() {
  for arg do
    case "$arg" in
      --basedir=*)  basedir=`echo "$arg" | sed -e 's/^[^=]*=//'`
                    bindir="$basedir/bin"
            if test -z "$datadir_set"; then
              datadir="$basedir/data"
            fi
            sbindir="$basedir/sbin"
            libexecdir="$basedir/libexec"
        ;;
      --datadir=*)  datadir=`echo "$arg" | sed -e 's/^[^=]*=//'`
            datadir_set=1
    ;;
      --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
      --service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;
    esac
  done
}

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" &amp;&amp; i='' &amp;&amp; break
        ;;
      'removed')
        # wait for this PID-file to disappear
        test ! -s "$pid_file_path" &amp;&amp; i='' &amp;&amp; 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&gt;/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 "$bindir/my_print_defaults";  then
  print_defaults="$bindir/my_print_defaults"
else
  # Try to find basedir in /etc/my.cnf
  conf=/mysql/install/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
    done
  fi

  # Hope it's in the PATH ... but I doubt it
  test -z "$print_defaults" &amp;&amp; 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 "/mysql/install/my.cnf"
then
  extra_args="-e /mysql/install/my.cnf"
fi

parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`

#
# Set pid file if not given
#
if test -z "$mysqld_pid_file_path"
then
  mysqld_pid_file_path=$datadir/`hostname`.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=/mysql/install/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args &gt;/dev/null &amp;
      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&gt;/dev/null)
      then
        echo $echo_n "Shutting down MySQL"
        kill $mysqld_pid
        # 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 &lt;  "$mysqld_pid_file_path"
      kill -HUP $mysqld_pid &amp;&amp; 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 &lt; "$mysqld_pid_file_path"
      if kill -0 $mysqld_pid 2&gt;/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

[root@localhost support-files]# pwd
/mysql/install/mysql/support-files

[root@localhost support-files]# chown mysql.mysql mysql

[root@localhost support-files]# chmod 755 mysql

[root@localhost support-files]# ll
total 32K
-rwxr-xr-x 1 mysql mysql  11K Oct  8 19:44 mysql
-rwxr-xr-x 1 mysql mysql 1.1K Apr 10 14:26 mysqld_multi.server
-rw-r--r-- 1 mysql mysql 2.1K Apr 10 15:58 mysql-log-rotate
-rwxr-xr-x 1 mysql mysql  11K Apr 10 15:58 mysql.server
[root@localhost support-files]# ./mysql status
 SUCCESS! MySQL running (3305)

[root@localhost support-files]# ./mysql stop
Shutting down MySQL.....2024-10-08T11:50:53.760574Z mysqld_safe mysqld from pid file /mysql/install/mysql.pid ended
 SUCCESS!
[1]+  Done                    /mysql/install/mysql/bin/mysqld_safe --defaults-file=/mysql/install/my.cnf --datadir=/mysql/install/data --pid-file=/mysql/install/mysql.pid  (wd: /mysql/install)
(wd now: /mysql/install/mysql/support-files)

[root@localhost support-files]# ./mysql start
Starting MySQL.............. SUCCESS!

[root@localhost support-files]# ./mysql status
 SUCCESS! MySQL running (4653)

使用systemd啟動服務器(可選)

[root@localhost support-files]# ./mysql stop
Shutting down MySQL.. SUCCESS!

[root@localhost support-files]#  cd /usr/lib/systemd/system

[root@localhost system]# touch mysqld.service

[root@localhost system]# chmod 644 mysqld.service

[root@localhost system]# vi mysqld.service
[root@localhost system]# cat mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=//dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Have mysqld write its state to the systemd notify socket
Type=notify

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/mysql/install/mysql/bin/mysqld --defaults-file=/mysql/install/my.cnf $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

[root@localhost system]# systemctl enable mysqld.service
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

[root@localhost system]# systemctl start mysqld.service

[root@localhost system]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2024-10-08 19:53:18 CST; 1s ago
     Docs: man:mysqld(8)
           //dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 4794 (mysqld)
   Status: "Server is operational"
    Tasks: 39
   Memory: 422.6M
   CGroup: /system.slice/mysqld.service
           └─4794 /mysql/install/mysql/bin/mysqld --defaults-file=/mysql/install/my.cnf

Oct 08 19:53:10 localhost systemd[1]: Starting MySQL Server...
Oct 08 19:53:18 localhost systemd[1]: Started MySQL Server.

[root@localhost system]# systemctl stop mysqld

自此mysql服務器自建完成。

mysql數據庫基礎使用介紹

mysql常用命令

查看mysql中有哪些數據庫：

mysql> show databases;  #注意：以分號結尾，分號是英文的分號。
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.02 sec)

選擇使用某個數據庫:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

查看某個數據庫下有哪些表:

mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| ndb_binlog_index                                     |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+

創建數據庫:

mysql> create database example;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

查看mysql數據庫的版本號：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

查看當前使用的是哪個數據庫:

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.01 sec)

導入數據庫表：

mysql> source D:\course\03-MySQL\document\test.sql  #注意：路徑中不要有中文。

查看表的結構：

mysql&gt; desc <table_name>; #describe縮寫為：desc

簡單查詢select

mysql> select * from db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N  | N                | N              | N                   | N                  | N            | N          | N            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)

#這種方式的缺點：
1、效率低（他會先把*號轉換為字段）
2、可讀性差。

給查詢的列起別名：使用as關鍵字起別名

mysql> select host,user as username from db; #as也可以省略
+-----------+---------------+
| host      | username      |
+-----------+---------------+
| localhost | mysql.session |
+-----------+---------------+
1 row in set (0.00 sec)

條件查詢

查詢語法格式：
select 字段1,字段2,字段3.... from 表名 where 條件;

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql> select host,user username  from user where user='mysql.session'; 
+-----------+---------------+
| host      | username      |
+-----------+---------------+
| localhost | mysql.session |
+-----------+---------------+
1 row in set (0.00 sec

表的創建

#創建一個學生表  
create table t_student(
      no int,
      name varchar(32),
      sex char(1),
      age int(3),
      email varchar(255)
    );

表的刪除

  #刪除表：
  drop table t_student; 
  // 當這張表不存在的時候會報錯
  // 如果這張表存在，刪除
  drop table if exists t_student;

插入數據insert

插入數據insert （DML）

語法格式：insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

mysql>  create table t_user(number bigint(32),username varchar(32),sex char(1) default '男');
mysql> desc t_user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| number   | bigint      | YES  |     | NULL    |       |
| username | varchar(32) | YES  |     | NULL    |       |
| sex      | char(1)     | YES  |     | 男      |       |
+----------+-------------+------+-----+---------+-------+

mysql> insert into t_user (number,username,sex) values (1,'user1','男'), (2,'user2','女'), (3,'user3','男');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t_user;
+--------+----------+------+
| number | username | sex  |
+--------+----------+------+
|      1 | user1    | 男   |
|      2 | user2    | 女   |
|      3 | user3    | 男   |
+--------+----------+------+
3 rows in set (0.00 sec)

修改update

語法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 條件;

注意：沒有條件限制會導致所有數據全部更新！

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
|    2 | jack     | 2000-10-11 | 2020-03-18 15:51:23 |
+------+----------+------------+---------------------+

update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

更新所有；

update t_user set name = 'abc';

刪除數據deleted

語法格式：delete from 表名 where 條件;

注意：沒有條件，整張表的數據會全部刪除！

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 刪除所有

快速刪除表中的數據；

//刪除dept_bak表中的數據
delete from dept_bak; 

mysql> select * from dept_bak;
Empty set (0.00 sec)

刪除表操作；

drop table 表名; // 刪除表。

mysql常見問題及解決思路

案例一：Too many connections（連接數過多，導致連接不上數據庫，業務無法正常進行）

問題復現：

mysql> SHOW VARIABLES LIKE 'max_connections'; #將當前數據庫允許最大連接數設置為1
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 800   |
+-----------------+-------+
1 row in set (0.03 sec)

mysql>  set global max_connections=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

[root@mysql ~]# mysql -uroot -predhat
mysql: [Warning] Using a password on the command line interface can be insecure. 
ERROR 1040 (HY000): Too many connections #報錯，太多連接

解決思路：

首先先要考慮在我們 MySQL 數據庫參數文件里面，對應的 max_connections 這個參數值是不是設置的太小了，導致客戶端連接數超過了數據庫所承受的最大值。
該值默認大小是 151，我們可以根據實際情況進行調整。

對應解決辦法：

set global max_connections=500

但這樣調整會有隱患，因為我們無法確認數據庫是否可以承擔這么大的連接壓力，就好比原來一個人只能吃一個饅頭，但現在卻非要讓他吃 10 個，他肯定接受不了。反應到服務器上面，就有可能會出現宕機的可能。

所以這又反映出了，我們在新上線一個業務系統的時候，要做好壓力測試。保證后期對數據庫進行優化調整。

其次可以限制 InnoDB的并發處理數量，如果 innodb_thread_concurrency = 0（這種代表不受限制） 可以先改成 16 或是 64 看服務器壓力。

如果非常大，可以先改的小一點讓服務器的壓力下來之后，然后再慢慢增大，根據自己的業務而定。
 

案例二 主從復制報錯類型

Last_SQL_Errno: 1062 （從庫與主庫數據沖突）
Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table test.t;

Duplicate entry ‘4’ for key ‘PRIMARY’,

Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

the event’s master log mysql-bin.000014, end_log_pos 1505

針對這個報錯，我們首先要考慮是不是在從庫中誤操作導致的。結果發現，我們在從庫中進行了一條針對有主鍵表的SQL語句的插入，導致主庫再插入相同 sql 的時候，主從狀態出現異常。發生主鍵沖突的報錯。
解決方法：在確保主從數據一致性的前提下，可以在從庫進行錯誤跳過。一般使用 percona-toolkit 中的 pt-slave-restart 進行。

在從庫完成如下操作：

[root@zs bin]# ./pt-slave-restart -uroot -proot123

之后最好在從庫中開啟 read_only 參數，禁止在從庫進行寫入操作。

Last_IO_Errno: 1593（server-id沖突）
Last_IO_Error:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

these ids must be different for replication to work

(or the –replicate-same-server-id option must be used on slave but this

does not always make sense; please check the manual before using it)

這個報錯出現之后，就能一目了然看到兩臺機器的 server-id 是一樣的。

在搭建主從復制的過程中，我們要確保兩臺機器的 server-id 是唯一的。這里再強調一下 server-id 的命名規則（服務器 ip 地址的最后一位+本 MySQL 服務的端口號）。

解決方法：

在主從兩臺機器上設置不同的 server-id。

Last_SQL_Errno: 1032（從庫少數據，主庫更新的時候，從庫報錯）
Last_SQL_Error:

Could not execute Update_rows event on table test.t; Can’t find record

in ‘t’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the

event’s master log mysql-bin.000014, end_log_pos 1708

解決問題的辦法：

根據報錯信息，我們可以獲取到報錯日志和position號，然后就能找到主庫執行的哪條sql，導致的主從報錯。

在主庫執行：

/usr/local/mysql/bin/mysqlbinlog –no-defaults -v -v –-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log

cat 1.log

#170720 14:20:15 server id 3 end_log_pos 1708 CRC32 0x97b6bdec Update_rows: table id 113 flags: STMT_END_F

### UPDATE `test`.`t`

### WHERE

### @1=4 /* INT meta=0 nullable=0 is_null=0 */

### @2=’dd’ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

### SET

### @1=4 /* INT meta=0 nullable=0 is_null=0 */

### @2=’ddd’ /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

# at 1708

#170720 14:20:15 server id 3 end_log_pos 1739 CRC32 0xecaf1922 Xid = 654

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

獲取到SQL語句之后，就可以在從庫反向執行SQL語句。把從庫缺少的SQL語句補全，解決報錯信息。

在從庫依次執行：

mysql> insert into t (b) values (‘ddd’);

Query OK, 1 row affected (0.01 sec)

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@node4 bin]# ./pt-slave-restart -uroot -proot123

案例三 MySQL安裝過程中的報錯

[root@zs data]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &[1] 3758

[root@zs data]# 170720 14:41:24 mysqld_safe Logging to ‘/data/mysql/error.log’.

170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720

14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended

170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql2017-07-20

14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Please use –explicit_defaults_for_timestamp server option

(see documentation for more details)./usr/local/mysql/bin/mysqld:

File ‘/data/mysql/mysql-bin.index’ not found (Errcode: 13 – Permission denied)

2017-07-20 14:41:25 4388 [ERROR] Aborting

解決思路：

遇到這樣的報錯信息，我們要學會時時去關注錯誤日志 error log 里面的內容。看見了關鍵的報錯點Permission denied，證明當前 MySQL 數據庫的數據目錄沒有權限。

解決方法：

[root@zs data]# chown mysql:mysql -R mysql

[root@zs data]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &

[1] 4402

[root@zs data]# 170720 14:45:56 mysqld_safe Logging to ‘/data/mysql/error.log’.

170720 14:45:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql

如何避免這類問題，個人建議在安裝 MySQL 初始化的時候，一定加上–user=mysql，這樣就可以避免權限問題。

./mysql_install_db –basedir=/usr/local/mysql/ –datadir=/data/mysql/ –defaults-file=/etc/my.cnf –user=mysql

案例四 數據庫密碼忘記的問題

[root@zs ~]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

[root@zs ~]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

解決思路：

目前是進入不了數據庫的情況，所以我們要考慮是不是可以跳過權限。因為在數據庫中，MySQL 數據庫中 user 表記錄著我們用戶的信息。
解決方法：啟動 MySQL 數據庫的過程中，可以這樣執行：

/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &

這樣啟動，就可以不用輸入密碼，直接進入 MySQL 數據庫了。然后在修改你自己想要改的 root 密碼即可。

update mysql.user set password=password(‘root123′) where user=’root’;

案例五 truncate 刪除數據，導致自動清空自增 ID，前端返回報錯 not found這個問題的出現，就要考慮下 truncate 和 delete 的區別了，看下實驗演練：

 首先先創建一張表：

CREATE TABLE `t` (

`a` int(11) NOT NULL AUTO_INCREMENT,

`b` varchar(20) DEFAULT NULL,

PRIMARY KEY (`a`),

KEY `b` (`b`)

) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8

插入三條數據：

mysql> insert into t (b) values (‘aa’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t (b) values (‘bb’);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t (b) values (‘cc’);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t;

+—–+——+

| a | b |

+—–+——+

| 300 | aa |

| 301 | bb |

| 302 | cc |

+—–+——+

3 rows in set (0.00 sec)

先用 delete 進行刪除全表信息，再插入新值。

結果發現 truncate 把自增初始值重置了，自增屬性從 1 開始記錄了。當前端用主鍵 id 進行查詢時，就會報沒有這條數據的錯誤。

個人建議不要使用 truncate 對表進行刪除操作，雖然可以回收表空間，但是會涉及自增屬性問題。這些坑，我們不要輕易鉆進去。

案例六 can’t open file (errno:24)

有的時候，數據庫跑得好好的，突然報不能打開數據庫文件的錯誤了。
解決思路：首先我們要先查看數據庫的 error log。然后判斷是表損壞，還是權限問題。還有可能磁盤空間不足導致的不能正常訪問表；操作系統的限制也要關注下；用 perror 工具查看具體錯誤！

linux:/usr/local/mysql/bin # ./perror 24

OS error code 24: Too many open files

超出最大打開文件數限制！ulimit -n 查看系統的最大打開文件數是 65535，不可能超出！那必然是數據庫的最大打開文件數超出限制！

在 MySQL 里查看最大打開文件數限制命令：show variables like ‘open_files_limit’;

發現該數值過小，改為 2048，重啟 MySQL，應用正常

處理方法：

repair table ；

chown mysql 權限

清理磁盤中的垃圾數據