MySQL学习
编辑
MySQL学习
最近一周学习MySQL学的很不舒服，觉得索然无味，重复的复制粘贴、完全搞不懂本质，自己重学一遍，换换口味。Bilibili找到21年的视频，有点旧了，但是讲的还是不错的。
尽可能完善这个笔记，复现，整理，提升！
MySQL是一款开源的关系型数据库，应用非常广泛。自从被Oracle收购，有闭源风险，所有有MariaDB 作为社区分支。
一、安装
首要的就是安装，肯定需要一定的规划，当然也要结合自己的实际情况。软件安装的方式，无非就常见的几种方式
- 编译安装，好多参数并不理解，自定义程度最高
- 二进制安装，直接解压放到指定位置，放到指定位置
YUM、APT源安装，最简单，但是版本不好控制，安装位置也是固定的，缺少自定义。APT可以文件界面选择版本。
最终我尽可能把三种方式都记录上，之前缺二进制，今天把二进制补上，图片最后上传。
1.0 找安装包
- 从官网找下载按钮，然后进去MySQL Community (GPL) Downloads »
- 可以直接选择
YUM仓库。或者从MySQL Community Server进去，然后从归档页，找需要的源码版本Source Code、二进制版本Linux - Generic。
- 对于YUM安装方式，还可以去MySQL repo服务器找对应系统的
rpm源
1.1 源码编译安装
hostnamectl set-hostname source-mysql
1.1.1 安装前准备
- 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
- 关闭SELinux
setenforce 0
sed -i 's/^SELINUX=*/SELINUX=disable/g' /etc/selinux/config
- 配置
yum
yum repolist
- 清理之前的环境
- 清理之前的
mysql包
yum -y remove mariadb mariadb-server mariadb-libs mariadb-devel
- 删除mysql用户
userdel -r mysql
- 删除
myqsl相关目录
rm -rf /var/lib/mysql
rm -rf /etc/my*
- 创建mysql用户
useradd -r mysql -M -s /bin/false
1.1.2 下载安装
- 安装编译工具
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake
- 下载源码包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.27.tar.gz
- 解压
tar xf mysql-boost-5.7.27.tar.gz
- 编译安装前检查，及参数设置
cd mysql-5.7.27
cmake . \
-DWITH_BOOST=boost/boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DPID_FILE=/usr/local/mysql/data/mysqld.pid \
-DINSTALL_MANDIR=/usr/share/man \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_BOOST：依赖Boost库，告诉cmakeBoost库的位置。-DCMAKE_INSTALL_PREFIX：安装路径-DSYSCONFDIR： 配置文件的路径-DMYSQL_DATADIR：数据存储的路径-DPID_FILEmysqld：服务的PID路径-DINSTALL_MANDIR：帮助文档的路径-DMYSQL_TCP_PORT： TCP端口-DMYSQL_UNIX_ADDR：UNIX套接字文件路径-DDEFAULT_CHARSET：默认字符集-DEXTRA_CHARSETS：所有额外的字符集-DDEFAULT_COLLATION：默认字符集排序-DWITH_READLINE：让MySQL命令行支持命令历史、行编辑等交互功能-DWITH_SSL：指定SSL支持来源为系统，而不是MySQL自带的版本-DWITH_EMBEDDED_SERVER：启用嵌入式服务器的构建。可以把MySQL嵌入应用-DENABLED_LOCAL_INFILE：允许从本地文件导入数据-DWITH_INNOBASE_STORAGE_ENGINE：启用InnoDB存储引擎
最终执行结果如下
....
-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- Configuring done
-- Generating done
CMake Warning:
Manually-specified variables were not used by the project:
PID_FILE
WITH_READLINE
-- Build files have been written to: /root/mysql-5.7.27
[root@source-mysql mysql-5.7.27]#
- 编译安装，大概半个小时
make && make install
最终效果如下
...
-- Installing: /usr/local/mysql/support-files/mysql-log-rotate
-- Installing: /usr/local/mysql/support-files/magic
-- Installing: /usr/local/mysql/share/aclocal/mysql.m4
-- Installing: /usr/local/mysql/support-files/mysql.server
[root@source-mysql mysql-5.7.27]#
1.1.3 基础配置
- 修改文件夹权限，初始化，会显示
root密码
cd /usr/local/mysql
chown -R mysql.mysql .
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[root@source-mysql mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2025-10-13T12:00:08.000667Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-10-13T12:00:08.153045Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-10-13T12:00:08.179711Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-10-13T12:00:08.234856Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 29d529ae-a82c-11f0-9b66-000c29e8cb85.
2025-10-13T12:00:08.235246Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-10-13T12:00:08.559344Z 0 [Warning] CA certificate ca.pem is self signed.
2025-10-13T12:00:08.713531Z 1 [Note] A temporary password is generated for root@localhost: Btj/X,X18arj
[root@source-mysql mysql]#
- 新建配置文件
vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
# default-character-set = utf8 --- mysqlbinlog无法使用旧的字符集参数
[mysqld]
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
character_set_server = utf8
pid-file=/usr/local/mysql/mysql.pid
- 临时启动
/usr/local/mysql/bin/mysqld --user=mysql &
验证
ps aux |grep mysql
登录验证，需要上面的密码
/usr/local/mysql/bin/mysql -uroot -p
- 修改root密码
/usr/local/mysql/bin/mysqladmin -u root -p'旧密码' password '新密码'
- 加入环境变量
cat >> /etc/profile <<EOL
export PATH=$PATH:/usr/local/mysql/bin
EOL
加载环境变量
source /etc/profile
- 登录
mysql -uroot -p
1.1.4 systemd管理
- 杀掉
mysql进程
ps aux |grep mysql
kill mysql进程号
- 编辑
service文件
cat > /etc/systemd/system/mysqld.service <<EOL
[Unit]
Description=MySQL Server
[Service]
ExecStart=/usr/local/mysql/bin/mysqld
User=mysql
Group=mysql
Restart=always
RestartSec=3
LimitNOFILE=infinity
[Install]
WantedBy=multi-user.target
EOL
- 重载
systemd配置文件
systemctl daemon-reload
- 开机自启mysql，名字与上一步的名称对应
systemctl enable --now mysqld
- 登录验证
mysql -uroot -p
1.2 YUM安装
从MySQL官网 或MySQL repo服务器找对应系统的 rpm 源
hostnamectl set-hostname yum-mysql
1.2.1 安装前准备
- 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
- 关闭SELinux
setenforce 0
sed -i 's/^SELINUX=*/SELINUX=disable/g' /etc/selinux/config
- 配置
yum
yum repolist
- 清理之前的环境
- 清理之前的
mysql包
yum -y remove mariadb mariadb-server mariadb-libs mariadb-devel
- 删除mysql用户
userdel -r mysql
- 删除
myqsl相关目录
rm -rf /var/lib/mysql
rm -rf /etc/my*
1.2.2 YUM安装
从MySQL官网 或MySQL repo服务器找对应系统的 rpm 源
- 配置MSQL安装源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum -y install yum-utils
yum-config-manager --enable mysql57-community
yum-config-manager --disable mysql80-community
- 导入GPG密钥，验证密钥文件存在
# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
ls -l /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
- 安装，需要哪个版本，去
/etc/yum.d/mysql*.repo启用哪个版本。
yum -y install mysql-community-server
...
Installed:
mysql-community-server.x86_64 0:5.7.44-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:5.7.44-1.el7 mysql-community-common.x86_64 0:5.7.44-1.el7 mysql-community-libs.x86_64 0:5.7.44-1.el7
Complete!
[root@yum-mysql ~]# systemctl enable mysqld --now
- 启动
systemctl enable mysqld --now
- 查找临时密码
grep password /var/log/mysqld.log
[root@yum-mysql ~]# grep password /var/log/mysqld.log
2025-10-13T12:58:16.949323Z 1 [Note] A temporary password is generated for root@localhost: .z=8eWU*KW+V
- 修改密码，编译安装的不能弱密码
mysqladmin -u root -p'Polish21..' password 'Root21..'
mysqladmin -u root -p'旧密码' password '新密码'
或者登录mysql 后
alter user root@localhost identified by '新密码';
- 验证
mysql -uroot -p
1.3 二进制安装
以 mysql5.7.26 和 mysql8.0.20 两种版本安在不同的机器，方法一模一样，只有部分步骤名称、路径不一样。
1.3.1 安装规范
| MySQL5.7 | MySQL8 | |
|---|---|---|
| 版本号 | 5.7.26 | 8.0.20 |
| 程序目录 | /opt/mysql/mysql-5.7.26/ | /opt/mysql/mysql-8.0.20/ |
| 数据目录 | /data/mysql/mysql3306/data | /data/mysql/mysql3306/data |
| 日志目录 | /data/mysql/mysql3306/logs | /data/mysql/mysql3306/logs |
| 配置文件 | /data/mysql/mysql3306/my3306.cnf | /data/mysql/mysql3306/my3306.cnf |
| 软链接 | /usr/local/mysql | /usr/local/mysql |
| ip地址 | 192.168.10.157 | 192.168.10.180 |
| 下载地址 | https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz | https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz |
| 临时上传目录 | /opt/server/tools | /opt/server/tools |
| 启动用户 | mysql | mysql |
1.3.2 环境准备
- VMware17 + CentOS7.9
- 配置好
YUM源，安装好lrzsz，vim
curl -o 1.sh https://scripts.bravexist.cn/centos7-repo-aggregator.sh
bash 1.sh
yum install lrzsz vim -y
- 创建好
mysql用户
useradd -M -s /sbin/nologin -r mysql
[root@centos7 ~]# useradd -M -s /sbin/nologin -r mysql
[root@centos7 ~]#
-M 不创建家目录
-s 设置不可登录的shell
-r 设置为系统用户
- 创建好目录
mkdir /opt/server/tools -p
mkdir /opt/mysql
mkdir /data/mysql/mysql3306/{data,logs} -p
1.3.3 安装
- 上传到
/opt/server/tools
cd /opt/server/tools
# mobaxterm，安装插件后，输入rz，然后回车，ctrl+鼠标右键，send-file using-Z-modem，选择文件发送
rz
- 解压
mysql5.7.26
tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@centos7 tools]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@centos7 tools]#
mysql8.0.20，这里是tar.xz 压缩包
tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@centos7 tools]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@centos7 tools]#
- 移动目录
mysql5.7.26
mv mysql-5.7.26-linux-glibc2.12-x86_64 /opt/mysql/mysql-5.7.26
[root@centos7 tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /opt/mysql/mysql-5.7.26
[root@centos7 tools]#
mysql8.0.20
mv mysql-8.0.20-linux-glibc2.12-x86_64 /opt/mysql/mysql-8.0.20
[root@centos7 tools]# mv mysql-8.0.20-linux-glibc2.12-x86_64 /opt/mysql/mysql-8.0.20
[root@centos7 tools]#
- 创建软链接
mysql5.7.26
ln -s /opt/mysql/mysql-5.7.26 /usr/local/mysql
mysql8.0.20
ln -s /opt/mysql/mysql-8.0.20 /usr/local/mysql
- 删除
mariadb
rpm -e --nodeps mariadb-libs
- 安装依赖包
yum install libaio-devel numactl -y
- 编辑配置文件
/data/mysql/mysql3306/my3306.cnf
~~推荐额外配置，方便结合systemd，PIDFile=/data/mysql/mysql3306/mysql.pid
pid-file=/data/mysql/mysql3306/mysql.pid，之后考虑。
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket=/data/mysql/mysql3306/mysql.sock
server_id=1
port=3306
log_error=/data/mysql/mysql3306/logs/error.log
log_bin=/data/mysql/mysql3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
[mysqld]服务端的配置块user=mysql指定mysql用户身份运行mysqld进程basedir=/usr/local/mysql软件安装根目录datadir=/data/mysql/mysql3306/data数据文件所在目录socket=/data/mysql/mysql3306/mysql.sockUNIX socket 文件路径server_id=1多实例时区分不同的实例，多实例间不可重复port=3306端口log_error=/data/mysql/mysql3306/logs/error.log错误日志log_bin=/data/mysql/mysql3306/logs/mysql-bin二进制日志binlog_format=row二进制日志的格式
。。。 之后很多还不明白，以后整合吧。
- 修改MySQL相关目录的用户组
chown -R mysql:mysql /usr/local/mysql/*
chown -R mysql:mysql /data/mysql
- 初始化数据库，往
datadir写入mysql运行必须的库
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data
--initialize-insecure初始化数据目录，但是不设置密码
- 加入环境变量，并激活环境变量
vim /etc/profile
export PATH="/usr/local/mysql/bin:$PATH"
. /etc/profile
- 启动mysql，并放到后台运行
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
- 尝试使用·socket进行连接
mysql -S /data/mysql/mysql3306/mysql.sock
mysql5.7.26
[root@centos7 mysql3306]# mysql -S /data/mysql/mysql3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
mysql8.0.20
[root@centos7 tools]# mysql -S /data/mysql/mysql3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
1.4 三种安装方式对比
| 三种安装方式对比 | 源码安装 | YUM源安装 | 二进制安装 |
|---|---|---|---|
| 配置文件 | /etc/my.cnf，可自定义 | /etc/my.cnf | 自定义位置 |
| 数据库目录 | /var/local/mysql/data，可自定义 | /var/lib/mysql | 自定义位置 |
| systemd管理 | 需要自行配置 | 默认支持 | 需要自行配置 |
| 初始化 | 手动 | 自动 | 手动 |
| 难易程度 | 一般 | 简单 | 简单 |
| 安装所需时间 | 大约半个小时 | 几分钟 | 几分钟 |
二、启动方式及排错
2.1 启动方式
共有四种启动方式，分别是 service、systemctl 、mysqld_safe、mysqld
2.1.1 service 【不推荐】
service已经被废弃，不推荐使用，并且确实不好用。
修改 /usr/local/mysql/support-files/mysql.server 文件里的 basedir 、datadir，然后移动到 /etc/init.d 目录下，修改名称为mysqld3306
启动
service mysqld3306 start
停止
service mysqld3306 stop
不好用，没有按照配置文件创建 socket 文件。
2.1.2 systemctl 【推荐】
也叫systemd 方式，好用。
创建 /etc/systemd/system/mysql3306.service 文件，编辑内容
[Unit]
Description=MySQL Server By
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
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
LimitNOFILE=5000
重载配置
systemctl daemon-reload
启动，这里的名字是service文件的名字
systemctl start mysqld3306.service
停止
systemctl stop mysqld3306.service
2.1.3 mysqld_safe
启动
mysqld_safe --defaults-file=/data/mysql/mysql3306/my3306.cnf &
停止
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
2.1.4 mysqld
启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
停止
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
2.1.5 区别
service ---> mysqld_safe ---> mysqld ---> 启动
systemctl ---> mysqld ---> 启动
mysqld_safe ---> mysqld ---> 启动
mysqld ---> 启动
一句话总结
mysqld 最直接，
mysqld_safe 更安全，
service 已过时，
systemctl 最推荐。
2.2 常见启动失败案例
2.2.1 目录权限问题
实验环境
chown -R root.root /data/mysql/mysql3306
启动，并验证，发现失败
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
ss -untlp
报错
[root@centos7 ~]# chown -R root.root /data/mysql/mysql3306
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 2317
[root@centos7 ~]# 2025-10-19T05:07:14.364013Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-10-19T05:07:14.364106Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T05:07:14.364128Z 0 [Note] mysqld (mysqld 5.7.26-log) starting as process 2317 ...
2025-10-19T05:07:14.367505Z 0 [ERROR] Could not open file '/data/mysql/mysql3306/logs/error.log' for error logging: Permission denied
2025-10-19T05:07:14.367524Z 0 [ERROR] Aborting
2025-10-19T05:07:14.367535Z 0 [Note] Binlog end
2025-10-19T05:07:14.367574Z 0 [Note] mysqld: Shutdown complete
[1]+ Exit 1 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1035,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1035,fd=4))
[root@centos7 ~]#
关键报错，不能打开XX 文件，权限被拒绝
2025-10-19T05:07:14.367505Z 0 [ERROR] Could not open file '/data/mysql/mysql3306/logs/error.log' for error logging: Permission denied
2025-10-19T05:07:14.367524Z 0 [ERROR] Aborting
解决方案
chown -R mysql.mysql /data/mysql/mysql3306
重新启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
ss -untlp
[root@centos7 ~]# chown -R mysql.mysql /data/mysql/mysql3306
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 2321
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1035,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1035,fd=4))
tcp LISTEN 0 128 [::]:3306 [::]:* users:(("mysqld",pid=2321,fd=33))
[root@centos7 ~]#
关闭
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
2.2.2 参数错误
实验环境，故意写错单词，或者参数与版本不匹配。
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket=/data/mysql/mysql3306/mysql.sock
[mysqld]
user=mysql
basedirrrrrrrrrrr=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket=/data/mysql/mysql3306/mysql.sock
启动，并验证，发现失败
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
ss -untlp
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 2354
[root@centos7 ~]#
[1]+ Exit 1 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1035,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1035,fd=4))
[root@centos7 ~]#
报错
[root@centos7 ~]# more /data/mysql/mysql3306/logs/error.log
2025-10-19T05:14:03.135335Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see d
ocumentation for more details).
2025-10-19T05:14:03.135420Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T05:14:03.135440Z 0 [Note] mysqld (mysqld 5.7.26-log) starting as process 2382 ...
2025-10-19T05:14:03.138583Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-10-19T05:14:03.138600Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-10-19T05:14:03.138603Z 0 [Note] InnoDB: Uses event mutexes
2025-10-19T05:14:03.138605Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2025-10-19T05:14:03.138607Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-10-19T05:14:03.138609Z 0 [Note] InnoDB: Using Linux native AIO
2025-10-19T05:14:03.138932Z 0 [Note] InnoDB: Number of pools: 1
2025-10-19T05:14:03.138995Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-10-19T05:14:03.139970Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-10-19T05:14:03.144367Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-10-19T05:14:03.145597Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setp
riority().
2025-10-19T05:14:03.157048Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2025-10-19T05:14:03.161134Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2025-10-19T05:14:03.161198Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2025-10-19T05:14:03.168132Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2025-10-19T05:14:03.168657Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2025-10-19T05:14:03.168668Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2025-10-19T05:14:03.169081Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2525282
2025-10-19T05:14:03.169502Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool
2025-10-19T05:14:03.169557Z 0 [Note] Plugin 'FEDERATED' is disabled.
2025-10-19T05:14:03.170150Z 0 [Note] InnoDB: Buffer pool(s) load completed at 251019 13:14:03
2025-10-19T05:14:03.171651Z 0 [ERROR] unknown variable 'basedirrrrrrrrrrr=/usr/local/mysql'
2025-10-19T05:14:03.171661Z 0 [ERROR] Aborting
2025-10-19T05:14:03.171666Z 0 [Note] Binlog end
2025-10-19T05:14:03.171742Z 0 [Note] Shutting down plugin 'ngram'
2025-10-19T05:14:03.171749Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2025-10-19T05:14:03.171752Z 0 [Note] Shutting down plugin 'ARCHIVE'
2025-10-19T05:14:03.171753Z 0 [Note] Shutting down plugin 'partition'
2025-10-19T05:14:03.171754Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2025-10-19T05:14:03.171756Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2025-10-19T05:14:03.171757Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2025-10-19T05:14:03.171758Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2025-10-19T05:14:03.171759Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2025-10-19T05:14:03.171760Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2025-10-19T05:14:03.171762Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2025-10-19T05:14:03.171763Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2025-10-19T05:14:03.171764Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2025-10-19T05:14:03.171765Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2025-10-19T05:14:03.171766Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2025-10-19T05:14:03.171768Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2025-10-19T05:14:03.171769Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2025-10-19T05:14:03.171770Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2025-10-19T05:14:03.171771Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2025-10-19T05:14:03.171772Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2025-10-19T05:14:03.171779Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2025-10-19T05:14:03.171780Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2025-10-19T05:14:03.171782Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2025-10-19T05:14:03.171783Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2025-10-19T05:14:03.171784Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2025-10-19T05:14:03.171785Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2025-10-19T05:14:03.171787Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2025-10-19T05:14:03.171788Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2025-10-19T05:14:03.171789Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2025-10-19T05:14:03.171790Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2025-10-19T05:14:03.171791Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2025-10-19T05:14:03.171793Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2025-10-19T05:14:03.171794Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2025-10-19T05:14:03.171795Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2025-10-19T05:14:03.171796Z 0 [Note] Shutting down plugin 'InnoDB'
2025-10-19T05:14:03.171826Z 0 [Note] InnoDB: FTS optimize thread exiting.
2025-10-19T05:14:03.171900Z 0 [Note] InnoDB: Starting shutdown...
2025-10-19T05:14:03.273092Z 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3306/data/ib_buffer_pool
2025-10-19T05:14:03.273860Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 251019 13:14:03
2025-10-19T05:14:04.791745Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2525301
2025-10-19T05:14:04.798333Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2025-10-19T05:14:04.798452Z 0 [Note] Shutting down plugin 'MEMORY'
2025-10-19T05:14:04.798479Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2025-10-19T05:14:04.798592Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2025-10-19T05:14:04.798599Z 0 [Note] Shutting down plugin 'MyISAM'
2025-10-19T05:14:04.798623Z 0 [Note] Shutting down plugin 'CSV'
2025-10-19T05:14:04.798633Z 0 [Note] Shutting down plugin 'sha256_password'
2025-10-19T05:14:04.798638Z 0 [Note] Shutting down plugin 'mysql_native_password'
2025-10-19T05:14:04.798901Z 0 [Note] Shutting down plugin 'binlog'
2025-10-19T05:14:04.799204Z 0 [Note] mysqld: Shutdown complete
[root@centos7 ~]#
关键报错，未知的变量，basedirrrrrrrrrrr
2025-10-19T05:14:03.171651Z 0 [ERROR] unknown variable 'basedirrrrrrrrrrr=/usr/local/mysql'
2025-10-19T05:14:03.171661Z 0 [ERROR] Aborting
解决方案，复原即可
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
user=mysql
basedirrrrrrrrrrr=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket=/data/mysql/mysql3306/mysql.sock
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket=/data/mysql/mysql3306/mysql.sock
重新启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
ss -untlp
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 2413
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1035,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1035,fd=4))
tcp LISTEN 0 128 [::]:3306 [::]:* users:(("mysqld",pid=2413,fd=32))
[root@centos7 ~]#
关闭
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
2.2.3 配置文件加载错误
mysql可以多实例安装，所以可能存在多个配置文件，如果不指定配置文件，会从默认的四个位置，
mysqld所在目录寻找，总之会启动失败，如果启动成功错误的配置文件，将是灾难性的。
2.2.3.1 实验环境1
启动时不指定配置文件
mysqld &
报错1，
[root@centos7 ~]# mysqld &
[1] 2446
[root@centos7 ~]# 2025-10-19T05:22:27.895630Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-10-19T05:22:27.895750Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T05:22:27.895772Z 0 [Note] mysqld (mysqld 5.7.26) starting as process 2446 ...
2025-10-19T05:22:27.897440Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2025-10-19T05:22:27.897460Z 0 [ERROR] Aborting
2025-10-19T05:22:27.897471Z 0 [Note] Binlog end
2025-10-19T05:22:27.897505Z 0 [Note] mysqld: Shutdown complete
[1]+ Exit 1 mysqld
[root@centos7 ~]#
关键报错
了解如何以root用户运行mysqld
2025-10-19T05:22:27.897440Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2025-10-19T05:22:27.897460Z 0 [ERROR] Aborting
2.2.3.2 实验环境2
上述实验并没有成功演示，缺失配置文件的情况，继续搞个实验环境，mysql用户不带配置文件
su -s /bin/bash mysql
[root@centos7 ~]# su -s /bin/bash mysql
bash-4.2$ whoami
mysql
bash-4.2$
以mysql用户运行，报错如下
mysqld &
bash-4.2$ mysqld &
[1] 2505
bash-4.2$ mysqld: Can't change dir to '/opt/mysql/mysql-5.7.26/data/' (Errcode: 2 - No such file or directory)
2025-10-19T05:34:37.219419Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2025-10-19T05:34:37.219492Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2025-10-19T05:34:37.359419Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-10-19T05:34:37.359461Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T05:34:37.359481Z 0 [Note] mysqld (mysqld 5.7.26) starting as process 2505 ...
2025-10-19T05:34:37.361238Z 0 [Warning] Can't create test file /opt/mysql/mysql-5.7.26/data/centos7.lower-test
2025-10-19T05:34:37.361248Z 0 [Warning] Can't create test file /opt/mysql/mysql-5.7.26/data/centos7.lower-test
2025-10-19T05:34:37.361294Z 0 [ERROR] failed to set datadir to /opt/mysql/mysql-5.7.26/data/
2025-10-19T05:34:37.361303Z 0 [ERROR] Aborting
2025-10-19T05:34:37.361308Z 0 [Note] Binlog end
2025-10-19T05:34:37.361341Z 0 [Note] mysqld: Shutdown complete
[1]+ Exit 1 mysqld
bash-4.2$
关键报错
这里mysql从默认的位置，找不到配置文件，结果以为mysqld 所在文件的data 目录下是数据文件，但实际没有data文件夹，所以会有不能创建文件的错误。
2025-10-19T05:34:37.361238Z 0 [Warning] Can't create test file /opt/mysql/mysql-5.7.26/data/centos7.lower-test
2025-10-19T05:34:37.361248Z 0 [Warning] Can't create test file /opt/mysql/mysql-5.7.26/data/centos7.lower-test
2025-10-19T05:34:37.361294Z 0 [ERROR] failed to set datadir to /opt/mysql/mysql-5.7.26/data/
2025-10-19T05:34:37.361303Z 0 [ERROR] Aborting
2.2.3.3 实验环境3
我们创建data文件夹，并把权限改为mysql ，继续实验看报错
mkdir /usr/local/mysql/data
chown mysql.mysql /usr/local/mysql/data
[root@centos7 ~]# mkdir /usr/local/mysql/data
[root@centos7 ~]# chown mysql.mysql /usr/local/mysql/data
[root@centos7 ~]# ll -d /usr/local/mysql/data
drwxr-xr-x. 2 mysql mysql 6 Oct 19 13:42 /usr/local/mysql/data
[root@centos7 ~]#
重新启动，以mysql用户启动
su -s /bin/bash mysql
mysqld &
报错如下，
[root@centos7 ~]# su -s /bin/bash mysql
bash-4.2$ mysqld &
[1] 2563
bash-4.2$ 2025-10-19T05:44:38.558656Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2025-10-19T05:44:38.558721Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2025-10-19T05:44:38.699702Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-10-19T05:44:38.699745Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T05:44:38.699764Z 0 [Note] mysqld (mysqld 5.7.26) starting as process 2563 ...
2025-10-19T05:44:38.702709Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-10-19T05:44:38.702726Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-10-19T05:44:38.702729Z 0 [Note] InnoDB: Uses event mutexes
2025-10-19T05:44:38.702731Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2025-10-19T05:44:38.702733Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-10-19T05:44:38.702735Z 0 [Note] InnoDB: Using Linux native AIO
2025-10-19T05:44:38.702875Z 0 [Note] InnoDB: Number of pools: 1
2025-10-19T05:44:38.702936Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-10-19T05:44:38.704301Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-10-19T05:44:38.708673Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-10-19T05:44:38.710144Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2025-10-19T05:44:38.720505Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2025-10-19T05:44:38.720724Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
2025-10-19T05:44:38.733144Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
2025-10-19T05:44:38.733430Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2025-10-19T05:44:38.805902Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2025-10-19T05:44:38.848817Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2025-10-19T05:44:38.848872Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-10-19T05:44:38.848882Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2025-10-19T05:44:38.848942Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2025-10-19T05:44:38.858839Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2025-10-19T05:44:38.858963Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2025-10-19T05:44:38.863796Z 0 [Note] InnoDB: Doublewrite buffer created
2025-10-19T05:44:38.868156Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2025-10-19T05:44:38.868173Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2025-10-19T05:44:38.868448Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-10-19T05:44:38.870267Z 0 [Note] InnoDB: Foreign key constraint system tables created
2025-10-19T05:44:38.870333Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2025-10-19T05:44:38.870788Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2025-10-19T05:44:38.870802Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2025-10-19T05:44:38.871093Z 0 [Note] InnoDB: sys_virtual table created
2025-10-19T05:44:38.871574Z 0 [Note] InnoDB: Waiting for purge to start
2025-10-19T05:44:38.922043Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 0
2025-10-19T05:44:38.922391Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2025-10-19T05:44:38.922499Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2025-10-19T05:44:38.922800Z 0 [Note] Salting uuid generator variables, current_pid: 2563, server_start_time: 1760852678, bytes_sent: 0,
2025-10-19T05:44:38.923221Z 0 [Note] Generated uuid: 'b3cb8627-acae-11f0-a0ee-000c29a14265', server_start_time: 721420367070266569, bytes_sent: 73351440
2025-10-19T05:44:38.923233Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b3cb8627-acae-11f0-a0ee-000c29a14265.
2025-10-19T05:44:38.923908Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-10-19T05:44:38.923976Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2025-10-19T05:44:38.924233Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2025-10-19T05:44:38.924949Z 0 [Note] IPv6 is available.
2025-10-19T05:44:38.924961Z 0 [Note] - '::' resolves to '::';
2025-10-19T05:44:38.924989Z 0 [Note] Server socket created on IP: '::'.
2025-10-19T05:44:38.925843Z 0 [Warning] Failed to open optimizer cost constant tables
2025-10-19T05:44:38.925935Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2025-10-19T05:44:38.925946Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2025-10-19T05:44:38.925985Z 0 [ERROR] Aborting
2025-10-19T05:44:38.926004Z 0 [Note] Binlog end
2025-10-19T05:44:38.926035Z 0 [Note] Shutting down plugin 'ngram'
2025-10-19T05:44:38.926041Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2025-10-19T05:44:38.926045Z 0 [Note] Shutting down plugin 'ARCHIVE'
2025-10-19T05:44:38.926046Z 0 [Note] Shutting down plugin 'partition'
2025-10-19T05:44:38.926048Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2025-10-19T05:44:38.926050Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2025-10-19T05:44:38.926051Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2025-10-19T05:44:38.926053Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2025-10-19T05:44:38.926054Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2025-10-19T05:44:38.926056Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2025-10-19T05:44:38.926057Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2025-10-19T05:44:38.926059Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2025-10-19T05:44:38.926060Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2025-10-19T05:44:38.926062Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2025-10-19T05:44:38.926063Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2025-10-19T05:44:38.926064Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2025-10-19T05:44:38.926066Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2025-10-19T05:44:38.926067Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2025-10-19T05:44:38.926069Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2025-10-19T05:44:38.926070Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2025-10-19T05:44:38.926072Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2025-10-19T05:44:38.926073Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2025-10-19T05:44:38.926075Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2025-10-19T05:44:38.926076Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2025-10-19T05:44:38.926077Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2025-10-19T05:44:38.926079Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2025-10-19T05:44:38.926081Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2025-10-19T05:44:38.926082Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2025-10-19T05:44:38.926083Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2025-10-19T05:44:38.926085Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2025-10-19T05:44:38.926086Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2025-10-19T05:44:38.926088Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2025-10-19T05:44:38.926089Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2025-10-19T05:44:38.926091Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2025-10-19T05:44:38.926093Z 0 [Note] Shutting down plugin 'InnoDB'
2025-10-19T05:44:38.926203Z 0 [Note] InnoDB: FTS optimize thread exiting.
2025-10-19T05:44:38.926602Z 0 [Note] InnoDB: Starting shutdown...
2025-10-19T05:44:39.027026Z 0 [Note] InnoDB: Dumping buffer pool(s) to /opt/mysql/mysql-5.7.26/data/ib_buffer_pool
2025-10-19T05:44:39.027317Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 251019 13:44:39
2025-10-19T05:44:40.535687Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1209961
2025-10-19T05:44:40.536883Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2025-10-19T05:44:40.536893Z 0 [Note] Shutting down plugin 'MEMORY'
2025-10-19T05:44:40.536898Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2025-10-19T05:44:40.536935Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2025-10-19T05:44:40.536938Z 0 [Note] Shutting down plugin 'MyISAM'
2025-10-19T05:44:40.536947Z 0 [Note] Shutting down plugin 'CSV'
2025-10-19T05:44:40.536951Z 0 [Note] Shutting down plugin 'sha256_password'
2025-10-19T05:44:40.536952Z 0 [Note] Shutting down plugin 'mysql_native_password'
2025-10-19T05:44:40.537041Z 0 [Note] Shutting down plugin 'binlog'
2025-10-19T05:44:40.537150Z 0 [Note] mysqld: Shutdown complete
[1]+ Exit 1 mysqld
bash-4.2$
关键报错
mysql运行所必须的一些表缺失，无法启动。
mysqld: Table 'mysql.plugin' doesn't exist
2025-10-19T05:44:38.922499Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2025-10-19T05:44:38.922800Z 0 [Note] Salting uuid generator variables, current_pid: 2563, server_start_time: 1760852678, bytes_sent: 0,
2025-10-19T05:44:38.923221Z 0 [Note] Generated uuid: 'b3cb8627-acae-11f0-a0ee-000c29a14265', server_start_time: 721420367070266569, bytes_sent: 73351440
2025-10-19T05:44:38.923233Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b3cb8627-acae-11f0-a0ee-000c29a14265.
2025-10-19T05:44:38.923908Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-10-19T05:44:38.923976Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2025-10-19T05:44:38.924233Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2025-10-19T05:44:38.924949Z 0 [Note] IPv6 is available.
2025-10-19T05:44:38.924961Z 0 [Note] - '::' resolves to '::';
2025-10-19T05:44:38.924989Z 0 [Note] Server socket created on IP: '::'.
2025-10-19T05:44:38.925843Z 0 [Warning] Failed to open optimizer cost constant tables
2025-10-19T05:44:38.925935Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2025-10-19T05:44:38.925946Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2025-10-19T05:44:38.925985Z 0 [ERROR] Aborting
2.2.3.4 解决方案
一定要正确指定配置文件。
2.2.3.5 默认行为
mysql --verbose --help |grep defaults-file
[root@centos7 ~]# mysql --verbose --help |grep defaults-file
--defaults-file=# Only read default options from the given file #.
[root@centos7 ~]#
默认会在以下几个位置寻找配置文件
mysql --help |grep my.cnf
[root@centos7 ~]# mysql --help |grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@centos7 ~]#
2.2.4 端口被占用
实验环境，先启动mysql3306，然后把mysql3307的配置文件中，端口改为3306
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 2605
[root@centos7 ~]#
vim /data/mysql/mysql3307/my3307.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket=/data/mysql/mysql3307/mysql.sock
server_id=2
port=3307
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket=/data/mysql/mysql3307/mysql.sock
server_id=2
port=3306
启动3307，并验证是否执行成功，$? 等于1，说明mysqld启动失败。
mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1035,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1035,fd=4))
tcp LISTEN 0 128 [::]:3306 [::]:* users:(("mysqld",pid=2605,fd=33))
[root@centos7 ~]#
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf
[root@centos7 ~]# echo $?
1
[root@centos7 ~]#
报错如下，从日志中看
more /data/mysql/mysql3307/logs/error.log
[root@centos7 ~]# more /data/mysql/mysql3307/logs/error.log
2025-10-19T05:58:34.315802Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2025-10-19T05:58:34.315886Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T05:58:34.315906Z 0 [Note] mysqld (mysqld 5.7.26-log) starting as process 2666 ...
2025-10-19T05:58:34.319291Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-10-19T05:58:34.319308Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-10-19T05:58:34.319311Z 0 [Note] InnoDB: Uses event mutexes
2025-10-19T05:58:34.319313Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2025-10-19T05:58:34.319315Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-10-19T05:58:34.319317Z 0 [Note] InnoDB: Using Linux native AIO
2025-10-19T05:58:34.319623Z 0 [Note] InnoDB: Number of pools: 1
2025-10-19T05:58:34.319709Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-10-19T05:58:34.320592Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-10-19T05:58:34.325019Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-10-19T05:58:34.326209Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of se
tpriority().
2025-10-19T05:58:34.337597Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2025-10-19T05:58:34.341694Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2025-10-19T05:58:34.341758Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2025-10-19T05:58:34.349602Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2025-10-19T05:58:34.350120Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2025-10-19T05:58:34.350130Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2025-10-19T05:58:34.350642Z 0 [Note] InnoDB: Waiting for purge to start
2025-10-19T05:58:34.401040Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2525079
2025-10-19T05:58:34.401957Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3307/data/ib_buffer_pool
2025-10-19T05:58:34.401984Z 0 [Note] Plugin 'FEDERATED' is disabled.
2025-10-19T05:58:34.405704Z 0 [Note] InnoDB: Buffer pool(s) load completed at 251019 13:58:34
2025-10-19T05:58:34.452278Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and p
rivate key
2025-10-19T05:58:34.452302Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2025-10-19T05:58:34.452349Z 0 [Note] IPv6 is available.
2025-10-19T05:58:34.452356Z 0 [Note] - '::' resolves to '::';
2025-10-19T05:58:34.452370Z 0 [Note] Server socket created on IP: '::'.
2025-10-19T05:58:34.452387Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
2025-10-19T05:58:34.452389Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2025-10-19T05:58:34.452394Z 0 [ERROR] Aborting
2025-10-19T05:58:34.452403Z 0 [Note] Binlog end
2025-10-19T05:58:34.452644Z 0 [Note] Shutting down plugin 'ngram'
2025-10-19T05:58:34.452652Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2025-10-19T05:58:34.452655Z 0 [Note] Shutting down plugin 'ARCHIVE'
2025-10-19T05:58:34.452656Z 0 [Note] Shutting down plugin 'partition'
2025-10-19T05:58:34.452658Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2025-10-19T05:58:34.452660Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2025-10-19T05:58:34.452661Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2025-10-19T05:58:34.452662Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2025-10-19T05:58:34.452663Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2025-10-19T05:58:34.452665Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2025-10-19T05:58:34.452666Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2025-10-19T05:58:34.452667Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2025-10-19T05:58:34.452675Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2025-10-19T05:58:34.452676Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2025-10-19T05:58:34.452678Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2025-10-19T05:58:34.452679Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2025-10-19T05:58:34.452680Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2025-10-19T05:58:34.452682Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2025-10-19T05:58:34.452683Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2025-10-19T05:58:34.452684Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2025-10-19T05:58:34.452685Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2025-10-19T05:58:34.452686Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2025-10-19T05:58:34.452688Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2025-10-19T05:58:34.452689Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2025-10-19T05:58:34.452690Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2025-10-19T05:58:34.452691Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2025-10-19T05:58:34.452693Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2025-10-19T05:58:34.452694Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2025-10-19T05:58:34.452695Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2025-10-19T05:58:34.452696Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2025-10-19T05:58:34.452697Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2025-10-19T05:58:34.452699Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2025-10-19T05:58:34.452700Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2025-10-19T05:58:34.452701Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2025-10-19T05:58:34.452702Z 0 [Note] Shutting down plugin 'InnoDB'
2025-10-19T05:58:34.452823Z 0 [Note] InnoDB: FTS optimize thread exiting.
2025-10-19T05:58:34.452906Z 0 [Note] InnoDB: Starting shutdown...
2025-10-19T05:58:34.553521Z 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3307/data/ib_buffer_pool
2025-10-19T05:58:34.553726Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 251019 13:58:34
2025-10-19T05:58:35.973364Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2525098
2025-10-19T05:58:35.977076Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2025-10-19T05:58:35.977103Z 0 [Note] Shutting down plugin 'MEMORY'
2025-10-19T05:58:35.977117Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2025-10-19T05:58:35.977332Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2025-10-19T05:58:35.977359Z 0 [Note] Shutting down plugin 'MyISAM'
2025-10-19T05:58:35.977402Z 0 [Note] Shutting down plugin 'CSV'
2025-10-19T05:58:35.977412Z 0 [Note] Shutting down plugin 'sha256_password'
2025-10-19T05:58:35.977417Z 0 [Note] Shutting down plugin 'mysql_native_password'
2025-10-19T05:58:35.977628Z 0 [Note] Shutting down plugin 'binlog'
2025-10-19T05:58:35.978637Z 0 [Note] mysqld: Shutdown complete
[root@centos7 ~]#
关键报错
不能启动服务，端口地址已经在使用，你已经有另一个mysql服务在3306端口吗？
2025-10-19T05:58:34.452387Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
2025-10-19T05:58:34.452389Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2025-10-19T05:58:34.452394Z 0 [ERROR] Aborting
解决方案
修改为不冲突的端口
vim /data/mysql/mysql3307/my3307.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3307/data
socket=/data/mysql/mysql3307/mysql.sock
server_id=2
port=3307
重新启动
mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
[1] 2744
[root@centos7 ~]#
关闭
mysqladmin -S /data/mysql/mysql3307/mysql.sock shutdown
2.2.5 二进制日志文件缺失
实验环境，删除一个二进制日志文件，只有删除最新的文件也会导致无法启动
cd /data/mysql/mysql3306/logs
rm mysql-bin.000018 -f
[root@centos7 ~]# cd /data/mysql/mysql3306/logs
[root@centos7 logs]# ll
total 88
-rw-r-----. 1 mysql mysql 7424 Oct 19 14:20 error.log
-rw-r-----. 1 mysql mysql 177 Oct 18 11:51 mysql-bin.000001
-rw-r-----. 1 mysql mysql 154 Oct 18 19:33 mysql-bin.000002
-rw-r-----. 1 mysql mysql 177 Oct 18 19:34 mysql-bin.000003
-rw-r-----. 1 mysql mysql 177 Oct 18 19:35 mysql-bin.000004
-rw-r-----. 1 mysql mysql 177 Oct 18 19:38 mysql-bin.000005
-rw-r-----. 1 mysql mysql 177 Oct 18 20:39 mysql-bin.000006
-rw-r-----. 1 mysql mysql 177 Oct 18 21:17 mysql-bin.000007
-rw-r-----. 1 mysql mysql 177 Oct 18 21:20 mysql-bin.000008
-rw-r-----. 1 mysql mysql 201 Oct 18 22:02 mysql-bin.000009
-rw-r-----. 1 mysql mysql 177 Oct 19 13:06 mysql-bin.000010
-rw-r-----. 1 mysql mysql 177 Oct 19 13:11 mysql-bin.000011
-rw-r-----. 1 mysql mysql 177 Oct 19 13:18 mysql-bin.000012
-rw-r-----. 1 mysql mysql 201 Oct 19 14:07 mysql-bin.000013
-rw-r-----. 1 mysql mysql 177 Oct 19 14:10 mysql-bin.000014
-rw-r-----. 1 mysql mysql 201 Oct 19 14:11 mysql-bin.000015
-rw-r-----. 1 mysql mysql 177 Oct 19 14:14 mysql-bin.000016
-rw-r-----. 1 mysql mysql 177 Oct 19 14:19 mysql-bin.000017
-rw-r-----. 1 mysql mysql 177 Oct 19 14:20 mysql-bin.000018
-rw-r-----. 1 mysql mysql 792 Oct 19 14:19 mysql-bin.index
-rw-r-----. 1 mysql mysql 3261 Oct 19 14:19 slow.log
[root@centos7 logs]#
[root@centos7 logs]# rm mysql-bin.000018
rm: remove regular file ‘mysql-bin.000018’? y
启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[root@centos7 logs]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 5231
[root@centos7 logs]#
[1]+ Exit 1 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
[root@centos7 logs]#
报错如下，
[root@centos7 ~]# more /data/mysql/mysql3306/logs/error.log
2025-10-19T06:24:41.346638Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2025-10-19T06:24:41.346724Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T06:24:41.346745Z 0 [Note] mysqld (mysqld 5.7.26-log) starting as process 5231 ...
2025-10-19T06:24:41.350244Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-10-19T06:24:41.350259Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-10-19T06:24:41.350261Z 0 [Note] InnoDB: Uses event mutexes
2025-10-19T06:24:41.350263Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2025-10-19T06:24:41.350265Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-10-19T06:24:41.350267Z 0 [Note] InnoDB: Using Linux native AIO
2025-10-19T06:24:41.350606Z 0 [Note] InnoDB: Number of pools: 1
2025-10-19T06:24:41.350677Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-10-19T06:24:41.351589Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-10-19T06:24:41.355863Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-10-19T06:24:41.357087Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of se
tpriority().
2025-10-19T06:24:41.368487Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2025-10-19T06:24:41.372813Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2025-10-19T06:24:41.372882Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2025-10-19T06:24:41.379297Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2025-10-19T06:24:41.379759Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2025-10-19T06:24:41.379766Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2025-10-19T06:24:41.380087Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2525441
2025-10-19T06:24:41.380707Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool
2025-10-19T06:24:41.380754Z 0 [Note] Plugin 'FEDERATED' is disabled.
2025-10-19T06:24:41.381561Z 0 [Note] InnoDB: Buffer pool(s) load completed at 251019 14:24:41
mysqld: File '/data/mysql/mysql3306/logs/mysql-bin.000018' not found (Errcode: 2 - No such file or directory)
2025-10-19T06:24:41.382779Z 0 [ERROR] Failed to open log (file '/data/mysql/mysql3306/logs/mysql-bin.000018', errno 2)
2025-10-19T06:24:41.382787Z 0 [ERROR] Could not open log file
2025-10-19T06:24:41.382791Z 0 [ERROR] Can't init tc log
2025-10-19T06:24:41.382794Z 0 [ERROR] Aborting
2025-10-19T06:24:41.382798Z 0 [Note] Binlog end
2025-10-19T06:24:41.382882Z 0 [Note] Shutting down plugin 'ngram'
2025-10-19T06:24:41.382889Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2025-10-19T06:24:41.382892Z 0 [Note] Shutting down plugin 'ARCHIVE'
2025-10-19T06:24:41.382893Z 0 [Note] Shutting down plugin 'partition'
2025-10-19T06:24:41.382894Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2025-10-19T06:24:41.382896Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2025-10-19T06:24:41.382897Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2025-10-19T06:24:41.382898Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2025-10-19T06:24:41.382900Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2025-10-19T06:24:41.382901Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2025-10-19T06:24:41.382902Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2025-10-19T06:24:41.382903Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2025-10-19T06:24:41.382904Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2025-10-19T06:24:41.382905Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2025-10-19T06:24:41.382907Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2025-10-19T06:24:41.382908Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2025-10-19T06:24:41.382909Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2025-10-19T06:24:41.382916Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2025-10-19T06:24:41.382917Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2025-10-19T06:24:41.382918Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2025-10-19T06:24:41.382919Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2025-10-19T06:24:41.382921Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2025-10-19T06:24:41.382922Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2025-10-19T06:24:41.382923Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2025-10-19T06:24:41.382924Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2025-10-19T06:24:41.382926Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2025-10-19T06:24:41.382927Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2025-10-19T06:24:41.382928Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2025-10-19T06:24:41.382929Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2025-10-19T06:24:41.382930Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2025-10-19T06:24:41.382932Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2025-10-19T06:24:41.382933Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2025-10-19T06:24:41.382934Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2025-10-19T06:24:41.382935Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2025-10-19T06:24:41.382937Z 0 [Note] Shutting down plugin 'InnoDB'
2025-10-19T06:24:41.383028Z 0 [Note] InnoDB: FTS optimize thread exiting.
2025-10-19T06:24:41.383097Z 0 [Note] InnoDB: Starting shutdown...
2025-10-19T06:24:41.484103Z 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql3306/data/ib_buffer_pool
2025-10-19T06:24:41.485016Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 251019 14:24:41
2025-10-19T06:24:42.995296Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2525460
2025-10-19T06:24:42.996513Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2025-10-19T06:24:42.996525Z 0 [Note] Shutting down plugin 'MEMORY'
2025-10-19T06:24:42.996531Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2025-10-19T06:24:42.996588Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2025-10-19T06:24:42.996593Z 0 [Note] Shutting down plugin 'MyISAM'
2025-10-19T06:24:42.996602Z 0 [Note] Shutting down plugin 'CSV'
2025-10-19T06:24:42.996606Z 0 [Note] Shutting down plugin 'sha256_password'
2025-10-19T06:24:42.996608Z 0 [Note] Shutting down plugin 'mysql_native_password'
2025-10-19T06:24:42.996691Z 0 [Note] Shutting down plugin 'binlog'
2025-10-19T06:24:42.997023Z 0 [Note] mysqld: Shutdown complete
[root@centos7 ~]#
关键报错
打开 /data/mysql/mysql3306/logs/mysql-bin.000018 这个文件失败。
mysqld: File '/data/mysql/mysql3306/logs/mysql-bin.000018' not found (Errcode: 2 - No such file or directory)
2025-10-19T06:24:41.382779Z 0 [ERROR] Failed to open log (file '/data/mysql/mysql3306/logs/mysql-bin.000018', errno 2)
2025-10-19T06:24:41.382787Z 0 [ERROR] Could not open log file
2025-10-19T06:24:41.382791Z 0 [ERROR] Can't init tc log
2025-10-19T06:24:41.382794Z 0 [ERROR] Aborting
解决方案
在mysql-bin.index 同步删除删除的文件
vim /data/mysql/mysql3306/logs/mysql-bin.index
重新启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
关闭
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
删除二进制文件的解决方案
如果二进制日志文件，占用空间过大，确实需要删除，在配置文件中，日志文件的过期时间改小一点
vim /data/mysql/mysql3306/my3306.cnf
expire_logs_days=7
2.2.6 修改共享表空间导致mysql启动失败
实验环境，sql中，查看共享表空间的大小，然后在配置文件中添加配置，超过共享表空间的大小，关闭mysql后重启
show variables like "%data_file%";
mysql> show variables like "%data_file%";
+----------------------------+------------------------+
| Variable_name | Value |
+----------------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+------------------------+
2 rows in set (0.00 sec)
mysql>
cat >> /data/mysql/mysql3306/my3306.cnf <<EOL
innodb_data_file_path=ibdata1:128M:autoextend
EOL
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 5097
[root@centos7 ~]#
[1]+ Exit 1 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
[root@centos7 ~]#
报错
[root@centos7 ~]# more /data/mysql/mysql3306/logs/error.log
2025-10-19T06:15:03.125918Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2025-10-19T06:15:03.126002Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2025-10-19T06:15:03.126022Z 0 [Note] mysqld (mysqld 5.7.26-log) starting as process 5112 ...
2025-10-19T06:15:03.129247Z 0 [Note] InnoDB: PUNCH HOLE support available
2025-10-19T06:15:03.129263Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2025-10-19T06:15:03.129266Z 0 [Note] InnoDB: Uses event mutexes
2025-10-19T06:15:03.129288Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2025-10-19T06:15:03.129295Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-10-19T06:15:03.129298Z 0 [Note] InnoDB: Using Linux native AIO
2025-10-19T06:15:03.129618Z 0 [Note] InnoDB: Number of pools: 1
2025-10-19T06:15:03.129682Z 0 [Note] InnoDB: Using CPU crc32 instructions
2025-10-19T06:15:03.130639Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2025-10-19T06:15:03.134954Z 0 [Note] InnoDB: Completed initialization of buffer pool
2025-10-19T06:15:03.136120Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of se
tpriority().
2025-10-19T06:15:03.146319Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) t
han specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
2025-10-19T06:15:03.146344Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2025-10-19T06:15:03.748375Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2025-10-19T06:15:03.748405Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2025-10-19T06:15:03.748409Z 0 [ERROR] Failed to initialize builtin plugins.
2025-10-19T06:15:03.748416Z 0 [ERROR] Aborting
2025-10-19T06:15:03.748423Z 0 [Note] Binlog end
2025-10-19T06:15:03.748472Z 0 [Note] Shutting down plugin 'MyISAM'
2025-10-19T06:15:03.748486Z 0 [Note] Shutting down plugin 'CSV'
2025-10-19T06:15:03.748675Z 0 [Note] mysqld: Shutdown complete
[root@centos7 ~]#
关键报错
MySQL 配置文件 .cnf 中定义的 InnoDB 系统表空间初始大小（innodb_data_file_path 或 innodb_data_file_path=ibdata1:8M:autoextend）和现有的 ibdata1 文件实际大小不匹配。
2025-10-19T06:15:03.146319Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) t
han specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
2025-10-19T06:15:03.146344Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2025-10-19T06:15:03.748375Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2025-10-19T06:15:03.748405Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2025-10-19T06:15:03.748409Z 0 [ERROR] Failed to initialize builtin plugins.
2025-10-19T06:15:03.748416Z 0 [ERROR] Aborting
解决方案
修改配置文件中的大小，小于等于，sql 中查到的值
vim /data/mysql/mysql3306/my3306.cnf
innodb_data_file_path=ibdata1:11M:autoextend
重新启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 5128
[root@centos7 ~]#
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1035,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1035,fd=4))
tcp LISTEN 0 128 [::]:3306 [::]:* users:(("mysqld",pid=5128,fd=33))
[root@centos7 ~]#
关闭
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
2.3 排错方法
- 明确数据库版本、启动方式、配置文件
- 找错误日志，
error.log
2.4 登录数据库方式
创建一个普通用户
create user qiankong@'%' identified by '123456';
mysql> create user qiankong@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| qiankong | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
mysql>
2.4.1 本地登录
mysql -S /data/mysql/mysql3306/mysql.sock -u qiankong -p
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock -uqiankong -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
2.4.2 远程登录
mysql -u qiankong -P3306 -h 192.168.10.157 -p
[root@centos7 ~]# mysql -u qiankong -P3306 -h 192.168.10.157 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
三、用户安全策略及忘记密码
3.1 用户安全策略
3.1.1 初始化时不设置密码
先停止数据库，清空 data、和logs 目录
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
rm -rf /data/mysql/mysql3306/{data,logs}/*
再重新初始化
/usr/local/mysql/bin/mysqld \
--defaults-file=/data/mysql/mysql3306/my3306.cnf \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/data/mysql/mysql3306/data
启动并验证，确实不需要密码
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
mysql -S /data/mysql/mysql3306/mysql.sock
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 5452
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
3.1.2 初始化时设置密码
先停止数据库，清空 data、和logs 目录
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown
rm -rf /data/mysql/mysql3306/{data,logs}/*
再重新初始化，重点在--initialize
/usr/local/mysql/bin/mysqld \
--defaults-file=/data/mysql/mysql3306/my3306.cnf \
--initialize \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/data/mysql/mysql3306/data
查看密码，密码为 my<rq;8jE=*A
more /data/mysql/mysql3306/logs/error.log
[root@centos7 ~]# more /data/mysql/mysql3306/logs/error.log
2025-10-19T08:52:12.552536Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
100
100
2025-10-19T08:52:12.757227Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-10-19T08:52:12.772763Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-10-19T08:52:12.827799Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generat
ing a new UUID: e7a515a1-acc8-11f0-87d1-000c29a14265.
2025-10-19T08:52:12.830473Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-10-19T08:52:12.831245Z 1 [Note] A temporary password is generated for root@localhost: my<rq;8jE=*A
[root@centos7 ~]#
启动后验证密码
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
mysql -S /data/mysql/mysql3306/mysql.sock
# 验证需要密码
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 需要正确的密码
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
3.1.3 修改密码的方式
主要有两种方式修改密码，
3.1.3.1 sql
通过 SQL 语句来修改密码，登录时已经输入密码，所以不需要验证旧密码
alter user root@'localhost' identified by '新密码';
alter user root@'localhost' identified by '123';
3.1.3.2 mysqladmin
通过 mysqladmin 来修改密码，需要验证旧密码
mysqladmin -uroot -p'旧密码' password '新密码' -S /mysql.sock
mysqladmin -S /data/mysql/mysql3306/mysql.sock -uroot -p'123' password '123456'
[root@centos7 ~]# mysqladmin -S /data/mysql/mysql3306/mysql.sock -uroot -p'123' password '123456'
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@centos7 ~]#
3.1.4 创建新的超级管理员用户
create user 用户名@'localhost' identified by '密码';
grant all privileges on *.* to 用户名@'localhost' with grant option;
create user qiankong@'localhost' identified by '123456';
grant all privileges on *.* to qiankong@'localhost' with grant option;
mysql> create user qiankong@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to qiankong@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql>
3.1.5 删除无用用户
查看所有的用户
select user,host from mysql.user;
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| qiankong | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
删除无用的用户，并立即生效**【不推荐这种删除方式】**
delete from mysql.user where user !='用户名';
flush privileges;
delete from mysql.user where user !='qiankong';
flush privileges;
mysql> delete from mysql.user where user !='qiankong';
Query OK, 3 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
3.2 忘记密码
- 关闭数据库
pkill mysqld
- 启动数据库时，加个参数
--skip-grant-tables，跳过授权表的验证
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --skip-grant-tables &
- 登录
mysql -S /data/mysql/mysql3306/mysql.sock
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
- 刷新权限
flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
mysql>
- 修改密码
alter user root@'localhost' identified by '123456';
alter user root@'localhost' identified by '123456';
- 关闭数据库，新的关闭方式，关闭后直接quit，然后就发现数据库已经被关闭。
shutdown;
- 重新启动
mysqld进程，不需要加--skip-grant-tables参数
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
- 验证新密码
mysql -S /data/mysql/mysql3306/mysql.sock -p
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 可以看到必须要密码
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
四、字符集及用户管理
4.1 MySQL字符集和校验规则
4.1.1 概念
字符集，对符号进行编码、排序的规则，常见的字符集有 utf-8，GBK 等
校验规则，排序比较的规则，例如是否区分大小写
4.1.2 查看方法
- MySQL支持的字符集，共有41种。
show character set;
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
mysql>
- 查看字符集的校对规则，共有222种，一种字符集可对应几种校对规则
show collation;
主要可关注 GBK、UTF8、UTF8MB4的校对规则。
...
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
...
gbk_chinese_ci不区分大小写gbk_bin区分大小写
- 查看当前数据库的字符集
法一，简略信息
\s
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.26-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /data/mysql/mysql3306/mysql.sock
Uptime: 2 hours 54 min 59 sec
Threads: 1 Questions: 21 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.002
--------------
mysql>
法二，详细信息
show variables like 'character%';
mysql> show variables like 'character%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_client | utf8 |# 客户端请求数据的字符集
| character_set_connection | utf8 |# 客户端与服务端连接的字符集
| character_set_database | latin1 |# 数据库的字符集
| character_set_filesystem | binary |# 文件系统的文件名不做任何转换
| character_set_results | utf8 |# 查询结果的字符集
| character_set_server | latin1 |# 服务端的字符集
| character_set_system | utf8 |# 操作系统的字符集
| character_sets_dir | /opt/mysql/mysql-5.7.26/share/charsets/ |# 字符集的目录
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)
mysql>
- 查看当前数据库的校对规则
show variables like 'collation%';
mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql>
4.1.3 字符集的设置
主要有两种方式，一种是临时设置（重启数据库修改失效），一种是永久设置（修改配置文件后重启数据库）。
而不同设置之间也有层级关系：
character_set_server > character_database > table
character_connection > character_set_results
4.1.3.1 临时设置服务端、客户端字符集
修改服务端的字符集
本质是修改环境变量。当前会话生效
set character_set_server=utf8;
对所有会话生效，针对新开的会话。
set global character_set_server=utf8;
修改客户端的字符集
set name gbk;
=
SET character_set_client = gbk;
SET character_set_connection = gbk;
SET character_set_results = gbk;
4.1.3.2 永久设置服务端、客户端字符集
修改配置文件，可以设置在不同的区块下
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
character_set_server=utf8
[client]
default_character_set=utf8
4.1.3.3 设置创建对象的字符集
数据库
create database 数据库名 character set 字符集;
create database d1 character set utf8mb4;
查看数据库的创建语句。
show create database 数据库名;
show create database d1;
表
create table 表名(字段1 字段类型, 字段2 字段类型)engine=存储引擎 default charset=字符集;
create table t1(id int)engine=innodb default charset=utf8;
查看表的创建语句。
show create table 表名;
show create table t1;
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
4.1.4 关于字符集的常识
| 对比 | utf8mb4 | utf8 | gbk |
|---|---|---|---|
| 一个汉字占用空间 | 3-4字节(常用汉字3字节) | 3字节 | 2字节 |
| 是否支持表情 | 是 | 否 | 否 |
| 英文占用空间 | 1字节 | 1字节 | 1字节 |
utf8mb4 是 完全版的 utf8
utf8 是 阉割版的 utf8mb4
4.1.5 彻底避免乱码
服务端配置/data/mysql/mysql3306/my3306.cnf
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
character_set_server=utf8
[client]
default_character_set=utf8
客户端配置 /etc/locate.conf
vim /etc/locate.conf
echo $LANG
[root@centos7 etc]# echo $LANG
en_US.UTF-8
[root@centos7 etc]#
额外发现
语言，地区，编码集
设置后，可以优先中文显示man文档。没有中午文档的，还是会显示英文。
4.2 用户管理
主要涉及用户的增删改查。
4.2.1 增加用户
create user 用户名@'%' identified by '设置的密码';
create user qiankong@'localhost' identified by '123';
create user qiankong@'%' identified by '123';
mysql> create user qiankong@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create user qiankong@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql>
4.2.2 删除用户
drop user 用户名@'%';
drop user qiankong@'localhost';
# 删除用户前
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| qiankong | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| qiankong | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
# 删除用户
mysql> drop user qiankong@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 删除用户后
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| qiankong | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql>
4.2.3 修改用户
修改用户名/重命名
法一，通过 DCL 语句
rename user 旧用户名@'%' to 新用户名@'%'
rename user qiankong@'%' to qiankong666@'%';
# 修改用户名前
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| qiankong | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
# 修改用户m
mysql> rename user qiankong@'%' to qiankong666@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| qiankong666 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql>
法二，通过DML语句，这种手动修改表的情况需要flush privileges;
update mysql.user set user='新用户名' where user='旧用户名' and host='%';
update mysql.user set user='qiankong666' where user='qiankong' and host='%';
注意：DML 语句修改用户后，需要使用flush privileges 刷新权限，否则确实无法使用新用户名登录。
原理：DML 语句会将修改写入磁盘，而MySQL 运行在内存中，默认不会重新从硬盘加载数据；而使用DCL 语句MySQL 就会从磁盘中重新加载。
修改用户密码
法一，通过sql 语句修改
alter user 用户名@'%' identified by '新密码';
alter user qiankong@'%' identified by '123456';
法二，通过mysqladmn 修改密码，此种方式需要知道旧密码。
mysqladmin -u用户名 -p'旧密码' -S sock文件 password '新密码';
mysqladmin -uqiankong -p'123456' -S /data/mysql/mysql3306/mysql.sock password '123';
[root@centos7 ~]# mysqladmin -uqiankong -p'123456' -S /data/mysql/mysql3306/mysql.sock password '123';
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@centos7 ~]#
警告，在命令行中使用密码可能不安全。
4.2.4 查询用户
- 查询当前用户
select current_user();
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
- 查询所有的用户
select user,host from mysql.user;
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| qiankong | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql>
4.3 权限管理
4.3.1 授权
grant 权限 on 数据库.表 to 用户@'主机名';
grant show databases on *.* to qiankong@'%';
注意：这里针对的新的连接，已有的连接，还是没有对应的权限。
4.3.2 收回权限
revoke 权限 on 数据库.表 from 用户@'主机名';
revoke show databases on *.* from qiankong@'%';
注意：这里针对的新的连接，已有的连接，还拥有相应的权限。
4.3.3 查询权限
- 查询某个用户的权限
show grants for 用户@'%';
show grants for qiankong@'%';
mysql> show grants for qiankong@'%';
+-------------------------------------------------------+
| Grants for qiankong@% |
+-------------------------------------------------------+
| GRANT SELECT, SHOW DATABASES ON *.* TO `qiankong`@`%` |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
默认权限
mysql> show grants for qiankong@'%';
+--------------------------------------+
| Grants for qiankong@% |
+--------------------------------------+
| GRANT USAGE ON *.* TO `qiankong`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>
USAGE仅仅可以连接，但是不能进行任何操作。
- 查询
MySQL中有哪些权限。
desc mysql.user\G
结尾带 _priv 的字段都是权限。
4.4 用户权限案例
4.4.1 清除连接
有时候删除用户后，但是连接还在，这些连接还有权限做一些操作。
环境准备
create user qiankong@'192.168.10.%' identified by '123456';
grant all on *.* to qiankong@'192.168.10.%';
客户端1登录
[root@centos7 ~]# mysql -uqiankong -P3306 -p -h 192.168.10.157
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 |
| d1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.07 sec)
mysql>
客户端2登录
[root@centos7 ~]# mysql -uqiankong -P3306 -p -h 192.168.10.157
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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 |
| d1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
开始实验
- 查看连接
show processlist;
mysql> show processlist;
+----+----------+--------------------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+--------------------------------+------+---------+------+----------+------------------+
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | qiankong | centos7.server.templaate:35374 | NULL | Sleep | 329 | | NULL |
| 6 | qiankong | 192.168.10.180:47226 | NULL | Sleep | 216 | | NULL |
+----+----------+--------------------------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
mysql>
- 杀掉某个连接；
kill 连接ID;
kill 5;
连接ID为5 已经不能使用。
[root@centos7 ~]# mysql -uqiankong -P3306 -p -h 192.168.10.157
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 |
| d1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.07 sec)
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
连接ID为6，还可以正常查询
[root@centos7 ~]# mysql -uqiankong -P3306 -p -h 192.168.10.157
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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 |
| d1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| d1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
如果连接很多的话，可以先把连接找出来，导出成sql语句，然后执行sql语句。
- 查看并开启，开启文件的导出导入权限
mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
关闭
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown -p
配置文件中开启
# vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
secure_file_priv="/tmp/"
重新启动后，查询
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 1432
[root@centos7 ~]# mysql -uroot -p -S /data/mysql/mysql3306/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 variables like 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
- 拼接
kill和 连接ID，到一个文件中
select user,id from information_schema.processlist where user = "qiankong";
select concat("kill ",id,";") from information_schema.processlist where user = "qiankong";
select concat("kill ",id,";") from information_schema.processlist where user = "qiankong" into outfile "/tmp/kill.sql";
- 指定导出的
sql文件指定，这里不能双引号。
source /tmp/kill.sql;
4.4.2 忘记密码
关闭 mysqld 服务
pkill mysqld
跳过权限表，跳过网络登录（更加安全）,此时使用 ss 验证端口就不够准确。
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --skip-grant-tables --skip-networking &
[root@centos7 ~]# mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --skip-grant-tables --skip-networking &
[1] 1478
[root@centos7 ~]# ss -untlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:22 *:* users:(("sshd",pid=1037,fd=3))
tcp LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1037,fd=4))
[root@centos7 ~]# ps aux |grep mysqld
mysql 1478 2.3 8.9 1151452 181848 pts/0 Sl 21:15 0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --skip-grant-tables --skip-networking
root 1508 0.0 0.0 112808 968 pts/0 S+ 21:15 0:00 grep --color=auto mysqld
[root@centos7 ~]#
登陆后，修改密码
mysql -S /data/mysql/mysql3306/mysql.sock
[root@centos7 ~]# mysql -S /data/mysql/mysql3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
刷新权限后，修改密码
flush privileges;
alter user root@'localhost' identified by 'Root21..';
如果不刷新权限会报错。
mysql> alter user root@'localhost' identified by 'Root21..';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
关闭后重启即可。
mysqladmin -S /data/mysql/mysql3306/mysql.sock shutdown -p
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
mysql -uroot -p -S /data/mysql/mysql3306/mysql.sock
4.4.3 显示某个ip登录
目前的账号
select user,host from mysql.user;
mysql> select user,host from mysql.user;
+----------+--------------+
| user | host |
+----------+--------------+
| qiankong | 192.168.10.% |
| root | localhost |
+----------+--------------+
2 rows in set (0.00 sec)
mysql>
可以创建一个精确匹配的账号。
create user qiankong@'192.168.10.180' identified by 'askjdhasjdgasudasd';
此时用户使用旧密码就无法登录了。
[root@centos7 ~]# hostname -I
192.168.10.180
[root@centos7 ~]# mysql -uqiankong -P3306 -p -h 192.168.10.157
Enter password:
ERROR 1045 (28000): Access denied for user 'qiankong'@'192.168.10.180' (using password: YES)
[root@centos7 ~]#
4.4.4 创建开发账号
开发账号一般使用增删改查的权限。select，insert ，update，delete
create user dev@'192.168.10.%' identified by '123456';
grant select,update,insert,delete on *.* to dev@'192.168.10.%';
4.4.5 创建复制账号
复制权限，主从复制的时候经常会遇到。replication slave
create user copy@'192.168.10.%' identified by '123456';
grant replication slave on *.* to copy@'192.168.10.%';
4.4.6 创建管理员账号
管理还可以进一步的权限授权，all privileges with grant option
create user admin@'192.168.10.%' identified by '123456';
grant all privileges on *.* to admin@'192.168.10.%' with grant option;
或，下面的写法更简洁，但是上面的写法更加标准
grant all on *.* to admin@'192.168.10.%' with grant option;
五、封面图
字数太多了，先截至了。
- 0
- 0
-
分享