前言
作为小站长，mysql数据库算是比较常用的了。作为运维，肯定遇到过数据被误删的情况。下面模拟数据库为误操作删除后的恢复过程。
mysql常用配置
开启binlog
修改配置
vim /etc/my.cnf.d/mariadb-server.cnf
server-id = 1
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10常用查询
-- 查看binlog 状态
use mysql
show variables like '%log_bin%';
-- 查看所有binlog文件列表
show master logs;
-- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
show master status;
-- 刷新日志，此刻开始产生一个新编号的binlog文件
Flush logs;
-- 每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志
-- 清空所有binlog
reset master;
-- 查看binlog 内容
-- 方法1
mysqlbinlog mysql-bin.000001
-- 方法2
show binlog events in 'mysql-bin.000001';
-- 指定查询
-- 从mysql-bin.000003 的pos 点328 开始
show binlog events in 'mysql-bin.000003' from 328;
-- 从pos点154开始查询，中间跳过1行，查询2条数据
show binlog events in 'mysql-bin.000004' from 256 limit 1,2;
数据备份恢复
#数据备份
mysqldump --single-transaction -uroot -proot@123 -S /opt/app/mysql/mysql.sock taskmonitor | /bin/gzip >/tmp/mysql_taskmonitor_`date +%m%d`.gz
#下载
scp root@ip:/tmp/mysql_taskmonitor_0913.gz .
#导入
gzip -d mysql_taskmonitor_0913.gz
mysql -uroot -p123456 taskmonitor < mysql_taskmonitor_0913
建库授权
CREATE DATABASE `taskmonitor` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
create user 'tmuser'@'%' IDENTIFIED BY 'tmuser@123';
grant all privileges on taskmonitor.* to 'tmuser'@'%';
flush privileges;清理数据库
删除所有表
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='wpc';模拟恢复实验
配置备份计划任务
检查binlog 是否开启
use mysql
show variables like '%log_bin%';备份脚本
vim /opt/scripts/backup_db_mysql.sh
#!/bin/bash
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
BACKUP_DIR="/data/db_backup"
DB_SOCKET="/var/lib/mysql/mysql.sock"
#DB_hostname="127.0.0.1"
DBNAME="taskmonitor"
DB_USER="root"
DB_PASS="123456"
TIME=`date +%Y%m%d%H%M%S`
LOCK_FILE="${BACKUP_DIR}/lock_file.tmp"
BKUP_LOG="/data/db_backup/${TIME}_bkup.log"
DEL_BAK=`date -d '10 days ago' '+%Y%m%d'`
#mkdir db_backup
[ ! -d $BACKUP_DIR ] && mkdir -p $BACKUP_DIR
##To judge lock_file
if [[ -f $LOCK_FILE ]];then
exit 255
else
echo $$ > $LOCK_FILE
fi
##dump databases##
echo ${TIME} >> ${BKUP_LOG}
echo "=======Start Bakup============" >>${BKUP_LOG}
${MYSQLDUMP} -S ${DB_SOCKET} -u${DB_USER} -p${DB_PASS} --databases ${DBNAME} | gzip -9 > ${BACKUP_DIR}/${TIME}.${DBNAME}.gz
echo "=======Finished Bakup============" >>${BKUP_LOG}
/bin/rm -f ${LOCK_FILE}
##del back 10 days before##
/bin/rm -f ${BACKUP_DIR}/${DEL_BAK}*.gz
定时全量备份，每周日4点10分 全备
10 4 * * 0 /bin/bash /opt/scripts/backup_db_taskmonitor.sh >/dev/null 2>&1手动执行
/bin/bash /opt/scripts/backup_db_taskmonitor.sh执行误删操作
drop database taskmonitor;恢复准备
数据恢复，登录数据库，刷新binlog
flush logs;备份 mysql-bin 文件
mkdir dbbak && cp /data/mysql/mysql-bin.* dbbak/根据binlog继续恢复，找出错误操作的pos
mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000002 |grep -C 10 -i "drop database"
查找mysqldump全备结束的pos点
grep -i "change master" /data/db_backup/2021-09-29-15-29-13.taskmonitor
开始恢复
通过全备份恢复数据
mysql -uroot -p123456 < /data/db_backup/2021-09-29-15-29-13.taskmonitor开始增量恢复到误操作,同一个binlog内
mysqlbinlog --start-position=371 --stop-position=604 mysql-bin.000002 |mysql -uroot -p123456
666666
1