MySQL的表空间问题

最新推荐文章于 2023-09-13 10:08:51 发布

路～～～

最新推荐文章于 2023-09-13 10:08:51 发布

阅读量303

点赞数

本文链接：https://blog.csdn.net/cxs_123/article/details/114784685

版权

前言

我们知道MySQL数据库中的数据信息是独立存储的，比如我在安装MySQL数据库时指定"/data/mysql/data"目录作为存储MySQL数据库数据信息的目录。

通过添加一块新的磁盘A，然后将磁盘A挂载在"/data/mysql/data"目录下，这样MySQL数据库产生的所用数据都会保存在此磁盘中，从而方便数据的管理和迁移，但是扩容却很麻烦。因为随着数据越来越多，磁盘的空间就会被沾满，这样数据库也就无法再运行了；

由于Linux中一个目录下只能挂载一块磁盘，若是磁盘没有分区，那么一块磁盘也只能挂载在一个目录下。所以当一个磁盘被写满之后，只能新建一个目录，然后挂载一块新的磁盘。在Linux系统中，由于每次进行数据访问时，只能访问一个目录，也就是一块磁盘，所以对于MySQL数据库来说，新旧数据存储在不同的目录下显然时无法运行的。所以就需要将磁盘A中的数据全部取出，然后换用一个更大空间的存储磁盘B，最后把磁盘B挂载在原目录下。这个过程不仅治标不治本、费时费力，还要把数据库服务停止运行很长时间，这在环境中是不被允许的。
在这里插入图片描述
对于这个问题，Orcale数据库创建了表空间(逻辑空间、可以无限大)的概念。表空间是一个中间件，ORCALE数据将不再直接访问磁盘进行数据的存取，而是访问表空间来存取数据。表空间可以用多块物理磁盘支撑的，当其中一块磁盘存满数据时，表空间可以允许加载新的磁盘，当对于Orcale来说，它始终访问的是表空间这块“磁盘”中。表空间的出现，轻松的解决了数据库磁盘扩容的问题。
在这里插入图片描述

所以MySQL也借鉴了Orcale的表空间概念，创建了自己的共享表空间：ibdata1~N。只不过把所有数据都存在了ibdateX这个文件中，这样多个MySQL数据库中的表的信息都存放在一个文件中。时间一长，ibdateX文件就会越来越大，又因为所有表的信息都放在ibdateX中，所以很难去管理。此方式的共享表空间只存在MySQL 5.5版本中。

MySQL表空间的演变

MySQL 5.6版本

由于MySQL 5.5版本共享表空间的缺陷，5.6版本后增加了独立表空间的概念，即将“数据字典(元数据)”、“表的列信息”、“表的数据，索引”分开存储，进行数据的解耦合。

案例：
实验条件：
使用MySQL 5.6版本的数据库
创建一个world数据库，并在world数据库中创建一个city表

 实验步骤：

1、在Linux中查看world数据库文件
在这里插入图片描述
可见city表中有一个".idb"文件，即表空间是独立的
2、切换至共享表空间

首先停掉MySQL服务：
[root@db01 ~] # systemctl stop mysqld

在配置文件"/etc/my.cnf"中添加如下配置：
[mysqld]
innodb_file_per_table=0

重启MySQL服务：
[root@db01 ~] # systemctl start mysqld

进入MySQL客户端查看一下：
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)
现在即为共享表空间的配置

3、重建world数据库及city表。然后查看数据库文件：
在这里插入图片描述
可以发现新建的city表只用一个".frm"文件，这就说明新建的变为共享表空间。city表中数据、索引信息都被存储到共享表空间ibdataX中了。
4、再装换成独立表空间

mysql> set global innodb_file_per_table=1;
mysql> alter table city engine=InnoDB;

这样就可以把原本为共享表空间的city表，变为独立表空间

MySQL 5.7

5.7版本下，临时表也被独立出来了(临时表空间，对应Linux中的文件为：ibtmp1)。可以从上图中看出，Genreal tablespaces中存储着一些临时表，而在5.6 版本中，临时表是存储在ibdata1中的Tables中的。
MySQL 8.0版本

8.0版本中，Doublewrite、undo信息也被独立出来了

表空间的数据文件

针对MySQL 5.7版本：
InnoDB存储引擎的物理结构最直观的就是Linux系统的中"/data/mysql/data"目录下的数据文件
ibdata1：就是所谓的共享表空间，存放着系统数据字典信息(元数据)、UNDO表空间(回滚表)等数据
ib_logfile0~ib_logfile1：存放着REDO日志文件，事务日志文件。
ibtmp1：临时表空间对应的数据文件，存储着临时表。在做join union操作时会产生中间表的临时数据，ibtmp1就是mysql分配出来用于暂时存储这些临时表的空间。用完就自动清理临时表
xxx.frm：存储着表的列信息
xxx.ibd：就是所谓的独立表空间，存储着表的数据行和索引

tip：共享表空间可以扩容，独立表空间不可以；独立表空间的大小取决于磁盘容量的大小。如果数据量过大，一般使用分布式存储来解决。

共享表空间的设置

1、查看共享表空间的设置

db01 [(none)]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:512M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

2、设置共享表空间

在MySQL的配置文件"/etc/my.cnf"中添加这两行参数：
innodb_data_file_path=ibdata1:512M:autoextend  //表示创建一个大小为512M的共享表空间，且512M空间使用完后自动扩容
innodb_autoextend_increment=64   //每一次自动扩容的空间大小为64M

若是想一次性配置两个共享表空间，可以如下设置：
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64

tip:切记！！！
配置共享表空间一定要在初始化MySQL之前就要配置好，配置好以后就不要再动了。

独立表空间迁移

上面也演示过再MySQL 5.6版本下，独立表空间的设置以及独立表空间和共享表空间的切换，这里再说一下：
1、查看独立表空间是否开启

db01 [(none)]>select @@innodb_file_per_table;  
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                      1 |
+-------------------------+

查看独立表空间是否开启，默认为1 开启；当然可以通过命令：set global innodb_file_per_table=0;然后重新远程连接Linux服务器，来使设置生效。生效后，再创建的新表就只会有“.frm”一个文件，".ibd"文件的内容就会被存进ibdata1中

tip:简述一下独立表空间的优缺点：
优点：
1、提升容错率，表A的独立表空间损坏后，其它表的独立表空间不会受到影响
2、使用XtraBackup或MySQL Enterprise Backup快速备份或还原表数据时，不会中断其它InnoDB表的使用
缺点：
对fsync系统调用来说不友好，如果使用共享表空间的话，单次系统调用就可以完成数据的落盘；但是如果使用独立表空间，可能就会增加fsync的次数来获取数据

2、独立表空间迁移
当我们想把几张表数据迁移到另外一个数据库时，这时使用mysqldump进行数据备份，然后再迁移就比较麻烦了，可以通过独立表空间迁移的方式，将表数据迁移到另外一个数据库中。
独立表空间迁移也适合数据库损坏没有备份时的数据恢复。

案例：迁移db01数据库中的city表到db02数据库中
步骤一：在db02数据库中创建一个和city表结构一致的空表
db01 [world]>show create table city;    //在db01数据库中先获取city表的建表语句
CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4;

将以上的建表语句在db02数据库中再建立一个city表
步骤二：将db02数据库中city表的"ibd"文件删除。然后登录db02数据库执行以下命令
db02 [(none)]>use world;  //city表所在的数据库
db02 [world]>alter table city discard tablespace;  //释放city表的表空间

步骤三：将db01数据库中city表的"ibd"文件拷贝过来，并且修改拷贝过来的"ibd"文件的属主属组

步骤四：执行以下命令，将拷贝过来的"ibd"文件的表空间进行导入
db02 [world]>alter table city import tablespace;   //告诉MySQL数据库系统已经导入新的数据了。若是不执行这一步，MySQL数据库系统则不会自己识别已经导入的数据，查询表中的数据时就会报错。

tip：为什么不能直接将".frm"和".ibd"文件直接拷贝到另一个数据库中从而实现表空间的迁移？
通过拷贝的方式将".frm"和".ibd"文件迁移到另一个数据库中，即使能在MySQL数据库中查看到表名（这是因为".frm"文件中存储着表的结构），但是无法读取表中的数据，即使有".ibd"文件。这是因为没有新的MySQL数据库中没有此表的数据字典（元数据）信息。