mysql表空间总结_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)