Mysql数据库自建，使用，问题排查最佳实践-弹性云主机-最佳实践

创建ECS并自建mysql服务器

前提条件

1.已创建ECS虚机，请参考指定ECS规格创建实例创建MySQL客户端的弹性云服务器。

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 <<EOF > /mysql/install/my.cnf
[mysql]
no-beep
prompt="\u@mydb \R:\m:\s [\d]> "
#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 -> 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 ] && 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" && 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 "$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" && 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 >/dev/null &
      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
        # 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

[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=http://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)
           http://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中有哪些数据库 : show databases;  #注意：以分号结尾，分号是英文的分号。
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.02 sec)

mysql>

选择使用某个数据库:
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;
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> 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; // 删除所有！

快速删除表中的数据？【truncate比较重要，必须掌握】

//删除dept_bak表中的数据
delete from dept_bak; //这种删除数据的方式比较慢。

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

delete语句删除数据的原理？（delete属于DML语句！！！）
  表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
  这种删除缺点是：删除效率比较低。
  这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

truncate语句删除数据的原理？
  这种删除效率比较高，表被一次截断，物理删除。
  这种删除缺点：不支持回滚。
  这种删除优点：快速。

用法：truncate table dept_bak; （这种操作属于DDL操作。）

大表非常大，上亿条记录？？？？
  删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
  可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
  但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

  truncate是删除表中的数据，表还在！

删除表操作？
  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 权限

清理磁盘中的垃圾数据

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

弹性云主机

弹性云主机

创建ECS并自建mysql服务器

前提条件

mysql部署

系统设置

确认系统及依赖库版本

确认mysql版本

防火墙及SELinux

创建用户及目录

创建MySQL

上传并解压

环境变量

初始化配置文件

初始化数据库

启库并修改密码

配置远程登录

连接测试

配置启动服务

使用systemd启动服务器(可选)

mysql数据库基础使用介绍

mysql常用命令

简单查询select

条件查询

表的创建

表的删除

插入数据insert

修改update

删除数据deleted

mysql常见问题及解决思路

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

弹性云主机

弹性云主机

创建ECS并自建mysql服务器

前提条件

mysql部署

系统设置

确认系统及依赖库版本

确认mysql版本

防火墙及SELinux

创建用户及目录

创建MySQL

上传并解压

环境变量

初始化配置文件

初始化数据库

启库并修改密码

配置远程登录

连接测试

配置启动服务

使用systemd启动服务器(可选)

mysql数据库基础使用介绍

mysql常用命令

简单查询select

条件查询

表的创建

表的删除

插入数据insert

修改update

删除数据deleted

mysql常见问题及解决思路