- innodb表空间可以有多个文件,而且支持裸设备。
- 表空间文件可以放在不同的文件系统或者物理磁盘上。
- 如果放在文件系统上,那么最大的表空间由系统的最大支持尺寸决定,而如果是裸设备的话则可以配置一个非常大的表空间。
- 表空间的大小可以设成自然增长。
mysql> show variables like '%file_path%';
+-----------------------+------------------------------------+
| Variable_name | Value |
+-----------------------+------------------------------------+
| innodb_data_file_path | ibdata1:15M;ibdata2:10M:autoextend:max:20M |
+-----------------------+------------------------------------+
1 row in set (0.00 sec)
参数innodb_data_file_path用来设置表空间的位置及大小等属性。
当前的参数值如下:
innodb_file_per_table=0
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path= ibdata1:15M;ibdata2:10M:autoextend:max:20M
说明我配置了两个表空间文件,第一个15M,第二个开始是10M,可自动扩展到最大20M.
mysql> create table t1 engine=innodb as select * from information_schema.tables;
Query OK, 136 rows affected (0.04 sec)
Records: 136 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 136 rows affected (0.01 sec)
Records: 136 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 272 rows affected (0.16 sec)
Records: 272 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 544 rows affected (0.07 sec)
Records: 544 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 1088 rows affected (0.26 sec)
Records: 1088 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 2176 rows affected (0.43 sec)
Records: 2176 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 4352 rows affected (0.12 sec)
Records: 4352 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 8704 rows affected (0.19 sec)
Records: 8704 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 17408 rows affected (0.40 sec)
Records: 17408 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
Query OK, 34816 rows affected (0.70 sec)
Records: 34816 Duplicates: 0 Warnings: 0
mysql> insert into t1 select * from t1;
ERROR 1114 (HY000): The table 't1' is full
mysql>
已无法扩展了,看一下数据文件的情况:
[root@rhel131 data]# ls -lth
total 132M
-rw-rw---- 1 mysql mysql 48M Nov 3 17:23 ib_logfile0
-rw-rw---- 1 mysql mysql 15M Nov 3 17:23 ibdata1
-rw-rw---- 1 mysql mysql 20M Nov 3 17:23 ibdata2
-rw-r----- 1 mysql root 146K Nov 3 17:22 rhel131.err
drwxr-xr-x 2 mysql mysql 4.0K Nov 3 17:22 test
-rw-rw---- 1 mysql mysql 48M Nov 3 15:01 ib_logfile1
drwx------ 2 mysql mysql 4.0K Oct 30 17:39 test2
-rw-rw---- 1 mysql mysql 5.2K Oct 24 13:09 rhel131.log
-rw-r--r-- 1 root root 3.9K Oct 18 04:06 mysqld_multi.log
-rw-rw---- 1 mysql mysql 56 Sep 27 15:15 auto.cnf
drwx------ 2 mysql mysql 4.0K Sep 27 15:14 mysql
drwx------ 2 mysql mysql 4.0K Sep 27 15:14 performance_schema
如果我这时候把ibdata1和ibdata2给删除掉会有什么后果?
[root@rhel131 data]# rm ibdata*
rm: remove regular file `ibdata1'? y
rm: remove regular file `ibdata2'? y
[root@rhel131 data]# service mysql start
Starting MySQL.. [ OK ]
mysql重启后会重置表空间的文件
[root@rhel131 data]# ls -lth
total 122M
-rw-rw---- 1 mysql mysql 15M Nov 3 17:24 ibdata1
-rw-rw---- 1 mysql mysql 10M Nov 3 17:24 ibdata2
-rw-rw---- 1 mysql mysql 48M Nov 3 17:24 ib_logfile0
-rw-rw---- 1 mysql mysql 48M Nov 3 17:24 ib_logfile1
-rw-r----- 1 mysql root 154K Nov 3 17:24 rhel131.err
drwxr-xr-x 2 mysql mysql 4.0K Nov 3 17:22 test
drwx------ 2 mysql mysql 4.0K Oct 30 17:39 test2
-rw-rw---- 1 mysql mysql 5.2K Oct 24 13:09 rhel131.log
-rw-r--r-- 1 root root 3.9K Oct 18 04:06 mysqld_multi.log
-rw-rw---- 1 mysql mysql 56 Sep 27 15:15 auto.cnf
drwx------ 2 mysql mysql 4.0K Sep 27 15:14 mysql
drwx------ 2 mysql mysql 4.0K Sep 27 15:14 performance_schema
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| t1 |
+----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> desc t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> check table t1;
+---------+-------+----------+-------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+-------------------------------+
| test.t1 | check | Error | Table 'test.t1' doesn't exist |
| test.t1 | check | status | Operation failed |
+---------+-------+----------+-------------------------------+
2 rows in set (0.00 sec)
表已损坏了,说明表空间文件一定要做好备份。