526互联

MySQL 数据目录

发布时间 2023-12-27 15:51:44作者: 小林当

MySQL 的数据目录

1. MySQL 的主要目录结构

  • 方式1：通过命令搜索
find / -name mysql
  • 方式2（推荐）：通过查看配置文件获取目录结构
vim /etc/my.cnf
  • （重要）- my.cnf 配置信息说明
[mysqld]
bind-address=0.0.0.0 
port=3306 #　端口号
user=mysql　# 用户
basedir=/usr/local/mysql #指定的是mysql 安装目录
datadir=/data/mysql #指定的是mysql数据目录
socket=/tmp/mysql.sock #mysql端口存放文件
log-error=/data/mysql/mysql_err.log # mysql 错误日志文件
pid-file=/data/mysql/mysql.pid # mysql 进程文件
#skip_ssl #关闭SSL加密连接
character_set_server=utf8mb4 # 指定数据服务字符集
symbolic-links=0
explicit_defaults_for_timestamp=true
lower_case_table_names=1 # mysql在windows下，默认是对表名大小写不敏感的，但是在[linux下，一些系统需要手动设置
#skip-grant-tables #免密码登陆
  • basedir：指定的是 mysql 安装目录
  • datadir：指定的是 mysql 数据目录
  • socket：mysql端口存放文件
  • log-error=/data/mysql/mysql_err.log：mysql 错误日志文件
  • character_set_server=utf8mb4 ：指定数据服务字符集
  • skip_ssl : 关闭SSL加密连接
  • lower_case_table_names=1 : mysql在windows下，默认是对表名大小写不敏感的，但是在[linux下，一些系统需要手动设置。
  • skip-grant-tables ：免密码登陆 （用户修改账号密码，上面文件没该配置）

1.1 数据库文件的存放路径 -（数据目录）

MySQL 服务器程序在启动是会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为为数据目录（对应 数据库的库，表等文件）。

MySQL 数据库文件的存放路径：（默认为：/var/lib/mysql）

其实数据目录对应着一个系统变量 datadir（该变量是全局系统变量），我们可以通过该系统变量查询 数据目录 的地址：

SELECT @@global.datadir;
# 或
SHOW GLOBAL VARIABLES LIKE '%datadir%';

1.2 相关命令目录 - （安装目录）

  • 方式1：通过命令搜索
find / -name "mysqladmin"
find / -name "mysqldump"
  • 方式2（推荐）：查询数据库系统变量 basedir（该变量是全局系统变量）
SELECT @@global.basedir;
# 或 
SHOW GLOBAL VARIABLES LIKE '%basedir%';

安装目录 下非常主要的 bin 目录，它里边放着许多关于控制客户端和服务器程序的命令。许多可执行文件，比如：

  • mysql：客户端（登录）命令
  • mysqld：启动数据库实例命令
  • mysqladmin：修改数据库用户密码命令
  • mysqldump：备份/导出 数据库命令

等...

1.2.1 mysqld服务和mysql服务启动程序

  • mysqld
1. mysqld就是mysql server，负责管理对mysql数据的访问。且 mysql server 有一套系统变量。
2. 当mysql server启动后，它会监听来自客户端的网络连接，并代表客户端管理数据库连接。
3. 可以影响mysql运行时的操作，系统变量可以在mysql server启动时设置，很多系统变量可以在mysql server运行时改变，用于动态影响服务器配置。
4. 用于显示mysql运行信息，包括运行性能特征。
5. mysql server 有一套命令参数。shell> `mysqld --verbose --help ``#``可以获取所有的命令参数

  • mysqld_safe

    mysql server 启动脚本。

  • mysql.server

    mysql server 启动脚本。

  • mysqld_multi

    mysql server启动脚本

1.2.2 mysql客户端命令

  • mysql：mysql客户端登录或数据导入命令
  • mysqldump：mysql数据导出命令
  • mysqlcheck：检查、修复、分析以及优化表的表维护
  • mysqladmin:用来检索版本、进程、以及服务器的状态信息
一、MySQL服务器端工具
1.mysqld：SQL后台保护程序(MySQL服务器进程)。该程序必须运行之后。客户端才能通过连接服务器端程序访问和操作数据库
2.mysqld_safe：MySQL服务脚本。mysql_safe增加了一些安全特性，如当出现错误时重启服务器，向错误日志文件写入运行时间信息
3.mysql.server：MySQL服务启动服本。调用mysqld_safe来启动MySQL服务
4.mysql_multi：服务器启动脚本，可以启动或停止系统上安装的多个服务
5.myiasmchk：用来描述、检查、优化和维护MyISAM表的实用工具
6.mysqlbug：MySQL缺陷报告脚本。它可以用来向MySQL邮件系统发送缺陷报告
7.mysql_install_db：用于默认权限创建MySQ授权表。通常只是在系统上首次安装MySQL时执行一次
    clear
一、MySQL客户端工具
1.myisampack：压缩MyISAP表，产生更小的只读表
2.mysql：交互式输入SQL语句或从文件以批处理模式执行SQL语句来操作数据库管理系统
3.mysaqlaccess：检查访问主机名、用户名和数据库组合的权限
4.mysqladmin：用来检索版本、进程、以及服务器的状态信息
5.mysqlbinlog：用于从二进制日志读取语句。在二进制日志文件中包含执行的语句，可用来帮助系统从崩溃中恢复
6.mysqlcheck：检查、修复、分析以及优化表的表维护
7.mysqldump：将MySQL数据库转储到一个文件
8.mysqlhotcopy：当服务器在运行时，快速备份MyISAM或ISAM表的工具
9.mysql import：使用load data infile将文本文件导入相关表的客户程序
10.perror：显示系统或MySQL错误代码含义的工具

1.3 配置文件目录 -（配置目录）

  1. /etc/my.cnf

  2. 还有一个配置文件一般在 share 目录下：可通过 find 搜索命令查找

find / -name mysql

2. 数据库和文件系统的关系

像 InnoDB，MyISAM 这样的存储引擎都是把表存储在磁盘上的，操作系统用来管理磁盘的结构被称为文件系统，换句话来说就是：InnoDB，MyISAM这样的存储引擎都是把表存储在文件系统上的。

  • 读数据：当我们想读数据的时候，存储引擎会从文件系统中把数据读出来返回给我们。
  • 写数据：当我们想写数据的时候，存储引擎会把这些数据写入到文件系统中。

个人理解：文件系统就是存放数据的系统。而数据是通过存储引擎读写到文件系统中的。所以存储引擎就是 "笔"，文件系统就是 "纸"

2.1 查看默认数据库

查看当前数据库实例中都有哪些数据库：

SHOW DATABASES;
# 结果集
information_schema
mysql
performance_schema
sys

可以看到有4个数据库是属于MySQL自带的系统数据。

mysql

​ MySQL 系统自带的核心数据库，它存储了 MySQL 的用户账户和权限信息，一些存储过程，事件的定义信息，一些运行过程中的产生的日志信息，一些帮助信息以及时区信息等。

information_schema

​ MySQL 系统自带的数据库，这个数据保库存着 MySQL 服务器 维护的所有其他数据库的信息，比如有哪些表，哪些视图，哪些触发器，哪些列，哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，又称为元数据。该库中该提供了一些以innodb_sys开头的表，用于表示内部系统表。

performance_schema

​ MySQL 系统自带的数据库，这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，可以用来 监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况信息。

sys

​ MySQL 系统自带的数据库，这个数据库主要是通过 "视图（view）" 的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

2.2 数据库在文件系统中的表示

当我们使用 "create database 数据库名" 语句创建一个数据库的时候，在 "文件系统" 上实际发生了什么呢？（两件事）

  1. 会在数据库服务对应的 "数据目录" 下创建一个和数据库名同名的子目录。
  2. 在与该数据库名同名的子目录下创建一个名为 "db.opt" 的文件（仅限 5.7 及之前的版本），这文件中包含了数据库的各种属性。

2.3 表在文件系统中的表示

数据其实都是以 "记录的形似" 插入到表中的，每个表的信息其实可以分为两种：

  • 表结构的定义（".frm"）
  • 表中的数据（".ibd"）

表结构就是:（ddl）定义语句。包含了，表名，列，列数据类型，约束等...

2.3.1 InnoDB 存储引擎模式

2.3.1.1 文件系统中的数据库文件中文件的表示：

5.7.xx

  • db.opt：用来存储该数据库的信息，例如：字符集，比较规则，存储引擎等其他信息。
  • xx.frm：用来存储 xx表结构信息的，例如，表名，哪些列，列的数据类型，约束，存储引擎等其他信息。
  • xx.ibd：用来存储 xx表的数据的（.ibd 又称为：独立表空间）。但表中的数据也有可能会存储在（系统表空间中：ibdatal）

8.0.xx

  • xx.ibd：用来存储 xx表的数据以及表结果信息的，相比 5.7.xx 来说，8.0 将 ".frm" 和 ".ibd" 合并在了一起（.ibd 又称为：独立表空间）。但表中的数据也有可能会存储在（系统表空间中：ibdatal）
2.3.1.2 表中数据和索引

....哈哈哈现在还不懂...

2.3.1.3 系统表空间
  • 默认情况下，"InnoDB" 会在 "数据目录" 下创建一个名为 ""ibdata1""，大小为 ""12M"" 的文件，该文件就对应着 "系统表空间"（该文件是自扩展文件）。
  • 修改系统表空间配置（my.cnf / ny.ini）：
[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend

配置说明：会创建这两个512M大小的文件作为 "系统表空间"，其中 "autoextend" 表示空间不足后自动扩展那个文件。

注意：在一个 MySQL服务器中，"系统表空间"只有一份。从 5.5.7 ~ 5.6.6 之间的版本，默认将表中的数据存储到 "系统表空间" 中。

2.3.1.4 系统表空间
  • 独立表空间：就是 为每一个表建立一个独立存储空间。有多少张表，就有多少个独立表空间。
  • 使用 "独立表空间" 来存储表数据的话，会在该表所属数据库对应的数据目录中创建一个表示 "独立表空间" 的文件，文件名和表名相同，扩展名为 ".ibd"
表名.ibd

"表名.ibd" 文件主要是用来存储 "表名"中的数据和索引。

8.0 之后是用来存储 "表"中的表结构信息和数据以及索引。

2.3.1.5 （系统 / 独立）表空间查看及设置

**我们可以通过设置 innodb_file_per_table 来指定使用的是 系统表空间 还是独立表空间。 **

  • 查看表空间设置 - （该系统变量是全局系统变量）
SELECT @@global.innodb_file_per_table;
# 或
SHOW  VARIABLES LIKE '%innodb_file_per_table%';
# 0：表示使用系统表空间，1：表示使用独立表空间
  • 表空间设置
  1. 方式1：通过配置文件设置（my.cnf / my.ini）。该方式需要重启服务才生效
[server]
innodb_file_per_table = 0;  # 0：表示使用系统表空间，1：表示使用独立表空间
  1. 方式2：通过修改系统变量的方式。该方式重启服务后会失效
SET innodb_file_per_table = 0; # 0：表示使用系统表空间，1：表示使用独立表空间
# 或
SET @@global.innodb_file_per_table = 0;
# 或
SET GLOBAL innodb_file_per_table = 0;

注意：innodb_file_per_table 参数的修改 只对新建的表起作用，对于已经分配了表空间的表并不起作用。

  • 如果我们想把已经存在 "系统表空间"-(表转移到)-> "独立表空间"中，可以使用以下语法：
ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;
  • 或者把已经存在 "独立表空间"-(表转移到)-> "系统表空间"中，可以使用以下语法：
ALTER TABLE 表名 TABLESPACE [=] innpdb_system;

2.3.2 MyISAM 存储引擎模式

2.3.2.1 表结构

存储表结构和 InnoDB 和 MyISAM 方式一样

表名.frm
2.3.2.2 表中数据和索引

在 MyISAM 中的引擎全部都是 "二级索引"，该引擎的 "数据和索引是分开" 的。所以在 "文件系统" 中也是使用不同的文件来存储数据文件和索引文件的。

  • MySQL 5.7.x
表名.frm 	#存储表结构
表名.MYD 	#存储表数据 (MYData)
表名.MYI 	#存储表索引 (MYIdex)
  • MySQL 8.0
表名_myisam_x.sdi 	#存储元数据
表名_myisam.MYD 	#存储表数据 (MYData)
表名_myisam.MYI 	#存储表索引 (MYIdex)
  • 对于 "InnoDB "表，SDI 与 "InnoDB" 用户表空间中的数据一起存储。
  • 对于 "MyISAM" 和其他存储引擎，它被写入数据目录中的 ".sdi" 文件中。
  • 在 8.0 之后 MyISAM 引擎和 InnoDB 引擎一样去掉了 ".frm" 文件，将数据放入了 ".sdi" 文件中。

注意：在MySQL 8.0中，MyISAM 存储引擎不提供分区支持。以前的版本在 8.0 中不能使用。

2.x 扩展

".frm"在MySQL中不存在了，那去了那里

放入了 ".ibd" 文件中，写入了 "ibd" 文件内部。可以通过 MySQL 安装时自带的（ibd2sdi）命令工具解析查看。

  • 到存储 "ibd" 文件目录下，执行下面的命令：
ibd2sdi --dump-file=xxx.txt xxx.ibd
# 命令解析说明：
　xxx.txt：表示解析后的新文件
　xxx.ibd：表示你要解析的文件

2.4 小结

2.4.1 InnoDB

如果"某表"采用 "InnoDB" ，就会在 "文件系统" 中的 "数据库目录" 下产生1个或者2个文件，如下：

  • "表名.frm"：描述表结构文件，字段长度等...
  • 如果采用 "系统表空间" 模式，数据信息和索引信息都会存储在 "ibdata1" 中（默认的，也可以自定义）
  • 如果采用 "独立表空间" 模式， "文件系统" 中的 "数据库目录" 中产生 "表名.ibd" 文件用来存储数据信息和索引信息。

此外：

  1. 5.7 中 "数据库目录" 中还会产生 "db.opt"文件用于保存数据库的相关配置。字符集，比较规则等...。而 8.0 不再提供 db.opt 文件
  2. 8.0 中 不再单独提供 "表名.frm"，而是合并在 "表名.ibd" 文件中了。

2.4.2 MyISAM

  • 如果"某表"采用 "MyISAM" ，就会在 "文件系统" 中的 "数据库目录" 下产生3个文件，如下：
  • 5.7 中："表名.frm"：用来描述表结构文件，字段长度等等..
  • 8.0 中："表名_myisam_x.sdi"：用来描述表结构文件，字段长度等等..
  • 表名.MYD (MYData)：数据信息文件，存储数据信息的文件（如果采用 独立表空间的存储模式）
  • 表名.MYI (MYIndex)：存储索引信息文件

2.5 视图在文件喜宴中的表示

MySQL 中的 "视图" 其实是 "虚拟的表"。所以在存储 "视图" 的时候不需要存储真实的数据，只需要把它的结果存储起来就就行了。和表一样，描述 "视图" 结构的文件也会被存储到所属数据库对应的子目录中，但只会存储一个 "视图名.frm" 的文件。

视图名.frm

2.6 其他文件

  • 服务器进程文件

    每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL 服务器会把自己的进程ID写入到一个文件中。

  • 服务器日志文件

    在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志，错误日志，二进制日志，redo日志等。

  • 默认/自动生成的 SSL 和 RSA 证书和密钥文件

    主要是为了客户端和服务器安全通信而创建的一些文件。