Mysql之数据备份及恢复

为什么要进行数据备份

在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
1.程序错误
2.认为操作错误
3.运算错误
4.磁盘错误
5.灾难和盗窃

数据备份的分类

1.物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

• 完全备份：每次对数据库进行完整的备份
• 差异备份：备份自从上次完全备份之后被修改过的文件
• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

2.逻辑备份：对数据库逻辑组件（如，表等数据库对象）的备份
3. 完全备份：每次对数据库进行完整的备份
4. 差异备份：备份自从上次完全备份之后被修改过的文件
5. 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

常用的备份

1. 物理冷备份：
   备份时数据库处于关闭状态，直接打包数据库文件
   备份速度快，恢复时也是最简单的

//备份
systemctl stop mysqld
mkdir /backup
tar -zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
//恢复
mkdir /back
mv /usr/local/mysql/data/ /back/
tar -zxvf /backup/mysql_all-2020-12-20.tar.gz
mv /backup/usr/local/mysql/data /usr/local/mysql/
systemctl start mysqld

2. 专用备份工具mydump或mysqlhotcopy
   mysqldump常用的逻辑备份工具
   mysqlhotcopy仅拥有备份MylSAM和ARCHIVE表

• 使用mysqldump进行完全备份存在的问题
  备份数据中有重复数据
  备份时间与恢复时间张

//单数据库备份
mkdir /backup
mysqldump -u root -p mysql > /backup/mysql.sql
//多数据库备份
mysqldump -u root -p --databases mysql mysql1 mysql2 > /backup/databases-mysql-aa-bb.sql
//备份所有数据库
mysqldump -u root -p --all-databases > /backup/all-data.sql
//恢复数据
use mysql
source /backup/mysql.sql
mysql -u root -p123456 mysql < /backup/mysql.sql

3. 增量备份
   使用二进制日志进行增量备份
   进行增量备份，需要刷新二进制日志
   增量备份的特点：
   没有重复数据，备份量不大，时间短
   恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

//开启增量备份
vi /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/data/mysql_bin
systemctl restart mysqld
//增量备份
mysqladmin -u root -p flush-logs  
//模拟错误操作
insert into test values(201001,'zhangsan');	
delete from test where name='lisi';		#误删除
mysqladmin -u root -p flush-logs 
cd /usr/local/mysql/data 
ls -lh	//查询增量备份的结果
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002	//查询二进制的内容

增量备份后的恢复

1. 一般恢复
   将所有备份的二进制日志内容全部恢复
2. 基于位置恢复
   数据库在某一时间点可能既有错误的操作也有正确的操作
   可以基于精准的位置跳过错误的操作

use test;
drop table test;
mysql  -u root -p aaa < /opt/bak_sql/aaa-2020-12-20.sql
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002
mysqlbinlog --no-defaults --stop-position='559' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-position='655' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p

3. 基于时间点恢复
   跳过某个发生错误的时间点实现数据恢复

use test;
drop table test;
mysql  -u root -p aaa < /opt/bak_sql/aaa-2020-12-20.sql
mysqlbinlog --no-defaults --stop-datetime='2020-12-20 17：20：47' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p
mysqlbinlog --no-defaults --start-datetime='2020-12-20 17：20：58 ' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p

4. 第三方工具备份
   免费的MySQL热备份软件Percona XtraBackup