mysql表空间总结

mysql 表空间管理与共享维护


INNODB 对于表的存储有两种形式
一种是共享表空间，及多张表放在一个文件中，还有一种是独立表空间，每个表都有独立的数据文件。
下面实验分别展示了两种形式


1 共享表空间






1.1 共享表空间配置


1.2.1 查看当前共享表空间


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


[root@localhost mysql]# du -h ibdata1 
12M     ibdata1


1.2.2 增加共享表空间
[root@localhost mysql]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_data_file_path=ibdata1:12M;ibdata2:24M:autoextend




1.2.3 查看变更后配置
mysql>  show variables like '%innodb_data_file_path%' ;
+-----------------------+------------------------------------+
| Variable_name         | Value                              |
+-----------------------+------------------------------------+
| innodb_data_file_path | ibdata1:12M;ibdata2:24M:autoextend |
+-----------------------+------------------------------------+
1 row in set (0.00 sec)






[root@localhost mysql]# ll
total 135200
-rw-rw---- 1 mysql mysql       56 Nov 29 09:45 auto.cnf
drwx------ 2 mysql mysql     4096 Nov 29 16:17 dao
-rw-rw---- 1 mysql mysql 12582912 Nov 29 16:58 ibdata1
-rw-rw---- 1 mysql mysql 25165824 Nov 29 16:58 ibdata2
-rw-rw---- 1 mysql mysql 50331648 Nov 29 16:58 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 29 08:16 ib_logfile1
-rw-rw---- 1 mysql mysql        5 Nov 29 16:58 localhost.localdomain.pid
drwx------ 2 mysql mysql     4096 Nov 29 08:16 mysql
srwxrwxrwx 1 mysql mysql        0 Nov 29 16:58 mysql.sock
drwx------ 2 mysql mysql     4096 Nov 29 08:16 performance_schema
-rw-r--r-- 1 root  root       117 Nov 29 08:16 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql      117 Nov 29 08:16 RPM_UPGRADE_MARKER-LAST
drwx------ 2 mysql mysql     4096 Nov 29 16:05 test




[root@localhost mysql]# du -h ibdata*
12M     ibdata1
24M     ibdata2






2 独立表空间
2.1 独立表空间参数
mysql> show variables like '%innodb_file_per_table%' ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)




2 查看目录
[root@localhost dao]# ll
total 4
-rw-rw---- 1 mysql mysql 65 Nov 29 16:04 db.opt
[root@localhost dao]# ls
db.opt


3 建表


4 查看目录
[root@localhost dao]# ls
dao_table.frm  dao_table.ibd  db.opt
5 文件作用
mysql> use dao ;
Database changed
mysql> create table dao_table(c1 int) ;
Query OK, 0 rows affected (0.04 sec)