mysql数据库基本操作语句,模拟误删数据恢复

行云流水
2022-04-22 / 1 评论 / 349 阅读 / 正在检测是否收录...

前言

作为小站长,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

评论 (1)

取消
只有登录/注册用户才可评论
  1. 头像
    Robinson
    中国河南省 · Windows 10 · Google Chrome
    沙发

    666666

    回复
  2. 头像
    1111
    · Windows 10 · Google Chrome
    板凳

    1

    回复