MySQL 独立表空间恢复案例

创建表的时候就会得到元数据。可以通过定义的方式对表的元数据进行生成

这个地方要注意的是

独立表空间当中 ibd & frm分别存储的是什么数据？

表空间：文件系统,为了更好的扩容数据库的存储空间

5.5以后的版本出现了共享表空间概念

表空间管理模式的出现是为了数据库的存储更容易扩展

5.6版本种默认的是独立表空间

1、共享表空间：共享表空间用来存元数据和系统日志

show variables like '%path%';

innodb_data_file_path=ibdata1:12M:autoextend #默认只有1个共享表空间的文件默认大小是12M 12M用完会自动增加

文件目录下：/data 通过ls –lh查看

-rw-rw----. 1 mysql mysql 76M Aug 2 22:18 ibdata1

5.6种默认存储什么？

系统数据、undo、临时表

5.7中，会将undo和临时表也都独立出来

vim /etc/my.cnf

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend --->错误的写法

第一个50需要注意，需要与当前文件的ibdata1大小一致

[ERROR] InnoDB: Data file ./ibdata1 is of a different size 4864 pages (rounded down to MB) than

specified in the .cnf file 3200 pages!

参数应该更改为：

innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

一般企业会设置2个 512M或者2G

2、独立表空间（5.6以后的默认模式）：

对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理

-rw-rw---- 1 mysql mysql 8710 Jun 25 16:25 city.frm

-rw-rw---- 1 mysql mysql 671744 Jun 25 16:25 city.ibd

city.ibd就被称之为独立表空间的数据文件

city.frm就是元数据文件就是创建表后生成的

独立表空间可以通过开关控制建议不要修改一旦关闭就会存到共享表空间里去了

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql>

alter table city discard tablespace;

alter table city import tablespace;

例子实践：

这个案例主要就是当表只存在frm文件时，通过之前创建表的语句创造出ibd文件，再重新导入表空间，这样就可以使用了

复制文件夹及文件夹下所有文件到对应文件夹

[root@db01 data]# cp -r db /data/3307/data/

第一个里程碑：在数据库文件下进行文件备份模拟删除

[root@db01-sa world]# pwd

/application/mysql/data/world

[root@db01-sa world]# cp city.ibd city.ibd.bak

第二个里程碑：删除表空间&查看表

mysql> alter table world.city discard tablespace;

Query OK, 0 rows affected (0.34 sec)

mysql> select * from world.city;

ERROR 1814 (HY000): Tablespace has been discarded for table 'city'

第三个里程碑：恢复ibd文件&将文件属组给mysql

[root@db01-sa world]# cp city.ibd.bak city.ibd

[root@db01-sa world]# chown -R mysql.mysql city.ibd

第四个里程碑：重新赋予表空间

mysql> alter table world.city import tablespace;

Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> select * from city;

+------+---------------------+-------------+------------------------+------------+

+------+---------------------+-------------+------------------------+------------+

| 1 | Kabul | AFG | Kabol | 1780000 |

| 2 | Qandahar | AFG | Qandahar | 237500 |

| 3 | Herat | AFG | Herat | 186800 |

| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |

| 5 | Amsterdam | NLD | Noord-Holland | 731200 |

| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |

| 7 | Haag | NLD | Zuid-Holland | 440900 |

| 8 | Utrecht | NLD | Utrecht | 234323 |

补充：如果在物理层面直接删除ibd文件会怎样？

mysql> select * from city;

ERROR 1146 (42S02): Table 'world.city' doesn't exist

第一个里程碑：将ibd文件备份恢复& 修改文件属组

[root@db01-sa world]# cp city.ibd.bak city.ibd

[root@db01-sa world]# chown -R mysql.mysql city.ibd

第二个里程碑：重启数据库

[root@db01-sa ~]# /etc/init.d/mysqld restart

第三个里程碑：查看表能否打开

mysql> select * from city;

+------+---------------------+-------------+------------------------+------------+

+------+---------------------+-------------+------------------------+------------+

| 1 | Kabul | AFG | Kabol | 1780000 |

| 2 | Qandahar | AFG | Qandahar | 237500 |

| 3 | Herat | AFG | Herat | 186800 |

| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |

| 5 | Amsterdam | NLD | Noord-Holland | 731200 |

| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |

备注：此次2个案例只在虚拟机上测试，最好是跨虚拟机再测试下

虚拟机上测试OK

truncate 作用一样类似于discard 表空间

七、老男孩Linux——MySQL学员案例

生产库：

confulence库 jira库

联想服务器（IBM）

磁盘500G 没有raid

centos 6.8

mysql 5.6.33 innodb引擎独立表空间

编译→制作rpm

/usr/bin/mysql

/var/lib/mysql

confulence jira

所有软件和数据都在"/"

断电了，启动完成后“/” 只读

fsck 重启

结果：confulence库在， jira库不见了

求助：

这种情况怎么恢复？

我问：

有备份没

求助：

连二进制日志都没有，没有备份，没有主从

我说：

没招了，jira需要硬盘恢复了。

求助：

1、jira问题拉倒中关村了

2、能不能暂时把confulence库先打开用着

将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

问：有没有工具能直接读取ibd

我说：我查查，最后发现没有

我想出一个办法来：

create table xxx

alter table confulence.t1 discard tablespace;

alter table confulence.t1 import tablespace;

虚拟机测试可行。

CREATE TABLE `city_new` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT '',

`CountryCode` char(3) NOT NULL DEFAULT '',

`District` char(20) NOT NULL DEFAULT '',

`Population` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

KEY `idx_popu` (`Population`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

面临的问题，confulence库中一共有107张表。

1、创建107和和原来一模一样的表。

他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库

mysqldump -uroot -ppassw0rd -B confulence --no-data >test.sql

拿到你的测试库，进行恢复

到这步为止，表结构有了。

2、表空间删除。

select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';

source /tmp/discard.sql

执行过程中发现，有20-30个表无法成功。主外键关系

ERROR 1215 (HY000): Cannot add foreign key constraint

很绝望，一个表一个表分析表结构，很痛苦。

set foreign_key_checks=0 跳过外键检查。

把有问题的表表空间也删掉了。

3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中

select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';

4、验证数据

表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

案例模仿：

案例最新：

经过测试跨虚拟机也是可以成功的，如果需要foreign key的检查用set忽略先把表结构导进去再说；

#第一个里程碑：导出建表的语句

CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT '',

`CountryCode` char(3) NOT NULL DEFAULT '',

`District` char(20) NOT NULL DEFAULT '',

`Population` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

#第二个里程碑：将表数据备份出来防止万一

[root@db01-sa world]# mysqldump -uroot -p123 world city >/server/scripts/backup_world_city.sql

#第三个里程碑：检查查看备份语句是否正常

[root@db01-sa world]# cat /server/scripts/backup_world_city.sql

#第四个里程碑：

mysql> drop table world.city;

Query OK, 0 rows affected (0.05 sec)

mysql> show tables;

+-----------------+

| Tables_in_world |

+-----------------+

| country |

| countrylanguage |

| people |

| people_bak |

+-----------------+

4 rows in set (0.00 sec)

[root@db01-sa world]# ll

total 1248

-rw-r----- 1 root root 8710 Jun 26 23:11 city.frm.bak

-rw-r----- 1 root root 589824 Jun 26 22:49 city.ibd.bak

#第五个里程碑：

CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT '',

`CountryCode` char(3) NOT NULL DEFAULT '',

`District` char(20) NOT NULL DEFAULT '',

`Population` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

[root@db01-sa world]# \rm -fr city.ibd

[root@db01-sa world]# cp city.ibd.bak city.ibd

[root@db01-sa world]# chown -R mysql.mysql city.ibd

mysql> use world;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 1

Current database: world

+-----------------+

| Tables_in_world |

+-----------------+

| city |

| country |

| countrylanguage |

| people |

| people_bak |

+-----------------+

5 rows in set (0.01 sec)

mysql> select * from city;

ERROR 1146 (42S02): Table 'world.city' doesn't exist

mysql> alter table world.city import tablespace;#导入表空间

Query OK, 0 rows affected, 2 warnings (0.10 sec)

mysql> select * from city;

+------+---------------------+-------------+------------------------+------------+

+------+---------------------+-------------+------------------------+------------+

| 1 | Kabul | AFG | Kabol | 1780000 |

| 2 | Qandahar | AFG | Qandahar | 237500 |

| 3 | Herat | AFG | Herat | 186800 |

| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |

posted on 2018-08-03 20:33 90500042陈阅读(4394) 评论(0) 编辑收藏举报