经常有朋友 “执行DELETE语句忘记带条件把表数据全删了” 造成数据丢失，其实可以通过 定时备份+binlog 来实现数据的恢复。比如：每隔7天全量备份一次，binglog保留8天的， 8>7，binlog还没过期清理就全量备份了，这样就保证了数据的完整性。

常见的备份方式有：mysqldump、xtrabackup、data目录复制，其中mysqldump是最简单的方式，只是它比较慢，这里不再介绍，主要介绍两种比较快的方式。

基于xtraBackup的全量备份和恢复

xtrabackup是第三方工具，需要安装配置，做如下准备工作：

A、在服务器上安装 xtrabackup 工具。

B、在 MySQL 中创建一个用于备份的用户（也可使用root），然后授予权限。

GRANT BACKUP_ADMIN ON *.* TO 'root'@'%';

FLUSH PRIVILEGES;

C、创建备份目录。

mkdir -p /home/mysql-full /home/mysql-full-binlog

# ----------- 全量备份 -----------# 1、清空备份目录sudo rm -rf /home/mysql-full/*# 2、执行全量备份xtrabackup \--defaults-file=/home/mysql8/my.cnf \--socket=/home/mysql8/mysqld.sock \--user=root --password="****" \--backup \--target-dir=/home/mysql-full \--datadir=/home/mysql8/data

# ----------- 数据恢复 ------------- 1、停止MySQL服务mysqladmin -u root -p{***} shutdown-- 2、把当前的binlog备份cp /home/mysql8/data/mysql-bin.* \/home/mysql-full-binlog/-- 3、清空数据目录（可以先备份）rm -rf /home/mysql8/data/*-- 4、准备备份（实现数据一致，必须）xtrabackup --prepare \--target-dir=/home/mysql-full-- 5、恢复备份（只有在准备后才能执行）xtrabackup --copy-back \--target-dir=/home/mysql-full \--datadir=/home/mysql8/data-- 6、把备份后产生的 binlog 拷回 datadircp /home/mysql-full-binlog/mysql-bin.* \/home/mysql8/data-- 7、启动MySQL服务/home/mysql8/bin/mysqld_safe \--defaults-file=/home/mysql8/my.cnf \--user=root &

# ----------- 解析执行binlog -----------# 1、获取解析的第一个binlog文件及开始位置cat {...}/xtrabackup_binlog_info #假设结果：mysql-bin.000003 193 74c80974:1-3# 2、寻找 ‘问题SQL’所在的binlog，# 获取 stop-positionmysqlbinlog -v mysql-bin.000003mysqlbinlog -v mysql-bin.000004mysqlbinlog -v mysql-bin.000005...#假设结果：mysql-bin.000005，#stop-position为462# 3、解析binlog# 从mysql-bin.000003的193位置开始，# 直到mysql-bin.000005的462为止结束/home/mysql8/bin/mysqlbinlog \  --start-position=193 \  --stop-position=462 \  --skip-gtids \  /home/mysql8/data/mysql-bin.000003 \  /home/mysql8/data/mysql-bin.000004 \  /home/mysql8/data/mysql-bin.000005 \  /home/mysql8/bin/mysql \    -S /home/mysql8/mysqld.sock \    -uroot -p{***}

基于data目录的全量备份和恢复

此方案是使用 Shell 脚本备份，无需安装任何三方软件。做如下准备工作：

A、创建备份目录。

mkdir -p /home/mysql-backup/{data,binlog}

# ----------- 全量备份 -----------# 1、停止应用，确认无大事务show processlist# 2、清空备份目录rm -rf /home/mysql-backup/{data,binlog}/*# 3、给MySQL加读锁，阻塞住DML和DDL; # 切binlog; 记录最新的文件名和position值# FLUSH TABLES WITH READ LOCK 会阻止DML，# 但不会阻止redo、undo这些，故需要记录position sudo /home/mysql8/bin/mysql -u root \-p{***} -S /home/mysql8/mysqld.sock -e "  FLUSH TABLES WITH READ LOCK;  FLUSH LOGS;  SHOW MASTER STATUS;" \  > /home/mysql-backup/data/position.txt # 4、停止MySQL服务、#（必须规范停止保证redo刷完） mysqladmin -u root -p{password} shutdown# 5、将整个data文件夹打包压缩# （保证隐藏文件都打包，不能使用cp）tar -zcf /home/mysql-backup/data.tar.gz -C /home/mysql8 data# 6、启动MySQL服务/home/mysql8/bin/mysqld_safe \--defaults-file=/home/mysql8/my.cnf \--user=root &

# ----------- 数据恢复 ----------- # 1、停止MySQL服务mysqladmin -u root -p{***} shutdown# 2、把当前的binlog备份cp /home/mysql8/data/mysql-bin.*   /home/mysql-backup/binlog/# 3、删除数据目录rm -rf /home/mysql8/data# 4、恢复之前备份的数据目录tar -zxf /home/mysql-backup/data/data.tar.gz   -C /home/mysql8# 5、把备份后产生的 binlog 拷回 datadircp /home/mysql-backup/binlog/mysql-bin.*   /home/mysql8/data# 6、启动MySQL服务/home/mysql8/bin/mysqld_safe \--defaults-file=/home/mysql8/my.cnf \--user=root &

# ----------- 解析执行binlog -----------# 1、获取要解析的第一个binlog及开始位置# (全量备份步骤3中产生的 position.txt中获取)cat /home/mysql-backup/data/position.txt #假设结果：mysql-bin.000003 193 # 2、寻找 ‘问题SQL’所在的binlog，# 获取 stop-positionmysqlbinlog -v mysql-bin.000003mysqlbinlog -v mysql-bin.000004mysqlbinlog -v mysql-bin.000005...#假设结果：mysql-bin.000005, # stop-position为462# 3、解析binlog，# 从mysql-bin.000003的193位置开始，# 到mysql-bin.000005的462位置结束/home/mysql8/bin/mysqlbinlog \  --start-position=193 \  --stop-position=462 \  --skip-gtids \  /home/mysql8/data/mysql-bin.000003 \  /home/mysql8/data/mysql-bin.000004 \  /home/mysql8/data/mysql-bin.000005 | \  /home/mysql8/bin/mysql \    -S /home/mysql8/mysqld.sock \    -uroot -p{***}

方案对比