表空间介绍

共享表空间优缺点

既然Innodb有共享表空间和独立表空间两种类型，那么这两种表空间存在肯定都有时候自己的应用的场景，存在即合理。以下是摘自mysql官方的一些介绍：

共享表空间的优点

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制（很多文档描述有点问题）。

数据和文件放在一起方便管理。

共享表空间的缺点

所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。

共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（可以理解为mysql的表空间10G，但是才使用10M，但是操作系统显示mysql的表空间为10G），进行数据库的冷备很慢；

独立表空间的优缺点

独立表空间的优点

每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

空间可以回收（除drop table操作处，表空不能自已回收）

Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

独立表空间的缺点

单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法

共享表空间存的那些数据?

1.系统数据

2.undo

3.临时表

#查看表空间
mysql> show variables like '%path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath            |                        |
| ssl_crlpath           |                        |
+-----------------------+------------------------+

独立表空间

#查看独立表空间是否开启
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

[root@db02 test1]# ll /data/test1/
total 236
-rw-rw---- 1 mysql mysql     61 Jun 17 15:43 db.opt  
-rw-rw---- 1 mysql mysql   8586 Jun 23 15:21 t1.frm  #表结构
-rw-rw---- 1 mysql mysql  98304 Jun 23 17:49 t1.ibd  #独立表空间
-rw-rw---- 1 mysql mysql   8622 Jun 23 17:32 t2.frm
-rw-rw---- 1 mysql mysql 114688 Jun 23 17:49 t2.ibd

企业实例(模拟表空间损坏)

mysql3307中test1库中t1,t2表空间损坏
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
| t2              |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from t1;
ERROR 1146 (42S02): Table 'test1.t1' doesn't exist '

#准备一个新的数据库mysql3308 
进入3308创建一个相同的库
mysql>create datebase test1;
mysql>use test1;
#找到当初建表时的sql语句 *****
mysql> CREATE TABLE `t1` (
->   `id` int(11) NOT NULL AUTO_INCREMENT,
->   `name` varchar(20) DEFAULT NULL,
->   PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t2` (
->   `id` int(11) NOT NULL AUTO_INCREMENT,
->   `score` tinyint(4) DEFAULT NULL,
->   `stu_id` int(11) DEFAULT NULL,
->   PRIMARY KEY (`id`),
->   KEY `stu_fk` (`stu_id`),
->   CONSTRAINT `stu_fk` FOREIGN KEY (`stu_id`) REFERENCES `t1`(`id`) ON DELETE CASCADE ON UPDATE CASCADE  
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
#如果有外键先创建时先不添加外键 或者创建完成后删除外键 alter table t2 drop foreign key stu_fk;

#删掉新建t1,t2的表空间文件
mysql> alter table test1.t1 discard tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table test1.t2 discard tablespace;
Query OK, 0 rows affected (0.00 sec)

#确认已删除表空间文件
[root@db02 data]# ll /data3308/data/test1
total 28
-rw-rw---- 1 mysql mysql   65 Jun 23 22:49 db.opt
-rw-rw---- 1 mysql mysql 8586 Jun 23 22:55 t1.frm
-rw-rw---- 1 mysql mysql 8622 Jun 23 23:06 t2.frm

#拷贝mysql3307下的t1,t2表空间文件到3308中
[[root@db02 data]# cp -a /data3307/data/test1/t{1,2}.ibd /data3308/data/test1/
[root@db02 data]# ll /data3308/data/test1/
total 236
-rw-rw---- 1 mysql mysql     65 Jun 23 22:49 db.opt
-rw-rw---- 1 mysql mysql   8586 Jun 23 22:55 t1.frm
-rw-r----- 1 mysql mysql  98304 Jun 23 22:41 t1.ibd
-rw-rw---- 1 mysql mysql   8622 Jun 23 23:06 t2.frm
-rw-r----- 1 mysql mysql 114688 Jun 23 22:41 t2.ibd

#mysql3308中导入表空间(一定要导入,否则会提示表空间被丢弃 ERROR 1814 (HY000): Tablespace has been discarded for table 't1')
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> alter table t2 import tablespace;
Query OK, 0 rows affected, 1 warning (0.01 sec)

#恢复外键
mysql> alter table t2 add constraint stu_fk foreign key(stu_id) references t1(id) on delete cascade on update cascade;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查看是否修复
mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | zhang3 |
|  4 | li4    |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+----+-------+--------+
| id | score | stu_id |
+----+-------+--------+
|  3 |    50 |      1 |
|  4 |    60 |      4 |
+----+-------+--------+
2 rows in set (0.00 sec)

#mysqldump导出数据
mysqldump -uroot -p -S /data3308/data/mysql.sock test1 t1 t2 > tmp/mysql.sql

#删除3307中test1库中t1,t2空表间
[root@db02 data]# cd /data3307/data/test1
[root@db02 test1]# ls
db.opt  t1.ibd  t2.frm  t2.ibd
[root@db02 test1]# 
[root@db02 test1]# rm -f t{1,2}.ibd

#导入表
mysql3307 -uroot -p test1< tmp/mysql.sql 或者
mysql3307 -uroot -p
set sql_log_bin=0 临时关闭binlog
source <tmp/mysql.sql


ps:也可以sed修改/tmp/mysql.sql文件中的库名通过
sed -i -e 's#t1#t1_new#g' -e 's#t2#t2_new#g' tmp/mysql.sql  改名后导入 
再删除掉原来已损坏的表空间,将t1_new和t2_new通过命令 altet table t1_new rename t1  改名为t1,t2