签到成功

知道了

CNDBA社区CNDBA社区

MySQL 表空间 说明

2021-08-22 16:03 1383 0 原创 MySQL
作者： dave
本文链接：http://www.cndba.cn/cndba/dave/article/4678

1 表空间概念

MySQL的数据存放在数据表中。表是一个逻辑上的概念，实际上数据存储在物理的表空间中。 表空间又由段 （ segment ） 、区 （ extent ） 、页 ( page / block ） 组成 。

MySQL的innodb 引擎中有如下几个表空间：系统表空间、临时表空间、undo 表空间。

2 sys表空间（共享表空间）

你可以像下面这样查看你的MySQL的系统表空间

mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.01 sec)

Value部分的的组成是：name:size:attributes

默认情况下，MySQL会初始化一个大小为12MB，名为ibdata1文件，并且随着数据的增多，它会自动扩容。

这个ibdata1文件是系统表空间，也是默认的表空间，也是默认的表空间物理文件，也是传说中的共享表空间。

2.1 配置sys表空间

系统表空间的数量和大小可以通过启动参数：innodb_data_file_path 来配置。 我们刚才查询的结果是默认值。 在my.cnf 文件中没有指定该参数情况，会在datadir 目录下生成默认的文件：

http://www.cndba.cn/cndba/dave/article/4678 

[dave@www.cndba.cn  data]# pwd
/mysql/data
[dave@www.cndba.cn  data]# ll ib*
-rw-r----- 1 mysql mysql      695 May  5 12:45 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 May 25 11:05 ibdata1
-rw-r----- 1 mysql mysql 50331648 May 25 11:05 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May  5 12:45 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May 25 17:34 ibtmp1
[dave@www.cndba.cn  data]#

我们也可以手工指定路径和大小：

# my.cnf
[mysqld]
innodb_data_file_path = ibdata1:18M:autoextend

这里的大小不能超过已经存在文件的实际大小，否则会报错如下错误：

2021-08-22T14:45:11.721662+08:00 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 4864 pages (rounded down to MB) than specified in the .cnf file: initial 6400 pages, max 0 (relevant if non-zero) pages!

然后重启mysql，让修改生效：

mysql>  show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:18M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

如果要指定共享表空间的存储路径，那么必须添加另外的参数：innodb_data_home_dir。

我们刚才没有指定，是因为没有指定innodb_data_home_dir时，ibdata1和ibdata2都在datadir(innodb_data_home_dir默认值为datadir)目录下创建。

如果所有表空间在同一个目录下，可以按如下方式指定：

[mysqld]
innodb_data_home_dir = mysql/data
innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M

如果多个表空间路径不同，那么必须先指定innodb_data_home_dir为空，如下：

[mysqld]
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M;/data/mysql/data1/ibdata2:12M:autoextend

否则会报如下错误：

2021-08-22T14:42:26.001481+08:00 0 [ERROR] InnoDB: File .//mysql/data/ibdata1: 'create' returned OS error 71. Cannot continue operation

2.2 file per table 表空间

在MySQL 5.6 之后，如果你想让每一个数据库表都有一个单独的表空间文件的话，可以通过参数innodb_file_per_table设置。 在mysql 5.7中，该参数默认是开启的。 http://www.cndba.cn/cndba/dave/article/4678

mysql>  show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

如果没有开启，可以手工修改配置文件，或者执行SQL 命令修改：http://www.cndba.cn/cndba/dave/article/4678

[mysqld]
innodb_file_per_table=ON

mysql> SET GLOBAL innodb_file_per_table=ON;

启用之后，每张业务表都有自己的独立的表空间，独立的表空间文件命名规则：表名.ibd。 

[dave@www.cndba.cn  data]# ll
total 286792
-rw-r----- 1 mysql mysql       56 May  5 12:45 auto.cnf
-rw------- 1 mysql mysql     1680 May  5 12:45 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 May  5 12:45 ca.pem
-rw-r--r-- 1 mysql mysql     1112 May  5 12:45 client-cert.pem
-rw------- 1 mysql mysql     1680 May  5 12:45 client-key.pem
drwxr-x--- 2 mysql mysql      120 May 24 22:54 cndba
-rw-r----- 1 mysql mysql      807 Aug 22 14:27 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Aug 22 14:46 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:46 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:27 ib_logfile0.bak
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:46 ib_logfile1
-rw-r----- 1 mysql mysql 50331648 May  5 12:45 ib_logfile1.bak
-rw-r----- 1 mysql mysql 12582912 Aug 22 14:46 ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  5 12:45 mysql
-rw-r----- 1 mysql mysql        6 Aug 22 14:46 mysqld.pid
drwxr-x--- 2 mysql mysql     8192 May  5 12:45 performance_schema
-rw------- 1 mysql mysql     1680 May  5 12:45 private_key.pem
-rw-r--r-- 1 mysql mysql      452 May  5 12:45 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 May  5 12:45 server-cert.pem
-rw------- 1 mysql mysql     1676 May  5 12:45 server-key.pem
drwxr-x--- 2 mysql mysql     8192 May  5 12:45 sys
[dave@www.cndba.cn  data]# cd cndba/
[dave@www.cndba.cn  cndba]# ll
total 328
-rw-r----- 1 mysql mysql 10816 May 24 00:25 dave1.frm
-rw-r----- 1 mysql mysql 98304 May 24 00:26 dave1.ibd
-rw-r----- 1 mysql mysql 10816 May 24 00:26 dave2.frm
-rw-r----- 1 mysql mysql 98304 May 24 00:26 dave2.ibd
-rw-r----- 1 mysql mysql 10816 May 24 00:25 dave.frm
-rw-r----- 1 mysql mysql 98304 May 24 00:25 dave.ibd
-rw-r----- 1 mysql mysql    67 May  5 12:58 db.opt
[dave@www.cndba.cn  cndba]#

独立表空间文件中仅存放该表对应数据、索引、insert buffer bitmap。

其余的诸如：undo信息、insert buffer 索引页、double write buffer 等信息依然放在默认表空间，也就是共享表空间中。

采用file per table的优点：http://www.cndba.cn/cndba/dave/article/4678http://www.cndba.cn/cndba/dave/article/4678

  1. 提升容错率，表A的表空间损坏后，其他表空间不会收到影响。
  2. 使用MySQL Enterprise Backup快速备份或还原在每表文件表空间中创建的表，不会中断其他InnoDB 表的使用

采用file per table的缺点：

  1. 对fsync系统调用来说不友好，如果使用一个表空间文件的话单次系统调用可以完成数据的落盘，但是如果你将表空间文件拆分成多个。原来的一次fsync可能会就变成针对涉及到的所有表空间文件分别执行一次fsync，增加fsync的次数。

3 临时表空间

临时表空间用于存放用户创建的临时表和磁盘内部临时表。

参数innodb_temp_data_file_path定义了临时表空间的一些名称、大小、规格属性。

mysql>  show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

查看临时表空间文件存放的目录

mysql>  show variables like '%innodb_data_home_dir%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_data_home_dir |       |
+----------------------+-------+
1 row in set (0.00 sec)

在上节已有说明，为空默认使用datadir的路径：

mysql>  show variables like '%datadir%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /mysql/data/ |
+---------------+--------------+
1 row in set (0.00 sec)

[dave@www.cndba.cn  data]# pwd
/mysql/data
[dave@www.cndba.cn  data]# ll ib*
-rw-r----- 1 mysql mysql      807 Aug 22 14:27 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Aug 22 14:46 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:46 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:27 ib_logfile0.bak
-rw-r----- 1 mysql mysql 50331648 Aug 22 14:46 ib_logfile1
-rw-r----- 1 mysql mysql 50331648 May  5 12:45 ib_logfile1.bak
-rw-r----- 1 mysql mysql 12582912 Aug 22 14:46 ibtmp1
[dave@www.cndba.cn  data]#

4 undo表空间

在innodb 引擎中，也有redo log ，undo log 的概念。 redo log 的概念，在之前的博客已经有说明，如下：http://www.cndba.cn/cndba/dave/article/4678

MySQL 日志系统 redo log 和 binlog 说明
https://www.cndba.cn/dave/article/4672

undo log 主要用来执行回滚。 undo log 就保存在undo 表空间中。但是要注意，默认情况下，innodb存储引擎对undo的管理采用段（rollback segment）的方式。每个回滚段中有1024个undo log segment。回滚段是在系统表空间（ibdata1）中分配的。 

mysql>  show variables like '%innodb_undo_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    |
| innodb_undo_log_truncate | ON    |
| innodb_undo_logs         | 128   |
| innodb_undo_tablespaces  | 0     |
+--------------------------+-------+
4 rows in set (0.00 sec)

我们也可以指定独立的undo 表空间，修改 innodb_undo_directory 和 innodb_undo_tablespaces 即可。 innodb_undo_directory 参数的默认值也是datadir，可以不用修改。

innodb_undo_tablespaces 参数只能在初始化的时候设置，而不能在后续使用过程中修改。

Redhat 7.7 平台 MySQL 5.7.33 安装手册（Tar包）
https://www.cndba.cn/dave/article/4509http://www.cndba.cn/cndba/dave/article/4678http://www.cndba.cn/cndba/dave/article/4678

注意重新初始化会删除所有数据，因为这里是测试环境，所以我们这里演示一下。 如果是生产环境，可以考虑先备份数据，重新初始化后在导入。

先修改/etc/my.cnf 参数，添加如下内容：

http://www.cndba.cn/cndba/dave/article/4678 

[mysqld]
innodb_undo_tablespaces = 4

重新初始化数据库：

http://www.cndba.cn/cndba/dave/article/4678

先清空/mysql/data 目录，否则会报错：

[dave@www.cndba.cn  data]# cd /usr/local/mysql/bin
[dave@www.cndba.cn  bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data

重置之后临时密码在error.log 中，登录之后修改即可。 

mysql> show variables like '%innodb_undo_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    |
| innodb_undo_log_truncate | ON    |
| innodb_undo_logs         | 128   |
| innodb_undo_tablespaces  | 4     |
+--------------------------+-------+
4 rows in set (0.00 sec)

此时在datadir 下生成了4个undo 表空间文件：

[dave@www.cndba.cn  data]# pwd
/mysql/data
[dave@www.cndba.cn  data]# ll undo*
-rw-r----- 1 mysql mysql 10485760 Aug 22 15:36 undo001
-rw-r----- 1 mysql mysql 10485760 Aug 22 15:36 undo002
-rw-r----- 1 mysql mysql 10485760 Aug 22 15:38 undo003
-rw-r----- 1 mysql mysql 10485760 Aug 22 15:36 undo004
[dave@www.cndba.cn  data]#

默认情况下，undo 文件是保存在共享表空间的，也即 ibdatafile 文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的 undo log 信息，这些信息全部保存在共享表空间中，因此共享表空间可能会变得很大，默认情况下，也就是 undo log 使用共享表空间的时候，被“撑大”的共享表空间是不会、也不能自动收缩的。

如果数据库有大的事务性操作，可以考虑使用独立的 undo 表空间。

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问：8181836次
  • 积分：4428
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ