mysql的innodb存储引擎（二）

1. innodb表空间可以有多个文件，而且支持裸设备。
2. 表空间文件可以放在不同的文件系统或者物理磁盘上。
3. 如果放在文件系统上，那么最大的表空间由系统的最大支持尺寸决定，而如果是裸设备的话则可以配置一个非常大的表空间。
4. 表空间的大小可以设成自然增长。

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)

表已损坏了，说明表空间文件一定要做好备份。