mysql数据恢复

<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">sed<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘ t1.txt | <span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">sed -r <span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘ | <span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">sed<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">s/### //g;s/\/\*.*/,/g<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘ | <span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">sed<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘ | <span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">sed<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">/^$/d<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘ > recover.sql<br>然后得到<br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #000000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">UPDATE db01.t1
SET
  @<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">1=<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">1<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #000000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"> ,
  @<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">2=<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">daiiy<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #000000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"> ,
  @<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">3=<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">2<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #000000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"> ,
  @<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">4=<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">guangzhou<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">‘<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #000000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"> ,
WHERE
  @<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">1=<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800080; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1">1<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #000000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"> ;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

其中 @1 @2 @3 对应的是你表的字段（左到右）

在处理
　sed -i ‘s/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g‘ recover.sql
　sed -i -r ‘s/(address=.*),/\1/g‘ recover.sql

验证一样语句对不对 cat recover.sql

6.链接数据库 mysql -h127.0.0 -uuser -ppwd
7.导入sql语句 source ~/recover.sql

相关语句

传送文件 scp filename user@host:addr（存放地址）

查看文件 less more head tail sed -n ‘10,100p‘
导出sql 导出整个数据库 mysqldump -u userName -p dabaseName > fileName.sql

　　　　导出数据库表结构 mysqldump -u userName -p -d dabaseName > fileName.sql

　　　　导出表数据 mysqldump -u userName -p dabaseName tableName > fileName.sql

　　　　导出表结构 mysqldump -u userName -p -d dabaseName tableName > fileName.sql

上面是用mysql log_bin恢复数据 前提是有打开日志 查看是否打开：show variables like ‘log_bin‘;

恢复数据 要么有备份要么有开log_bin 不然貌似没办法了。

<span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #0000ff; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"><span style="font-size: 16px; font-weight: normal; margin-left: 4px; margin-right: 4px; float: none; color: #800000; text-align: left; text-indent: 0px; white-space: pre-wrap; clear: none; display: inline; opacity: 1"> </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

mysql数据恢复

标签：base64   删除   mysql-bin   where   margin   any   sed   head   mysq