由于mysql系统经常出现无法启动(一般是由于服务器异常断电等原因),所以经常需要进行数据恢复,此文章系公司同事白宝奎所写,本人代为转发,希望能给需要的人带来一些帮助,文章如下
近日,有线上mysql数据库奔溃,所以记录了一下恢复步骤,亲测好用。
恢复步骤如下:
- 将错误的数据库备份出来,重新注册服务,等待使用
- 切换到mysql18088\data文件夹,在data文件夹里面找到多个mysql.bin.*格式的二进制文件,这些文件记录着mysql从注册到使用再到崩溃的所有sql语句,包括增删改查,commit,use等等操作的语句,先以修改日期排序这些文件,等待使用。此处需注意:排序后的文件名并不一定完全是有序的,详情见下图
- 以管理员身份运行cmd,cd到bin目录下,执行恢复语句,格式:mysqlbinlog -d 数据库名 --base64-output=DECODE-ROWS mysql-bin.*文件, mysql-bin.*文件,..... > 数据库名.sql,此处需要注意msql-bin.*的文件顺序很关键,比如:我要恢复2020.5.29到2020.6.29的数据,所以语句如下图所示:
执行成功后会生成sql文件,该sql文件就是这个数据库从创建到奔溃之间的所有语句,文件有大有小正常,因为每个库存储的量不一样。注:mysqlbinlog语句还有其它参数,其他用法可自行百度。
- 使用mysql工具恢复数据,工具有很多,我就用SQLyog来举例,如下图,导入文件,导入时,勾掉选项 “发生错误时退出”,执行导入操作。
- 执行完毕查看SQLyog错误日志,看是否有错误,如下图,用记事本打开,查看哪些语句执行有误
此外需要注意两点:
- 如果导入的数据库跟其它数据库有外键关系,那么要先确认关联的数据库数据要全,否则导入时会有一些外键冲突的语句报错,导致数据只成功恢复了一部分
- 乱码问题,生成sql文件前要先查看mysql文件夹下my.ini文件,查看编码格式是否是utf8,如果不是一定要修改,否则可能会出现乱码,my.ini添加四条语句如下:
default-character-set=utf8
character_set_server=utf8
default-storage-engine=innodb
collation-server=utf8_general_ci
修改完毕后,重启mysql服务即可