MySQL 独立表空间恢复案例

最新推荐文章于 2024-10-21 15:35:52 发布
最新推荐文章于 2024-10-21 15:35:52 发布
阅读量346 收藏
点赞数
文章标签： 数据库 jira 运维
原文链接：http://www.cnblogs.com/nodchen/p/9416175.html
版权

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

这个地方要注意的是

独立表空间当中   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;

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

| ID   | Name                               | CountryCode | District               | Population |

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

|    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;

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

| ID   | Name                               | CountryCode | District               | Population |

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

|    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;

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

| ID   | Name                               | CountryCode | District               | Population |

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

|    1 | Kabul                              | AFG         | Kabol                  |    1780000 |

|    2 | Qandahar                           | AFG         | Qandahar               |     237500 |

|    3 | Herat                              | AFG         | Herat                  |     186800 |

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

转载于:https://www.cnblogs.com/nodchen/p/9416175.html

恢复MySQL表空间数据的步骤
SmufHtml的博客
10-01 510
MySQL是一种常用的关系型数据库管理系统，当数据库发生故障或意外情况导致表空间数据损坏时，我们需要进行数据恢复。通过按照上述步骤操作，您可以成功恢复MySQL表空间数据。首先，我们需要停止MySQL服务，以确保在恢复过程中数据库不会接收新的写入操作。完成数据恢复后，我们需要启动MySQL服务，以便数据库可以正常提供服务。在进行数据恢复之前，需要创建一个相同结构的空表，以便后续将恢复的数据导入其中。接下来，我们需要删除原有的表空间文件，以便后续恢复。接下来，我们需要将备份的表空间数据导入到新创建的空表中。
Mysql-高级实战案例
program哲学
07-23 234
本文介绍Mysql高级应用实战案例，包括海量高并发系统的数据库设计
mysql 5.6 使用传输表空间迁移表或恢复误删除的表
lidan3959的专栏
05-06 2690
mysql 5.6 使用传输表空间的方式恢复误删除的表 一，简单说明： 1），传输表空间的限制：   1，mysql 版本 5.6.6 及其以上，并且版本建议源和目标版本建议都是GA版并且大版本一样   2，表引擎为innodb并且开启独立表空间  innodb_file_per_table   3，处于导出状态的表只允许读也即是”read_only“状态   4，DI
mysql独立表空间frm_MySQL 独立表空间恢复案例
weixin_35750483的博客
01-19 158
生产库：confulence库 jira库联想服务器(IBM)磁盘500G 没有raidcentos 6.8mysql 5.6.33 innodb引擎 独立表空间编译→制作rpm/usr/bin/mysql/var/lib/mysqlconfulence jira所有软件和数据都在"/"断电了，启动完成后“/” 只读fsck 重启结果：confulence库 在 ， jira库不...
MySQL数据库表空间损坏如何修复
yu33575的博客
03-04 1683
MySQL数据库表空间损坏如何修复
mysql表空间损坏怎么办_mysql的表坏了怎么修复啊？
weixin_29879257的博客
01-19 760
展开全部1. 表损坏的原因分析32313133353236313431303231363533e4b893e5b19e31333264626661以下原因是导致mysql 表毁坏的常见原因：1、 服务器突然断电导致数据文件损坏。2、 强制关机，没有先关闭mysql 服务。3、 mysqld 进程在写表时被杀掉。4、 使用myisamchk 的同时，mysqld 也在操作表。5、 磁盘故...
MySQL真实故障案例(恢复表)
bjgaocp的博客
07-21 269
案例背景: 硬件及软件环境: 联想服务器（IBM） 磁盘500G 没有raid centos 6.8 mysql 5.6.33 innodb引擎 独立表空间 备份没有，日志也没开 开发用户专用库: jira(bug追踪) 、 confluence(内部知识库) ------>LNMT 故障描述: 断电了，启动完成后“/” 只读 fsck 重启,系统成功启动,mysql启动不了。 结果：confulence库在 ， jira库不见了 能不能暂时把confulence库先打开用着 将生产库c
mysql drop table 恢复_DROP TABLE 恢复
weixin_35683330的博客
01-27 1627
当DROP TABLE指令敲下的时候，你很爽，你有考虑过后果么？如果该表真的没用，你DROP到无所谓，如果还有用的，这时你肯定吓惨了吧，如果你有备份，那么恭喜你，逃过一劫，如果没有备份呢？这时就该绝望了？NO! 如果你的表是innodb表，那么还有希望挽救，如果是myisam表，那么真的没救了。前面文章介绍了 Recover InnoDB dictionary，这是恢复数据的前提。恢复innodb...
如何恢复Mysql数据库的详细介绍
09-10
InnoDB存储引擎的表数据和索引是存储在一个共享表空间内的`ibdata1`文件，以及单独的`.frm`文件。当尝试复制`.frm`文件而忽视`ibdata1`时，虽然能看到表结构，但在执行查询时会出现“表不存在”的错误。解决这个问题...
Docker (六)：mysql 主从复制、redis集群3主3从【扩缩容案例】
热门推荐
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
09-03 5万+
🟢 Docker (六)：mysql 主从复制、redis集群3主3从【扩缩容案例】
008 --- 恢复mysql innodb 表空间
pang_2899的博客
06-19 159
1、重要命令 alter table city discard tablespace； alter table city import tablespace； 真实案例： 开发用户： jira（bug追踪） 、confluence （内部知识库） ---->lnmt 联想服务器：（IBM） 磁盘500G 没有raid centos 6.8 mysql 5.6.33...
MySQL表空间还原_RMAN进行表空间的备份和恢复
weixin_34568147的博客
02-08 160
1、创建表空eygle 2、创建用户eygle，隶属于eygle表空间；赋予用户DBA权限 3、egyle用户登录DB，创建表test_egyle 4、rman备份eg1、创建表空eygle2、创建用户eygle，隶属于eygle表空间；赋予用户DBA权限3、egyle用户登录DB，创建表test_egyle4、rman备份egyle表空间RMAN> backup tablespace ey...
mysql表空间损坏后怎么先启动服务_MySQL 启动后随即关闭或启动失败 [ innodb表空间损坏、innodb_force_recovery强制启动 ]...
weixin_36155560的博客
01-21 94
OS: CentOs7MySQL：5.7.26-log开发环境数据库mysql运行一段时间了，突然出现了异常：启动mysql后随即就又关闭了，mysql服务启动失败！查看mysql错误日志如下： 分析日志后发现，数据库无法重启的原因是因为ibdata1文件(即共享表空间)损坏，重启后无法正常恢复。解决办法：1. 需要跳过恢复步骤，修改my.cnf文件，在my.cnf中的[mysqld]中添加...
mysql表空间恢复_mysql通过表空间来恢复或者传递数据
weixin_33323907的博客
02-07 262
mysql的备份工具通常有 mysqldump ,mysqlpump(5.7后新特性)等备份工具，这里我们可以尝试使用表空间进行传递方式是：拷贝数据文件+拷贝表空间 对应innodb引擎就是 ibd文件和cfg文件执行此种方式的要求(1)需要使用独立表空间，开启innodb_file_per_table参数；(2)在做表导出时，该表只允许读不允许写；(3)导入导出的数据data page si...
【innodb阅读笔记】之通过独立表空间恢复数据表
最新发布
weixin_43262384的博客
10-21 456
要想通过独立表空间恢复数据，数据库必须设置为开启独立表空间，通过参数设置，5.7版本默认开启独立表空间。
Mysql Innodb表空间被废弃
qq_22313643的博客
04-27 1646
问题： 大佬，请教一个数据库大表(单表4亿条)移机后Tablespace has been discarded 的问题： 数据库版本5.7.22， innodb引擎， 大表做了按月分区。 现在做了这样的操作： A, B 两个完全相同的数据库,表也一样。 将A库的 表 a 的idb文件复制替换了...
记一次MySQL数据库恢复（附方案）
欢迎关注公众号：【码农突围】，公号后台回复9999，可以获取一份500页的LeetCode刷题笔记。
01-04 695
恢复步骤概要 备份frm、ibd文件 如果mysql版本发生变化，安装回原本的mysql版本 创建和原本库名一致新库，字符集都要保持一样 通过frm获取到原先的表结构，通过的得到的表结构创建一个和原先结构一样的空表。 使用“ALTER TABLE DISCARD TABLESPACE;”命令卸载掉表空间 将原先的ibd拷贝到mysql的仓库下 添加用户权限 “chown . .ibd”,如果是操作和mysql的使用权限一致可以跳过 通过“ALTER TABLE IMPORT TABLESPACE;”命令恢
Mysql 给表添加字段
wdlizhi的博客
08-24 6207
第一步： DROP TABLE IF EXISTS `city`; 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 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值