MySQL · 数据目录

发表于 | 更新于 | 分类于 | 阅读次数：

简介

文件系统：操作系统用来管理磁盘的功能，像 InnoDB、 MyISAM 这样的存储引擎都是把表存储在文件系统上。

安装目录：用来存储关于控制客户端程序和服务器程序的命令（如 mysql，mysqld，mysqld_safe 等）。

1
2
3
4
5
6
7
mysql> show variables like 'basedir';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| basedir       | /usr/local/mysql-5.7.23-el7-x86_64/ |
+---------------+-------------------------------------+
1 row in set (0.00 sec)

数据目录：用来存储 MySQL 在运行过程中产生的数据。

1
2
3
4
5
6
7
mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /data/mysql-tdds/ |
+---------------+-------------------+
1 row in set (0.00 sec)

系统数据库

  • mysql

    它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

  • information_schema

    存储了 MySQL 服务器维护的所有其他数据库的信息，比如有表、视图、触发器、列、索引等信息，这些并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。

  • performance_schema

    存储了 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等。

  • sys

    主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解 MySQL 服务器的一些性能。

目录结构

数据库

每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
.
├── auto.cnf
├── error.log
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── mysql
├── mysql-binlog.000615
├── mysql-binlog.000616
├── mysql-binlog.000617
├── ...
├── mysql-binlog.index
├── mysql.pid
├── mysql.sock
├── mysql.sock.lock
├── performance_schema
├── slow.log
├── slow_query.log
├── sys
└── tdm
  • ibdata1：系统表的空间文件，undo 信息保存在系统文件 ibdata1 。
  • ib_logfile0：保存 InnoDB 引擎表的事务日志信息，其文件大小尺寸固定，不可以改变。
  • *.index：索引文件，用于记录哪些日志文件正在被使用。
  • mysql-binlog.*：记录数据库所有的 DDL 和 DML（除了数据查询语句）语句事件。

表

InnoDB表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
.
├── db.opt
├── test.frm
└── test.ibd

# db.opt：这个文件中包含了该数据库的各种属性，例如：数据库的字符集和比较规则
default-character-set=utf8
default-collation=utf8_general_ci

# 表结构文件：存储表名称、列类型、约束条件、字符集等专门用于描述表结构的文件
test.frm

# InnoDB表数据存储文件
test.ibd

系统表空间（system tablespace）

ibdata1 是一个自扩展文件，默认 12M，配置文件可配置任意大小，不够用的时候会自己增加文件大小

1
2
innodb_data_home_dir  = /data/mysql
innodb_data_file_path = ibdata1:1G:autoextend

Tips：从 MySQL 5.5.7 到 MySQL 5.6.6 之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间

独立表空间（file-per-table tablespace）

在 MySQL 5.6.6 以及之后的版本，InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间。

1
test.ibd  # 用来存储test表中的数据和索引

innodb_file_per_table：指定使用系统表空间还是独立表空间来存储数据，该参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。

  • 当 innodb_file_per_table 的值为 0 时，代表使用系统表空间

  • 当 innodb_file_per_table 的值为 1 时，代表使用独立表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

# test表从独立表空间移动到系统表空间
ALTER TABLE test TABLESPACE innodb_system;

# test表从系统表空间移动到独立表空间
ALTER TABLE test TABLESPACE innodb_file_per_table;

MyISAM表

MyISAM 并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下，在 MyISAM 中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的，所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。

1
2
3
test.frm  # 表结构文件
test.MYD  # 表数据文件
test.MYI  # 表索引文件

视图

视图是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个 视图名.frm 的文件。

1
2
3
4
5
6
7
8
9
10
11
.
├── db.opt
├── host_summary_by_file_io.frm
├── host_summary_by_file_io_type.frm
├── host_summary_by_stages.frm
├── host_summary_by_statement_latency.frm
├── host_summary_by_statement_type.frm
├── host_summary.frm
├── innodb_buffer_stats_by_schema.frm
├── innodb_buffer_stats_by_table.frm
└── innodb_lock_waits.frm

其他文件

数据目录除了存储数据文件以外，还包括为了更好运行程序的一些额外文件，主要包括这几种类型的文件：

进程文件

MySQL 服务器会把自己的进程 ID 写入到一个文件中 mysql.pid

日志文件

在 mysqld 服务运行过程中，会产生各种各样的日志，例如：查询日志、错误日志、二进制日志、redo 日志等

如何修改数据存储目录？

背景：随着数据量的增长，MySQL 数据所占的空间会越来越大，而默认情况下 MySQL 数据盘空间可能不够，这时候，就需要修改存储路径。

实施操作

  • 停服务
1
2
/etc/init.d/mysqld stop
mysqladmin -S /tmp/mysql.sock -u'xx' -p'xx' shutdown
  • 迁移目录
1
2
3
mkdir /data_sdb/mysql
cp -r /data/mysql/* /data_sdb/mysql
chown -R mysql:mysql /data_sdb/mysql
  • 修改配置文件 my.cnf
  • 启动服务
  • 重新配置主从关系
1
2
3
4
5
6
7
CHANGE MASTER TO
MASTER_HOST='10.200.17.40',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=4;