mysql 使用binlog 恢复数据

1.恢复大原则

想恢复数据，比如删除一行，如果没有备份，要从插入数据的那一行或者那一天开始恢复。
如果从未备份，想恢复删除的数据，要从系统开始一点一点恢复，会把这个系统建立后所有执行的语句再执行一遍。所以要经常备份。否则出幺蛾子难搞

2.恢复操作 默认安装数据文件大概再这个位置 C:\ProgramData\MySQL\MySQL Server 8.0\Data

1.把binlog日志复制到另外文件夹，这个是恢复的点位。
2.如果有备份使用备份恢复数据库，否则删掉的比较久的数据，只能从日志的开头开始恢复了。
3.寻找出错的binlog日志。备份时间和错误操作的时间。
show master status;最后一个日志
4.加这个可以控制能看懂字符
--base64-output=decode-rows
按照时间抓
mysqlbinlog --no-defaults --start-datetime="2023-08-03 09:16:59" --stop-datetime="2023-08-03 20:59:48" --base64-output=decode-rows C:\DESKTOP-SRMKC0Q-bin.000116 -d 11111> d:\test5.sql
按照行数抓
mysqlbinlog --no-defaults --start-position=4 --stop-position=610 C:\DESKTOP-SRMKC0Q-bin.000116 -d 11111> d:\test5.sql
5.mysql -uroot -proot
6.use tardb;
7.source d:\test5.sql;

 

 

3.binlog设定
MySQL-uroot -p 进去MySQL 系统
binlog管理
在[mysqld]标签内增加如下内容
expire_logs_days=30
max_binlog_size=1024M
 
修改保存后，以下3种情况才生效
1)当binlog大小超过max_binlog_size
2)手动执行flush logs
3)重新启动

 
为了使之生效，需要执行flush logs。
mysql> flush logs;

4 windows备份

@echo off

echo link message
set host=127.0.0.1
set port=3306
set user=root
set pass=root
echo backupname
set dbname=11111
echo getdatainfo 2022-12-13-14-30-40
set target_folder=D:\backup
mkdir %target_folder%
set backup_date=%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
echo 设置备份文件的路径
set backupfile=D:\backup\%dbname%-%backup_date%.sql
echo 使用mysqldump对指定的MySql进行备份
echo 注意路径中有空格的要加上双引号
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile%
echo 删除过期文件，这里是超过30天就删除
forfiles /p D:\backup\ /s /m *.sql /d -30 /c "cmd /c del @file /f"
exist

 

5.linux 全量备份

1、创建一个备份脚本文件，例如 backup_mysql.sh,并将以下代码复制到文件中：

[root@k8s-node3 ~]# vim /hqtbj/hqtwww/Script/mysql/mysql_bak_full.sh
#!/bin/bash 
#mysqldump.sh
#Date: 2022-05-17
#Author: fandaoshuai
#Function: mysql数据库全量备份,并保留一个月；一个月之后再清理；

DB_User='xxx' #数据库用户
DB_Passwd='xxxx' #数据库密码

DATE=`date -d "now" +%Y-%m-%d` #显示当前的时间
Old_DATE=`date -d "-1 month" +%Y-%m-%d` #显示一个月前的时间

Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/full/$DATE #备份目录
Old_Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/full/$Old_DATE #一个月前的备份目录

echo $DATE"备份开始"
##创建目录##
if [ ! -d "$Bak_Dir" ];then
  mkdir -p $Bak_Dir
  echo "备份目录不存在，已创建;"
fi

##开始备份##
echo "开始全量备份"
BackupName=FULL-$DATE.sql.gz #备份完成之后的名字
/usr/local/mysql/bin/mysqldump -h127.0.0.1 -P3306 -u${DB_User} -p${DB_Passwd} --all-databases 2>/dev/null | gzip > $Bak_Dir/$BackupName
if [ $? -eq 0 ];then
  echo "数据库全量备份已完成："$Bak_Dir/$BackupName
else
  echo $BackupName"-数据库全量备份失败"
fi

##清理一个月前的备份文件##
echo "开始清理一个月前的备份文件-备份目录为：" $Old_Bak_Dir
if [ -d "$Old_Bak_Dir" ];then
  rm -rf $Old_Bak_Dir
  echo "一个月前的备份文件已被清理;" 
fi

6.linux 增量备份
[root@k8s-node3 ~]# vim /hqtbj/hqtwww/Script/mysql/mysql_bak_incremental.sh 
#!/bin/bash 
#mysqldump.sh
#Date: 2022-05-17
#Author: fandaoshuai
#Function: mysql数据库增量备份,并保留一个月；一个月之后再清理；

DB_User='xxx' #数据库用户
DB_Passwd='xxxx' #数据库密码

DATE=`date -d "now" +%Y-%m-%d` #显示当前的时间
Old_DATE=`date -d "-1 month" +%Y-%m-%d` #显示一个月前的时间

Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/incremental/$DATE #备份目录
Old_Bak_Dir=/hqtbj/hqtwww/data/mysql-bak/incremental/$Old_DATE #一个月前的备份目录

Binlog_Dir=/usr/local/mysql/logs #binlog的日志目录
Binlog_File=$Binlog_Dir/mysql-bin.index  #binlog日志的索引文件

echo $DATE"备份开始"
/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -P3306 -u${DB_User} -p${DB_Passwd} flush-log #刷新binlog日志文件

Counter=`wc -l $Binlog_File | awk '{print $1}'`
NextNum=0

##创建目录##
if [ ! -d "$Bak_Dir" ];then
  mkdir -p $Bak_Dir
  echo "备份目录不存在，已创建;"
fi

##开始备份##
for file in `cat $Binlog_File`
do
  Base=`basename $file`
  NextNum=`expr $NextNum + 1`
  if [ $NextNum -eq $Counter ]
    then
    echo $Base "新日志文件已经跳过"
  else
    dest=$Bak_Dir/$Base
    if (test -e $dest)
    then
    echo $Base"备份文件已存在"
    else
    echo "拷贝备份文件"$Base"至备份目录"
    cp $Binlog_Dir/$Base $Bak_Dir
    echo "增量备份文件"$Base"成功"
    fi
  fi
done

##清理一个月前的备份文件##
echo "开始清理一个月前的备份文件-备份目录为：" $Old_Bak_Dir
if [ -d "$Old_Bak_Dir" ];then
  rm -rf $Old_Bak_Dir
  echo "一个月前的备份文件已被清理;" 
fi

7.linux 自动备份任务
[root@k8s-node3 ~]# crontab -e
0 4 */7 * * sh /hqtbj/hqtwww/Script/mysql/mysql_bak_full.sh 1>>/hqtbj/hqtwww/Script/mysql/logs/mysql_full.log 2>&1
0 4 * * * sh /hqtbj/hqtwww/Script/mysql/mysql_bak_incremental.sh 1>>/hqtbj/hqtwww/Script/mysql/logs/mysql_incremental.log 2>&1