记录一次数据库的恢复经历

  测试环境下的数据被莫名其妙的修改了，全表更新错误，影响正常的开发。由于没有备份，通过备份无法恢复。只有通过binlog来恢复：

中间遇到的问题和经历说下

 1).使用root权限，找到mysql的my.cnf目录和binlog目录,找到里面的bin_log.里面含有binglog的日志目录.其实就是通过mysql的命令行界面，输入

SHOW VARIABLES LIKE '%log_bin%';

看到的内容类似

比如我自己的目录:

my.cnf
/db/data0/mysql/3306/my.cnf
binlog
/db/data0/mysql/3306/binlog/

2）进入到mysql的 bin目录，使用mysqlbinlog命令

mysql bin路径：/usr/local/mysql/bin/

使用binglog 先要查看具体某一天的：可以使用命令
mysqlbinlog --base64-output=decode-rows -v --start-datetime='2017-12-10 00:00:00' --stop-datetime='2017-12-10 23:01:01' -d 数据库名称 binlog日志路径以及名称

3）找到 最后一天的positon
[root@hcloud ~]# mysqlbinlog  /db/data0/mysql/3306/binlog/binlog.000150 

内容一般如下：

#160809 5:09:28 server id 1 end_log_pos 2698 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1470733768/*!*/;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23)
/*!*/;
# at 2698
#160809 5:19:49 server id 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1470734389/*!*/;
update Test_DB.OneTb set name='user10'
/*!*/;
# at 2795
#160809 5:30:38 server id 1 end_log_pos 2814 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

如果想要截止到某一天，可以通过以下命令导出：

 mysqlbinlog  --base64-output=decode-rows -v  --stop-position="2698" /db/data0/mysql/3306/binlog/binlog.000150 > Backup_1.sql 

导入到当前目录，比如：/db/data0/mysql/3306/binlog/Backup_1.sql
,此时进入mysql目录，如果服务器有多个mysql服务可以这样登录：（socket目录在my.cnf里面指定了）

mysql --user=root --port=端口号 --socket=/tmp/mysql.sock -p密码（注意密码前面不要有空格）

之后进去mysql之后，进入自己的mysql要恢复的数据库，执行以下sql
source /db/data0/mysql/3306/binlog/Backup_1.sql

4）如果想要恢复某天到某天的可以使用如下命令：

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2017-11-06 09:16:54"
--stop-datetime="2017-11-27 18:00:00" --database=zm_gaiay_net_cn /db/data0/mysql/3306/binlog/binlog.000150
| /usr/local/mysql/bin/mysql -uroot -port16234 --socket=/tmp/mysql.sock -p1234 -v zm_gaiay_net_cn

5）如果是update语句全表更新，想要恢复到之前的数据：

1）找到更改的时间点
2）使用命令 mysqlbinlog --base64-output=decode-rows -v --start-datetime="2017-11-06 09:16:54"
--stop-datetime="2017-11-27 18:00:00" /db/data0/mysql/3306/binlog/binlog.000150 > update.sql
导入到sql文件中

3）通过shell脚本 替换所有的update语句，shell脚本如下：

#!/bin/bash
# by ray

iswhere=1   #判断循环的行的位置，1表示在where后，0表示不再where后
colNum=0    #计算列数，一般在binlog日志内第一列为@1,第二列为@2一次类推
whereNum=0  #判断where后面字段出现的次数，便于拼接字符串，第一次出现不适用都会，第二次以后使用逗号拼接
setNum=0 #判断set后面字段出现的次数，便于拼接字符串，第一次出现不适用都会，第二次以后使用逗号拼接

replaceColumn(){   #把@开头的列替换为列配置文件内的列，安配置文件的顺序执行
     cat $1 | while read line
     do
          colNum=$[${colNum}+1]
          sed -i "s/@${colNum}/${line}/g" ./execSQL.sql  #替换列
     done
}

getSQL(){   #获取sql
     sql1=''
     sql_result=''
     sql_condition=''
     while read line #读取处理过的binlog日志
     do
          if [[ ${line} =~ 'UPDATE' ]];then     #匹配是否update
               if [ "${sql1}" != "" ];then
                    echo ${sql1}' '${sql_result}' '${sql_condition}';' >> ./execSQL.sql  #打印sql
                    sql1=''
                    sql_result=''
                    sql_condition=''
                    whereNum=0
                    setNum=0
               fi
             sql1=${line}        #拼接sql字符串，获取update
        elif [[ ${line} =~ 'WHERE' ]];then
             sql_condition=${line}   #拼接字符串，把binlog日志内where后面内容
             iswhere=1               #判断是否为where，因为要把where和set后面的内容互换
        elif [[ ${line} =~ 'SET' ]];then
             sql_result=' SET '${sql_result} #拼接字符串
             iswhere=0
        elif [[ ${iswhere} -eq 1 ]];then            #1为where后面，把binlog日志where后面的内容拼接到sql的set后
             if [[ ${whereNum} -eq 0 ]];then              #判断where字符串后的字符串是否一次出现
                  sql_result=${sql_result}' '${line}
                  whereNum=1                    #设置为1，表示不是第一次出现
             elif [[ ${whereNum} -eq 1 ]];then
                  sql_result=${sql_result}', '${line}
             fi
        elif [[ ${iswhere} -eq 0 ]];then           #判断是否为set后面的字符串
             if [[ ${setNum} -eq 0 ]];then               #判断set字符串后的字符串是否一次出现
                  sql_condition=${sql_condition}' '${line}
                  setNum=1                     #设置为1，表示不是第一次出现
             elif [[ ${setNum} -eq 1 ]];then
                  sql_condition=${sql_condition}' and '${line}
             fi
        fi
     done < $1   #把文件用while循环读取每一行
     echo ${sql1}' '${sql_result}' '${sql_condition}';' >> ./execSQL.sql    #最后一行退出循环，所以要打印最后一行
     echo "commit;" >> ./execSQL.sql
     replaceColumn $2
}


#脚本的入口，调用函数获取内容
if [ -e $1 ];then  #判断第一个参数是否为文件
     getSQL $1 $2
else
    echo $1' is not a file!!'
fi

 4）执行shell脚本  bash getSQL.sh '/tmp/update.sql' "./column.txt" （绝对路径）

5）执行execSQL.sql有可能会执行不了，如果执行不了，可以通过替换 里面的@1 @2 替换为相应的字段
  中间出现的问题：1.表里面字段有 utf-8mb4无法恢复2.state状态如果是负数，通过binlog转换后会出现诸如-2 （65524） 这样的，需要修改下；