mysql innodb存储引擎的表空间

InnoDB存储引擎的表空间

1.共享表空间（ibdata1）

1）存储的内容

1.系统数据
2.临时表
3.undo 日志		事务的日志 redo undo

2）查看共享表空间

mysql> show variables like '%path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath            |                        |
| ssl_crlpath           |                        |
+-----------------------+------------------------+
3 rows in set (0.00 sec)

3）配置共享表空间

#1.编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

#2.启动数据库报错
[root@db01 ~]# systemctl restart mysqld.service
#为了查看报错
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/db01.pid).

#3.看日志报错
[root@db01 ~]# less /usr/local/mysql/data/db01.err
2020-10-29 18:10:47 16917 [ERROR] InnoDB: Data file ./ibdata1 is of a different size 4864 pages (rounded down to MB) than specified in the .cnf file 768 pages!

#4.错误原因
共享表空间ibdata1设置的结束大小与实际ibdata1大小不符合

#5.解决问题
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#修改ibdata1大小与数据目录下的大小一致即可
innodb_data_file_path=ibdata1:76M;ibdata2:12M:autoextend

#6.重启服务
[root@db01 ~]# systemctl restart mysqld.service

2.独立表空间

1）查看独立表空间

#1.物理层面查看
[root@db01 ~]# ll /usr/local/mysql/data/xiangqin
总用量 144
-rw-rw---- 1 mysql mysql   8785 10月 28 19:05 user.frm
-rw-rw---- 1 mysql mysql 131072 10月 28 19:05 user.ibd

#2.数据库查看
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)