Mysql数据库自建，使用，问题排查最佳实践
更新时间 2025-03-21 17:59:15
最近更新时间: 2025-03-21 17:59:15
本文主要为您介绍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 权限
清理磁盘中的垃圾数据
