Mysql备份及binlog日志恢复数据

小伙伴们开发中遇到数据库被误删导致数据丢失的情况,备份的可以恢复部分数据,其他数据从binlog恢复

 

1.编写shell脚本数据库备份脚本备份数据库

#!/bin/sh
#database info
DB_USER="root"
DB_PASS="123456"
DB_HOST="127.0.0.1"

# Others vars
BIN_DIR="/usr/bin"            #the mysql bin path
BCK_DIR="/root/sqlbeifen"    #备份文件存储的位置,不存在自行创建
DATE=`date +%F-%H`
mysqldump -u$DB_USER -p$DB_PASS --flush-logs --all-databases  > $BCK_DIR/db_$DATE.sql #全量备份数据库并且刷新日志( --flush-logs刷新日志很重要,恢复时及时找到上次备份钱的biinlog文件)

根据需要加入定时任务执行,每天备份或每周备份根据需要随意

 

2.查看是否开启binlog日志,开启日志

mysql> show variables like 'log_bin';

显示已开启,若未开启请修改mysql配置文件开启binlog

Centos一般在/etc下的my.cnf文件,在[mysqld]添加如下代码

log-bin = /usr/local/var/mysql/logs/mysql-bin.log #存储位置(目录不存在自行创建)
expire-logs-days = 14 #保留天数
max-binlog-size = 500M #但文件最大空间
server-id = 1

重启mysql

查看日志状态
show master status; 

 

3.如何回复丢失数据

首先导入最新一次的备份sql(此步奏自行百度),再找到对应时间点的最后一个binlog文件

备份时会刷新log,可根据备份时间找到最后一次备份前的binlog日志,如为mysql-binlog.000002

找到需要恢复的操作节点(2583删除,那我我们恢复到2342结束不就可以了),有开始位置和结束位置(无需开始或结束位置删除对应参数 --start-position 或 --stop-position)则执行下面的命令,请更换对你你系统内mysql的安装路径

执行命令

/Applications/MxSrvs/bin/mysql/bin/mysqlbinlog /Applications/MxSrvs/binlog/mysql-bin.000002 --stop-position=2342 | /Applications/MxSrvs/bin/mysql/bin/mysql -uroot -p

输入密码后看数据是否恢复