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)
