mysql数据通过data文件恢复
mysql磁盘文件被损坏，无法启动，能看到data文件，在没有备份的话如何复原？
情景1：知道数据库中的表结构
1、先执行表结构语句
2、解除绑定
|
1
2
3
|
alter table `表1` discard tablespace;alter table `表2` discard tablespace;...... |
此时当前数据库下所有的.idb文件都会被删除，也就是只剩下表结构，原表数据被删除
3、导入 .idb文件现有数据库
也就是添加表数据，不过，目前还没有绑定
|
1
2
3
|
alter table `表1` import tablespace;alter table `表2` import tablespace;...... |
mysql数据恢复方法个人总结
mysql具有binlog功能，可以记录对表进行过的操作、执行过的sql语句；
以下主要是总结下binlog的开启方法、使用方法等；
如果误删数据库表中的某些数据、或者误删整个表、误修改表结构等，就可以使用这个方法进行恢复。
步骤
1.如果想使用MySql数据恢复功能，首先需要修改Mysql配置文件，开启binlog功能。（有些版本binlog默认是不开启的，因此需要手动修改配置文件开启）
(1)windows环境
配置文件名称为my.ini,可以安装软件everything搜索这个文件。路径样例如下：
C:ProgramDataMySQLMySQL Server 5.7my.ini
(2)linux环境
配置文件名称为my.cnf,可以使用find命令搜索文件所在位置：
|
1
|
sudo find / -name my.cnf |
位置样例如下：
|
1
|
/etc/my.cnf |
2.找到配置文件后(my.ini/my.cnf)，新增以下配置即可(当然先看看配置文件是否已配置了这行，不要重复了)：
|
1
2
|
log-bin=mysqlbinlogbinlog-format=ROW |
或者也可以指定binlog的位置（下方是linux下的路径）：
|
1
2
|
log-bin=/data/mysql/log/mysqlbinlogbinlog-format=ROW |
注意，指定binlog在某个文件夹下时，必须自己创建文件夹，否则重启mysql会报错。
3.配置完成后，重启mysql。
4.现在，对mysql的数据库、表、数据等操作、执行增删改sql，都会记录到binlog中。（搜索mysqlbinlog就可以找到这个日志文件）
5.进行测试，新建一个表binlog_test，列随意，然后写几行数据，然后清空表数据。（准备使用binlog还原数据）
6.查看binlog的命令如下：
|
1
2
3
4
|
mysql> show binlog events; #只查看第一个binlog文件的内容mysql> show binlog events in 'mysqlbinlog.000002';#查看指定binlog文件的内容mysql> show binary logs; #获取binlog文件列表mysql> show master status； #查看当前正在写入的binlog文件 |
7.首先看当前使用的是哪个binlog:
|
1
|
show master status; |
本人执行后，发现当前使用的binlog是：mysqlbinlog.000004
8.然后查看这个binlog的内容：
|
1
|
show binlog events in 'mysqlbinlog.000004'; |
执行后，可以从查询结果的Info列中，找到刚才创建binlog_test表的sql（对应的Event_type是Query）;
可以看到刚才清空表数据的记录行，Event_type是Delete_rows；
在上一些，可以看到创建表后、新增数据的Event_type是Write_rows；
9.分析这个binlog，我们的目标是还原刚才被删除的数据，因此找到create table语句后的第一个Event_type是Query的行，它的Pos为460；
再找到Event_type是Delete_rows上面最接近的、Info中是COMMIT的行，它的Event_type是Xid，End_log_pos是1199。
（每一行的End_log_pos对应下一行的Pos）
10.现在找到了一个范围，从460到1199之间的执行日志，就是给表binlog_test增加数据的日志，因此只要重复执行这之间的操作，就可以还原数据了。
11-1.windows下，找到mysqlbinlog.exe的位置；
linux下，找到mysqlbinlog的位置（这个是mysql的bin目录下的一个可执行文件）；
然后执行以下命令，即可还原数据：
mysqlbinlog --no-defaults --start-position=460 --stop-position=1199 "C:ProgramDataMySQLMySQL Sever 5.7Datamysqlbinlog.000004" -d test | mysql -uroot -proot test
其中，指定了开始位置为460，结束位置为1199，命令执行后就会读取日志中指定范围内的命令并重新执行。
其它命令的含义为：
-
"
-d test"的意思是，指定数据库为test（binlog_test表在test数据库中） -
"
|"是管道符 -
"
mysql -uroot -proot test"是mysql的账号密码、登陆目标数据库 -
"
-uroot"：账号是root -
"
-proot"：密码是root -
"
test"：数据库是test
11-2.也可以把binlog日志中，指定范围内的日志导出，然后自己执行source命令还原数据，如下：
mysqlbinlog "C:ProgramDataMySQLMySQL Sever 5.7Datamysqlbinlog.000004" -d test --skip-gtids --start-position=460 --stop-position=1199 > test.sql
执行这个命令后，指定范围内的日志被导出到test.sql中；
然后登陆mysql，执行命令：
|
1
2
|
mysql> use test; mysql> source test.sql; |
这样就可以还原数据了。
其它笔记
1.如果使用truncate删除表的数据，binlog中也会新增几行记录，记录truncate操作，样例如下：
mysqlbinlog.000004 | 3122 | Query | 1 | 3211 | use 'test'; TRUNCATE 'binlog_test'
如果想恢复数据，就需要找到最开始插入数据的Pos、到truncate之前的End_log_pos，然后重新执行这期间的命令。
2.增删改操作会记录到binlog中，慢查询sql也可以记录到慢查询log中，不过也需要自己修改配置文件开启慢查询日志。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/csdn2100/article/details/106286845
