寻觅生命中的那一片浅草......

文章带标签 MySQL

利用undrop-for-innodb恢复MySQL数据

这是一个MySQL数据恢复工具,我们尝试下恢复MySQL数据

1 环境说明

  • VMware 8H2G
  • CentOS 6.2 64bit
  • MySQL version: 5.7.17-11 Percona Server
  • 一个没有任何读写的测试数据库实例
  • innodb_file_per_table on

2 安装

比较简单

cd /dist/src
git clone https://github.com/twindb/undrop-for-innodb.git
cd undrop-for-innodb/
make
gcc `$basedir/bin/mysql_config --cflags` `$basedir/bin/mysql_config --libs` -o sys_parser sys_parser.c

3 环境准备

create database recover;
use recover;
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

insert into actor(first_name, last_name) values('zhang', 'jian');
insert into actor(first_name, last_name) values('zhan', 'jian');
insert into actor(first_name, last_name) values('zha', 'jian');
insert into actor(first_name, last_name) values('zh', 'jian');
insert into actor(first_name, last_name) values('z', 'jian');

checksum table actor;
+-----------+------------+
| Table     | Checksum   |
+-----------+------------+
| per.actor | 2184463059 |
+-----------+------------+
1 row in set (0.00 sec)

# 此处模拟误删除表
DROP TABLE actor;

4 开始恢复

由于我们有表结构SQL了,所以本例没有恢复表结构,参考资料中有恢复表结构的操作

避免磁盘被覆盖写

由于是innodb_file_per_table on,就是drop操作会直接删除文件,此时比较稳妥的处理是关掉所有服务,将文件系统挂载为只读,由于本例是测试环境,没有任何写入,所以没有做以下处理

/etc/init.d/mysql stop
# 或者加-f参数,不过好像比较暴力
mount -o remount,ro /data

开始在磁盘上查找InnoDB页文件

本操作会在/dist/src/undrop-for-innodb/生成pages-sda5

cd /dist/src/undrop-for-innodb/

# 73G为df -h显示的sda5的总大小
# 以下指令本例大概会消耗20+小时
./stream_parser -f /dev/sda5 -s 1G -t 73G

将ibdata1分页

此操作不会影响ibdata1,会在/dist/src/undrop-for-innodb/生成pages-ibdata1

./stream_parser -f /data/database/mysql/ibdata1 

获取TABLE ID

21616为TABLE ID

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql  | grep 'recover/actor'  
00000001E113    2F0000018C08A0  SYS_TABLES      "recover/actor" 21616   4       33      0       80      ""      21619

获取INDEX ID

其中36728和36729为NDEX ID

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql  | grep '21616'
00000001E113    2F0000018C071D  SYS_INDEXES     21616   36728   "PRIMARY"       1       3       21619   4294967295
00000001E113    2F0000018C078F  SYS_INDEXES     21616   36729   "idx\_actor\_last\_name"        1       0       21619   4294967295

恢复数据

根据上面找到的INDEX ID找到对应的pages,sakila/actor.sql为建表SQL文件,好像只需要用到36728这个INDEX ID

从输出可以看到,数据找回了

./c_parser -6f pages-sda5/FIL_PAGE_INDEX/0000000000036728.page -t sakila/actor.sql
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
00000001E106    A6000001D60110  actor   201     "zhang" "jian"  "2017-12-04 15:58:38"
00000001E107    A7000002D30110  actor   202     "zhan"  "jian"  "2017-12-04 15:58:38"
00000001E109    A9000002D50110  actor   203     "zha"   "jian"  "2017-12-04 15:58:38"
00000001E10B    AA000002D60110  actor   204     "zh"    "jian"  "2017-12-04 15:58:38"
00000001E10E    AC000002D80110  actor   205     "z"     "jian"  "2017-12-04 15:58:38"

5 其他

  • 大内存和读写性能好的磁盘将有效加快恢复进度

6 参考资料

MySQL · 数据恢复 · undrop-for-innodb

github-undrop-for-innodb

通过XFW访问MySQL无返回的问题

1. 症状概述

最近我们新加了一台机,在xx的机器上访问MySQL,可以正常连上,执行show databases等马上有结果的指令,可以正常返回,但如果表记录又上千万,select count(1) from xxx的时候,则会没有返回。

犹记得几个月前,我们在C机有个脚本通过http方式提交数据到远程机器S,S将结果存到MySQL,同样也是经过XFW,由于处理时间比较长(大概1分钟吧),C这边一直卡住, 因为没有收到S的返回,但在S通过tcpdump抓包发现,S是有返回的,而且尝试多次返回,说明什么?连接断了呗,但C和S都感知不到这个断开的操作,感觉是XFW检测到此数据连接没有数据发送,把它断了。

最后是在S上加了索引,使得存入MySQL操作秒返回,才解决了这个问题。天下武功,唯快不破,和XFW斗亦如是。

2. 排查过程

2.1 MySQL版本

首先说明下本次新加的机器,我们升级MySQL到5.7,以往的机器都是5.5,那是不是5.7的问题呢?

2.1.1 SUSE系统

以下都是在C(SUSE系统)上连接:

1.连MySQL 5.7(A)时,实际早就挂了(服务器端top看MySQL的CPU占用),但客户端这边还是阻塞状态,过了很久才显示:

ERROR 2013 (HY000): Lost connection to MySQL server during query

2.连MySQL 5.5时(B),偶尔会卡住,在服务器端抓包,没有数据过来,意味着实际上还没有连到过去,但如果能连过去,一般可以获取到返回

但我们后来通过tcpdump抓包发现,第一点的结论其实是错的,因为在MySQL的CPU占用降下去之后,从3306端口是有数据发出去的,也就是并没有挂,查询有结果,并有发出去,但客户端没有收到,C端等到超时了才退出并报错

2.1.2 CentOS系统

刚好有1台CentOS系统的机器,测试下,结果和上面SUSE系统一样

2.2 TCP的问题

2.2.1 SUSE系统

基于上面一开始提到的http提交数据的问题, 我们又感觉是tcp的问题,怎么验证?我们在A上开了ssh的密码登录,然后在C上ssh到A,然后A上执行mysql连接,再进行查询,结果发现,也是没有返回,最后提示

Disconnected; connection lost (Connection closed.).

那很明显了,就是tcp连接断开了

2.2.2 CentOS系统

上网查询发现CentOS上,ssh有一个维持心跳的参数,于是试了下

# 5s发送一次心跳包
ssh -o ServerAliveInterval=5 -pxxx username@A

通过上面的参数连接后,发现此时可以正常获取结果了

记得关闭A上的ssh密码登录

3. 解决

3.1 libkeepalive.so

通过以上测试,发现问题出现在tcp,那就有解决的方向了,但MySQL是没有这种维持心跳的参数的,但我们找到了一个libkeepalive.so的软件,通过它封装下,可以设置应用层的心跳,那就不用设置内核参数了

LD_PRELOAD=libkeepalive.so KEEPIDLE=5 KEEPCNT=3 KEEPINTVL=5 mysql  -h xxx
  • KEEPIDLE,空闲多久就开始发送心跳,此处为5
  • KEEPINTVL,多久发送一次心跳,此处为5
  • KEEPCNT,一共尝试多少次,此处为3次,如果3次都发送不成功,则认为失败,不再尝试

但比较遗憾, 没有生效,无论是SUSE还是CentOS

3.2 内核参数

我们在C端把内核参数设置下,感觉是比较重(需要修改内核参数)的解决办法。

# 多久探测一次,此处为5s
sysctl -w net.ipv4.tcp_keepalive_intvl=5
# 空闲多久后开始探测,此处为5s
sysctl -w net.ipv4.tcp_keepalive_time=5

通过上述设置,再直接用mysql连接就正常了。

以下是系统默认值,net.ipv4.tcp_keepalive_probes的意思是,每「net.ipv4.tcp_keepalive_time 」间隔,就探测一次,探测「net.ipv4.tcp_keepalive_probes」次,此处为9次,就停止探测

net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_intvl = 5

为避免影响其他程序,建议只在测试时通过命令行设置,在测试完后,通过sysctl -p还原为系统默认配置

其他

ZZ:拷贝ibd文件的方式迁移数据

在mysql5.5之前,mysql实例中innodb引擎表的迁移是个头疼的问题,要么使用mysqldump导出,要么使用物理备份的方法,但是在mysql5.6之后的版本中,可以使用一个新特性,方便地迁移Innodb表,这样一来大的innodb表的迁移就显得很easy,这个特性就是innodb表空间传输:

详细请参考:Innodb 表空间传输迁移数据

zz:InnoDB个性化备份

来自云栖:InnoDB个性化备份
主要看下面,在slave上执行 stop slave,等一会,就可以通过cp目录的方式备份,好犀利

mysqldump引起MySQL内存飙升事例一则

最近有台MySQL,在运行一段时间后,res和virt内存都会去到很高,res会占到物理内存的90%,最终导致业务异常

期间做过的尝试:

  • 用tcpcopy来把生产环境的流量导到测试服,但MySQL内存并无异常
  • 将innodbfilepertable=1修改为innodbfilepertable=0,运行1天后,内存飙升还是比较厉害

上面的措施都没有用。

下面这个,不确定有没有试过,也记录下吧

mysqldump -q,就是把数据尽快写到磁盘,而不是存在内存

最后是同事看监控,发现在凌晨,MySQL备份后,内存才飙升,我们最后定位到MySQL内存异常的原因是以下2个:

  • xxx_db下表太多:近2W了,一个区服就一个表来寸配置
  • 由于表太多,下面的指令把整个库的表结构导出来的时候,估计会把整个结构先写入内存,但理论上,这个表结构不会大
mysqldump -d $1 > $1_db_struc.sql

解决办法:

  • 不导整个库的结构,一个表一个表地导(这个也是目前的做法),或者直接就不导结构,因为都是一样的
  • 减少表数量,我们后来把它改为了这种方式,单个表存所有区服的信息
2018年七月
« 2月    
 1
2345678
9101112131415
16171819202122
23242526272829
3031